体验ProxySQL 1.4.1对MySQL Group Replication的内置支持

2017-08-09 阅读: ProxySQL

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_usernamemysql-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能否完成自动切换。

标题:体验ProxySQL 1.4.1对MySQL Group Replication的内置支持
本文链接:https://blog.frognew.com/2017/08/proxysql-1.4-and-mysql-group-replication.html
转载请注明出处。

目录