体验ProxySQL 1.4.1对MySQL Group Replication的内置支持
2017-08-09
ProxySQL 1.4.1是ProxySQL 1.4的首个稳定版本，加入了对MySQL Group Replication的原生支持。
目前我们两套环境使用的是ProxySQL 1.3，使用proxysql_scheduler调用自定义的MySQL GR检查脚本实现故障切换。
现在1.4内置了对GR的支持，这里体验一下。
1.环境信息 #
我们的环境是3节点的MySQL GR集群，采用的是单主模式。
1mysql> show global variables like 'group_replication_single_primary_mode';
2+---------------------------------------+-------+
3| Variable_name | Value |
4+---------------------------------------+-------+
5| group_replication_single_primary_mode | ON |
6+---------------------------------------+-------+
71 row in set (0.01 sec)
8
9mysql> SELECT * FROM performance_schema.replication_group_members\G
10*************************** 1. row ***************************
11CHANNEL_NAME: group_replication_applier
12 MEMBER_ID: 22b31248-5f38-11e7-8f5d-1866da8c2439
13 MEMBER_HOST: db1
14 MEMBER_PORT: 3306
15MEMBER_STATE: ONLINE
16*************************** 2. row ***************************
17CHANNEL_NAME: group_replication_applier
18 MEMBER_ID: 22b95cdd-5f38-11e7-b20c-1866da8c2fcd
19 MEMBER_HOST: db3
20 MEMBER_PORT: 3306
21MEMBER_STATE: ONLINE
22*************************** 3. row ***************************
23CHANNEL_NAME: group_replication_applier
24 MEMBER_ID: 22bb1812-5f38-11e7-97e4-1866da8c6175
25 MEMBER_HOST: db2
26 MEMBER_PORT: 3306
27MEMBER_STATE: ONLINE
283 rows in set (0.00 sec)
29
30mysql> show global status like 'group_replication_primary_member';
31+----------------------------------+--------------------------------------+
32| Variable_name | Value |
33+----------------------------------+--------------------------------------+
34| group_replication_primary_member | 22b95cdd-5f38-11e7-b20c-1866da8c2fcd |
35+----------------------------------+--------------------------------------+
361 row in set (0.00 sec)
37
38mysql> SELECT member_host as "primary master"
39 FROM performance_schema.global_status
40 JOIN performance_schema.replication_group_members
41 WHERE variable_name = 'group_replication_primary_member'
42 AND member_id=variable_value;
43
44+----------------+
45| primary master |
46+----------------+
47| db3 |
48+----------------+
491 row in set (0.00 sec)
通过上面这些查询可以看出，目前GR集群中的单主节点为db2。
2.ProxySQL 1.4.1和MySQL Group Replication #
ProxySQL的安装这里略过，安装好并启动ProxySQL之后，接下来我们来配置ProxySQL对MySQL Group Replication的支持。
先通过管理接口登录进入ProxySQL：
1mysql -u admin -P 6032 -p -h 127.0.0.1
往mysql_servers表插入我们前面的3个GR节点信息：
1INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.61.3', 3306);
2INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.61.4', 3306);
3INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '192.168.61.5', 3306);
往global_variables表中通过变量mysql-monitor_username和mysql-monitor_password设置ProxySQL监控MySQL的用户和密码。
注意需要提前在MySQL中创建好这个用户和密码。并授予如下权限：
- 在sys.*授予SELECT权限
- 在performance_schema.*授予SELECT权限
并在MySQL中执行这个脚本，创建GR状态检查相关的函数和视图。
往mysql_group_replication_hostgroups表中插入GR复制的主机组信息：
1insert into mysql_group_replication_hostgroups
2(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)
3values (2,4,3,1,1,1,1,100);
总共分了4个组，含义是分别是：
- 1:offline group
- 2:writer group
- 3:reader group
- 4:backup group
使前面配置生效：
1save mysql servers to disk;
2load mysql servers to runtime;
接下来我们来看一下ProxySQL的SQLite中和对后端MySQL监控相关的表：
1show tables from monitor;
2+------------------------------------+
3| tables |
4+------------------------------------+
5| mysql_server_connect |
6| mysql_server_connect_log |
7| mysql_server_group_replication_log |
8| mysql_server_ping |
9| mysql_server_ping_log |
10| mysql_server_read_only_log |
11| mysql_server_replication_lag_log |
12+------------------------------------+
其中mysql_server_ping_log包含了ProxySQL对后端MySQL健康检查的日志：
1select * from mysql_server_ping_log;
2+-------------+------+------------------+----------------------+------------+
3| hostname | port | time_start_us | ping_success_time_us | ping_error |
4+-------------+------+------------------+----------------------+------------+
5| 192.168.61.3 | 3306 | 1502289675140841 | 128 | NULL |
6| 192.168.61.4 | 3306 | 1502289675150934 | 249 | NULL |
7| 192.168.61.5 | 3306 | 1502289675161045 | 259 | NULL |
8| 192.168.61.3 | 3306 | 1502289735140935 | 94 | NULL |
9| 192.168.61.4 | 3306 | 1502289735151041 | 218 | NULL |
10......
我们可以看到ProxySQL一直对后端的MySQL做健康检查，而且是成功的。
mysql_server_group_replication_log表中是对MySQL GR检查的日志：
1select * from mysql_server_group_replication_log order by time_start_us;
2+-------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
3| hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error |
4+-------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
5| 192.168.61.3 | 3306 | 1502289900133579 | 2314 | YES | YES | 0 | NULL |
6| 192.168.61.4 | 3306 | 1502289900133776 | 2143 | YES | YES | 0 | NULL |
7| 192.168.61.5 | 3306 | 1502289900133998 | 2218 | YES | NO | 0 | NULL |
8...
从GR检查日志可以看出当前db3(192.168.61.5)为GR中的单主节点。
最后我们查看一下runtime_mysql_servers确认一下MySQL GR集群中各节点的实际状态：
1 select hostgroup_id, hostname, status from runtime_mysql_servers;
2+--------------+-------------+--------+
3| hostgroup_id | hostname | status |
4+--------------+-------------+--------+
5| 2 | 192.168.61.5 | ONLINE |
6| 3 | 192.168.61.5 | ONLINE |
7| 3 | 192.168.61.4 | ONLINE |
8| 3 | 192.168.61.3 | ONLINE |
9+--------------+-------------+--------+
可以看出单主节点db3(192.168.61.5)是我们前面划分的writer group 2中的节点。接下来，可以尝试将db3的MySQL服务停掉，看ProxySQL能否完成自动切换。