MySQL一台Replicate从机同步备份多台Master主机

主机A上的数据库DBA1、DBA2，主机B上的数据库DBB1，实时同步到从机C上。

1. A服务器配置

# /etc/my.cnf
server-id=1
log-bin=binlog-a
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
binlog-do-db=DBA1
binlog-do-db=DBA2

# 创建用户
CREATE USER 'replicate'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%';
FLUSH PRIVILEGES;

2. B服务器配置

# /etc/my.cnf
server-id=2
log-bin=binlog-b
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
binlog-do-db=DBB1

# 创建用户
CREATE USER 'replicate'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%';
FLUSH PRIVILEGES;

3. C服务器配置

# /etc/my.cnf
server-id=3
log-bin=binlog-c
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
relay-log=relaylog-c
read-only=1

# 只复制需要的数据库
replicate-do-db=DBA1
replicate-do-db=DBA2
replicate-do-db=DBB1


配置多源复制
-- 清除旧设置（可选）
STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;

-- 配置A服务器复制通道
CHANGE MASTER TO
MASTER_HOST='IP-A',
MASTER_PORT='3306',
MASTER_USER='USER',
MASTER_PASSWORD='PASSWORD',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'channel_A';

-- 配置B服务器复制通道
CHANGE MASTER TO
MASTER_HOST='IP-B',
MASTER_PORT='3306',
MASTER_USER='USER',
MASTER_PASSWORD='PASSWORD',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'channel_B';

-- 启动两个复制通道
START SLAVE FOR CHANNEL 'channel_A';
START SLAVE FOR CHANNEL 'channel_B';


检查复制状态
-- 查看所有通道状态
SHOW SLAVE STATUS\G

-- 查看指定通道状态
SHOW SLAVE STATUS FOR CHANNEL 'channel_A'\G
SHOW SLAVE STATUS FOR CHANNEL 'channel_B'\G

数据导入导出

# 导出
mysqldump -uroot -p --single-transaction --source-data=2 --set-gtid-purged=ON --databases DBA1 DBA2 > db_dump.sql
# 导入
mysql -uroot -p < db_dump.sql