MySQL Group Replication mgr 单主 proxysql 读写分离配置过程

GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; /mgr/mysql/bin/mysql -h227.0.0.1 -P24802 <addition_to_sys.sql [root@mgr1 ~]# cat addition_to_sys.sql USE sys; DELIMITER $$ CREATE FUNCTION IFZERO(a INT, b INT) RETURNS INT DETERMINISTIC RETURN IF(a = 0, b, a)$$ CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT) RETURNS INT DETERMINISTIC RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$ CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000)) RETURNS TEXT(10000) DETERMINISTIC RETURN GTID_SUBTRACT(g, '')$$ CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000)) RETURNS INT DETERMINISTIC BEGIN DECLARE result BIGINT DEFAULT 0; DECLARE colon_pos INT; DECLARE next_dash_pos INT; DECLARE next_colon_pos INT; DECLARE next_comma_pos INT; SET gtid_set = GTID_NORMALIZE(gtid_set); SET colon_pos = LOCATE2(':', gtid_set, 1); WHILE colon_pos != LENGTH(gtid_set) + 1 DO SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1); SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1); SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1); IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN SET result = result + SUBSTR(gtid_set, next_dash_pos + 1, LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) - SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1; ELSE SET result = result + 1; END IF; SET colon_pos = next_colon_pos; END WHILE; RETURN result; END$$ CREATE FUNCTION gr_applier_queue_length() RETURNS INT DETERMINISTIC BEGIN RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), (SELECT @@global.GTID_EXECUTED) ))); END$$ CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC BEGIN RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id)); END$$ CREATE VIEW gr_member_routing_candidate_status AS SELECT sys.gr_member_in_primary_partition() as viable_candidate, IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only', 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only, sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$ DELIMITER ; [root@mgr1 ~]# sz addition_to_sys.sql

[root@mgr1 proxysql]# /etc/init.d/proxysql start Starting ProxySQL: 2019-07-19 03:48:26 [INFO] Using config file /etc/proxysql.cnf 2019-07-19 03:48:26 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates. DONE! [root@mgr1 proxysql]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET mysql-monitor_username='rootuser'; Query OK, 1 row affected (0.00 sec) mysql> SET mysql-monitor_password='123456'; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('rootuser','123456',10); Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24801); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24802); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24803); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24804); Query OK, 1 row affected (0.00 sec)

mysql> 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.01 sec) mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime; Query OK, 0 rows affected (0.06 sec) Query OK, 0 rows affected (0.19 sec) Query OK, 0 rows affected (0.04 sec) Query OK, 116 rows affected (0.02 sec) Query OK, 32 rows affected (0.08 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 127.0.0.1 | 24803 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 5 rows in set (0.04 sec) mysql> select * from scheduler; Empty set (0.00 sec) mysql> exit Bye

[root@mgr1 proxysql]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24802 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 201 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@mgr1 proxysql]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> exit Bye

[root@mgr1 proxysql]# /mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s2/s2.cnf & [root@mgr1 proxysql]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24802 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+-----------+-------------+-------------+--------------+ | group_replication_applier | | | NULL | OFFLINE | +---------------------------+-----------+-------------+-------------+--------------+ 1 row in set (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.36 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 5c7975ec-a9cd-11e9-a8c9-0800273906ff | mgr1 | 24801 | ONLINE | | group_replication_applier | 69908c35-a9cd-11e9-8b78-0800273906ff | mgr1 | 24802 | ONLINE | | group_replication_applier | 79ca1c48-a9cd-11e9-9526-0800273906ff | mgr1 | 24803 | ONLINE | | group_replication_applier | e9ef573e-a9dc-11e9-8003-0800273906ff | mgr1 | 24804 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@mgr1 proxysql]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.01 sec) mysql> exit Bye [root@mgr1 proxysql]# cd [root@mgr1 ~]# cat test.sh for i in {1..100} do /mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "SELECT @@server_id;" #/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "BEGIN;SELECT @@server_id;commit;" done [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 61 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 39

[root@mgr1 ~]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 104 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime; Query OK, 0 rows affected (0.30 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.04 sec) Query OK, 116 rows affected (0.01 sec) Query OK, 32 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers tomysql> exit mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 24802 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 127.0.0.1 | 24804 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.01 sec) mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 30 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 34 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 36 [root@mgr1 ~]# vim test.sh -bash: vim: command not found [root@mgr1 ~]# vi test.sh for i in {1..100} do /mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "SELECT @@server_id;" #/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "BEGIN;SELECT @@server_id;commit;" done for i in {1..100} do #/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "SELECT @@server_id;" "test.sh" 6L, 224C written [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 100 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 0 [root@mgr1 ~]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 305 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers tomysql> select * from runtime_mysql_servers; mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> exit Bye

[root@mgr1 ~]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24801 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 140 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@mgr1 ~]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 306 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 100 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 0 [root@mgr1 ~]# vi test.sh for i in {1..100} do #/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "SELECT @@server_id;" /mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "BEGIN;SELECT @@server_id;commit;" done for i in {1..100} do /mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "SELECT @@server_id;" "test.sh" 6L, 224C written [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 56 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 44 [root@mgr1 ~]# /mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s1/s1.cnf & [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 51 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 49 [root@mgr1 ~]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24801 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+-----------+-------------+-------------+--------------+ | group_replication_applier | | | NULL | OFFLINE | +---------------------------+-----------+-------------+-------------+--------------+ 1 row in set (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.27 sec) mysql> exit Bye [root@mgr1 ~]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24801 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 5c7975ec-a9cd-11e9-a8c9-0800273906ff | mgr1 | 24801 | ONLINE | | group_replication_applier | 69908c35-a9cd-11e9-8b78-0800273906ff | mgr1 | 24802 | ONLINE | | group_replication_applier | 79ca1c48-a9cd-11e9-9526-0800273906ff | mgr1 | 24803 | ONLINE | | group_replication_applier | e9ef573e-a9dc-11e9-8003-0800273906ff | mgr1 | 24804 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@mgr1 ~]# sh test.sh >test.txt [root@mgr1 ~]# cat test.txt |grep 1 |wc -l 28 [root@mgr1 ~]# cat test.txt |grep 2 |wc -l 0 [root@mgr1 ~]# cat test.txt |grep 3 |wc -l 28 [root@mgr1 ~]# cat test.txt |grep 4 |wc -l 44 [root@mgr1 ~]# /mgr/mysql/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 707 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from runtime_mysql_servers; +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 127.0.0.1 | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.01 sec) mysql> exit Bye

SET mysql-monitor_username='rootuser'; SET mysql-monitor_password='123456'; INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('rootuser','123456',10); insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24801); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24802); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24803); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',24804); 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); save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime;

相关阅读