[root@wallet01 ~]# MySQL -uroot -p < addition_to_sys.sql
Enter password:
[root@wallet01 ~]# mysql -uroot -p
Enter 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 -p
Enter 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 -p
Enter 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: 10
backup_writer_hostgroup: 20
reader_hostgroup: 30
offline_hostgroup: 40
active: 1
max_writers: 1
writer_is_also_reader: 0
max_transactions_behind: 0
comment: NULL
1 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 ?, ? |
本文名称：【MySQL】数据库中间件--ProxySQL+MGR
地址分享：
http://kswsj.cn/article/gopjdh.html
