MySQL5.7 主主复制配置

先安装、配置好两台 MySQL 服务器

• Server1 IP：192.168.22.17

• server2 IP：192.168.22.18

1. 修改 my.cnf 配置文件

my.cnf 配置文件关于复制部分的配置

###replication settings
server_id=2217                                    # 唯一
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog-ignore-db=mysql
log_bin=/usr/local/mysql/logs/binlog/mysql_bin

auto_increment_offset=1                           # 奇数数主键，另外一台设置为2
auto_increment_increment=2                        # 步长
binlog_cache_size=2M
binlog_format=row
expire_logs_days=7
slave_skip_errors=ddl_exist_errors
relay_log=relay2217.log                           # 注意区分一下
relay_log_recovery=1
sync_binlog=1
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates
binlog_gtid_simple_recovery=true

修改好 my.cnf 配置文件重启 mysql

2. 创建同步账号

Server1：

mysql> grant replication slave on *.* to 'repl18'@'192.168.22.18' identified by '123456'; 

Server2：

mysql> grant replication slave on *.* to 'repl17'@'192.168.22.17' identified by '123456'; 

3. 互告 bin-log 信息

Server1：

mysql> show master status;

记录下 File 名及 Position 号：mysql_bin.000005, 1084

Server2：

mysql> show master status;

记录下 File 名及 Position 号：mysql_bin.000005, 927

4. 配置同步

Server1：

mysql> change master to master_host='192.168.22.18',master_user='repl17'，master_port=3306,master_password='123456',master_log_file='mysql_bin.000005',master_log_pos=927;

Server2：

mysql> change master to master_host='192.168.22.17',master_user='repl18',master_port=3306,,master_password='123456',master_log_file='mysql_bin.000005',master_log_pos=1084;

5. 启动服务

在两台服务器上面都执行：

mysql> start slave;

6. 查看服务状态

在两台服务器上面都执行：

Server1：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.22.18
                  Master_User: repl17
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 927
               Relay_Log_File: relay2217.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ... ...

Server2：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.22.17
                  Master_User: repl18
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 1084
               Relay_Log_File: relay2218.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ... ...