mysql双主复制

一开始就规划主主复制，配置就比较简单。但一个库已经有数据，则需要导出导入，之后步骤一样。

环境：192.168.7.221服务器上两个端口3306和3309

1、M1（3306）

vi /etc/my.cnf
server-id = 1
log-bin=mysql-bin
log-bin-index=binlogs.index
log-slave-updates
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db=lmis
replicate-ignore-db=mysql

重启后创建复制用户

grant replication slave on *.* to slave@'%' identified by 'mysql';

当前位置

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      328 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2、M2（3309）

vi /etc/my.cnf
server-id = 4
log-bin=mysql-bin
log-bin-index=binlogs.index
log-slave-updates
auto_increment_offset=2
auto_increment_increment=2
replicate-do-db=lmis
replicate-ignore-db=mysql

重启后创建复制用户

grant replication slave on *.* to slave@'%' identified by 'mysql';

根据M1位置配置M2 slave

change master to
master_host ='192.168.7.221',
master_port= 3306,
master_user= 'slave',
master_password= 'mysql',
master_log_file='mysql-bin.000013',
master_log_pos=120;

启动

start slave;

当前位置

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      598 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3、根据M2位置配置M1 slave

change master to
master_host ='192.168.7.221',
master_port= 3309,
master_user= 'slave',
master_password= 'mysql',
master_log_file='mysql-bin.000001',
master_log_pos=598;

启动

start slave;

4、最后均可使用show slave status\G查看同步状态

注意：自增列，因为会更新同一张表，自增一般为主键，所以需要设置自增的间隔和初始值