mysql主主配置

主主同步就是在本身的主从同步基础上再去从数据库上做一个主从同步，也就是互为主从的关系。

但是有个问题，主主同步是两台服务器都有写权限，这就涉及到自增长重复问题。比如有自增长属性的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
            ..................