Centos7.5部署MySQL5.7基于GTID主从复制+并行复制+半同步复制+读写分离(ProxySQL)

 

 

 

之前已经详细介绍了Mysql基于GTID主从复制的概念，原理和配置，下面整体记录下MySQL5.7基于GTID主从复制+并行复制+增强半同步复制+读写分离环境的实现过程，以便加深对mysql新特性GTID复制的理解和实际业务场景中部署应用。

一、实验环境

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@mysql-master ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
 
为了方便实验，关闭所有节点的防火墙
[root@mysql-master ~]# systemctl stop firewalld
[root@mysql-master ~]# firewall-cmd --state
not running
 
[root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
SELINUX=disabled
[root@mysql-master ~]# setenforce 0              
setenforce: SELinux is disabled
[root@mysql-master ~]# getenforce                
Disabled

二、安装Mysql5.7

?
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
在三个mysql节点机上使用yum方式安装Mysql5.7，参考：https://www.cnblogs.com/kevingrace/p/8340690.html
  
安装MySQL yum资源库
[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
  
安装MySQL 5.7
[root@mysql-master ~]# yum install -y mysql-community-server
  
启动MySQL服务器和MySQL的自动启动
[root@mysql-master ~]# systemctl start mysqld.service
[root@mysql-master ~]# systemctl enable mysqld.service
  
设置登录密码
由于MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，
这个密码记录在/var/log/mysqld.log文件中，使用下面的命令可以查看此密码：
[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
  
使用上面查看的密码TaN.k:*Qw2xs 登录mysql，并重置密码为123456
[root@mysql-master ~]# mysql -p                 #输入默认的密码：TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
  
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
  
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
  
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  
查看mysql版本
[root@mysql-master ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set (0.00 sec)
 
=====================================================================
温馨提示
mysql5.7通过上面默认安装后，执行语句可能会报错：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
 
这个报错与Mysql 密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值：
解决办法：
set global validate_password_policy=0;
set global validate_password_length=1;

三、配置基于GTID的主从复制 

传统的基于binlog position复制的方式有个严重的缺点：如果slave连接master时指定的binlog文件错误或者position错误，会造成遗漏或者重复，
很多时候前后数据是有依赖性的，这样就会出错而导致数据不一致。

从MYSQL5.6开始，mysql开始支持GTID复制。GTID的全称是global transaction id，表示的是全局事务ID。GTID的分配方式为uuid:trans_id，其中：uuid是每个mysql服务器都唯一的，记录在$datadir/auto.cnf中。如果复制结构中，任意两台服务器uuid重复的话(比如直接冷备份时，auto.conf中的内容是一致的)，在启动复制功能的时候会报错。这时可以删除auto.conf文件再重启mysqld。

基于GTID主从复制的优点大致有：
-  保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总是会被执行。
-  不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标。
-  故障转移到新的master的时候很方便，简化了很多任务。
-  很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了，那么一定是一致的。
-  当然，MySQL提供了选项可以控制跳过某些gtid事务，防止slave第一次启动复制时执行master上的所有事务而导致耗时过久。
-  虽然对于row-based和statement-based的格式都能进行gtid复制，但建议采用row-based格式。 

                                                              基于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
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
1）mysql-master主数据库上的操作
  
在my.cnf文件中配置GTID主从复制
[root@mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
  
symbolic-links = 0
  
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
  
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
    
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
   
#relay log
skip_slave_start = 1
  
配置后，记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
  
登录mysql，并查看master状态， 发现多了一项"Executed_Gtid_Set "
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 317e2aad-1565-11e9-9c2e-005056ac6820 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
  
查看确认gtid功能打开
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)
  
查看确认binlog日志功能打开
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
  
授权slave复制用户，并刷新权限
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
  
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
  
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212                                                |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  
再次查看master状态
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      622 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
  
这里需要注意一下：
启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了GTID模式后，
在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的GTID值。
    
需要先在主数据库机器上把目标库备份一下，假设这里目标库是kevin（为了测试效果，下面手动创建）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
  
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
  
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.27 sec)
  
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");   
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
  
mysql> select * from kevin.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | congcong |
|  2 | huihui   |
|  3 | grace    |
+----+----------+
3 rows in set (0.00 sec)
  
把kevin库备份出来
[root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases kevin -uroot -p123456 > /root/kevin.sql
  
这里稍微注意下：
mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database
mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases
  
[root@mysql-master ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-master ~]# cat /root/kevin.sql
-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost    Database: kevin
-- ------------------------------------------------------
-- Server version       5.7.24-log
.............
.............
--
-- GTID state at the beginning of the backup
--
  
SET @@GLOBAL.GTID_PURGED='317e2aad-1565-11e9-9c2e-005056ac6820:1-5';
  
然后把备份的/root/kevin.sql文件拷贝到mysql-slave1从数据库服务器上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/kevin.sql root@172.16.60.212:/root/
  
==============================================================
2）mysql-slave1从数据库上的操作
  
在my.cnf文件中配置GTID主从复制
与主服务器配置大概一致，除了server_id不一致外，从服务器还可以在配置文件里面添加："read_only＝on" ,
使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制；
[root@mysql-slave1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
  
symbolic-links = 0
  
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
  
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
    
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
   
#relay log
skip_slave_start = 1
read_only = on
  
配置完成后，记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
  
接着将主数据库目标库的备份数据kevin.sql导入到从数据库里
[root@mysql-slave1 ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-slave1 ~]# mysql -p123456
.........
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
  
mysql> source /root/kevin.sql;
  
mysql> select * from kevin.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | congcong |
|  2 | huihui   |
|  3 | grace    |
+----+----------+
3 rows in set (0.00 sec)
  
在从数据库里，使用change master 配置主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
  
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.26 sec)
  
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
  
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.60.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1357
               Relay_Log_File: mysql-slave1-relay-bin.000002
                Relay_Log_Pos: 417
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
................
................
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5
                Auto_Position: 1
  
由上面可知，mysql-slave1节点已经和mysql-master节点配置了主从同步关系
  
3） mysql-master主数据库上进行状态查看和测试测试插入
  
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1357 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
  
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
  
mysql> insert into kevin.haha values(4,"beijing"),(5,"hefei"),(10,"xihu");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
  
mysql> delete from kevin.haha where id<4;
Query OK, 3 rows affected (0.10 sec)
  
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
  
4）mysql-slave1从数据库上查看
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
  
发现mysql-slave1从数据库已经将新插入的数据同步过来了，由此，基于GTID的主从同步复制关系已经正常部署完成了！

四、并行复制 (解决主从复制延迟问题)

一般Mysql主从复制有三个线程参与，都是单线程：Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从)。复制出现延迟一般出在两个地方:
-  SQL线程忙不过来 (可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因)
-  网络抖动导致IO线程复制延迟(次要原因)。

MySQL主从复制延迟的解决办法：MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。并行复制的机制，是MySQL的一个非常重要的特性，可以很好的解决MySQL主从延迟问题！

在MySQL 5.6中，设置参数slave_parallel_workers = 4(>1)，即可有4个SQL Thread（coordinator线程）来进行并行复制，其状态为：Waiting for an evant from Coordinator。但是其并行只是基于Schema的，也就是基于库的。如果数据库实例中存在多个Schema，这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形，那基于库的并发就没有卵用。其核心思想是：不同schema下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致。

MySQL 5.6版本支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。但是基于schema的并行复制存在两个问题：
1) crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。
2) 最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。

注意：mysql 5.6的MTS是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些。设置为库级别的事务时，不允许这样做，会报错。

在MySQL 5.7中，引入了基于组提交的并行复制（官方称为Enhanced Multi-threaded Slaves，即MTS），设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一个schema下，slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。其核心思想：一个组提交的事务都是可以并行回放（配合binary log group commit）；slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。其中，变量slave-parallel-type可以有两个值：１）DATABASE 默认值，基于库的并行复制方式；２）LOGICAL_CLOCK，基于组提交的并行复制方式；

MySQL 5.7开启Enhanced Multi-Threaded Slave很简单，只需要在Slave从数据库的my.cnf文件中如下配置即可:

?
1
2
3
4
5
6
# slave
 slave-parallel-type=LOGICAL_CLOCK
 slave-parallel-workers=8        #一般建议设置4-8，太多的线程会增加线程之间的同步开销
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 relay_log_recovery=ON

MySQL 5.7是基于组提交的并行复制，并且是支持"真正"的并行复制功能，这其中最为主要的原因：就是slave服务器的回放与主机是一致的, 即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有要求）。

MySQL5.7的并行复制，期望最大化还原主库的并行度，实现方式是在binlog event中增加必要的信息，以便slave节点根据这些信息实现并行复制。MySQL5.7的并行复制建立在group commit的基础上，所有在主库上能够完成prepared的语句表示没有数据冲突，就可以在slave节点并行复制。

所以在并行复制环境中，除了在Slace从数据库中配置之外，还需要在Master主数据库上的my.cnf文件中添加binlog_group_commit配置，否则从库无法做到基于事物的并行复制:

?
1
2
binlog_group_commit_sync_delay = 100               
binlog_group_commit_sync_no_delay_count = 10

binlog_group_commit_sync_delay，这个参数控制着日志在刷盘前日志提交要等待的时间，默认是0也就是说提交后立即刷盘，但是并不代表是关闭了组提交，当设置为0以上的时候，就允许多个事物的日志同时间一起提交刷盘，也就是我们说的组提交。组提交是并行复制的基础，我们设置这个值的大于0就代表打开了组提交的延迟功能，而组提交是默认开启的。最大值只能设置为1000000微妙。
binlog_group_commit_sync_no_delay_count ，这个参数表示我们在binlog_group_commit_sync_delay等待时间内，如果事物数达到这个参数的设定值，就会触动一次组提交，如果这个值设为为0的话就不会有任何的影响。如果到达时间但是事物数并没有达到的话，也是会进行一次组提交操作的。

MySQL 5.7并行复制的思想简单易懂，一言以蔽之： 一个组提交的事务都是可以并行回放 ，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：
-  DATABASE：默认值，基于库的并行复制方式
-  LOGICAL_CLOCK：基于组提交的并行复制方式

支持并行复制的GTID
如何知道事务是否在一组中，又是一个问题，因为原版的MySQL并没有提供这样的信息。在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。那么如果用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？故MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型，如:

?
1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000003 | 4   | Format_desc    | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |
| mysql-bin.000003 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 259 | Query          | 88 | 330 | BEGIN |
| mysql-bin.000003 | 330 | Table_map      | 88 | 373 | table_id: 108 (aaa.t) |
| mysql-bin.000003 | 373 | Write_rows     | 88 | 413 | table_id: 108 flags: STMT_END_F |
......

这意味着在 MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid ，而这GTID中就存在着组提交的信息。

LOGICAL_CLOCK
然而，通过上述的SHOW BINLOG EVENTS，并没有发现有关组提交的任何信息。但是通过mysqlbinlog工具，用户就能发现组提交的内部信息:

如下查看一个binlog日志

?
1
2
3
4
5
root@localhost:~# mysqlbinlog mysql-bin.0000002 | grep last_committed
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=0        sequence_number=1       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=1        sequence_number=2       rbr_only=yes
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=2        sequence_number=3       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=3        sequence_number=4       rbr_only=yes
可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放 (一般是当执行的sql语句并发数大的情况下会进行组提交)。上面这个binlog日志里没有组提交信息(last_committed数值都不相等)，下一个事物的last_committed永远都和上一个事物的sequence_number是相等的，这是因为事物是顺序提交的！这么理解起来并不奇怪。
下面看一下组提交模式的事物：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@localhost:~# mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259  CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8
#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9
#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10
#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11
#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12
#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13
...

例如上述last_committed为0的事务有6个，这意味什么呢？意味着这6个事物是作为一个组提交的，6个事物在perpare截断获取相同的last_committed而且相互不影响，最终是会作为一个组进行提交?，这就是所谓的组提交！上面表示组提交时提交了6个事务，而这6个事务在从机是可以进行并行回放的。

总之：MySQL 5.7推出的Enhanced Multi-Threaded Slave解决了困扰MySQL长达数十年的复制延迟问题，再次提醒一些无知的PostgreSQL用户，不要再停留在之前对于MySQL的印象，物理复制也不一定肯定比逻辑复制有优势，而MySQL 5.7的MTS已经完全可以解决延迟问题。总之， 5.7版本后，主从复制延迟问题将不存在！

                 　　　　　                                 基于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
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
1）在mysql-slave1从数据库的my.cnf 文件中添加下面MTS并行复制的配置
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
    
symbolic-links = 0
    
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
    
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
      
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
     
#relay log
skip_slave_start = 1
read_only = on
    
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                               #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
    
配置完成后，记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
    
2）在mysql-master主数据库的my.cnf 文件中添加最后两行配置
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
    
symbolic-links = 0
    
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
    
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
      
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
     
#relay log
skip_slave_start = 1
    
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100              
binlog_group_commit_sync_no_delay_count = 10
    
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog
    
配置完成后，记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
    
登录mysql，查看binlog_group_commit
mysql> show variables like 'binlog_group_commit_%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 100   |
| binlog_group_commit_sync_no_delay_count | 10    |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
    
设置binlog_group_commit的上面两个参数，否则从库无法做到基于事物的并行复制
这两参数共同决定。第一个表示该事务组提交之前总共等待累积到多少个事务
第二个参数则表示该事务组总共等待多长时间后进行提交，任何一个条件满足则进行后续操作。
因为有这个等待，可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交，从而获得更高的吞吐量。
  
3）登录mysql-slave1从数据库
  
上面在配置并行复制后，主从数据库的mysqld服务都重启了，此时需要重启从数据库上的slave，这样才能恢复正常的主从同步状态！
记住：只要主数据库的mysqld服务重启，那么从数据库上就要重启slave，以恢复主从同步状态！！！
  
mysql> show slave status \G;       
...........
...........
             Slave_IO_Running: No
            Slave_SQL_Running: No
    
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
    
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
    
mysql> show slave status \G;
..........
..........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..........
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-11
                Auto_Position: 1
    
这样，mysql-slave1从数据库就恢复了与mysql-master主数据库的同步关系了
  
查看优化项
mysql> use mysql;                                 #切入到mysql库
Database changed
mysql> select * from slave_worker_info;                 #查看slave_worker_info表
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name                  | Relay_log_pos | Master_log_name   | Master_log_pos | Checkpoint_relay_log_name       | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 | ./mysql-slave1-relay-bin.000005 |           656 | mysql-bin.000008 |            481 | ./mysql-slave1-relay-bin.000005 |                      369 | mysql-bin.000008          |                       194 |                0 |                    64 |                                                                 |              |
|  2 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)
    
以上可以查看到，mysql库下的slave_worker_info表下查看到4个线程
   
也可以使用下面命令查看线程数，这个跟在my.cnf文件里配置的是一样的！
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.00 sec)
  
4）同步复制测试
在mysql-master主数据库插入新数据
mysql> insert into kevin.haha values(21,"caiyi"),(22,"manman"),(23,"titi");
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0
    
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)
    
然后在mysql-slave1从数据库查看，发现新数据已经同步过来了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)
 
最后在mysql-master主数据库上查看组提交信息
[root@mysql-master ~]# cd /var/lib/mysql
[root@mysql-master mysql]# ll mysql-bin*
-rw-r----- 1 mysql mysql  751 Jan 12 18:08 mysql-bin.000001
-rw-r----- 1 mysql mysql 1365 Jan 12 19:28 mysql-bin.000002
-rw-r----- 1 mysql mysql 1326 Jan 12 19:42 mysql-bin.000003
-rw-r----- 1 mysql mysql   57 Jan 12 19:28 mysql-bin.index
 
[root@mysql-master mysql]# mysqlbinlog mysql-bin.000003 |grep last_committed
#190112 19:37:06 server id 1  end_log_pos 259 CRC32 0x893c0ae8  GTID    last_committed=0       sequence_number=1       rbr_only=yes
#190112 19:37:12 server id 1  end_log_pos 542 CRC32 0xc36def61  GTID    last_committed=1       sequence_number=2       rbr_only=yes
#190112 19:42:42 server id 1  end_log_pos 825 CRC32 0x361701a2  GTID    last_committed=2       sequence_number=3       rbr_only=yes
#190112 19:42:48 server id 1  end_log_pos 1108 CRC32 0x8ba858c7 GTID    last_committed=3       sequence_number=4       rbr_only=yes
 
如上，通过最新的binlog日志看到，last_committed数值都不一样，即没有事务是在同一个组内提交的，也就是说这个日志里没有组提交信息。
这是因为上面在插入新数据时，执行的sql语句并发数小，所以没有进行组提交！
当sql并发数大的一定程度时，就会进行组提交～

五、半同步复制

半同步复制: 默认情况下MySQL的复制是异步的，master将新生成的binlog发送给各slave后，无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack)，直接就认为这次DDL/DML成功了, 半同步复制(semi-synchronous replication)是指master将新生成的binlog发送给各slave时, 只需等待一个(默认)slave返回的ack信息就返回成功。

MySQL 5.7对半同步复制作了大改进，新增了一个master线程。在MySQL 5.7以前，master上的binlog dump线程负责两件事：dump日志给slave的io_thread；接收来自slave的ack消息。它们是串行方式工作的。在MySQL 5.7中，新增了一个专门负责接受ack消息的线程ack collector thread。这样master上有两个线程独立工作，可以同时发送binlog到slave和接收slave的ack。还新增了几个变量，其中最重要的是 rpl_semi_sync_master_wait_point ，它使得MySQL半同步复制有两种工作模型。

半同步复制的两种类型
从MySQL 5.7.2开始，MySQL支持两种类型的半同步复制。这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制，它有两种值：AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后，默认值为AFTER_SYNC，在此版本之前，等价的类型为AFTER_COMMIT。这个变量控制的是master何时提交、何时接收ack以及何时回复成功信息给客户端的时间点。
AFTER_SYNC模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk), 之后才允许接收slave的ack回复，接收到ack之后才会提交事务，并返回成功信息给客户端。
AFTER_COMMIT模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk)，然后直接提交事务。之后才允许接收slave的ack回复，然后再返回成功信息给客户端。

如下作图，理解起来就容易了。(前提: 已经设置了sync_binlog=1，否则binlog刷盘时间由操作系统决定)

根据上面的图解，接下来分析下这两种模式的优缺点。

AFTER_SYNC
-  对于所有客户端来说，它们看到的数据是一样的，因为它们看到的数据都是在接收到slave的ack后提交后的数据。
-  这种模式下，如果master突然故障，不会丢失数据，因为所有成功的事务都已经写进slave的relay log中了，slave的数据是最新的。

AFTER_COMMIT
-  不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端，它只有在master提交事务且收到slave的ack后才能看到提交的数据。但对于那些非本次事务的请求客户端，它们在master提交后就能看到提交后的数据，这时候master可能还没收到slave的ack。
-  如果master收到ack回复前，slave和master都故障了，那么将丢失这个事务中的数据。

在MySQL 5.7.2之前，等价的模式是 AFTER_COMMIT ，在此版本之后，默认的模式为 AFTER_SYNC ，该模式能最大程度地保证数据安全性，且性能上并不比 AFTER_COMMIT 差。

                                                               基于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
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
Mysql 半同步复制配置可以参考: https://www.cnblogs.com/kevingrace/p/10228694.html
 
开启半同步复制的方法有两种: mysql命令行启动; my.cnf文件里添加启动配置;
推荐在my.cnf文件里添加半同步启动配置方式
 
1) 配置mysql-master主数据库上 my.cnf文件，添加启动半同步复制的配置
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
     
symbolic-links = 0
     
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
     
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
       
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
      
#relay log
skip_slave_start = 1
     
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100             
binlog_group_commit_sync_no_delay_count = 10
     
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog
 
#开启半同步复制 (超时时间为1s)
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
 
配置完成后，记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
 
2) 配置mysql-slave从数据库上 my.cnf文件，添加启动半同步复制的配置
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
   
symbolic-links = 0
   
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
   
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
     
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
read_only = on
   
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                               #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
 
# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
 
配置完成后，记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
 
3）在mysql-master主数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_master.so 文件存在。 一般mysql安装后就默认产生)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.25 sec)
 
查看插件是否加载成功
mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)
 
查看半同步是否在运行
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)
 
4）在mysql-slave1从数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在。 一般mysql安装后就默认产生)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)
 
查看插件是否加载成功
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)
 
这个时候，查看mysql-slave1的半同步是否运行
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)
 
发现还是OFF，明明已经在my.cnf文件里开启了！
这是因为此时还没有生效，必须从数据库上的IO线程才能生产！！！
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> START SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.00 sec)
 
然后再查看mysql-slave1的半同步状态，发现就已经开启了！
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
 
查看此时的主从同步状态
mysql> show slave status \G;
...........
...........
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
 
发现主从同步异常，这个时候再重启下slave即可！
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
 
mysql> show slave status \G;
............
............
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 
5) 在mysql-master主数据库上查看
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
 
从上面信息，发现Rpl_semi_sync_master_clients的数值为1，说明此时mysql-master主数据库已经有一个半同步复制的从机，即mysql-slave1节点。
Rpl_semi_sync_master_yes_tx的数值为0， 说明此时还没有半同步复制的sql语句被执行。
 
接着在mysql-master主数据库上插入和更新数据，测试半同步复制
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.01 sec)
 
mysql> insert into kevin.haha values(30,"shikui"),(31,"shibo"),(32,"shijuan");
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> update kevin.haha set id=8 where name="beijing";
Query OK, 1 row affected (0.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
接着再去mysql-slave1从数据库上查看，发现新数据已经同步过来了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)
 
接着再去mysql-master主数据库上查看，如下：
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 1      |
| Rpl_semi_sync_master_net_avg_wait_time     | 0      |
| Rpl_semi_sync_master_net_wait_time         | 0      |
| Rpl_semi_sync_master_net_waits             | 2      |
| Rpl_semi_sync_master_no_times              | 0      |
| Rpl_semi_sync_master_no_tx                 | 0      |
| Rpl_semi_sync_master_status                | ON     |
| Rpl_semi_sync_master_timefunc_failures     | 0      |
| Rpl_semi_sync_master_tx_avg_wait_time      | 133362 |
| Rpl_semi_sync_master_tx_wait_time          | 266725 |
| Rpl_semi_sync_master_tx_waits              | 2      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 2      |
+--------------------------------------------+--------+
14 rows in set (0.01 sec)
 
从上面信息可发现Rpl_semi_sync_master_yes_tx的数值为2，即发生了两条半同步复制的sql语句，就是上面执行的那两条(insert 和update) sql语句。

 以上都是在mysql-master主数据库和mysql-slave1从数据库之间实现的基于GTID的主从复制、并行复制、半同步复制，即"一主一从"架构。 现在再把mysql-slave2的从节点添加进去，调整为"一主两从"的同步架构。添加操作记录如下:

?
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
1）在mysql-master主数据库上操作
先添加同步权限
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.14 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)
 
备份主库
[root@mysql-master ~]# mysqldump -u root -p'123456' --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > 213_slave.sql
 
将备份文件拷贝到mysql-slave2从数据库上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/213_slave.sql root@172.16.60.213:/root/
 
记录当前的gtid
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-16 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)
 
2）在mysql-slave2从数据库上操作
首先在my.cnf文件添加相关同步配置 （跟mysql-slave1从节点的my.cnf配置相比，除了将server_id修改为3之外，其他配置内容都一样！）
[root@mysql-slave2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave2 ~]# >/etc/my.cnf
[root@mysql-slave2 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
    
symbolic-links = 0
    
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
    
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
      
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
     
#relay log
skip_slave_start = 1
read_only = on
    
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                               #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
  
# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
 
配置完成后，不要忘记重启mysql服务
[root@mysql-slave2 ~]# systemctl restart mysqld
 
恢复备份库
[root@mysql-slave2 ~]# ll /root/213_slave.sql
-rw-r--r-- 1 root root 805694 Jan 13 00:26 /root/213_slave.sql
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> source /root/213_slave.sql;
 
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)
 
检查一下mysql-slave2从数据库上当前的gtid
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.01 sec)
 
由于是从master-master主库备份恢复过来的，所以此时mysql-slave2从数据库的gtid和主库的gtid是一样的！
 
接着进行主从同步
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; 
Query OK, 0 rows affected, 2 warnings (0.18 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
 
=================================================================
如果遇到报错：
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
 
解决办法：
stop slave;
reset slave;
start slave;
=================================================================
 
查看，发现主从同步正常
mysql> show slave status \G;
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 
并行复制查看
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.01 sec)
 
mysql> select * from mysql.slave_worker_info; 
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Checkpoint_relay_log_name | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  2 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)
 
接着在mysql-slave2从数据库上开启半同步
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)
 
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)
 
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.02 sec)
 
mysql> START SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> show status like 'Rpl_semi_sync_slave_status';     
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
 
3） 现在回到mysql-master主数据库查看
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         1 | 2658b203-1565-11e9-9f8b-005056880888 |
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
 
如上信息可知mysql-master主数据库现在有两个从数据库，分别为mysql-slave1 和 mysql-slave2
 
mysql> show status like '%Rpl_semi%';                                                                     
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 2      |
| Rpl_semi_sync_master_net_avg_wait_time     | 0      |
| Rpl_semi_sync_master_net_wait_time         | 0      |
| Rpl_semi_sync_master_net_waits             | 4      |
| Rpl_semi_sync_master_no_times              | 0      |
| Rpl_semi_sync_master_no_tx                 | 0      |
| Rpl_semi_sync_master_status                | ON     |
| Rpl_semi_sync_master_timefunc_failures     | 0      |
| Rpl_semi_sync_master_tx_avg_wait_time      | 98077  |
| Rpl_semi_sync_master_tx_wait_time          | 392310 |
| Rpl_semi_sync_master_tx_waits              | 4      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 4      |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)
 
如上信息可知，mysql-master主数据库现在有两个半同步复制的从库，即mysql-slave1 和mysql-slave2
 
4）现在测试下同步效果
在mysql-master主数据库上更新数据
mysql> delete from kevin.haha where id>10;
Query OK, 6 rows affected (0.20 sec)
 
mysql> insert into kevin.haha values(11,"changping"),(12,"wangjing");
Query OK, 2 rows affected (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
在mysql-slave1从数据库查看，发现已经同步过来了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)
 
在mysql-slave2从数据库查看，发现已经同步过来了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)

到此，已经完成了Mysql5.7"一主两从"架构上基于GTID主从复制+并行复制+增强半同步复制环境，下面再说下读写分离环境：

六、读写分离 （MySQL ProxySQL）

ProxySQL是用C++语言开发的，也是percona推的一款中间件，虽然也是一个轻量级产品，但性能很好(据测试，能处理千亿级的数据)，功能也足够，能满足中间件所需的绝大多数功能，包括：
-  最基本的读写分离，且安装起来也十分方便。
-  可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。
-  可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略。
-  监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)。

ProxySQL是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL 中间件产品 ！

 

posted @ 2020-02-18 10:15  是烫的不是自来卷  阅读(299)  评论(0)  编辑  收藏  举报