（一）多主复制概述
所谓多主复制，是将多个主库的数据复制到一个从库中。通常用于数据仓库整合数据，比如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： mysql> create database db1; mysql> use db1 mysql> create table test01 -> ( -> id1 int not null auto_increment, -> name varchar(30), -> primary key(id1) -> ); mysql> insert into test01 values(1,'a'); |
主库2： mysql> create database db2; mysql> use db2 mysql> create table test02 -> ( -> id2 int not null auto_increment, -> name varchar(30), -> primary key(id2) -> ); mysql> insert into test02 values(2,'b'); |
|
备库： mysql> create database db1; mysql> create database db2; |
|
(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=1835for 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： mysql> insert into test01 values(11,'aa'); |
主库2： mysql> insert into test02 values(22,'bb'); |
|
备库： mysql> select * from db1.test01; +-----+------+ | id1 | name | +-----+------+ | 1 | a | | 11 | aa | +-----+------+ 2 rows in set (0.00 sec) mysql> select * from db2.test02; +-----+------+ | id2 | name | +-----+------+ | 2 | b | | 22 | bb | +-----+------+ 2 rows in set (0.00 sec |
|
数据复制正常。
(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.复制管理 |