MySQL Group Replication mgr 单主 proxysql 读写分离配置过程
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，1、前期准备,mgr安装见上一篇文章2、创建用户和导入脚本GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; /mgr/mysq
千家信息网最后更新 2026年11月01日MySQL Group Replication mgr 单主 proxysql 读写分离配置过程
1、前期准备,mgr安装见上一篇文章
2、创建用户和导入脚本
GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; /mgr/mysql/bin/mysql -h227.0.0.1 -P24802 =((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),'YES', 'NO' ) FROM performance_schema.replication_group_members JOINperformance_schema.replication_group_member_stats USING(member_id));END$$CREATE VIEW gr_member_routing_candidate_status AS SELECTsys.gr_member_in_primary_partition() as viable_candidate,IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROMperformance_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 3、mgr现有结构及其主节点信息
[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 192Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> show variables like '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only | OFF || read_only | OFF || super_read_only | OFF || transaction_read_only | OFF || tx_read_only | OFF |+-----------------------+-------+5 rows in set (0.01 sec)mysql> SELECT @@server_id;+-------------+| @@server_id |+-------------+| 2 |+-------------+1 row in set (0.00 sec)mysql> exitBye4、proxysql启动并配置
[root@mgr1 proxysql]# /etc/init.d/proxysql startStarting ProxySQL: 2019-07-19 03:48:26 [INFO] Using config file /etc/proxysql.cnf2019-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 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.5、添加监控用户和后端连接用户
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)6、配置默认组信息，组ID含义如下写组：10备写组：20读组：30离线组（不可用）：40
mysql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1);Query OK, 1 row affected (0.01 sec)7、添加服务器地址
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)8、添加路由规则并保持
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)9、查看服务器配置和运行时服务器配置
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> exitBye10、关闭当前主服务mysql测试
[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 201Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye11、重新开启原来的主服务器
[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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 -l61[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 3912、重新保持一下观察
[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 104Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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;"donefor 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 -l100[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 305Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye13、重启当前主服务mysql观察
[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 140Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 306Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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;"donefor 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 -l0[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 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[root@mgr1 ~]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24801Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 18Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 707Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye14、关键命令备份
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;
服务
配置
服务器
用户
信息
观察
关键
前期
含义
命令
地址
备份
篇文章
线组
结构
脚本
节点
规则
路由
上一
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
什么是科技互联网
广东猪兼强互联网科技有限公司
苏州找大状互联网科技有限公司
腾讯阿里互联网科技
北京互联网科技公司
互联网 科技创新
互联网科技革命
深圳期待互联网科技
海南科技产业互联网平台
银川新华互联网科技职业技术培训学校图片
开一家科技互联网公司需要多少钱
科技类公司包括互联网吗
互联网科技和法律行业
科技互联网海报
高科技互联网套路
互联网科技数字货币
唯美互联网科技
互联网科技对化妆品行业
网互联网络科技
互联网高科技绘画
为想互联网科技怎么样
互联网科技有限公司经营范围
阿里巴巴算科技企业还是互联网
华服互联网科技官网
互联网经济与金融科技
武汉健翔互联网络科技有限公司
乌苏新华互联网科技做什么的
张召忠谈中国互联网科技
互联网科技股为什么大涨
科技信息时代互联网发展
