MySQL Group Replication mgr 单主 proxysql 读写分离配置过程
发表于：2025-01-22 作者：千家信息网编辑
千家信息网最后更新 2025年01月22日，1、前期准备,mgr安装见上一篇文章2、创建用户和导入脚本GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; /mgr/mysq
千家信息网最后更新 2025年01月22日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安全错误
数据库的锁怎样保障安全
服务器和应用管理系统
河南十匹马网络技术智联招聘
最热门的电脑软件开发
卡桑德拉 数据库
网络安全招聘需要的证书
服务器怎么开启安全
幼儿网络安全学习
国家安全级服务器
赛酷睿 软件开发
网络技术vlsm例题讲解
内部服务器出错是啥意思
如何提高网络安全意识文章
数据库代表性人物
rpg服务器怎么样
虚拟网络安全心得
数据库录入异常类型
宽带连接服务器名称
佛山地产软件开发报价
服务器插件管理
将数据库拷贝到另一台机器上
网络技术行业
街道网络安全周
各大方舟主播的服务器怎么玩
什么数据库可以检索第一作者
服务器 saS硬盘
60岁后能不能软件开发
psn 换服务器
解压到服务器
网络安全教案幼儿园中班
yuga 数据库
