CentOS 7.5 部署 MySQL 5.7 基于GTID主从复制+并行复制+半同步复制+读写分离(ProxySQL) 环境- 运维笔记 (完整版)
转载于https://www.cnblogs.com/kevingrace/p/5569652.html
之前已经详细介绍了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-releaseCentOS Linux release 7.5.1804 (Core)为了方便实验，关闭所有节点的防火墙[root@mysql-master ~]# systemctl stop firewalld[root@mysql-master ~]# firewall-cmd --statenot 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/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 1gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_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 changedmysql> 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备份时，指定备份的具体库，使用--databasemysql5.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/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 2gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1read_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
|
1）在mysql-slave1从数据库的my.cnf 文件中添加下面MTS并行复制的配置[root@mysql-slave1 ~]# vim /etc/my.cnf[mysqld]datadir = /var/lib/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 2gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1read_only = on #MTSslave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制slave-parallel-workers = 4 #这里设置线程数为4 (最大线程数不能超过16，即最大线程为16)master_info_repository = TABLErelay_log_info_repository = TABLErelay_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/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 1gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #为了数据安全再配置sync_binlog=1innodb_flush_log_at_trx_commit =1#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog 配置完成后，记得重启mysql服务[root@mysql-master ~]# systemctl restart mysqld 登录mysql，查看binlog_group_commitmysql> 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的上面两个参数，否则从库无法做到基于事物的并行复制! 这两个参数共同决定了是否触发组提交操作！第二个参数表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务)；第一个参数则表示该事务组总共等待多长时间后进行提交(如上要总共等待100毫秒的时间)，任何一个条件满足则进行后续操作。因为有这个等待，可以让更多事务的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 changedmysql> 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数值都不一样，即没有事务是在同一个组内提交的，也就是说这个日志里没有组提交信息。这是因为没有满足binlog_grup_commit那两个参数的条件，所以没有进行组提交！ |
五、半同步复制
半同步复制: 默认情况下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/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 1gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #为了数据安全再配置sync_binlog=1innodb_flush_log_at_trx_commit =1#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog#开启半同步复制 (超时时间为1s)plugin-load=rpl_semi_sync_master=semisync_master.sorpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 1000配置完成后，记得重启mysql服务[root@mysql-master ~]# systemctl restart mysqld2) 配置mysql-slave从数据库上 my.cnf文件，添加启动半同步复制的配置[root@mysql-slave1 ~]# vim /etc/my.cnf[mysqld]datadir = /var/lib/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 2gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1read_only = on #MTSslave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制slave-parallel-workers = 4 #最大线程16master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = on# 开启半同步复制plugin-load=rpl_semi_sync_slave=semisync_slave.sorpl_semi_sync_slave_enabled=1配置完成后，记得重启mysql服务[root@mysql-slave1 ~]# systemctl restart mysqld3）在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: Yes5) 在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: 0mysql> 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/记录当前的gtidmysql> 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/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 3gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = mysql-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1read_only = on #MTSslave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制slave-parallel-workers = 4 #最大线程16master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = on # 开启半同步复制plugin-load=rpl_semi_sync_slave=semisync_slave.sorpl_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.sqlmysql> 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从数据库上当前的gtidmysql> 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-slave2mysql> 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-slave24）现在测试下同步效果在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 中间件产品 ！
ProxySQL读写分离环境部署记录
下面操作是在mysql-proxy代理层节点上完成的（需要注意： 两个从库都要开启 read_only=on）：
1）先在本机安装mysql客户端，用于在本机连接到ProxySQL的管理接口
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo[mariadb] name = MariaDBbaseurl = http://yum.mariadb.org/10.3.5/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1安装mysql-clinet客户端[root@mysql-proxy ~]# yum install -y MariaDB-client=================================================如果遇到报错：Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64 You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest解决办法：[root@mysql-proxy ~]# rpm -qa|grep mariadb*mariadb-libs-5.5.56-2.el7.x86_64[root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps[root@mysql-proxy ~]# yum install -y MariaDB-client |
2）安装ProxySQL （中文安装手册: https://github.com/malongshuai/proxysql/wiki）
|
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
|
[root@mysql-proxy ~]# vim /etc/yum.repos.d/proxysql.repo[proxysql_repo]name= ProxySQL YUM repositorybaseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasevergpgcheck=1gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key执行安装[root@mysql-proxy ~]# yum -y install proxysql[root@mysql-proxy ~]# proxysql --versionProxySQL version 1.4.13-15-g69d4207, codename Truls启动ProxySQL[root@mysql-proxy ~]# chkconfig proxysql on[root@mysql-proxy ~]# systemctl start proxysql [root@mysql-proxy ~]# systemctl status proxysql启动后会监听两个端口，默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。[root@mysql-proxy ~]# netstat -tunlpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 23940/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 23940/proxysql |
3）向ProxySQL中添加MySQL节点
|
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
|
ProxySQL也是有管理接口和客户端接口，通过配置文件/etc/proxysql.cnf可以看到管理和客户端接口的信息.[root@mysql-proxy ~]# cat /etc/proxysql.cnf .........admin_variables={ admin_credentials="admin:admin"# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" mysql_ifaces="0.0.0.0:6032"# refresh_interval=2000# debug=true}mysql_variables={ threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000# interfaces="0.0.0.0:6033;/tmp/proxysql.sock" interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000..........通过上面，可以看到管理接口的端口是6032，账号密码是admin(可以动态修改),允许客户端连接， 客户端接口的端口是6033，账号密码通过管理接口去设置。在mysql-proxy本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin.下面是通过管理端口6032去连接的(下面连接命令中后面的--prompt 'admin'字段可以不加，也是可以登录进去的)[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> 'Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.admin> show databases;+-----+---------------+-------------------------------------+| seq | name | file |+-----+---------------+-------------------------------------+| 0 | main | || 2 | disk | /var/lib/proxysql/proxysql.db || 3 | stats | || 4 | monitor | || 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |+-----+---------------+-------------------------------------+5 rows in set (0.001 sec)ProxySQL提供了几个库，每个库都有各自的意义；- main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容， 不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。- disk 是持久化到硬盘的配置，sqlite数据文件。- stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。- monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。主要注意main和monitor数据库中的表admin> show tables from main;+--------------------------------------------+| tables |+--------------------------------------------+| global_variables || mysql_collations || mysql_group_replication_hostgroups || mysql_query_rules || mysql_query_rules_fast_routing || mysql_replication_hostgroups || mysql_servers || mysql_users || proxysql_servers || runtime_checksums_values || runtime_global_variables || runtime_mysql_group_replication_hostgroups || runtime_mysql_query_rules || runtime_mysql_query_rules_fast_routing || runtime_mysql_replication_hostgroups || runtime_mysql_servers || runtime_mysql_users || runtime_proxysql_servers || runtime_scheduler || scheduler |+--------------------------------------------+20 rows in set (0.001 sec)几个重要字段含义：global_variables 设置变量，包括监听的端口、管理账号等。mysql_collations 相关字符集和校验规则。mysql_query_rules 定义查询路由规则。admin> show tables from monitor;+------------------------------------+| tables |+------------------------------------+| mysql_server_connect_log || mysql_server_group_replication_log || mysql_server_ping_log || mysql_server_read_only_log || mysql_server_replication_lag_log |+------------------------------------+5 rows in set (0.000 sec)runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 10 表示写组，20表示读组。admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306);Query OK, 1 row affected (0.001 sec)admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306);Query OK, 1 row affected (0.000 sec)admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306);Query OK, 1 row affected (0.000 sec)查看这3个节点是否插入成功，以及它们的状态。admin> select * from mysql_servers\G;*************************** 1. row *************************** hostgroup_id: 10 hostname: 172.16.60.211 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment:*************************** 2. row *************************** hostgroup_id: 10 hostname: 172.16.60.212 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment:*************************** 3. row *************************** hostgroup_id: 10 hostname: 172.16.60.213 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment:3 rows in set (0.000 sec)ERROR: No query specifiedadmin>如上修改后，加载到RUNTIME，并保存到diskadmin> load mysql servers to runtime;Query OK, 0 rows affected (0.005 sec)admin> save mysql servers to disk; Query OK, 0 rows affected (0.468 sec) |
4）监控后端MySQL节点
|
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
|
如上添加Mysql节点之后，还需要监控这些后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication client权限。即在mysql-master主数据库节点行执行：mysql> create user monitor@'172.16.60.%' identified by 'P@ssword1!';Query OK, 0 rows affected (0.08 sec)mysql> grant replication client on *.* to monitor@'172.16.60.%';Query OK, 0 rows affected (0.07 sec)mysql> flush privileges;Query OK, 0 rows affected (0.07 sec)然后回到mysql-proxy代理层节点上配置监控admin> set mysql-monitor_username='monitor';Query OK, 1 row affected (0.001 sec)admin> set mysql-monitor_password='P@ssword1!';Query OK, 1 row affected (0.000 sec)修改后，加载到RUNTIME，并保存到diskadmin> load mysql variables to runtime;Query OK, 0 rows affected (0.001 sec)admin> save mysql variables to disk;Query OK, 96 rows affected (0.180 sec)验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。以下是连接是否正常的监控(对connect指标的监控)：(在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常)admin> select * from mysql_server_connect_log;+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+| hostname | port | time_start_us | connect_success_time_us | connect_error |+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+| 172.16.60.213 | 3306 | 1547436747566683 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547436748167350 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436748767837 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436807566915 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.211 | 3306 | 1547436808112262 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547436808657603 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.213 | 3306 | 1547436867566829 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547436868151063 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436868735236 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436927567060 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.211 | 3306 | 1547436928247532 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547436928927772 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547436987567058 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436988049160 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547436988531003 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.212 | 3306 | 1547437047567194 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547437048132480 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547437048697765 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.211 | 3306 | 1547437107567258 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547437108310411 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547437109053480 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.213 | 3306 | 1547437167567303 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547437168158076 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547437168748763 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547437227567525 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.211 | 3306 | 1547437228288921 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547437229010153 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.211 | 3306 | 1547437287567412 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547437288343609 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547437289119851 | 0 | Access denied for user 'monitor'@'docker-node2' (using password: YES) || 172.16.60.213 | 3306 | 1547437295397678 | 1388 | NULL || 172.16.60.211 | 3306 | 1547437295826682 | 1377 | NULL || 172.16.60.212 | 3306 | 1547437296255659 | 8072 | NULL |+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+33 rows in set (0.001 sec)以下是对心跳信息的监控(对ping指标的监控)admin> select * from mysql_server_ping_log;+---------------+------+------------------+----------------------+------------------------------------------------------------------------+| hostname | port | time_start_us | ping_success_time_us | ping_error |+---------------+------+------------------+----------------------+------------------------------------------------------------------------+| 172.16.60.213 | 3306 | 1547436798804839 | 0 | Access denied for user 'monitor'@'172.16.60.213' (using password: YES) || 172.16.60.211 | 3306 | 1547436798875555 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.212 | 3306 | 1547436798946212 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.213 | 3306 | 1547436808804803 | 0 | Access denied for user 'monitor'@'172.16.60.213' (using password: YES) || 172.16.60.212 | 3306 | 1547436808931415 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) || 172.16.60.211 | 3306 | 1547436809058082 | 0 | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |........................................| 172.16.60.213 | 3306 | 1547437295030407 | 335 | NULL || 172.16.60.211 | 3306 | 1547437295159540 | 218 | NULL || 172.16.60.212 | 3306 | 1547437295288638 | 172 | NULL || 172.16.60.211 | 3306 | 1547437365241682 | 337 | NULL || 172.16.60.211 | 3306 | 1547437375031403 | 435 | NULL |....................................| 172.16.60.213 | 3306 | 1547437395160777 | 469 | NULL || 172.16.60.211 | 3306 | 1547437395289919 | 377 | NULL |+---------------+------+------------------+----------------------+------------------------------------------------------------------------+183 rows in set (0.001 sec)但是，read_only和replication_lag的监控日志都为空。admin> select * from mysql_server_read_only_log;Empty set (0.000 sec)admin> select * from mysql_server_replication_lag_log;Empty set (0.000 sec)例如，指定写组的id为10，读组的id为20。admin> insert into mysql_replication_hostgroups values(10,20,1);Query OK, 1 row affected (0.000 sec)在该配置加载到RUNTIME生效之前，先查看下各mysql server所在的组。admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;+--------------+---------------+------+--------+--------+| hostgroup_id | hostname | port | status | weight |+--------------+---------------+------+--------+--------+| 10 | 172.16.60.211 | 3306 | ONLINE | 1 || 10 | 172.16.60.212 | 3306 | ONLINE | 1 || 10 | 172.16.60.213 | 3306 | ONLINE | 1 |+--------------+---------------+------+--------+--------+3 rows in set (0.000 sec)3个节点都在hostgroup_id=10的组中。现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。admin> load mysql servers to runtime;Query OK, 0 rows affected (0.005 sec)admin> save mysql servers to disk;Query OK, 0 rows affected (0.406 sec)一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。例如，此处所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。现在看结果admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;+--------------+---------------+------+--------+--------+| hostgroup_id | hostname | port | status | weight |+--------------+---------------+------+--------+--------+| 10 | 172.16.60.211 | 3306 | ONLINE | 1 || 20 | 172.16.60.212 | 3306 | ONLINE | 1 || 20 | 172.16.60.213 | 3306 | ONLINE | 1 |+--------------+---------------+------+--------+--------+3 rows in set (0.000 sec)admin> select * from mysql_server_read_only_log;+---------------+------+------------------+-----------------+-----------+-------+| hostname | port | time_start_us | success_time_us | read_only | error |+---------------+------+------------------+-----------------+-----------+-------+| 172.16.60.212 | 3306 | 1547438982938183 | 694 | 1 | NULL || 172.16.60.211 | 3306 | 1547438982950903 | 741 | 0 | NULL |............................| 172.16.60.211 | 3306 | 1547439054961006 | 517 | 0 | NULL || 172.16.60.213 | 3306 | 1547439054977658 | 479 | 1 | NULL |+---------------+------+------------------+-----------------+-----------+-------+147 rows in set (0.001 sec) |
5）配置mysql_users
|
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
|
上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。首先，在mysql-master主数据库节点上执行：(只需master执行即可，会复制给两个slave)mysql> grant all on *.* to root@'172.16.60.%' identified by 'passwd';Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> grant all on *.* to sqlsender@'172.16.60.%' identified by 'P@ssword1!';Query OK, 0 rows affected, 1 warning (0.15 sec)mysql> flush privileges;Query OK, 0 rows affected (0.06 sec)然后回到mysql-proxy代理层节点，配置mysql_users表，将刚才的两个用户添加到该表中。admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);Query OK, 1 row affected (0.001 sec)admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);Query OK, 1 row affected (0.000 sec)admin> load mysql users to runtime;Query OK, 0 rows affected (0.001 sec)admin> save mysql users to disk;Query OK, 0 rows affected (0.108 sec)mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup：- username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。- password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行 select password(PASSWORD)，然后将加密结果复制到该字段。- default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认 情况下将路由到hostgroup_id=10组中的某个节点。admin> select * from mysql_users\G*************************** 1. row *************************** username: root password: passwd active: 1 use_ssl: 0 default_hostgroup: 10 default_schema: NULL schema_locked: 0transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000*************************** 2. row *************************** username: sqlsender password: P@ssword1! active: 1 use_ssl: 0 default_hostgroup: 10 default_schema: NULL schema_locked: 0transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 100002 rows in set (0.000 sec)虽然这里没有详细介绍mysql_users表，但上面标注了"注意本行"的两个字段必须要引起注意。只有active=1的用户才是有效的用户。至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。我们期望的值为1，所以在继续下面的步骤之前，先查看下这个值，如果为0，则执行下面的语句修改为1。admin> update mysql_users set transaction_persistent=1 where username='root';Query OK, 1 row affected (0.000 sec)admin> update mysql_users set transaction_persistent=1 where username='sqlsender';Query OK, 1 row affected (0.000 sec)admin> load mysql users to runtime;Query OK, 0 rows affected (0.001 sec)admin> save mysql users to disk;Query OK, 0 rows affected (0.098 sec)然后，另开一个终端，分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id" #这是通过转发端口6033连接的，连接的是转发到后端真正的数据库!+-------------+| @@server_id |+-------------+| 1 |+-------------+[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"+--------------------+| Database |+--------------------+| information_schema || kevin || mysql || performance_schema || proxy_test || sys |+--------------------+[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);'[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'+----------------------+| Tables_in_proxy_test |+----------------------+| t |+----------------------+ |
6）读写分离：配置路由规则
|
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
|
ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。本案例作为一个入门配置，实现一个最简单的语句级路由规则，从而实现读写分离。必须注意，这只是实验，实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个:mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后才支持该快速路由表。本案例只介绍第一个表。插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);Query OK, 2 rows affected (0.001 sec) admin> load mysql query rules to runtime;Query OK, 0 rows affected (0.000 sec) admin> save mysql query rules to disk;Query OK, 0 rows affected (0.179 sec) 需要注意： select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。 再来测试下，读操作是否路由给了hostgroup_id=20的读组。[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'+-------------+| @@server_id |+-------------+| 3 |+-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'+-------------+| @@server_id |+-------------+| 2 |+-------------+ 读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;'+-------------+| @@server_id |+-------------+| 1 |+-------------++-------------+| @@server_id |+-------------+| 3 |+-------------+ 显然，一切都按照预期进行。最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。以下是该表的一个输出格式示例(和本案例无关)。admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;+----+----------+------------+----------------------------------+| hg | sum_time | count_star | digest_text |+----+----------+------------+----------------------------------+| 10 | 236771 | 1 | create table t(id int) || 10 | 53767 | 1 | create database proxy_test || 20 | 10344 | 1 | select @@server_id || 20 | 3350 | 3 | select @@server_id || 10 | 2469 | 2 | SELECT DATABASE() || 10 | 1678 | 2 | select @@server_id || 10 | 1193 | 1 | show databases || 10 | 1191 | 1 | start transaction || 10 | 815 | 1 | show tables from proxy_test || 10 | 289 | 1 | reate table t(id int) || 10 | 162 | 1 | commit || 10 | 0 | 6 | select @@version_comment limit ? || 10 | 0 | 4 | select @@version_comment limit ? || 10 | 0 | 1 | select @@version_comment limit ? |+----+----------+------------+----------------------------------+14 rows in set (0.002 sec) |
7）最后测试通过proxysql插件进行读写分离
|
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
|
mysql-master主数据库查看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-slave1和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) 在mysql-proxy代理层节点，通过proxysql进行数据写入，并查看[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");'[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id=10' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+-----------+| id | name |+----+-----------+| 5 | hefei || 8 | beijing || 11 | changping || 12 | wangjing || 21 | zhongguo || 22 | xianggang || 23 | taiwan | 在mysql-master主数据库节点查看mysql> select * from kevin.haha;+----+-----------+| id | name |+----+-----------+| 5 | hefei || 8 | beijing || 11 | changping || 12 | wangjing || 21 | zhongguo || 22 | xianggang || 23 | taiwan |+----+-----------+7 rows in set (0.00 sec) 在mysql-slave1和mysql-slave2从数据库节点查看mysql> select * from kevin.haha;+----+-----------+| id | name |+----+-----------+| 5 | hefei || 8 | beijing || 11 | changping || 12 | wangjing || 21 | zhongguo || 22 | xianggang || 23 | taiwan |+----+-----------+7 rows in set (0.00 sec) 发现在客户端通过proxysql插件更新的数据，已经写到mysql-master主数据库上，并同步到mysql-slave1和mysql-slave2两个从数据库上了！==========================需要注意=======================上面测试的客户机是在mysql-proxy本机，所以用127.0.0.1.如果是在别的客户机上，那么连接命令中就用proxysql的地址，即"mysql -uroot -ppasswd -P6033 -h172.16.60.214"比如在mysql-slave2机器上连接proxysql插件：[root@mysql-slave2 ~]# mysql -uroot -ppasswd -P6033 -h172.16.60.214mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 47Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || kevin || mysql || performance_schema || proxy_test || sys |+--------------------+6 rows in set (0.00 sec)删除上面创建的proxy_test测试库mysql> drop database proxy_test;Query OK, 1 row affected (0.16 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || kevin || mysql || performance_schema || sys |+--------------------+5 rows in set (0.00 sec)mysql> select * from kevin.haha;+----+-----------+| id | name |+----+-----------+| 5 | hefei || 8 | beijing || 11 | changping || 12 | wangjing || 21 | zhongguo || 22 | xianggang || 23 | taiwan |+----+-----------+7 rows in set (0.00 sec) |
至此，MySQL5.7基于GTID主从复制+并行复制+半同步复制+读写分离的环境部署工作已完成。
