Mysql之多源复制

最新推荐文章于 2024-08-01 19:21:29 发布

恒悦sunsite

最新推荐文章于 2024-08-01 19:21:29 发布

阅读量1.7k

点赞数 4

分类专栏： mysql 文章标签： mysql 多源复制多主一从 GTID

本文链接：https://blog.csdn.net/carefree2005/article/details/113935161

版权

mysql 专栏收录该内容

36 篇文章 7 订阅

订阅专栏

一、多源复制简介

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

可以集中备份，在从库上备份，不会影响线上的数据正常运行；
节约购买从库服务器的成本，只需要一个从库服务器即可；
数据汇总在一起，方便后期做数据统计；
减轻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)