作者：俊达
如果后端MySQL使用了Group Replication，可通过配置mysql_group_replication_hostgroups表来实现高可用
1 mysql_group_replication_hostgroups
| 字段 | 描述 |
|---|---|
| writer_hostgroup | 写hostgroup。read_only和super_read_only OFF的节点。 |
| backup_writer_hostgroup | 如果可写的节点数超过max_writers，将超出限制的节点的hostgroup设置为backup_writer_hostgroup |
| reader_hostgroup | 读hostgroup。read_only或super_read_only ON的节点放入改hostgroup。 |
| offline_hostgroup | 如果后端mysql实例健康检查失败或group replication状态异常，则会被移入offline_hostgroup |
| active | 是否启用。 |
| max_writers | 可写节点数上限。超过该限制数量的可写节点会放入backup_writer_hostgroup |
| writer_is_also_reader | 如果设置为0，则只有只读节点加入reader_hostgroup。 |
| 如果设置成1，则writer_hostgroup中的节点会同时加入reader_hostgroup；如果设置为2，则只会把backup_writer_hostgroup中的节点加入reader_hostgroup。（如果backup_writer_hostgroup中无节点，好像也会把writer_hostgroup中的节点加入到reader_hostgroup，疑似bug）。 | |
| max_transactions_behind | |
| comment |
2 配置集群节点
insert into mysql_servers (
hostgroup_id, hostname, port, max_replication_lag)
values ( 200, '172.16.121.236', 3306, 3);
insert into mysql_servers (
hostgroup_id, hostname, port, max_replication_lag)
values ( 210, '172.16.121.237', 3306, 3);
load mysql servers to runtime;
SAVE MYSQL servers TO DISK;
3 配置集群信息
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, comment)
values(200, 201, 210, 202, 1, 1, 0, 0, 'mysql mgr cluster 1');
load mysql servers to runtime;
save mysql servers to disk;
4 创建和配置用户信息
在后端创建用户
mysql> create user 'mgr'@'%' identified by 'mgr123';
Query OK, 0 rows affected (0.06 sec)
mysql> grant select,insert,update,delete,create,drop on *.* to 'mgr'@'%';
Query OK, 0 rows affected (0.02 sec)
在proxysql配置用户信息
insert into mysql_users
(username, password, transaction_persistent, backend, frontend, default_hostgroup, comment)
values ('mgr', 'mgr123', 1, 1, 1, 200, 'backend user for mgr cluster');
load mysql users to runtime;
save mysql users to disk;
按上面的步骤配置后，就实现了基于MySQL Group Replication和ProxySQL的高可用架构。
更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw
