主主同步就是在本身的主从同步基础上再去从数据库上做一个主从同步，也就是互为主从的关系。
但是有个问题，主主同步是两台服务器都有写权限，这就涉及到自增长重复问题。比如有自增长属性的ID字段的test表，停掉A机器，在B上对test表执行插入操作，返回插入ID为1; 再停掉slave，在A上执行插入操作，返回的插入ID也是1 ; 然后同时启动两台机器，就会出现主键ID重复！
为了避免这个问题，我们在两台主机上设备不同的自增长，A插入奇数ID，B插入偶数ID。
master：192.168.1.1
slave： 192.168.1.2
一、在master上的配置：
1.修改my.cnf配置
# 添加如下配置，不是覆盖 vim /etc/my.cnf server-id = 1 log-bin = mysql-bin binlog-ignore-db = mysql,information_schema sync_binlog = 1 binlog_checksum = none binlog_format = mixed auto-increment-increment = 2 # 自增长为2 auto-increment-offset = 1 # 从1开始，也就是全是奇数ID slave-skip-errors = all
2.重启mysql，授权同步用户
mysql> grant replication slave,replication client on *.* to repl@'192.168.1.2' identified by "123qqq...A";
mysql> flush privileges;
3.查看状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 609 | | | | +------------------+----------+--------------+------------------+-------------------+
二、在slave上的配置：
1.修改my.cnf配置
vim /etc/my.cnf server-id = 2 log-bin = mysql-bin binlog-ignore-db = mysql,information_schema sync_binlog = 1 binlog_checksum = none binlog_format = mixed auto-increment-increment = 2 # 自增长为2，也就是递增2 auto-increment-offset = 2 # 从2开始，也就是插入偶数ID slave-skip-errors = all
2.重启mysql，授权同步用户
mysql> grant replication slave ,replication client on *.* to repl@'192.168.1.1' identified by "123qqq...A"; mysql> flush privileges;
3.查看状态
show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 609 | | | | +------------------+----------+--------------+------------------+-------------------+
三、在slave上做同步master的设置：
mysql> change master to
master_host='192.168.1.1',
master_user='repl',
master_password='123qqq...A',
master_log_file='mysql-bin.000001',
master_log_pos=609;
mysql> start slave; mysql> show slave status G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 158 nelay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 750 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes # 两个都显示YES，表示同步成功 Slave_SQL_Running: Yes ..................
四、在master上做同步slave的设置：
mysql> change master to
master_host='192.168.1.2',
master_user='repl',
master_password='123qqq...A',
master_log_file='mysql-bin.000001',
master_log_pos=609; mysql> start slave; mysql> show slave status G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 256 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 750 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ..................