介绍
MySQL多源复制，将多台Master端库以“主从复制”的方式同步到一台Slave端，可以有效的节省主机资源。
| 操作系统 | 数据库版本 | 角色 | IP | 主机名 |
| CentOS 7.6 | MySQL 8.0.29 | Master A | 192.168.1.71 | host71 |
| CentOS 7.6 | MySQL 8.0.29 | Master B | 192.168.1.72 | host72 |
| CentOS 7.6 | MySQL 8.0.29 | Master C | 192.168.1.73 | host73 |
部署步骤
1. 配置my.cnf（根据个人环境调整其他参数）
server_id = 71 #三台保持唯一性
port = 3380
log_bin = /data/mysql/log_bin
binlog_expire_logs_seconds = 259200
gtid_mode = on
log-slave-updates = on
enforce-gtid-consistency = on
2. host72端备份+创建复制用户repll
[root]# mysqldump host72 > host72.sql
mysql> create user 'rep'@'192.168.1.%' identified by 'Replication@80';
mysql> grant repication slave,replication client on host72.* to 'repl'@'192.168.1.%';
3. host73端备份+创建复制用户repl
mysqldump host73 > host73.sql
mysql> create user 'rep'@'192.168.1.%' identified by 'Replication@80';
mysql> grant repication slave,replication client on host72.* to 'repl'@'192.168.1.%';
4. host71端恢复数据
[root]# mysql -e "reset master;" && mysql < host72.sql
[root]# mysql -e "reset master;" && mysql < host73.sql
5. 配置主从
mysql> change master to MASTER_HOST='192.168.1.72',MASTER_USER='repl',MASTER_PASSWORD='Gaoyu@029',MASTER_PORT=3380,MASTER_AUTO_POSITION=1 for channel 'host72';
mysql> change master to MASTER_HOST='192.168.1.73',MASTER_USER='repl',MASTER_PASSWORD='Gaoyu@029',MASTER_PORT=3380,MASTER_AUTO_POSITION=1 for channel 'host73';
6. 配置复制过滤
配置复制过滤只从192.168.1.72上复制host72库
mysql> change replication filter REPLICATE_DO_DB=(host72) for channel 'host72';配置复制过滤只从192.168.1.73复制host73库
mysql> change replication filter REPLICATE_DO_DB=(host73) for channel 'host73';
7. 启动slave
mysql> start slave for channel 'host72';
mysql> start slave for channel 'host73';
8. 查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.72
Master_User: repl
Master_Port: 3380
Connect_Retry: 60
Master_Log_File: log_bin.000001
Read_Master_Log_Pos: 13921353
Relay_Log_File: host71-relay-bin-host72.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: log_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: host72
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: 13921353
Relay_Log_Space: 632
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: 280
Master_UUID: a6e44940-13a0-11ed-a04c-000c298c9f0b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: 372e5c4b-f558-11ec-8b0e-000c2965423e:1-6,
a6e44940-13a0-11ed-a04c-000c298c9f0a:1-333,
a6e44940-13a0-11ed-a04c-000c298c9f0b:1-29073,
b7d20853-f539-11ec-917b-000c29667c57:1-1373759,
ea671d5c-f552-11ec-be3b-000c298c9f0b:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: host72
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 2. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.73
Master_User: repl
Master_Port: 3380
Connect_Retry: 60
Master_Log_File: log_bin.000010
Read_Master_Log_Pos: 277
Relay_Log_File: host71-relay-bin-host73.000010
Relay_Log_Pos: 409
Relay_Master_Log_File: log_bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: host73
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: 277
Relay_Log_Space: 924
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: 73
Master_UUID: 372e5c4b-f558-11ec-8b0e-000c2965423e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: 372e5c4b-f558-11ec-8b0e-000c2965423e:4-6
Executed_Gtid_Set: 372e5c4b-f558-11ec-8b0e-000c2965423e:1-6,
a6e44940-13a0-11ed-a04c-000c298c9f0a:1-333,
a6e44940-13a0-11ed-a04c-000c298c9f0b:1-29073,
b7d20853-f539-11ec-917b-000c29667c57:1-1373759,
ea671d5c-f552-11ec-be3b-000c298c9f0b:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: host73
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
2 rows in set, 1 warning (0.01 sec)
至此，多源复制配置完毕。
