MySQL Group Replication mgr 单主 proxysql 读写分离配置过程
发表于：2025-11-13 作者：千家信息网编辑
千家信息网最后更新 2025年11月13日，1、前期准备,mgr安装见上一篇文章2、创建用户和导入脚本GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; /mgr/mysq
千家信息网最后更新 2025年11月13日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安全错误
数据库的锁怎样保障安全
服务器硬盘 15k
纯js数据库交互
谁等有关机关依照网络安全法
石化网络安全宣传报道
网信部门如何应对网络安全
交通银行软件开发中心二面
服务器装什么防护软件好
销售企业实时数据库哪家好
彩票3d软件开发
蜂窝网络安全教育
我的世界1.16.4生存服务器
网络技术基础案例文库
西安卓越软件开发有限地址
国家网络安全研究院建设思路
中国电子长城服务器
无感抓拍是网络安全法的多少条
世界上最强的网络安全公司
津门论剑共话网络安全
常州中环网络技术有限公司
第四届中国汽车网络安全峰会
软件开发公司的工作特征是什么
网吧服务器连接显示器用什么线
软件开发者单位
廊坊市泰鑫互联网科技有限公司
德黑兰会议记录软件开发
数据库查询 去重
绑定阿里企业邮箱服务器类型
72岁的老太被网络技术骗了
上海服务器迁移公司电话
查看某个数据库实例
