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

[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 <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

[root@wallet04 ~]# yum install -y proxysql

[root@wallet04 ~]# service proxysql start
Starting ProxySQL: 2019-10-18 16:40:55 [INFO] Using config file /etc/proxysql.cnf
2019-10-18 16:40:55 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.

[root@wallet04 ~]# service proxysql status
ProxySQL is running (8428).

[root@wallet04 ~]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

Admin>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 ?, ?                                                 |