（一）多主复制概述
所谓多主复制，是将多个主库的数据复制到一个从库中。通常用于数据仓库整合数据，比如OLTP系统为了分散业务压力，对业务进行分库分表，当要对数据进行分析的时候，可以使用多主复制将数据整合到同一个数据库实例上，便于统一分析。MySQL从5.7版本开始支持多主复制。
本文通过搭建多主复制环境来了解MySQL多主复制的特点。
（二）基础环境
| 主库1 | 主库2 | 从库 | |
| 服务器IP地址 | 192.168.10.11 | 192.168.10.12 | 192.168.10.13 |
| MySQL版本 | 5.7.24 | 5.7.24 | 5.7.24 |
| 待同步的数据库 | db1 | db2 | 从库上创建空库db1和db2 |
（三）多主复制搭建
(3、1)创建用于复制的用户(主库执行)
在2个主数据库上创建用于复制的用户rep，用户需具有“replication slave”权限。每个数据库上复制账号可以不相同，这里为了测试方便，创建为相同的账号。
mysql> grant replication slave on *.* to 'rep'@'%' identified by '123';
(3、2)参数修改（从库执行）
mysql> SET GLOBAL master_info_repository = 'TABLE';
mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
(3、3)主库创建测试数据，备库创建空数据库
| 主库1：
“`
l</span><span style="color: #808080;">></span> <span style="color: #0000ff;">use</span><span style="color: #000000;"> db1 l</span><span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> test01 “` |
主库2：
“`
l</span><span style="color: #808080;">></span> <span style="color: #0000ff;">use</span><span style="color: #000000;"> db2 l</span><span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> test02 “`
|
|
备库：
“`
“` |
|
(3、4)将主库数据手动同步到备库（从库执行）
在备库上执行远程导出操作：
[root@slavedb ~]# mysqldump -uroot -p123456 -h192.168.10.11 --master-data=2 --set-gtid-purged=OFF -P3306 db1 --single-transaction > db1.sql
[root@slavedb ~]# mysqldump -uroot -p123456 -h192.168.10.12 --master-data=2 --set-gtid-purged=OFF -P3306 db2 --single-transaction > db2.sql
在备库上执行导入操作：
[root@slavedb ~]# mysql -h192.168.10.13 -P3306 -uroot -p123456 db1 < /root/db1.sql
[root@slavedb ~]# mysql -h192.168.10.13 -P3306 -uroot -p123456 db2 < /root/db2.sql
(3、5)开启从库同步db1（从库执行）
STEP1:确认主库1导出到的日志位置
cat db1.sql |grep "CHANGE MASTER"|less
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000029', MASTER_LOG_POS=1835;
STEP2：将主库1添加到复制环境
mysql> change master to
-> master_host='192.168.10.11',
-> master_port=3306,
-> master_user='rep',
-> master_password='123',
-> master_log_file='master-bin.000029',
-> master_log_pos=1835
for channel ‘ch1’
;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
STEP3:开启主库1的复制
mysql> start slave
for channel ‘ch1’
;
Query OK, 0 rows affected (0.00 sec)
(3、6)开启从库同步db2（从库执行）
STEP1:确认主库2导出到的日志位置
cat db2.sql |grep "CHANGE MASTER"|less
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000029', MASTER_LOG_POS=1419;
STEP2：将主库2添加到复制环境
change master to
master_host='192.168.10.12',
master_port=3306,
master_user='rep',
master_password='123',
master_log_file='master-bin.000029',
master_log_pos=1419 for channel 'ch2';
STEP3:开启主库2的复制
mysql> start slave for channel 'ch2';
(3、7)测试数据复制是否正常
| 主库1：
“`
</div> </td> td> <p>主库2：</p> <div style="padding: 5px; border: 1px solid #cccccc; border-image: none; background-color: #f5f5f5;"> “` |
数据复制正常。
(3、8)确认复制状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000029
Read_Master_Log_Pos: 1835
Relay_Log_File: slavedb-relay-bin-ch1.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1835
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: caa64a22-481a-11ea-b0f1-000c29fb6200
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8a885841-481c-11ea-bdc4-000c29840f0f:1-10,
caa64a22-481a-11ea-b0f1-000c29fb6200:1-466040
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: ch1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.12
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000029
Read_Master_Log_Pos: 1419
Relay_Log_File: slavedb-relay-bin-ch2.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1419
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: caa64a22-481a-11ea-b0f1-000c29fb6200
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8a885841-481c-11ea-bdc4-000c29840f0f:1-10,
caa64a22-481a-11ea-b0f1-000c29fb6200:1-466040
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: ch2
Master_TLS_Version:
2 rows in set (0.00 sec)
【完成】
附录：
| MySQL复制(replication)文档集合:
1.复制概述 2.基于二进制日志文件位置(binlog)配置复制 3.基于全局事物标识符(GTID)配置复制 4.多源复制 5.级联复制 6.半同步复制 7.延迟复制 8.复制过滤规则 9.对复制进行故障排除 10.故障切换 11.复制管理 |