MySQLGroupReplicationmgr单主proxysql读写分离配置过程
1、前期准备,mgr安装见上一篇文章
创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于网站制作、成都网站制作、扶余网络推广、小程序开发、扶余网络营销、扶余企业策划、扶余品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联公司为所有大学生创业者提供扶余建站搭建服务，24小时服务热线：18980820575，官方网址：www.cdcxhl.com
2、创建用户和导入脚本
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 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 192
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> 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> exit
Bye4、proxysql启动并配置
[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.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> exit
Bye10、关闭当前主服务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 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
Bye11、重新开启原来的主服务器
[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
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 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
Bye13、重启当前主服务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 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
Bye14、关键命令备份
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;
本文名称：MySQLGroupReplicationmgr单主proxysql读写分离配置过程
网站路径：https://www.cdcxhl.com/article28/ghdocp.html
成都网站建设公司_创新互联，为您提供动态网站、网站设计、App开发、小程序开发、服务器托管、软件开发
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联
- 高防秒解服务器托管哪家好？要多少钱？ 2022-10-04
- 服务器托管使用固态硬盘好吗 2022-10-08
- 企业选择服务器托管的5个优势 2022-08-08
- 服务器托管的好处有什么呢？ 2022-10-02
- 武汉服务器托管费用要多少钱？武汉服务器托管时长有哪些？ 2022-10-05
- 服务器租用、服务器托管、云服务器：哪个适合你 2021-03-02
- 服务器托管常见故障有哪些？托管服务器出现故障如何处理？ 2022-10-03
- 服务器托管难？4个技巧帮你选择好的机房 2022-10-08
- 哪些行业适合美国高防服务器托管业务？ 2022-10-09
- 服务器托管一年的价格怎么算？ 2022-10-08
- 什么是服务器托管?服务器托管有什么用 2022-10-07
- 服务器托管硬件配置如何选择? 2022-10-02