mysql多源复制详解

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

大家应该知道,mysql比起其他数据库,最大的特色是就是主从复制,不过5.7之前的版本最多就只支持一主多从的复制方式,对于一些统计类的需求,就需要跨库,这是比较麻烦的事情,以往只能交由数据库中间件(mycat等)去做这种事情,还有备份操作,也只能一个个库的用脚本去轮询或并发备份,不可谓不费时费力。然后,多源复制的概念出来了。

多源复制的概念最早是mariadb社区提出的,后来mysql官方积极引入到5.7的版本中来(5.6最新版也是没有这个功能的),percona就不说了,也是随后推出。这个功能很好的解决了上面说的问题,最直接的当然是省事省钱了,如果把并发复制也开启,在内网环境下,延时几乎是可以忽略的。

个人建议,多源复制不宜做唯一的从库,而是做第二/三从库或层级从库比较好,主要目的应该是统计库或备份库。因为通常来说,多源复制为了避免某些数据冲突是会忽略某些数据库和表的复制的,而为了提高数据库高可用,是需要整库同步的,例如MHA和PXC环境几乎就是要主从是一致的,这样就有冲突而导致不能做高可用了,所以,做第二/三从库或层级从库才可以避免。


前置环境说明

现在测试的架构是主库1的work1和主库2的work2,进行多源复制到从库3中,而且忽略系统库mysql库,这么简单我就不打算画图了,估计网上也有很多,因为后面内容不少,我也不想占版面了,所以随便说一下就算了。

主库1:mysql版本:阿里云rdsmysql5.6.34,ip地址:10.2.0.5

主库2:mysql版本:自建percona-server5.7.18,ip地址:10.2.0.6

从库3:mysql版本:自建percona-server5.7.18,ip地址:10.2.0.7

目的就是:

主库1---work1\

                       从库3

主库2---work2/

怎么搭建mysql数据库我就不说了,这点各位自行研究,我想要看这篇文章的人估计在搭建方面至少是没压力的,同时也假设是有一定业务的数据库存在,其实没有也可以,模拟就行。

然后,看下面的操作。


导出与导入数据库

就算不是做多源复制,普通的主从复制环境都需要把数据导出和导入数据,毕竟binlog不是一直都有记录的(保留策略问题),数据量多起来的话,让他从新跑也是不现实的。

而由于我们多源复制的原意是只复制有需求的数据库,如果使用xtrabackup就不太合适了,因为默认也是会备份mysql库的,这个时候用mysqldump就合适一些了,除非前面一个库用xtrabackup,下一个用mysqldump就可以。我觉得mysqlpump和mydumper也是可以尝试一下。

下面来看看操作

1
2
3
4
5
#从主库导出数据库
mysqldump -uroot -p '******'  -h10.2.0.5 -P3306 --triggers -R --single-transaction \
--no-autocommit --master-data=2 -q -e --databases work1 >work1.sql
mysqldump -uroot -p '******'  -h10.2.0.6 -P3306 --triggers -R --single-transaction \
--no-autocommit --master-data=2 -q -e --databases work2 >work2.sql

可能导出的时候会有个warnings的警告,说会导出设定GTID的sql,不过这个正是我们后面需要的东西,所以可以忽略。

然后,就导入到目的从库环境吧,只要数据不冲突,都可以导进去。理论上,多源复制要禁止一切同名数据库的存在,不然就不是多源复制了。不过,有一种情况我觉得可以尝试下,我个人还没试过,就是同一个库名,多源复制不同的数据表,各位有兴趣可以自行尝试,这里不展开。

1
2
3
4
5
6
7
8
9
10
11
#导入数据库到从库
mysql -uroot -p '******'  -h10.2.0.7 -P3306
>create database work1
>use work1 
> source  work1.sql
>create database work2
>use work2 
> source  work2.sql
#也可以这样
mysql -uroot -p '******'  -h10.2.0.7 -P3306 -e  "create database work1;use work1;source work1.sql;"
mysql -uroot -p '******'  -h10.2.0.7 -P3306 -e  "create database work2;use work2;source work2.sql;"

导完之后,如果严谨一些,就看看数据表是不是全都导进去了,其他就等下一步开始配置了。


配置多源复制环境

以下操作,大部分都是在从库执行的,主库最多就是授权一下,如果已经做过了主从架构,一般这个也是早就授权过的了,直接拿来用就可以的了。

如果配过主从复制环境,应该知道旧模式是要确定pos位置,新模式则是要设置GTID的编号。至于怎么确定,直接more看一下就知道了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#看以下sql的备份文件
more  work1.sql
     .
     .
     .
-- GTID state at the beginning of the backup 
--
SET @@GLOBAL.GTID_PURGED='3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008';
--
-- Position to start replication or point- in - time  recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.001284' , MASTER_LOG_POS=3954096;
     .
     .
     .

这里不打算详细解析,有兴趣的可以看我另一篇关于搭建普通主从架构的文章,里面有详细解析。现在这里可以看到gtid和pos值,后面我们拿来用就可以了。

然后,进入正题:

首先,我们要把从库配置文件改一下,添加些东西。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#在从库的my.cnf配置文件中添加配置项,需要重启mysql实例
vim my.cnf
[mysqld]
master_info_repository=TABLE
relay_log_info_repository=TABLE
replicate_wild_do_table=work1.%
replicate_wild_do_table=work2.%
read -only
#5.6以上参数,并行复制线程数
slave_parallel_workers = 4
#5.7新参数,并行复制模式选择,5.6没用
#slave_parallel_type = LOGICAL_CLOCK
#当然也可以在线实现设置,目的就是把主从配置信息改为记录到表
mysql>SET GLOBAL master_info_repository =  'TABLE' ;
mysql>SET GLOBAL relay_log_info_repository =  'TABLE' ;
mysql>change replication filter REPLICATE_IGNORE_DB=(mysql) ;

需要特别注意replicate_wild_do_table这个参数,这个参数的意思是只执行某个库或某个表的同步语句,其他库和表都不处理,从而达到选择性复制业务库的目的,这就不会有无用的数据了,也不会互相干扰。官方文档解析只能一个参数标记一个库,而且这个参数是全局通用,也就是说多源复制下,所有源频道都通用这个配置。

开启只读read-only就不用多说了,多源复制一般就只有读的需求,就不要让他们有写库的可能了。

并发复制多线程slave_parallel_workers是5.6,5.7新出的功能,能有效加快复制的效率,特别5.7是支持事务并发复制的,速度相当可观,这里设置了每个复制连接有4个并发线程。

slave_parallel_type是选择并发复制方式,默认是为了兼容5.6以库模式的并发复制,这里是改成5.7新出的以组提交事务的方式并发复制,并发效果更佳,但是要特别注意如果主从服务器数据库版本不一致的话,最好还是不要改,因为只有mysql5.7是默认开启组提交功能的

--------------------------------------------------------------------

然后,在主库授权复制的账号,已做过同类账户授权的可以忽略

1
2
3
4
5
#在主库授权,已做过的可以忽略
mysql -uroot -p '******'  -h10.2.0.5 -P3306
grant replication slave on *.* to  'rep' @ '%'  identified by  '123123'
mysql -uroot -p '******'  -h10.2.0.6 -P3306
grant replication slave on *.* to  'rep' @ '%'  identified by  '123123'

---------------------------------------------------------------------

好了,开始正式配置多源复制,这个GTID模式其实比起传统pos的模式更复杂一些,不过,未来都是用GTID多的了,所以我就拿GTID模式来做演示为主。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#登录进从库数据库
mysql -uroot -p '******'  -h10.2.0.7 -P3306
#先清空所有主从结构的记录
reset slave all;
#配置阿里云的数据库,这是GTID模式
change master to
master_host= '10.2.0.5' ,
master_user= 'rep' ,
master_password= '123123' ,
master_port=3306,
MASTER_AUTO_POSITION = 1
FOR CHANNEL  'al_RDS' ;
#配置自建的数据库,这是GTID模式
change master to
master_host= '10.2.0.6' ,
master_user= 'rep' ,
master_password= '123123' ,
master_port=3306,
MASTER_AUTO_POSITION = 1
FOR CHANNEL  'me_mysql' ;
#重置GTID值
reset master;
#设置GTID值
SET @@GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-486646,3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008';

需要特别注意的两点是,多源复制提供了channel的标识,区分了不同的源频道,所以配置的时候就需要加上指定频道名称FOR CHANNEL 'al_RDS';这样。而GTID的值和replicate_wild_do_table参数一样,默认是全局的配置,让源频道各取所需,所以,我们的gtid值,应该是所有*.sql文件的gtid值的合集,用‘,’号分隔,最终就出现我这个要设置这么多GTID的情况。

然后都配置完成了,就可以启动了,启动和关闭都可以指定特定的源频道,相当方便,下面列举一下命令。

1
2
3
4
5
6
7
8
#启动/关闭所有源频道
start /stop  slave;
#启动/关闭单一的源频道
start /stop  slave  for  channel  'al_RDS' ;
#当然重置也是可以单独频道的
#RESET SLAVE FOR CHANNEL 'al_RDS';
#查看状态也是可以单独的,不指定就是全部了
#show slave status for channel 'al_RDS';

然后,看看状态,show slave status看看。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
#在从库上执行命令
mysql -uroot -p '******'  -h10.2.0.7 -P3306
mysql> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.2.0.5
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.001297
           Read_Master_Log_Pos: 5607291
                Relay_Log_File: beifen1-relay-bin-al_rds.000030
                 Relay_Log_Pos: 5607464
         Relay_Master_Log_File: mysql-bin.001297
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: work1.%,work2.%
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 5607291
               Relay_Log_Space: 5607767
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2721321239
                   Master_UUID: 4cdc2a74-6299-11e6-95ce-008cfaf595bc
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has  read  all relay log; waiting  for  more  updates
            Master_Retry_Count: 86400
                   Master_Bind: 
       Last_IO_Error_Timestamp: 
      Last_SQL_Error_Timestamp: 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
            Retrieved_Gtid_Set: 4cdc2a74-6299-11e6-95ce-008cfaf595bc:38888940-39258544
             Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645,
1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544
                 Auto_Position: 1
          Replicate_Rewrite_DB: 
                  Channel_Name: al_rds
            Master_TLS_Version: 
*************************** 2. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.2.0.6
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000013
           Read_Master_Log_Pos: 246854093
                Relay_Log_File: beifen1-relay-bin-me_mysql.000004
                 Relay_Log_Pos: 155502415
         Relay_Master_Log_File: mysql-bin.000013
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: work1.%,work2.%
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 246854093
               Relay_Log_Space: 155502632
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 253241
                   Master_UUID: 817498dc-2676-11e7-a673-00163e024674
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has  read  all relay log; waiting  for  more  updates
            Master_Retry_Count: 86400
                   Master_Bind: 
       Last_IO_Error_Timestamp: 
      Last_SQL_Error_Timestamp: 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
            Retrieved_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:514003-640645
             Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645,
1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544
                 Auto_Position: 1
          Replicate_Rewrite_DB: 
                  Channel_Name: me_mysql
            Master_TLS_Version: 
2 rows  in  set  (0.00 sec)

很长,我们看到有两条记录,其中Replicate_Wild_Do_Table,Executed_Gtid_Set是全局通用,两边都有,印证了我说的参数是全局的。然后各自的Retrieved_Gtid_Set是不同的,他们都自行选择了,相当智能。再看到Channel_Name就是他们不同的频道名字了。然后可以看到,Slave_IO_Running和Slave_SQL_Running的双yes,Master_Log_File=Relay_Master_Log_File,Read_Master_Log_Pos=Exec_Master_Log_Pos,Seconds_Behind_Master为0,所以现在是同步的。

--------------------------------------------------------------------------

如果是传统的模式,反而更简单,不用设置GTID的值,类似下面这样指定log文件名和pos即可,不用设置SET @@GLOBAL.GTID_PURGED就可以启动。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#设置频道一
change master to
master_host= '10.2.0.5' ,
master_user= 'rep' ,
master_password= '123123' ,
master_port=3306,
MASTER_LOG_FILE= 'mysql-bin.001284' ,
MASTER_LOG_POS=3954096
FOR CHANNEL  'al_RDS' ;
#设置频道二
change master to
master_host= '10.2.0.6' ,
master_user= 'rep' ,
master_password= '123123' ,
master_port=3306,
MASTER_LOG_FILE= 'mysql-bin.000014' ,
MASTER_LOG_POS=67456
FOR CHANNEL  'me_mysql' ;
#启动
start slave;

--------------------------------------------------------------------------


问题汇总

1.在停止多元复制环境时要注意并行复制的进度,例如出现下面这种情况,就先等一等再停止。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#请关注Executed_Gtid_Set:项
show slave status\G
     .
     .
     .
              Master_Server_Id: 2721321239
                   Master_UUID: 4cdc2a74-6299-11e6-95ce-008cfaf595bc
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Reading event from the relay log
            Master_Retry_Count: 86400
                   Master_Bind: 
       Last_IO_Error_Timestamp: 
      Last_SQL_Error_Timestamp: 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
            Retrieved_Gtid_Set: 4cdc2a74-6299-11e6-95ce-008cfaf595bc:50007036-50049107
             Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-47551250,
3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758,
4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-50010063:50010080-50010093:50010099-50010101:50010108:50010130-50010139:50010145-50010148:50010158:50010179-50010184:50010190-50010200:50010207:50010215-50010221:50010227-50010236:50010243:50010276-50010285:50010291-50010293:50010300:50010308-50010312:50010326-50010328:50010371-50010373:50010391-50010393:50010403-50010405:50010427-50010429:50010464-50010466:50010480-50010482:50010490-50010496:50010518-50010520:50010538-50010540:50010551-50010553:50010574
                 Auto_Position: 1
          Replicate_Rewrite_DB: 
                  Channel_Name: al_rds
            Master_TLS_Version: 
*************************** 2. row ***************************
                Slave_IO_State: Waiting  for  master to send event
     .
     .
     .

那是因为你这么一停止,并行复制就中途停止了,就有可能出现有些数据回滚不了,或者有些数据复制错误,然后后续你还想把他起来就很大可能会报错了,所以宁愿先等一等,再停止。

当然了,如果是线上环境,究竟要等到什么时候?所以最好就是数据库不繁忙的时候再做。要么,你就是准备好重做的心态了,那就来吧。



     本文转自arthur376 51CTO博客,原文链接:http://blog.51cto.com/arthur376/1944300,如需转载请自行联系原作者








相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
SQL 关系型数据库 MySQL
面试官:说一下MySQL主从复制的原理?
面试官:说一下MySQL主从复制的原理?
160 0
面试官:说一下MySQL主从复制的原理?
|
12月前
|
SQL 存储 关系型数据库
MySQL主从复制之原理&一主一从部署流程—2023.04
MySQL主从复制之原理&一主一从部署流程—2023.04
371 0
|
5月前
|
SQL 关系型数据库 MySQL
MySQL中主从复制的原理和配置命令
要原因包括提高性能、实现高可用性、数据备份和灾难恢复。了解两大线程( I/O 和 SQL)I/O线程:目的:I/O线程主要负责与MySQL服务器之外的其他MySQL服务器进行通信,以便复制(replication)数据。 功能: 当一个MySQL服务器作为主服务器(master)时,I/O线程会将变更日志(binary log)中的事件传输给从服务器(slave)。从服务器上的I/O线程负责接收主服务器的二进制日志,并将这些事件写入本地的中继日志(relay log)。 配置: 在MySQL配置文件中,你可以通过配置参数如和来启用二进制日志和指定服务器ID。log-bin server
128 1
MySQL中主从复制的原理和配置命令
|
2月前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
52 0
|
2月前
|
SQL canal 关系型数据库
(二十四)全解MySQL之主从篇:死磕主从复制中数据同步原理与优化
兜兜转转,经过《全解MySQL专栏》前面二十多篇的内容讲解后,基本对MySQL单机模式下的各方面进阶知识做了详细阐述,同时在前面的《分库分表概念篇》、《分库分表隐患篇》两章中也首次提到了数据库的一些高可用方案,但前两章大多属于方法论,并未涵盖真正的实操过程。接下来的内容,会以目前这章作为分割点,开启MySQL高可用方案的落地实践分享的新章程!
819 1
|
5月前
|
关系型数据库 MySQL Linux
【mysql】MySql主从复制,从原理到实践!
【mysql】MySql主从复制,从原理到实践!
115 0
|
5月前
|
SQL 容灾 关系型数据库
MySQL 主从复制原理
MySQL 主从复制原理
71 1
MySQL 主从复制原理
|
12月前
|
SQL 关系型数据库 MySQL
MySql主从复制原理及其搭建
MySql主从复制原理及其搭建
|
SQL 负载均衡 关系型数据库
MySQL主从复制的原理与实操+mycat2读写分离
MySQL主从复制的原理与实操+mycat2读写分离
226 0
|
SQL 算法 关系型数据库
mysql主从复制的原理和实战
mysql主从复制的原理和实战
下一篇
无影云桌面