作者：俊達

如果后端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