[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 ?, ? |
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
