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