成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

MySQLGroupReplicationmgr單主proxysql讀寫(xiě)分離配置過(guò)程

1、前期準(zhǔn)備,mgr安裝見(jiàn)上一篇文章

創(chuàng)新互聯(lián)公司專(zhuān)注為客戶(hù)提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站制作、成都網(wǎng)站制作、扶余網(wǎng)絡(luò)推廣、小程序開(kāi)發(fā)、扶余網(wǎng)絡(luò)營(yíng)銷(xiāo)、扶余企業(yè)策劃、扶余品牌公關(guān)、搜索引擎seo、人物專(zhuān)訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供扶余建站搭建服務(wù)，24小時(shí)服務(wù)熱線：18980820575，官方網(wǎng)址：jinyejixie.com

2、創(chuàng)建用戶(hù)和導(dǎo)入腳本

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現(xiàn)有結(jié)構(gòu)及其主節(jié)點(diǎn)信息

[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
Bye

4、proxysql啟動(dòng)并配置

[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、添加監(jiān)控用戶(hù)和后端連接用戶(hù)

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、配置默認(rèn)組信息，組ID含義如下寫(xiě)組：10備寫(xiě)組：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、添加服務(wù)器地址

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、添加路由規(guī)則并保持

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、查看服務(wù)器配置和運(yùn)行時(shí)服務(wù)器配置

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

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

mysql> select * from scheduler;
Empty set (0.00 sec)

mysql> exit
Bye

10、關(guān)閉當(dāng)前主服務(wù)mysql測(cè)試

[root@mgr1 proxysql]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24802                    
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 201
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@mgr1 proxysql]# /mgr/mysql/bin/mysql  -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> exit
Bye

11、重新開(kāi)啟原來(lái)的主服務(wù)器

[root@mgr1 proxysql]# /mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s2/s2.cnf &
[root@mgr1 proxysql]# /mgr/mysql/bin/mysql -h227.0.0.1 -P24802                   
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.36 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5c7975ec-a9cd-11e9-a8c9-0800273906ff | mgr1        |       24801 | ONLINE       |
| group_replication_applier | 69908c35-a9cd-11e9-8b78-0800273906ff | mgr1        |       24802 | ONLINE       |
| group_replication_applier | 79ca1c48-a9cd-11e9-9526-0800273906ff | mgr1        |       24803 | ONLINE       |
| group_replication_applier | e9ef573e-a9dc-11e9-8003-0800273906ff | mgr1        |       24804 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
[root@mgr1 proxysql]# /mgr/mysql/bin/mysql  -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> exit
Bye
[root@mgr1 proxysql]# cd
[root@mgr1 ~]# cat test.sh 

for i in {1..100} 
do 
/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13  -P6033 -e "SELECT @@server_id;"
#/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "BEGIN;SELECT @@server_id;commit;"
done
[root@mgr1 ~]# sh test.sh >test.txt           
[root@mgr1 ~]# cat test.txt |grep 2 |wc -l
61
[root@mgr1 ~]# cat test.txt |grep 3 |wc -l 
0
[root@mgr1 ~]# cat test.txt |grep 1 |wc -l 
0
[root@mgr1 ~]# cat test.txt |grep 4 |wc -l 
39

12、重新保持一下觀察

[root@mgr1 ~]# /mgr/mysql/bin/mysql  -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 104
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime;
Query OK, 0 rows affected (0.30 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 116 rows affected (0.01 sec)

Query OK, 32 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers tomysql> exit                                                                                                                                                                                          mysql> select * from runtime_mysql_servers;
+--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port  | gtid_port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 127.0.0.1 | 24801 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24803 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24802 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24804 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 127.0.0.1 | 24802 | 0         | OFFLINE_HARD | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 127.0.0.1 | 24804 | 0         | OFFLINE_HARD | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------+-------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.01 sec)

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

mysql> exit
Bye
[root@mgr1 ~]# sh test.sh >test.txt
[root@mgr1 ~]# cat test.txt |grep 1 |wc -l                                      
0
[root@mgr1 ~]# cat test.txt |grep 2 |wc -l 
30
[root@mgr1 ~]# cat test.txt |grep 3 |wc -l 
34
[root@mgr1 ~]# cat test.txt |grep 4 |wc -l 
36
[root@mgr1 ~]# vim test.sh 
-bash: vim: command not found
[root@mgr1 ~]# vi test.sh  
for i in {1..100}
do
/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13  -P6033 -e "SELECT @@server_id;"
#/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13 -P6033 -e "BEGIN;SELECT @@server_id;commit;"
done

for i in {1..100}
do
#/mgr/mysql/bin/mysql -urootuser -p123456 -h292.168.56.13  -P6033 -e "SELECT @@server_id;"
"test.sh" 6L, 224C written
[root@mgr1 ~]# sh test.sh >test.txt        
[root@mgr1 ~]# cat test.txt |grep 1 |wc -l
100
[root@mgr1 ~]# cat test.txt |grep 2 |wc -l 
0
[root@mgr1 ~]# cat test.txt |grep 3 |wc -l 
0
[root@mgr1 ~]# cat test.txt |grep 4 |wc -l 
0
[root@mgr1 ~]# /mgr/mysql/bin/mysql  -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 305
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;load mysql users to runtime;load mysql servers tomysql> select * from runtime_mysql_servers;                                                                                                                                                          mysql> select * from runtime_mysql_servers;                                                                                                                                                          +--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 127.0.0.1 | 24801 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24803 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24802 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 30           | 127.0.0.1 | 24804 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

mysql> exit
Bye

13、重啟當(dāng)前主服務(wù)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
Bye

14、關(guān)鍵命令備份

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;

本文名稱(chēng)：MySQLGroupReplicationmgr單主proxysql讀寫(xiě)分離配置過(guò)程
網(wǎng)站路徑：http://jinyejixie.com/article28/ghdocp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動(dòng)態(tài)網(wǎng)站、網(wǎng)站設(shè)計(jì)、App開(kāi)發(fā)、小程序開(kāi)發(fā)、服務(wù)器托管、軟件開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)公司
独山县| 嵊州市| 广宁县| 博爱县| 伊川县| 鸡西市| 沙田区| 山东省| 武胜县| 榆社县| 常州市| 蓝田县| 正宁县| 布尔津县| 永福县| 凌云县| 裕民县| 咸阳市| 石屏县| 遂宁市| 汶川县| 云霄县| 启东市| 黄龙县| 常州市| 云南省| 新安县| 陇川县| 公安县| 弥渡县| 交城县| 平舆县| 宜君县| 沙雅县| 商洛市| 孟州市| 布拖县| 白河县| 兴文县| 大城县| 庆城县|