【MySQL】数据库中间件--ProxySQL+MGR

[root@wallet01 ~]# mysql -uroot -p < addition_to_sys.sql Enter password:[root@wallet01 ~]# mysql -uroot -pEnter password:mysql> select * from sys.gr_member_routing_candidate_status;+------------------+-----------+---------------------+----------------------+| viable_candidate | read_only | transactions_behind | transactions_to_cert |+------------------+-----------+---------------------+----------------------+| YES              | NO        |                   0 |                    0 |+------------------+-----------+---------------------+----------------------+1 row in set (0.00 sec)mysql> create user monitor@'192.168.40.%' identified by 'monitor@2019';   Query OK, 0 rows affected (0.11 sec)mysql> grant select on sys.* to monitor@'192.168.40.%'; Query OK, 0 rows affected (0.04 sec)[root@wallet02 ~]# mysql -uroot -pEnter password:mysql> select * from sys.gr_member_routing_candidate_status;+------------------+-----------+---------------------+----------------------+| viable_candidate | read_only | transactions_behind | transactions_to_cert |+------------------+-----------+---------------------+----------------------+| YES              | YES       |                   0 |                    0 |+------------------+-----------+---------------------+----------------------+1 row in set (0.00 sec)[root@wallet03 ~]# mysql -uroot -pEnter password:mysql> select * from sys.gr_member_routing_candidate_status;+------------------+-----------+---------------------+----------------------+| viable_candidate | read_only | transactions_behind | transactions_to_cert |+------------------+-----------+---------------------+----------------------+| YES              | YES       |                   0 |                    0 |+------------------+-----------+---------------------+----------------------+1 row in set (0.00 sec)[root@wallet04 ~]# cat <insert into mysql_servers(hostgroup_id,hostname,port)       values(10,'192.168.40.34',3306),            (10,'192.168.40.35',3306),            (10,'192.168.40.36',3306);Query OK, 3 rows affected (0.00 sec)Admin>load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec)Admin>save mysql servers to disk;Query OK, 0 rows affected (0.42 sec)Admin>select hostgroup_id,hostname,port,status,weight from mysql_servers;+--------------+---------------+------+--------+--------+| hostgroup_id | hostname      | port | status | weight |+--------------+---------------+------+--------+--------+| 10           | 192.168.40.34 | 3306 | ONLINE | 1      || 10           | 192.168.40.35 | 3306 | ONLINE | 1      || 10           | 192.168.40.36 | 3306 | ONLINE | 1      |+--------------+---------------+------+--------+--------+3 rows in set (0.00 sec)Admin>set mysql-monitor_username='monitor';Query OK, 1 row affected (0.00 sec)Admin>set mysql-monitor_password='monitor@2019';Query OK, 1 row affected (0.00 sec)Admin>load mysql variables to runtime;Query OK, 0 rows affected (0.00 sec)Admin>save mysql variables to disk;Query OK, 136 rows affected (0.06 sec)Admin>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(10,20,30,40,1,1,0,0);Query OK, 1 row affected (0.00 sec)Admin>load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec)Admin>save mysql servers to disk;Query OK, 0 rows affected (0.49 sec)Admin>select * from mysql_group_replication_hostgroups\G*************************** 1. row ***************************       writer_hostgroup: 10backup_writer_hostgroup: 20       reader_hostgroup: 30      offline_hostgroup: 40                 active: 1            max_writers: 1  writer_is_also_reader: 0max_transactions_behind: 0                comment: NULL1 row in set (0.00 sec)Admin>select hostgroup_id, hostname, port,status from runtime_mysql_servers;+--------------+---------------+------+--------+| hostgroup_id | hostname      | port | status |+--------------+---------------+------+--------+| 10           | 192.168.40.34 | 3306 | ONLINE || 30           | 192.168.40.35 | 3306 | ONLINE || 30           | 192.168.40.36 | 3306 | ONLINE |+--------------+---------------+------+--------+3 rows in set (0.00 sec)Admin>insert into mysql_users(username,password,default_hostgroup,transaction_persistent)       values('sakila','sakila@2019',10,1);Query OK, 1 row affected (0.00 sec)Admin>load mysql users to runtime;Query OK, 0 rows affected (0.00 sec)Admin>save mysql users to disk;Query OK, 0 rows affected (0.14 sec)Admin>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);Query OK, 2 rows affected (0.00 sec)Admin>load mysql query rules to runtime;Query OK, 0 rows affected (0.00 sec)Admin>save mysql query rules to disk;Query OK, 0 rows affected (0.20 sec)Admin>select hostgroup,digest_text from stats_mysql_query_digest;+-----------+------------------------------------------------------------------------------------+| hostgroup | digest_text                                                                        |+-----------+------------------------------------------------------------------------------------+| 10        | DELETE FROM `rental` WHERE (`rental_id`=?)                                         || 30        | SELECT * FROM `store` LIMIT ?, ?                                                   || 10        | SHOW COLUMNS FROM `sakila`.`customer`                                              || 10        | SHOW COLUMNS FROM `sakila`.`actor`                                                 || 30        | SELECT * FROM `country` WHERE (`country_id`=?) LIMIT ?                             || 10        | UPDATE `country` SET `last_update`=? WHERE (`country_id`=?)                        || 30        | SELECT * FROM `country` LIMIT ?, ?                                                 |