1、主机说明:
master-A:192.168.0.161
master-B:192.168.0.162
2、 授权用户
#server mysqld start
master-A>grant replication slave,file on *.* to 'tongbu'@'192.168.0.162' identified by '123456';
master-B>grant replication slave,file on *.* to 'tongbu'@'192.168.0.161' identified by '123456';
3、修改my.cnf
编辑配置文件vi /etc/my.cnf 加入以下内容
log-bin=mysql-bin
server-id=1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-ignore-db=mysql
replicate-do-db=test
log-slave-updates
slave-skip-errors=all
sync-binlog=1
auto-increment-increment=2
auto-increment-offset=1
备注:另一台 server-id=2;auto-increment-offset=2
binlog-do-db=test修改需要同步的数据库名
4、查看同步的起始文件
master-A>show master status; //获得的参数写入B中
master-B>show master status; //获得的参数写入A中
记录初始的master_log_file的文件名和position
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 11809679 | mysql | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
我们把file=mysql-bin.000019和position=11809679 记下来
5、用change master语句指定同步位置
master-A>change master to master_host='192.168.7.162',master_user='tongbu', master_password='123456',master_log_file='mysqlbin.000019',master_log_pos=11809679;
master-B>change master to master_host='192.168.7.161',master_user='tongbu', master_password='123456',master_log_file='mysqlbin.000019',master_log_pos=11809679;
启动同步 注意红色部分从show master status表中获得file=mysql-bin.000019和position=11809679
master-A> start slave;
master-A> show slave status\G;
master-B> start slave;
master-B> show slave status\G;
如果之前您的同步已经开启change之前要停止同步stop slave;
根据上述命令显示结果,检查slave状态
结果如下时,同步正常启动
Slave_IO_Running:YES
Slave_SQL_Running:YES
6、 检测同步是否正常完成
master-A>create tables aaa (id int);
master-B>use test;
master-B>show tables;
master-B服务器中出现aaa表时,同步完成。