MySQL单/多数据源主从同步

概述

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

环境

MySQL版本信息：8.0.15

数据库配置如下

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

多数据源主从同步

Master配置（主）

1. 配置server-id（配置完成记得重启mysql）

#linux路径：/etc/my.cnf （配置文件方式）
server-id=1 #主机id，不可重复
#或者（sql执行方式）
SET GLOBAL server_id = 2;

2. 开启BInlog

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

log-bin=mysql-bin

3. 查看主库binlog日志坐标

mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000012 | 1111|              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

4. 导出主库数据库及导入从库

#其中--master-data选项可以忽略,如果配置多个数据库用空格分开,当然也可以使用 --ignore-table来忽略你不需要初始化的表
#导出全部数据库
mysqldump --all-databases --master-data > dbdump.db
#导出单个或部分库
mysqldump --databases test -uroot -p --master-data >dbdump.db
#导出数据库中某一张表
mysqldump -h localhost  -u root -p 数据库 表名  > 表名.sql

#导入全部数据
mysql -uroot -p < dbdump.db
#导入单个表，存放路径下名称
mysql> source /home/user/表名.sql

Slave设置（从）

1. 配置从库与主库的关系

这一步是最关键的一步,主要设计到多数据源,多数据源可能会有多个数据源的数据库重名问题,或者需要重命名master的数据库名称.
我们使用SQL在Slave上添加两个数据源:

# MASTER_HOST主库ip  MASTER_USER主库账号  MASTER_PASSWORD主库密码  MASTER_LOG_FILE主库Binlog   MASTER_LOG_POS主库Position 
# 注意:此处一定要与主库的 show master status;查出的信息一样

#第一个数据源
mysql>  CHANGE MASTER TO
		MASTER_HOST='192.168.10.1',
		MASTER_USER='repl',
		MASTER_PASSWORD='******',
		MASTER_LOG_FILE='mysql-bin.000012',
		MASTER_LOG_POS=1111
		FOR CHANNEL "source_1";
		
#第二个数据源
mysql>  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.接下来我们可以设置数据库名映射:
#第一个数据库映射
mysql>  CHANGE REPLICATION FILTER 
		REPLICATE_REWRITE_DB = ((test, sync_1))
		FOR CHANNEL "source_1";
#第二个数据库映射
mysql>  CHANGE REPLICATION FILTER 
		REPLICATE_REWRITE_DB = ((test, sync_2))
		FOR CHANNEL "source_2";

2. 启动同步

#启动同步:
mysql>  START SLAVE;

3. 查看同步状态信息

# 查看slave_io_running、slave_sql_running都是YES 代表成功
mysql>  show slave status \G;

4. 删除并停止REPLICA:

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

单数据源主从同步

Master配置（主）

与多数据源配置Master配置一致

Slave设置（从）

在/etc/my.cnf中添加如下配置，配置完同样需重启MySQL服务。

# 设置server_id，同一局域网中需要唯一
server_id=1002
# relay_log配置中继日志
relay_log=mysql-relay-bin
# slave设置为只读（具有super权限的用户除外）
read_only=1

1. 配置从库与主库的关系

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

# MASTER_HOST主库ip  MASTER_USER主库账号  MASTER_PASSWORD主库密码  MASTER_LOG_FILE主库Binlog   MASTER_LOG_POS主库Position 
# 注意:此处一定要与主库的 show master status;查出的信息一样

#关联主库
mysql>  change master to master_host='xxx.xx.x.xxx',master_user='root',master_password='rd176o@UYO',master_log_file='mysql-bin.000039', master_log_pos=2345;

2. 启动同步

#启动同步:
mysql>  START SLAVE;

3. 查看同步状态信息

# 查看slave_io_running、slave_sql_running都是YES 代表成功
mysql>  show slave status \G;

4. 删除并停止REPLICA:

stop slave;

问题处理：

mysql从库同步数据失败，slave功能无法启用

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决:
1、获取上一次master-bin-log日志同步位置

mysql> SELECT * FROM `mysql`.`slave_master_info`\G;
*************************** 1. row ***************************
              Number_of_lines: 31
              Master_log_name: mysql-bin.000240
               Master_log_pos: 156
                         Host: 172.18.0.4
                    User_name: slave1
                User_password: slave1123456
                         Port: 3306
                Connect_retry: 60
                  Enabled_ssl: 0
                       Ssl_ca: 
                   Ssl_capath: 
                     Ssl_cert: 
                   Ssl_cipher: 
                      Ssl_key: 
       Ssl_verify_server_cert: 0
                    Heartbeat: 30
                         Bind: 
           Ignored_server_ids: 0
                         Uuid: 7f06a326-b845-11ea-85b9-0242ac110002
                  Retry_count: 86400
                      Ssl_crl: 
                  Ssl_crlpath: 
        Enabled_auto_position: 0
                 Channel_name: 
                  Tls_version: 
              Public_key_path: 
               Get_public_key: 0
            Network_namespace: 
 Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
             Tls_ciphersuites: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

2、重置slave并设置master信息

#停止同步
stop slave;
#1.清除 master.info,relay-log.info文件(记录)，
#2.删除所有的relay log，包括还没有应用完的日志，创建一个新的relay log文件
reset slave;
#关联主库
change master to master_host="172.18.0.4",master_user="slave1",master_password="slave1123456",master_log_file="mysql-bin.000240",master_log_pos=156;
#启动同步
start slave;

常用命令；

#连接mysql
mysql -uroot -h 127.0.0.1 -p
mysql -h127.0.0.1  -uroot -p

#显示那些线程正在运行
mysql> show processlist;
#查看数据库
mysql> show databases;
#选择数据库
mysql> use 库名;
#查看表
mysql> show tables;
#查看表结构
mysql> DESC 表名;
#查看数据库版本
mysql> select version();
#查看建表语句
mysql> show create table 表名;

#启动mysql 
service mysqld start  #------------ 8.0版本是mysqld
service mysql start #---------------5.5.7版本是mysql

#重启 mysql Service
service mysqld restart #-------------------------->8.0版本是mysqld
service mysql restart #-------------------------->5.5.7版本命令  

#mysqld 脚本启动：
/etc/init.d/mysqld start

#查看mysql的最大连接数
show variables like '%max_connections%';

#查看慢日志是否开启
SHOW VARIABLES LIKE 'slow_query%'

#导出数据库
mysqldump -h localhost  -u root -p 数据库   > xxx.sql
#导出数据库单表
mysqldump -h localhost  -u root -p 数据库  表名> xxx.sql

#导入数据库
mysql> source /home/user/xxx.sql