1、基础环境
10.0.0.100 master
10.0.0.101 master
10.0.0.102 slave
image.png
2、从库准备
多源复制于之前常规的主从复制还是有一定的配置方面的区别的
- 配置文件，在原有从库的基础上，需要单独加上下面的配置
master_info_repository =table
relay_log_info_repository =table
#不加这两个参数，下面我们在加入主库时会报错
3、从库备份恢复
mysqldump -uroot -p123 -h10.0.0.100 --master-data=2 --single-transaction -A >/tmp/full.sql
mysqldump -uroot -p123 -h10.0.0.101 --master-data=2 --single-transaction >/tmp/full1.sql
mysql> source /tmp/full.sql;
mysql> source /tmp/full1.sql;
此时我们要复制的两个主库的数据，那么需要我们备份恢复两份数据，100一份，101一份
4、加入主库并开启出从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.100',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.101',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave for CHANNEL 'Master_1';
Query OK, 0 rows affected (0.01 sec)
mysql> start slave for CHANNEL 'Master_2';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 314
Relay_Log_File: db03-relay-bin-master_1.000003
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000018
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: 314
Relay_Log_Space: 691801
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: 100
Master_UUID: 0706ef47-886c-11eb-a9f0-000c29aec9e2
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: 0706ef47-886c-11eb-a9f0-000c29aec9e2:1-13
Executed_Gtid_Set: 0706ef47-886c-11eb-a9f0-000c29aec9e2:1-13,
1518e04e-8638-11eb-a908-000c29aec9e2:1-24,
7e75646b-8638-11eb-83ab-000c29516958:1-3,
8c88da03-86be-11eb-ac3c-000c29aec9e2:1-10,
93f12623-86ce-11eb-9689-000c29aec9e2:1-67,
979a98c9-86ce-11eb-96b5-000c29eb57ff:1-55
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master_1
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 21120919
Relay_Log_File: db03-relay-bin-master_2.000002
Relay_Log_Pos: 21103502
Relay_Master_Log_File: mysql-bin.000012
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: 21120919
Relay_Log_Space: 21103717
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: 101
Master_UUID: 979a98c9-86ce-11eb-96b5-000c29eb57ff
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: 979a98c9-86ce-11eb-96b5-000c29eb57ff:1-55
Executed_Gtid_Set: 0706ef47-886c-11eb-a9f0-000c29aec9e2:1-13,
1518e04e-8638-11eb-a908-000c29aec9e2:1-24,
7e75646b-8638-11eb-83ab-000c29516958:1-3,
8c88da03-86be-11eb-ac3c-000c29aec9e2:1-10,
93f12623-86ce-11eb-9689-000c29aec9e2:1-67,
979a98c9-86ce-11eb-96b5-000c29eb57ff:1-55
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master_2
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> \q
如需在多源复制的基础上配置过滤复制，可参考下面配置
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR
CHANNEL "master_1";
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR
CHANNEL "master_2";
