MySql数据库主主同步配置步骤

时间:2021-07-27 16:17:24
测试环境:
 192.168.1.192
 192.168.1.193
 
 mysql5.2
 
 
配置192服务器:
 /usr/local/mysql/bin/mysql -uroot -p
授权:
 grant replication slave,file on *.* to 'xm1'@'192.168.1.193' identified by '100200';
刷新:
 flush privileges;
 
配置193服务器:
 /usr/local/mysql/bin/mysql -uroot -p
授权:
 grant replication slave,file on *.* to 'xm2'@'192.168.1.192' identified by '100200';
刷新:
 flush privileges;
 
 
停掉两台服务器的mysql服务:
  /etc/init.d/mysqld stop
 
接下来配置两台服务器的my.cnf文件:(开启二进制日志)
 
vi /etc/my.cnf
 
192服务器:
user = mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
skip-name-resolve
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
 
193服务器:
user = mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
skip-name-resolve
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
 
 
同时启动mysql服务:
 /etc/init.d/mysqld start
 
在192和193执行相同的操作:
 /usr/local/mysql/bin/mysqld_safe &
 
之后进入mysql
 /usr/local/mysql/bin/mysql -uroot -p
 
192服务器:
 flush tables with read lock\G
 
 show master status\G
 
193服务器:
 flush tables with read lock;
 show master status\G
 
备份数据。
 
表解锁:
 UNLOCK TABLES;
停掉slave
 slave stop;
 
在192.193两台服务器执行CHANGE MASTER TO命令。
192:
change master to master_host='192.168.1.193', master_user='xm2', master_password='100200',master_log_file='mysql-bin.000070',master_log_pos=106;
193:
change master to master_host='192.168.1.192', master_user='xm1', master_password='100200',master_log_file='mysql-bin.000067',master_log_pos=106;
 
192:
show processlist\G
193:
show processlist\G
 
192:
showdatabases;
use test;
show tables;
 
193:
showdatabases;
use test;
show tables;
 
之后就是测试的过程
 
分别向两个数据库创建
192:
create table table1;
切换到193:
show tables;
193:
create table table2;
切换到192:
show tables;
 
能彼此看见数据库,证明主主配置好了!