MySQL多源主从复制

一、多源主从复制原理

概念

多源主从复制是MySQL数据库中的一种高级复制技术，它允许一个从库（Slave）同时从多个主库（Master）同步数据

MySQL5.7中添加了channel（通道）来判别不同的数据源，这样slave可以非常简单的进行多数据源的配置与区分

原理图

二、环境准备

使用MySQL版本5.7.44

操作系统	主机地址	主机名
CentOS 7.4	192.168.252.140	mysql-master1
CentOS 7.4	192.168.252.142	mysql-master2
CentOS 7.4	192.168.252.143	mysql-slave1
CentOS 7.4	192.168.252.141	mysql-slave2

配置文件解析

vim /etc/hosts
192.168.252.140 mysql-master1
192.168.252.142 mysql-master2
192.168.252.143 mysql-slave1
192.168.252.141 mysql-slave2

数据库配置文件修改

双主文件配置

mysql-master1：
vim /etc/my.cnf

[mysqld]
...
server-id=1
log-bin=/var/log/sql-bin/mylog
binlog_format=statement
master-info-repository=TABLE
relay-log-info-repository=TABLE
...


mysql-master2：
vim /etc/my.cnf

[mysqld]
...
server-id=2
log-bin=/var/log/sql-bin/mylog
binlog_format=statement
master-info-repository=TABLE
relay-log-info-repository=TABLE
...

双从文件配置

mysql-slave1：
vim /etc/my.cnf

[mysqld]
...
server-id=3
log-bin=/var/log/sql-bin/mylog
binlog_format=statement
master-info-repository=TABLE
relay-log-info-repository=TABLE
...


mysql-slave2：
vim /etc/my.cnf

[mysqld]
...
server-id=4
log-bin=/var/log/sql-bin/mylog
binlog_format=statement
master-info-repository=TABLE
relay-log-info-repository=TABLE
...

问题

master-info-repository=TABLE
relay-log-info-repository=TABLE
从库必须开启这两个选项将配置记录在表中并通过信道传输进行多源主从复制，否则会报错
报错信息：
ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

三、进行双主双从配置

双主配置

mysql-master1:

1.创建主节点用户并查找binglog日志的文件名和位置信息
mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by '@Syh2025659';
mysql-master2:
mysql> show  master status;


2.添加节点
mysql> change master to 
mysql> master_host='192.168.252.142', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000008', 
mysql> master_host=836 for channel 'mysql-master2';

3.刷新权限
mysql> flush privileges;

4.启动slave
start  slave;
show slave status\G;




mysql-master2:

1.创建主节点用户并查找binglog日志的文件名和位置信息
mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by '@Syh2025659';
mysql-master1:
mysql> show  master status;


2.添加节点mysql-master2
mysql> change master to 
mysql> master_host='192.168.252.140', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000002', 
mysql> master_host=154 for channel 'mysql-master1';

3.刷新权限
mysql> flush privileges;

4.启动slave
start  slave;
show slave status\G;

双从配置

mysql-slave1:

1.添加节点
mysql> change master to 
mysql> master_host='192.168.252.142', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000008', 
mysql> master_host=836 for channel 'mysql-master2';


mysql> change master to 
mysql> master_host='192.168.252.140', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000002', 
mysql> master_host=154 for channel 'mysql-master1';

2.刷新权限
mysql> flush privileges;

3.开启
mysql> start slave for channel 'mysql-master1';
mysql> start slave for channel 'mysql-master2';
mysql> show slave status\G;

mysql-slave2:

1.添加节点
mysql> change master to 
mysql> master_host='192.168.252.142', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000008', 
mysql> master_host=836 for channel 'mysql-master2';


mysql> change master to 
mysql> master_host='192.168.252.140', 
mysql> master_user='repl',
mysql> master_password='@Syh2025659',
mysql> master_log_file='mylog.000002', 
mysql> master_host=154 for channel 'mysql-master1';

2.刷新权限
mysql> flush privileges;

3.开启
mysql> start slave for channel 'mysql-master1';
mysql> start slave for channel 'mysql-master2';
mysql> show slave status\G;

四、验证测试

集群

1.mysql-master1节点上：
mysql> create database testbase1;
2.mysql-master2节点上：
mysql> create table testbase1.table1(id int,age int);
mysql> insert into  testbase1.table1 values(1,2),(2,3),(4,4);
3.查看各个节点能查询到数据

高可用

1.停掉一个节点mysql-master1
systemctl stop firewalld
2.在mysql-master2创建数据
mysql> create table  testbase1.table2(name char(3),home varchar(20));
3.查看从节点是否有表

修复

1.恢复从节点mysql-master1
systemctl start mysqld
最新版可以自动恢复