一、多源复制简介

  所谓多源复制，就是多台主库的数据同步到一台从库服务器上，从库创建通往每个主库的管道。在Mysql5.7之前的版本中，只支持一主一从，一主多从，多主多从的复制架构。从Mysql5.7版本开始支持多主一从的复制方式。搭建过程支持GTID复制模式和binlog+position方式复制。多源复制的好处：

  1. 可以集中备份，在从库上备份，不会影响线上的数据正常运行；
  2. 节约购买从库服务器的成本，只需要一个从库服务器即可；
  3. 数据汇总在一起，方便后期做数据统计；
  4. 减轻DBA维护工作量。
    本博文以GTID复制方式介绍多主一从的搭建方式。

二、多源复制搭建步骤

1、主从规划

序号

角色

server-id

主机IP

主机名

1

master

124

192.168.0.124

test1

2

master

125

192.168.0.125

test2

3

slave

126

192.168.0.126

test3

2、安装数据库

使用RPM方式安装mysql5.7数据库

rpm -ivh mysql-community-common-5.7.33-1.el7.x86_64.rpm
 rpm -ivh mysql-community-libs-5.7.33-1.el7.x86_64.rpm
 rpm -ivh mysql-community-client-5.7.33-1.el7.x86_64.rpm
 rpm -ivh mysql-community-server-5.7.33-1.el7.x86_64.rpm

3、配置GTID复制参数

test1添加GTID参数配置

#GTID配置
 server-id=124
 log_bin=mysql-bin
 log_slave_updates=1
 gtid_mode=on
 enforce_gtid_consistency=on
 binlog_format=row

test2添加GTID参数配置

#GTID配置
 server-id=125
 log_bin=mysql-bin
 log_slave_updates=1
 gtid_mode=on
 enforce_gtid_consistency=on
 binlog_format=row

test3添加GTID参数配置

#GTID配置
 server-id=126
 log_bin=mysql-bin
 log_slave_updates=1
 gtid_mode=on
 enforce_gtid_consistency=on
 binlog_format=row
 #多源复制从库配置
 master_info_repository=table
 relay_log_info_repository=table

4、创建复制账号

mysql> create user bak124@’%’ identified by ‘Test!123’;
 Query OK, 0 rows affected (0.00 sec)

 mysql> grant replication slave on . to bak124@’%’;
 Query OK, 0 rows affected (0.00 sec)mysql> create user bak125@’%’ identified by ‘Test!123’;
 Query OK, 0 rows affected (0.00 sec)

 mysql> grant replication slave on . to bak125@’%’;
 Query OK, 0 rows affected (0.00 sec)

5、主库上备份导出需要同步的库

假设test1主库上需要同步备份的库未db124和test124，test2主库上需要备份的库为test125，首先在主库上创建数据库，然后使用mysqldump备份工具导出需要同步的库。

mysql> create database db124;
 Query OK, 1 row affected (0.01 sec)

 mysql> create database test124;
 Query OK, 1 row affected (0.00 sec)
 mysql> exit
 Bye
 [root@test1 opt]# mysqldump -uroot -p --master-data=2 --single-transaction --set-gtid-purged=OFF --databases --add-drop-database db124 test124 > s124.sql
 Enter password:mysql> create database test125;
 Query OK, 1 row affected (0.03 sec)

 mysql> exit
 Bye
 [root@test2 opt]# mysqldump -uroot -p --master-data=2 --single-transaction --set-gtid-purged=OFF --databases --add-drop-database test125 > s125.sql
 Enter password:

6、从库上导入主库备份的库

[root@test3 opt]# mysql -uroot -p < s124.sql
 Enter password:
 [root@test3 opt]# mysql -uroot -p < s125.sql
 Enter password:
 [root@test3 opt]#

7、配置主从复制

mysql> change master to
 -> MASTER_HOST=‘192.168.0.124’,
 -> MASTER_USER=‘bak124’,
 -> MASTER_PASSWORD=‘Test!123’,
 -> MASTER_PORT=3306,
 -> master_auto_position=1 for CHANNEL ‘s124’;
 Query OK, 0 rows affected, 2 warnings (0.03 sec)

 mysql> change master to
 -> MASTER_HOST=‘192.168.0.125’,
 -> MASTER_USER=‘bak125’,
 -> MASTER_PASSWORD=‘Test!123’,
 -> MASTER_PORT=3306,
 -> master_auto_position=1 for CHANNEL ‘s125’;
 Query OK, 0 rows affected, 2 warnings (0.02 sec)

8、开启主从复制

mysql> start slave for channel ‘s124’;
 Query OK, 0 rows affected (0.00 sec)
 mysql> start slave for channel ‘s125’;
 Query OK, 0 rows affected (0.01 sec)

9、验证数据是否同步

1)、在test1主库上新建表

mysql> use db124;
 Database changed
 mysql> CREATE TABLE IF NOT EXISTS `t1`(
 -> `runoob_id` INT UNSIGNED AUTO_INCREMENT,
 -> `runoob_company` VARCHAR(100) NOT NULL,
 -> `runoob_name` VARCHAR(40) NOT NULL,
 -> PRIMARY KEY ( `runoob_id` )
 -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 Query OK, 0 rows affected (0.04 sec)

 mysql> use test124;
 Database changed
 mysql> CREATE TABLE IF NOT EXISTS `t2`(
 -> `s124_id` INT UNSIGNED AUTO_INCREMENT,
 -> `s124_company` VARCHAR(100) NOT NULL,
 -> `s124_name` VARCHAR(40) NOT NULL,
 -> PRIMARY KEY ( `s124_id` )
 -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 Query OK, 0 rows affected (0.02 sec)

 mysql> insert into t2 (s124_id,s124_company,s124_name) values (‘1’,‘yhxx’,‘wuhs’);
 Query OK, 1 row affected (0.05 sec)

2）、在test3从库上查看

mysql> use db124;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed
 mysql> show tables;
 ±----------------+
 | Tables_in_db124 |
 ±----------------+
 | t1 |
 ±----------------+
 1 row in set (0.00 sec)
 mysql> select * from test124.t2;
 ±--------±-------------±----------+
 | s124_id | s124_company | s124_name |
 ±--------±-------------±----------+
 | 1 | yhxx | wuhs |
 ±--------±-------------±----------+
 1 row in set (0.00 sec)

3）、在test2主库上新建表

mysql> CREATE TABLE IF NOT EXISTS `t3`(
 -> `s125_id` INT UNSIGNED AUTO_INCREMENT,
 -> `s125_company` VARCHAR(100) NOT NULL,
 -> `s125_name` VARCHAR(40) NOT NULL,
 -> PRIMARY KEY ( `s125_id` )
 -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 Query OK, 0 rows affected (0.04 sec)
 mysql> insert into t3 (s125_id,s125_company,s125_name) values (‘1’,‘sac’,‘sunru’);
 Query OK, 1 row affected (0.01 sec)

 mysql> insert into t3 (s125_id,s125_company,s125_name) values (‘2’,‘yhxx’,‘wuhs’);
 Query OK, 1 row affected (0.01 sec)

4）、在test3从库上查看

mysql> select * from t3;
 ±--------±-------------±----------+
 | s125_id | s125_company | s125_name |
 ±--------±-------------±----------+
 | 1 | sac | sunru |
 | 2 | yhxx | wuhs |
 ±--------±-------------±----------+
 2 rows in set (0.00 sec)


----------------------------我是华丽的分割线----------------------------
至此Mysql5.7两主一从复制模式搭建完成

三、常用检查命令及注意事项

1、常用命令

  • show slave status for channel 'id’查看单个channel slave的状态
mysql> show slave status for channel ‘s124’\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.0.124
 Master_User: bak124
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 2085
 Relay_Log_File: test3-relay-bin-s124.000002
 Relay_Log_Pos: 2298
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 …
 Retrieved_Gtid_Set: b9468eb8-74db-11eb-8e41-000c290b9fa0:1-8
 Executed_Gtid_Set: b799ae0c-74db-11eb-8404-000c2964c7fe:1-8,
 b8253ff7-74db-11eb-9897-000c29043152:1-3,
 b9468eb8-74db-11eb-8e41-000c290b9fa0:1-8
 Auto_Position: 1
 Replicate_Rewrite_DB:
 Channel_Name: s124
 Master_TLS_Version:
 1 row in set (0.00 sec)
  • 通过查询replication_connection_status检查主从同步状态信息
mysql> select * from performance_schema.replication_connection_status\G
 *************************** 1. row ***************************
 CHANNEL_NAME: s124
 GROUP_NAME:
 SOURCE_UUID: b9468eb8-74db-11eb-8e41-000c290b9fa0
 THREAD_ID: 77
SERVICE_STATE: ON
 COUNT_RECEIVED_HEARTBEATS: 23
 LAST_HEARTBEAT_TIMESTAMP: 2021-02-22 22:09:02
 RECEIVED_TRANSACTION_SET: b9468eb8-74db-11eb-8e41-000c290b9fa0:1-8
 LAST_ERROR_NUMBER: 0
 LAST_ERROR_MESSAGE:
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
 *************************** 2. row ***************************
 CHANNEL_NAME: s125
 GROUP_NAME:
 SOURCE_UUID: b799ae0c-74db-11eb-8404-000c2964c7fe
 THREAD_ID: 79
SERVICE_STATE: ON
 COUNT_RECEIVED_HEARTBEATS: 22
 LAST_HEARTBEAT_TIMESTAMP: 2021-02-22 22:08:52
 RECEIVED_TRANSACTION_SET: b799ae0c-74db-11eb-8404-000c2964c7fe:1-8
 LAST_ERROR_NUMBER: 0
 LAST_ERROR_MESSAGE:
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
 2 rows in set (0.00 sec)

2、注意事项

  • 多个master上不能拥有相同的数据库名，否则在从库会出现数据覆盖的现象；
  • master1、master2——>slave复制账号要求不一致；
  • 在从库需要设置参数将主从间复制信息记录到表中，见搭建步骤第3步；
  • 如果是新建库，可以跳过第5步和第6步。