MySQL单/多数据源主从同步

概述

MySQL的主从同步(Replication)是MySQL自带的一个非常有用且强大功能,可以实现数据实时备份、读写分离等功能. 有时候,我们需要将多个主库(Master)或者源数据库的数据同时同步到一个MySQL里,这种场景可能大家不是熟悉,这里整理一下整个配置过程.

数据库信息

MySQL版本信息: Distrib 8.0.26, for Linux (x86_64)

如果你的MySQL 位5.7版本, 下面的所有操作也是可以兼容的.

我测试的数据库配置信息如下:

DB Name	Host	Port
master1	192.168.10.1	3306
master2	192.168.10.2	3306
slave	192.168.10.3	3306

Master库配置

设置server id

可以通过SQL:

SET GLOBAL server_id = 2;

或者在/etc/my.cnf配置文件的[mysqld]下添加:

server-id=1

打开Binlog

Binlog是MySQL一个非常强大的机制,这个机制可以实现诸多功能,可以成为其他系统(比如FlinkCDC/Canal/Debezium)和MySQL的一种联系方式,另外MySQL自身的Replication也是机遇这个机制实现的.言归正传,打开Binlog,只需要在/etc/my.cnf配置文件的[mysqld]下添加:

log-bin=mysql-bin

MySQL基本上所有的命令都可以用SQL实现,当然也可以配置到配置文件中(如/etc/my.cnf),但是需要注意SQL命令的实效性,有的命令是当前Session有效的,所以很多关键的配置可以放置到配置文件中.另外,修改配置文件后,需要重启才会生效.

另外,可以通过binlog-do-db和binlog-ignore-db配置你需要输出binlog的数据库.

添加用户

所有的Master和Slave连接都是通过用户和密码连接的,所以需要为主从同步添加用户,并为这个用户设置权限.具体的SQL如下:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

准备初始化数据

如果你的Master数据库上已经存在一些数据,比如表等,则需要将当前数据库的信息导出,然后在Slave中执行,使Master和Slave有一样的初始化状态.
在导出数据前,我们需要禁止数据库更新,即只允许读:

FLUSH TABLES WITH READ LOCK;

接着查看Master信息:

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	1111	test

其中的File和Position需要记录下来,后面需要配置在Slave中.

然后,通过mysqldump命令来导出数据,如果你想导出全部数据库,则使用下面的命令:

mysqldump --all-databases --master-data > dbdump.db

从master上导出单个或部分库:

mysqldump --databases test -uroot -p --master-data >dbdump.db

其中--master-data选项可以忽略,如果配置多个数据库用空格分开,当然也可以使用 --ignore-table来忽略你不需要初始化的表.

数据导出后,这可以释放我们设置的锁,允许数据库更新:

UNLOCK TABLES;

Slave设置

设置 server id

参考上面Master设置server id,需要保证所有的数据库的server id都是唯一的.

初始化数据库

将我们上面导出的dump文件dbdump.db在指定的数据库里面执行,或者用mysql命令执行:

mysql -uroot -p < dbdump.db

设置Master数据源

这一步是最关键的一步,主要设计到多数据源,多数据源可能会有多个数据源的数据库重名问题,或者需要重命名master的数据库名称.

我们使用SQL在Slave上添加两个数据源:

CHANGE MASTER TO
MASTER_HOST='192.168.10.1',
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1111
FOR CHANNEL "source_1";

CHANGE MASTER TO
MASTER_HOST='192.168.10.2',
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_LOG_FILE='mysql-bin.000022',
MASTER_LOG_POS=2222
FOR CHANNEL "source_2";

对于多数据源的核心是添加CHANNEL,这样可以标识不同的Master,并且后面通过CHANNEL来设置不同的FILTER.
接下来我们可以设置数据库名映射:

CHANGE REPLICATION FILTER 
REPLICATE_REWRITE_DB = ((test, sync_1))
FOR CHANNEL "source_1";

CHANGE REPLICATION FILTER 
REPLICATE_REWRITE_DB = ((test, sync_2))
FOR CHANNEL "source_2";

查看Slave信息和异常信息:

SHOW REPLICA STATUS;

启动同步:

START SLAVE;

停止并删除REPLICA:

STOP SLAVE FOR CHANNEL 'source_1';
RESET SLAVE CHANNEL 'source_1';

参考

上面所有的步骤都是通过官方文档总结出来的,如果遇到什么问题,可以直接在官网找到答案.

[1]: 在Slave中设置Master相关信息: https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html
[2]: 官方多数据源配置文档: https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-configuration.html
[3]: 官方多数据源配置: https://dev.mysql.com/doc/refman/8.0/en/replication.html