再看看节点的分组调整情况：
admin> select hostgroup_id, hostname, port,status from runtime_mysql_servers; +--------------+----------------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+----------------+------+--------+ | 10 | 192.168.100.22 | 3306 | ONLINE | | 30 | 192.168.100.24 | 3306 | ONLINE | | 30 | 192.168.100.23 | 3306 | ONLINE | +--------------+----------------+------+--------+查看对MGR的监控指标。
Admin> select hostname, port, viable_candidate, read_only, transactions_behind, error from mysql_server_group_replication_log order by time_start_us desc limit 6; +----------------+------+------------------+-----------+---------------------+-------+ | hostname | port | viable_candidate | read_only | transactions_behind | error | +----------------+------+------------------+-----------+---------------------+-------+ | 192.168.100.24 | 3306 | YES | YES | 0 | NULL | | 192.168.100.23 | 3306 | YES | YES | 0 | NULL | | 192.168.100.22 | 3306 | YES | NO | 0 | NULL | | 192.168.100.24 | 3306 | YES | YES | 0 | NULL | | 192.168.100.23 | 3306 | YES | YES | 0 | NULL | | 192.168.100.22 | 3306 | YES | NO | 0 | NULL | +----------------+------+------------------+-----------+---------------------+-------+6.配置mysql_users
在node1节点上执行：
grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';回到ProxySQL，向mysql_users表插入记录。
delete from mysql_users; insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('root','P@ssword1!',10,1); load mysql users to runtime; save mysql users to disk;7.配置测试用的读写分离规则
delete from mysql_query_rules; insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',30,1); load mysql query rules to runtime; save mysql query rules to disk;测试是否按预期进行读写分离。
mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'create database gr_test;' mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'select user,host from mysql.user;' mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'show databases;'查看语句路由状态：
admin> select hostgroup,digest_text from stats_mysql_query_digest; +-----------+----------------------------------+ | hostgroup | digest_text | +-----------+----------------------------------+ | 10 | show databases | | 30 | select user,host from mysql.user | | 10 | create database gr_test | | 10 | select @@version_comment limit ? | +-----------+----------------------------------+select语句路由到读组hg=30上，show操作按照默认主机组路由到hg=10，create操作路由到hg=10这个写组。
8.测试MGR故障转移
将MGR的某个节点停掉，例如直接关闭当前master节点node1的mysql服务。
在node1上执行：
systemctl stop mysqld然后，看看ProxySQL上的节点状态。
admin> select hostgroup_id, hostname, port,status from runtime_mysql_servers; +--------------+----------------+------+---------+ | hostgroup_id | hostname | port | status | +--------------+----------------+------+---------+ | 10 | 192.168.100.23 | 3306 | ONLINE | | 40 | 192.168.100.22 | 3306 | SHUNNED | | 30 | 192.168.100.24 | 3306 | ONLINE | +--------------+----------------+------+---------+结果显示node1的状态为SHUNNED，表示该节点被ProxySQL避开了。且node2节点移到了hg=10的组中，说明该节点被选举为了新的Master节点。
再将node1加回组中。在node1上执行：
shell> systemctl start mysqld mysql> start group_replication;然后，看看ProxySQL上的节点状态。
admin> select hostgroup_id, hostname, port,status from runtime_mysql_servers; +--------------+----------------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+----------------+------+--------+ | 10 | 192.168.100.23 | 3306 | ONLINE | | 30 | 192.168.100.22 | 3306 | ONLINE | | 30 | 192.168.100.24 | 3306 | ONLINE | +--------------+----------------+------+--------+可见，node1已经重新ONLINE。