vlambda博客
学习文章列表

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试


目录

一、MySQL主从复制简介 1.1MySQL主从复制简介 1.2、主从复制的好处 1.3MySQL主从复制流程 1.4、主从拓扑结构二、3主1从多源复制搭建 2.1MySQL环境初始化 2.2、主库配置 2.3、从库配置 2.4、查询多源复制 2.5、测试多源复制 2.6、注意的点

一、MySQL主从复制简介

1.1、MySQL主从复制简介

MySQL主从复制(MySQL Replication)是指从一个MySQL主服务器(master)将数据拷贝到另一台或多台MySQL从服务器(slaves)的过程。将主数据库的DDL和DML操作通过二进制日志(binlog)传到从服务器(slave)上,然后在从服务器上对这些日志重新执行,从而使得主从服务器的数据保持同步。
MySQL从3.23版本开始提供复制的功能。

MySQL的Replication是一个多MySQL数据库做主从同步的方案,广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。

1.2、主从复制的好处

主从复制有以下几方面的好处:

  1. 数据备份(Data Backup)
    只是简单的对数据库进行备份,降低数据丢失的风险,

  2. 线下统计
    用于报表等对数据时效性要求不高的场合。

  3. 负载均衡(Load Balance)、读写分离
    主要用在MySQL集群,解决单点故障或做故障切换;以降低单台服务器的负载和风险,如实现读写分离,可以使得服务器访问负荷比较均衡。

  4. 数据分发(Data DistributIOn)、灾备
    主要用于多数据中心或异地备份,实现数据分发与同步。

  5. 高可用和数据容错(High Availability and Failover)
    MySQL自带的健康监控和检测,根据配置的时间间隔,可以检测主库是否正常工作,一旦发现主库宕机或无法正常工作,就会选择到最好的一个备库上。

1.3、MySQL主从复制流程

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

1.4、主从拓扑结构

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试
【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

二、3主1从多源复制搭建

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

2.1、MySQL环境初始化

 1-- 搭建3主1从
2
3-- 配置网络环境
4docker pull mysql:5.7.30
5docker network create --subnet=172.72.0.0/24 mysql-network
6
7-- 创建参数目录
8mkdir -p /lhrmysqltest2/master1/conf.d
9mkdir -p /lhrmysqltest2/master2/conf.d
10mkdir -p /lhrmysqltest2/master3/conf.d
11mkdir -p /lhrmysqltest2/slave/conf.d
12
13-- 删除之前的
14docker rm -f mysql5730M33265 mysql5730M33266 mysql5730M33267 mysql5730S33268
15
16-- 申请主库1
17docker run -d --name mysql5730M33265 \
18   -h master1 -p 33265:3306 --net=mysql-network --ip 172.72.0.10 \
19   -v /lhrmysqltest2/master1/conf.d:/etc/mysql/conf.d \
20   -e MYSQL_ROOT_PASSWORD=lhr \
21   mysql:5.7.30
22
23
24-- 申请主库2
25docker run -d --name mysql5730M33266 \
26   -h master2 -p 33266:3306 --net=mysql-network --ip 172.72.0.11 \
27   -v /lhrmysqltest2/master2/conf.d:/etc/mysql/conf.d \
28   -e MYSQL_ROOT_PASSWORD=lhr \
29   mysql:5.7.30
30
31
32-- 申请主库3
33docker run -d --name mysql5730M33267 \
34   -h master3 -p 33267:3306 --net=mysql-network --ip 172.72.0.12 \
35   -v /lhrmysqltest2/master3/conf.d:/etc/mysql/conf.d \
36   -e MYSQL_ROOT_PASSWORD=lhr \
37   mysql:5.7.30
38
39
40-- 申请从库
41docker run -d --name mysql5730S33268 \
42   -h slave1 -p 33268:3306 --net=mysql-network --ip 172.72.0.13 \
43   -v /lhrmysqltest2/slave/conf.d:/etc/mysql/conf.d \
44   -e MYSQL_ROOT_PASSWORD=lhr \
45   mysql:5.7.30
46
47
48-- 配置主库1的参数
49cat > /lhrmysqltest2/master1/conf.d/my.cnf << "EOF"
50[mysqld]
51port=3306
52character_set_server=utf8mb4
53secure_file_priv=
54server-id = 573033265
55log-bin =
56binlog_format=row
57expire_logs_days = 30
58max_binlog_size  = 100M
59binlog-ignore-db = mysql
60binlog-ignore-db = information_schema
61binlog-ignore-db = performance_schema
62binlog-ignore-db = sys
63replicate_ignore_db=information_schema
64replicate_ignore_db=performance_schema
65replicate_ignore_db=mysql
66replicate_ignore_db=sys
67gtid-mode=ON
68enforce-gtid-consistency=on
69skip_name_resolve
70report_host=172.72.0.10
71EOF
72
73
74-- 配置主库2的参数
75cat > /lhrmysqltest2/master2/conf.d/my.cnf << "EOF"
76[mysqld]
77port=3306
78character_set_server=utf8mb4
79secure_file_priv=
80server-id = 573033266
81log-bin = 
82binlog_format=row
83expire_logs_days = 30
84max_binlog_size  = 100M
85binlog-ignore-db = mysql
86binlog-ignore-db = information_schema
87binlog-ignore-db = performance_schema
88binlog-ignore-db = sys
89replicate_ignore_db=information_schema
90replicate_ignore_db=performance_schema
91replicate_ignore_db=mysql
92replicate_ignore_db=sys
93gtid-mode=ON
94enforce-gtid-consistency=ON
95skip_name_resolve
96report_host=172.72.0.11
97EOF
98
99
100-- 配置主库3的参数
101cat > /lhrmysqltest2/master3/conf.d/my.cnf << "EOF"
102[mysqld]
103port=3306
104character_set_server=utf8mb4
105secure_file_priv=
106server-id = 573033267
107log-bin = 
108binlog_format=row
109expire_logs_days = 30
110max_binlog_size  = 100M
111binlog-ignore-db = mysql
112binlog-ignore-db = information_schema
113binlog-ignore-db = performance_schema
114binlog-ignore-db = sys
115replicate_ignore_db=information_schema
116replicate_ignore_db=performance_schema
117replicate_ignore_db=mysql
118replicate_ignore_db=sys
119gtid-mode=ON
120enforce-gtid-consistency=ON
121skip_name_resolve
122report_host=172.72.0.12
123EOF
124
125
126
127-- 配置从库的参数
128cat > /lhrmysqltest2/slave/conf.d/my.cnf << "EOF"
129[mysqld]
130port=3306
131character_set_server=utf8mb4
132secure_file_priv=
133server-id = 573033268
134log-bin = 
135binlog_format=row
136expire_logs_days = 30
137max_binlog_size  = 100M
138binlog-ignore-db = mysql
139binlog-ignore-db = information_schema
140binlog-ignore-db = performance_schema
141binlog-ignore-db = sys
142replicate_ignore_db=information_schema
143replicate_ignore_db=performance_schema
144replicate_ignore_db=mysql
145replicate_ignore_db=sys
146gtid-mode=ON
147enforce-gtid-consistency=ON
148skip_name_resolve
149report_host=172.72.0.13
150master-info-repository = table
151relay-log-info-repository = table
152EOF
153
154
155-- 重启主机
156docker restart mysql5730M33265
157docker restart mysql5730M33266
158docker restart mysql5730M33267
159docker restart mysql5730S33268
160docker ps
161
162docker exec -it mysql5730M33265 bash
163docker exec -it mysql5730M33265 mysql -uroot -plhr
164
165mysql -uroot -plhr -h192.168.66.35 -P33265 -e "select @@hostname,@@server_id,@@server_uuid"
166mysql -uroot -plhr -h192.168.66.35 -P33266 -e "select @@hostname,@@server_id,@@server_uuid"
167mysql -uroot -plhr -h192.168.66.35 -P33267 -e "select @@hostname,@@server_id,@@server_uuid"
168mysql -uroot -plhr -h192.168.66.35 -P33268 -e "select @@hostname,@@server_id,@@server_uuid"

2.2、主库配置

 1-- 在3台主库分别创建复制用户
2mysql -uroot -plhr -h192.168.66.35 -P33265
3mysql -uroot -plhr -h192.168.66.35 -P33266
4mysql -uroot -plhr -h192.168.66.35 -P33267
5grant replication slave on *.* to repl@'%' identified by 'lhr';
6select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 
7
8
9
10show master status \G;
11show slave hosts;
12select @@hostname,@@server_id,@@server_uuid;

2.3、从库配置

 1-- 从库做以下配置
2mysql -uroot -plhr -h192.168.66.35 -P33268
3
4-- 配置主库1到从库的复制路径
5change master to 
6master_host='172.72.0.10',
7master_port=3306,
8master_user='repl',
9master_password='lhr',
10master_auto_position=1  FOR CHANNEL 'Master1';
11
12-- 配置主库2到从库的复制路径
13change master to 
14master_host='172.72.0.11',
15master_port=3306,master_user='repl',
16master_password='lhr',
17master_auto_position=1  FOR CHANNEL 'Master2';
18
19
20-- 配置主库3到从库的复制路径
21change master to 
22master_host='172.72.0.12',
23master_port=3306,master_user='repl',
24master_password='lhr',
25master_auto_position=1  FOR CHANNEL 'Master3';
26
27-- 启动所有SLAVE
28mysql> START SLAVE;
29-- 也可以单独启动需要同步的通道
30mysql> START SLAVE FOR CHANNEL 'master1';
31mysql> START SLAVE FOR CHANNEL 'master2';
32mysql> START SLAVE FOR CHANNEL 'master3';

2.4、查询多源复制

 1-- 在从库查询
2show slave status \G;
3
4-- 如果要查看单一信道的复制的详细状态,可以使用以下命令:
5mysql> SHOW SLAVE STATUS FOR CHANNEL 'master1'\G;
6
7-- 通过表查询
8select a.master_log_pos,a.host,a.user_name,a.user_password,a.port,a.uuid,a.channel_name from  mysql.slave_master_info a;
9select * from mysql.slave_relay_log_info;
10select * from mysql.slave_worker_info;
11select * from mysql.gtid_executed;
12
13-- 在 performance_schema 库中,提供了复制相关的一些视图,可供查看复制相关的信息。
14
15select * from performance_schema.replication_applier_configuration;
16select * from performance_schema.replication_applier_status;
17select * from performance_schema.replication_applier_status_by_coordinator;
18select * from performance_schema.replication_applier_status_by_worker;
19select * from performance_schema.replication_connection_configuration;
20select * from performance_schema.replication_connection_status;
21select * from performance_schema.replication_group_member_stats;
22select * from performance_schema.replication_group_members;
23
24-- 合并SQL
25select rcc.CHANNEL_NAME,rcc.`HOST`,rcc.`PORT`,rcc.`USER`,rcc.CONNECTION_RETRY_COUNT,rcc.CONNECTION_RETRY_INTERVAL,
26rcs.SOURCE_UUID,rcs.THREAD_ID,rcs.SERVICE_STATE,rcs.COUNT_RECEIVED_HEARTBEATS,rcs.LAST_HEARTBEAT_TIMESTAMP,rcs.LAST_ERROR_NUMBER,rcs.LAST_ERROR_MESSAGE,rcs.LAST_ERROR_TIMESTAMP
27from performance_schema.replication_connection_configuration rcc, 
28     performance_schema.replication_connection_status rcs
29where rcc.CHANNEL_NAME=rcs.CHANNEL_NAME;
30
31-- 线程查询
32SELECT *
33FROM performance_schema.threads a
34WHERE a.`NAME` IN ( 'thread/sql/slave_IO''thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
35
36SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;
37
38MySQL [(none)]> SELECT *
39    -> FROM performance_schema.threads a
40    -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO''thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
41+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
42| THREAD_ID | NAME                 | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                      | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
43+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
44|        29 | thread/sql/slave_io  | FOREGROUND |              4 | root             | 172.72.0.1       | NULL           | Connect             |              252 | Waiting for master to send event                       | NULL             |               28 | NULL | YES          | YES     | NULL            |           75 |
45|        30 | thread/sql/slave_sql | FOREGROUND |              5 | root             | 172.72.0.1       | NULL           | Connect             |              325 | Slave has read all relay log; waiting for more updates | NULL             |               28 | NULL | YES          | YES     | NULL            |           76 |
46|        31 | thread/sql/slave_io  | FOREGROUND |              6 | root             | 172.72.0.1       | NULL           | Connect             |              252 | Waiting for master to send event                       | NULL             |               28 | NULL | YES          | YES     | NULL            |           77 |
47|        32 | thread/sql/slave_sql | FOREGROUND |              7 | root             | 172.72.0.1       | NULL           | Connect             |              322 | Slave has read all relay log; waiting for more updates | NULL             |               28 | NULL | YES          | YES     | NULL            |           78 |
48|        33 | thread/sql/slave_io  | FOREGROUND |              8 | root             | 172.72.0.1       | NULL           | Connect             |              252 | Waiting for master to send event                       | NULL             |               28 | NULL | YES          | YES     | NULL            |           79 |
49|        34 | thread/sql/slave_sql | FOREGROUND |              9 | root             | 172.72.0.1       | NULL           | Connect             |              320 | Slave has read all relay log; waiting for more updates | NULL             |               28 | NULL | YES          | YES     | NULL            |           80 |
50+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
516 rows in set (0.09 sec)

2.5、测试多源复制

 1--  测试多源
2-- mysql -uroot -plhr -h192.168.66.35 -P33265
3create database master1;
4use master1;
5CREATE TABLE `test1` (`id` int(11DEFAULT NULL,`count` int(11DEFAULT NULL);
6insert into test1 values(1,1);
7
8-- mysql -uroot -plhr -h192.168.66.35 -P33266
9create database master2;
10use master2;
11CREATE TABLE `test2` (`id` int(11DEFAULT NULL,`count` int(11DEFAULT NULL);
12insert into test2 values(2,2);
13
14
15-- mysql -uroot -plhr -h192.168.66.35 -P33267
16create database master3;
17use master3;
18CREATE TABLE `test3` (`id` int(11DEFAULT NULL,`count` int(11DEFAULT NULL);
19insert into test3 values(3,3);
20
21
22
23-- 从库查询
24-- mysql -uroot -plhr -h192.168.66.35 -P33268
25show databases;
26SELECT * FROM master1.test1;
27SELECT * FROM master2.test2;
28SELECT * FROM master3.test3;

从库查询:

 1MySQL [(none)]> show databases;
2+--------------------+
3| Database           |
4+--------------------+
5| information_schema |
6| master1            |
7| master2            |
8| master3            |
9| mysql              |
10| performance_schema |
11| sys                |
12+--------------------+
137 rows in set (0.05 sec)
14
15MySQL [(none)]> SELECT * FROM master1.test1;
16+------+-------+
17| id   | count |
18+------+-------+
19|    1 |     1 |
20+------+-------+
211 row in set (0.06 sec)
22
23MySQL [(none)]> SELECT * FROM master2.test2;
24+------+-------+
25| id   | count |
26+------+-------+
27|    2 |     2 |
28+------+-------+
291 row in set (0.05 sec)
30
31MySQL [(none)]> SELECT * FROM master3.test3;
32+------+-------+
33| id   | count |
34+------+-------+
35|    3 |     3 |
36+------+-------+
371 row in set (0.05 sec)
38
39MySQL [(none)]>

2.6、注意的点

1、初次配置耗时较长,需要将各个master的数据dump下来,再source到slave上。

2、需要考虑各master数据增长频率,slave的数据增长频率是这些数据的总和。如果太高,会导致大量的磁盘IO,造成数据更新延迟,最严重的是会影响正常的查询。

3、如果多个主数据库实例中存在同名的库,则同名库的表都会放到一个库中;

4、如果同名库中的表名相同且结构相同,则数据会合并到一起;如果结构不同,则先建的有效。

      

本文结束。


【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

• 若有侵权请联系小麦苗删除

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

★DB宝分享的IT资料:https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解:https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试

【DB宝65】MySQL主从之多源复制(3主1从)搭建及同步测试