【MySQL】數(shù)據(jù)庫中間件--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??,???????????????????????????????????????????????????|