多源复制就是将多个主数据库的数据同步到一个从数据库的过程，每个数据源都会拥有一个复制通道，从而实现AB-->C

实验环境

主机名/IP	角色	系统
server01/192.168.1.101	mysql-master1	ubuntu22.04
server02/192.168.1.102	mysql-master2	ubuntu22.04
server03/192.168.1.103	mysql-slave	ubuntu22.04

mysql-master1配置

锁表，禁止备份途中进行写操作

mysql -u root -p
flush tables with read lock;
exit;

备份数据库（一般只备份业务数据库）

mysqldump -u root -p --databases world > /tmp/101.sql

解锁

mysql -u root -p
unlock tables;

创建同步用户，赋予复制权限

create user 'slave'@'192.168.1.103' identified by 'wakamizu';
grant replication slave on *.* to 'slave'@'192.168.1.103';
flush privileges;
exit;

开启二进制日志，开放监听接口

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log_bin=mysql-bin
server-id=1
#设置监听地址
bind-address=0.0.0.0
#防止主键重复
binlog_format=mixed
#强制binlog与磁盘同步
sync_binlog=1
#开启gtid，提供更可靠的数据一致性，防止数据回环问题
enforce-gtid-consistency=on
gtid-mode=on

重启服务器

systemctl restart mysql

查看master状态

mysql -u root -p 
show master status \G;

mysql-master2配置

锁表，禁止备份途中进行写操作

mysql -u root -p
flush tables with read lock;
exit;

备份数据库（一般只备份业务数据库）

mysqldump -u root -p --databases sakila > /tmp/102.sql

解锁

mysql -u root -p
unlock tables;

创建同步用户，赋予复制权限

create user 'slave'@'192.168.1.103' identified by 'wakamizu';
grant replication slave on *.* to 'slave'@'192.168.1.103';
flush privileges;
exit;

开启二进制日志，开放监听接口

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log_bin=mysql-bin
server-id=2
#设置监听地址
bind-address=0.0.0.0
#防止主键重复
binlog_format=mixed
#强制binlog与磁盘同步
sync_binlog=1
#开启gtid，提供更可靠的数据一致性，防止数据回环问题
enforce-gtid-consistency=on
gtid-mode=on

重启服务器

systemctl restart mysql

查看master状态

mysql -u root -p 
show master status \G;

mysql-slave配置

将lmaster的备份数据复制过来，然后导入数据库

mysql -u root -p < /tmp/101.sql
mysql -u root -p < /tmp/102.sql

开启二进制日志

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id=3
relay_log=mysql-relay-bin
bind-address=0.0.0.0
replicate-do-db=sakila
replicate-do-db=world
enforce-gtid-consistency=on
gtid-mode=on

重启服务器

systemctl restart mysql

配置主从复制

mysql -u root -p 
change master to \
master_host='192.168.1.101',  \
master_user='slave',  \
master_password='wakamizu',  \
master_auto_position = 1,  \
get_master_public_key=1 \
for channel 'M_101';
change master to \
master_host='192.168.1.102',  \
master_user='slave',  \
master_password='wakamizu',  \
master_auto_position = 1,  \
get_master_public_key=1 \
for channel 'M_102';

注意：如果slave用户的身份验证插件是caching_sha2_password，则需要指GET_MASTER_PUBLIC_KEY=1，以获取主服务器的RSA公钥进行身份验证增强连接的安全性，可用语句：select User,plugin from mysql.user where User="slave" 查看

开启复制

start replica for channel 'M_101';
start replica for channel 'M_102';

查看复制状态

show replica status for channel 'M_101' \G;
show replica status for channel 'M_102' \G;

停止复制

stop replica for channel 'M_101' \G;
stop replica for channel 'M_102' \G;

重置

reset replica;