當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL用中间件ProxySQL实现读写分离和主节点故障应用无感应

發(fā)布時間：2024/8/26 数据库 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL用中间件ProxySQL实现读写分离和主节点故障应用无感应小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

昨天做的用proxysql實現(xiàn)的讀寫分離，但是在實際的應(yīng)用中這樣的結(jié)構(gòu)還很不完整，如果主節(jié)點(diǎn)出現(xiàn)故障那么整個拓?fù)涞臄?shù)據(jù)庫也無法通過proxysql來調(diào)用了，所以還需要增加主節(jié)點(diǎn)故障后proxysql能夠自動切換到新的主節(jié)點(diǎn)的功能。

（MGR）組復(fù)制能夠完成主節(jié)點(diǎn)故障后推選出來新的主節(jié)點(diǎn)，不過在應(yīng)用層不可能通過改新的主節(jié)點(diǎn)的IP來連接新的主節(jié)點(diǎn)，通過mgr+proxysql可能實際主節(jié)點(diǎn)故障時應(yīng)用無感應(yīng)自動切換到新的主節(jié)點(diǎn)。

描述下上面的實現(xiàn)思路：三個節(jié)點(diǎn)使multi-primary的方式連接，應(yīng)用通過連接ProxySQL中間件，根據(jù)sql的屬性（是否為select語句）來決定連接哪一個節(jié)點(diǎn)，一個可寫節(jié)點(diǎn)，兩個只讀節(jié)點(diǎn)（其實三個都是可寫節(jié)點(diǎn)，只不過通過proxysql進(jìn)行了讀寫分離）。如果默認(rèn)的可寫節(jié)點(diǎn)掛掉的話，proxysql通過定期運(yùn)行的調(diào)度器會將另一個只讀節(jié)點(diǎn)的其中一臺設(shè)為可寫節(jié)點(diǎn)，實際主節(jié)點(diǎn)故障應(yīng)用無感應(yīng)的要求。

上述的整個過程中，應(yīng)用無需任何變動。應(yīng)用從意識發(fā)生了故障，到連接重新指向新的主，正常提供服務(wù)，秒級別的間隔。

下面做一下這個配置：

1.各server說明：

qht131????172.17.61.131????master1

qht132????172.17.61.132????master2

qht133????172.17.61.133????master3

qht134????172.17.61.134????proxysql

multi-primary的主從復(fù)制結(jié)構(gòu) ：

mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql'; ? ?
Query OK, 0 rows affected (0.41 sec) ? ?
? ??
mysql> GRANT ALL ON * . * TO??'proxysql'@'%';
Query OK, 0 rows affected (0.00 sec)
? ??
mysql> create user 'sbuser'@'%' IDENTIFIED BY 'sbpass'; ? ?
Query OK, 0 rows affected (0.00 sec) ? ?
? ??
mysql> GRANT ALL ON * . * TO 'sbuser'@'%'; ? ?
Query OK, 0 rows affected (0.00 sec) ? ?
? ??
mysql> FLUSH PRIVILEGES; ? ?
Query OK, 0 rows affected (0.07 sec)?
3.創(chuàng)建檢查MGR節(jié)點(diǎn)狀態(tài)的函數(shù)和視圖

參照前面的博客，在MGR主節(jié)點(diǎn)上執(zhí)行下面鏈接中的SQL

https://github.com/lefred/mysql_gr_routing_check/blob/master/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 ;
4.在proxysql中增加帳號

????mysql> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1);????

Query OK, 1 row affected (0.00 sec) ? ?
mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username'; ? ?
Query OK, 1 row affected (0.00 sec) ? ?
? ??
mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password'; ? ?
Query OK, 1 row affected (0.00 sec) ? ?
mysql> LOAD MYSQL SERVERS TO RUNTIME; ?
Query OK, 0 rows affected (0.23 sec) ?
??
mysql> SAVE MYSQL SERVERS TO DISK; ?
Query OK, 0 rows affected (0.08 sec) ?
測試一下能否正常登入數(shù)據(jù)庫
[root@qht134 proxysql]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| qht131?????|
+------------+
5.配置proxysql

?
mysql>??delete from mysql_servers;
Query OK, 6 rows affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.17.61.131',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.17.61.132',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.17.61.133',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.131',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.132',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql>??insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.133',3306);
Query OK, 1 row affected (0.00 sec)
?
mysql> select * from??mysql_servers ;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname??????| port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1????????????| 172.17.61.131 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
| 1????????????| 172.17.61.132 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
| 1????????????| 172.17.61.133 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
| 2????????????| 172.17.61.131 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
| 2????????????| 172.17.61.132 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
| 2????????????| 172.17.61.133 | 3306 | ONLINE | 1??????| 0???????????| 1000????????????| 0???????????????????| 0???????| 0??????????????|?????????|
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
hostgroup_id = 1代表write group，針對我們提出的限制，這個地方只配置了一個節(jié)點(diǎn)；hostgroup_id = 2代表read group，包含了MGR的所有節(jié)點(diǎn),目前只是Onlinle的，等配置過scheduler后，status就會有變化。

對于上面的hostgroup配置，默認(rèn)所有的寫操作會發(fā)送到hostgroup_id為1的online節(jié)點(diǎn)，也就是發(fā)送到寫節(jié)點(diǎn)上。所有的讀操作，會發(fā)送為hostgroup_id為2的online節(jié)點(diǎn)。

需要確認(rèn)一下沒有使用proxysql的讀寫分離規(guī)則。因為我昨天配置了這個地方，所以需要刪除，以免影響后面的測試。

mysql> delete from mysql_query_rules;
Query OK, 2 rows affected (0.50 sec)
?
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
最后我們需要將global_variables，mysql_servers、mysql_users表的信息加載到RUNTIME，更進(jìn)一步加載到DISK:

mysql> LOAD MYSQL VARIABLES TO RUNTIME; ?
Query OK, 0 rows affected (0.00 sec) ?
??
mysql> SAVE MYSQL VARIABLES TO DISK; ?
Query OK, 94 rows affected (0.02 sec) ?
??
mysql> LOAD MYSQL SERVERS TO RUNTIME; ?
Query OK, 0 rows affected (0.00 sec) ?
??
mysql> SAVE MYSQL SERVERS TO DISK; ?
Query OK, 0 rows affected (0.03 sec) ?
??
mysql> LOAD MYSQL USERS TO RUNTIME; ?
Query OK, 0 rows affected (0.00 sec) ?
??
mysql> SAVE MYSQL USERS TO DISK; ?
Query OK, 0 rows affected (0.01 sec) ?
6.配置scheduler

首先，請在Github地址https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker下載相應(yīng)的腳本

這個地址有三個腳本可供下載：

proxysql_groupreplication_checker.sh：用于multi-primary模式，可以實現(xiàn)讀寫分離，以及故障切換，同一時間點(diǎn)多個節(jié)點(diǎn)可以多寫

gr_mw_mode_cheker.sh：用于multi-primary模式，可以實現(xiàn)讀寫分離，以及故障切換，不過在同一時間點(diǎn)只能有一個節(jié)點(diǎn)能寫

gr_sw_mode_checker.sh：用于single-primary模式，可以實現(xiàn)讀寫分離，以及故障切換

由于我的環(huán)境是multi-primary模式，所以選擇/proxysql_groupreplication_checker.sh腳本。

接著，將我們提供的腳本proxysql_groupreplication_checker.sh放到目錄/var/lib/proxysql/下，并增加可以執(zhí)行的權(quán)限(重要) 。

[root@qht134 ~]# chmod a+x /var/lib/proxysql/proxysql_groupreplication_checker.sh
最后，我們在proxysql的scheduler表里面加載如下記錄，然后加載到RUNTIME使其生效，同時還可以持久化到磁盤:

mysql> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5)
????-> VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');
Query OK, 1 row affected (0.00 sec)
mysql> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
?
mysql> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.03 sec)
scheduler各column的說明：

active : 1: enable scheduler to schedule the script we provide

interval_ms : invoke one by one in cycle (eg: 5000(ms) = 5s represent every 5s invoke the script)
filename: represent the script file path
arg1~arg5: represent the input parameters the script received

腳本proxysql_groupreplication_checker.sh對應(yīng)的參數(shù)說明如下:

arg1 is the hostgroup_id for write

arg2 is the hostgroup_id for read
arg3 is the number of writers we want active at the same time
arg4 represents if we want that the member acting for writes is also candidate for reads
arg5 is the log file

schedule信息加載后，就會分析當(dāng)前的環(huán)境，mysql_servers中顯示出當(dāng)前只有qht131是可以寫的，qht132以及qht133是用來讀的。

mysql> ?select * from ?mysql_servers ;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
因為schedule的arg4我設(shè)為了0，就表示可寫的節(jié)點(diǎn)不能用于讀。那我將arg4設(shè)置為1試一下：

mysql> update scheduler set arg4=1;
Query OK, 1 row affected (0.00 sec)
?
mysql> select * from scheduler;
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| id | active | interval_ms | filename ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | arg1 | arg2 | arg3 | arg4 | arg5 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| comment |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| 1 ?| 1 ? ? ?| 10000 ? ? ? | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1 ? ?| 2 ? ?| 1 ? ?| 1 ? ?| /var/lib/proxysql/proxysql_groupreplication_checker.log | ? ? ? ? |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
1 row in set (0.00 sec)
?
mysql> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.01 sec)
?
mysql> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
?
mysql> select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------- ? -----------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_repli ? cation_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------- ? -----------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------- ? -----------+---------+----------------+---------+
6 rows in set (0.00 sec)
?
arg4設(shè)置為1之后，qht131節(jié)點(diǎn)用來寫的同時也可以被用來寫。

便于下面的測試還是將arg4設(shè)為0：

mysql> ?update scheduler set arg4=0;
Query OK, 1 row affected (0.00 sec)
?
mysql> ?SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.02 sec)
?
mysql> ?LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> ?select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
各個節(jié)點(diǎn)的gr_member_routing_candidate_status視圖也顯示了當(dāng)前節(jié)點(diǎn)是否是正常狀態(tài)的，proxysql就是讀取的這個視圖的信息來決定此節(jié)點(diǎn)是否可用。

mysql> select * from sys.gr_member_routing_candidate_status\G
*************************** 1. row ***************************
? ? viable_candidate: YES
? ? ? ? ? ?read_only: NO
?transactions_behind: 0
transactions_to_cert: 0
1 row in set (0.01 sec)
7.設(shè)置讀寫分離：

mysql> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply)
? ? -> values (1,"^SELECT",2,1);
Query OK, 1 row affected (0.00 sec)
?
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
?
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.03 sec)
match_pattern的規(guī)則是基于正則表達(dá)式的，

active表示是否啟用這個sql路由項，

match_pattern就是我們正則匹配項，
destination_hostgroup表示我們要將該類sql轉(zhuǎn)發(fā)到哪些mysql上面去，這里我們將select轉(zhuǎn)發(fā)到group 2，。
apply為1表示該正則匹配后，將不再接受其他匹配，直接轉(zhuǎn)發(fā)。

對于for update需要在gruop1上執(zhí)行，可以加上規(guī)則：

insert?into?mysql_query_rules(active,match_pattern,destination_hostgroup,apply)?values(1,'^SELECT.*FOR?UPDATE$',1,1);?

通過一個循環(huán)連接proxysql，由于是select 語句，一直連接的是qht132和qht133

[root@qht133 ~]# while true; do mysql -h 172.17.61.134 -u proxysql -pproxysql -P 6033 -e "select @@hostname, sleep(3)" 2>/dev/null; done
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht132 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht133 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht132 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht133 ? ? | ? ? ? ?0 |
+------------+----------+
8.設(shè)置故障應(yīng)用無感應(yīng)：

在讀寫分離中，我設(shè)置了qht131為可寫節(jié)點(diǎn)，qht132,qht133為只讀節(jié)點(diǎn)

如果此時qht131變成只讀模式的話，應(yīng)用能不能直接連到其它的節(jié)點(diǎn)進(jìn)行寫操作？

現(xiàn)手動將qht131變成只讀模式：

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
觀察一下mysql_servers的狀態(tài)，自動將group1的qht132改成了online,group2的qht131,qht133變成online了，就表示將qht132變?yōu)榭蓪懝?jié)點(diǎn)，其它兩個節(jié)點(diǎn)變?yōu)橹蛔x節(jié)點(diǎn)了。

mysql> select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
通過模擬的連接也可以看到select語句都連接到qht131和qht133進(jìn)行了。

[root@qht133 ~]# while true; do mysql -h 172.17.61.134 -u proxysql -pproxysql -P 6033 -e "select @@hostname, sleep(3)" 2>/dev/null; done
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht133 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht131 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht133 ? ? | ? ? ? ?0 |
+------------+----------+
+------------+----------+
| @@hostname | sleep(3) |
+------------+----------+
| qht131 ? ? | ? ? ? ?0 |
+------------+----------+
將qht131變?yōu)榭蓪懩Ｊ胶?#xff0c;mysql_servers也恢復(fù)過來了。

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ?select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
經(jīng)過測試將qht131節(jié)點(diǎn)停止組復(fù)制（stop group_replication）后，mysql_servers表的信息也會正常的切換新的節(jié)點(diǎn)。待qht131再加入到組復(fù)制后，mysql_servers也會正常的將qht131改成online狀態(tài)。

mysql> select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)

容易出現(xiàn)的錯誤：

mysql> ?select * from ?mysql_servers ;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_HARD | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)
所有節(jié)點(diǎn)都o(jì)ffline了，錯誤日志如下：

[2018-05-22 23:57:52] read node [hostgroup_id: 2, hostname: 172.17.61.133, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT'
ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'qht134' for table 'gr_member_routing_candidate_status'
[2018-05-22 23:57:55] current write node [hostgroup_id: 2, hostname: 172.17.61.131, port: 3306, isOK: 0] is not OK, we need to do switch over
ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'qht134' for table 'gr_member_routing_candidate_status'
[2018-05-22 23:57:55] read node [hostgroup_id: 2, hostname: 172.17.61.132, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT'
ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'qht134' for table 'gr_member_routing_candidate_status'
從錯誤日志上看出是權(quán)限的問題，proxysql用戶沒有足夠的權(quán)限讀取數(shù)據(jù)。

解決：

mysql> GRANT ALL ON * . * TO??'proxysql'@'%'; ?mysql> flush privileges;
mysql> ?select * from ?mysql_servers;
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname ? ? ?| port | status ? ? ? | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 ? ? ? ? ? ?| 172.17.61.131 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.132 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 1 ? ? ? ? ? ?| 172.17.61.133 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.131 | 3306 | OFFLINE_SOFT | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.132 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
| 2 ? ? ? ? ? ?| 172.17.61.133 | 3306 | ONLINE ? ? ? | 1 ? ? ?| 0 ? ? ? ? ? | 1000 ? ? ? ? ? ?| 0 ? ? ? ? ? ? ? ? ? | 0 ? ? ? | 0 ? ? ? ? ? ? ?| ? ? ? ? |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)
參考：

http://lefred.be/content/ha-with-mysql-group-replication-and-proxysql/

https://blog.csdn.net/d6619309/article/details/54602556
---------------------?
作者：zuozhiji?
來源：CSDN?
原文：https://blog.csdn.net/jolly10/article/details/80415685?
版權(quán)聲明：本文為博主原創(chuàng)文章，轉(zhuǎn)載請附上博文鏈接！

總結(jié)

以上是生活随笔為你收集整理的MySQL用中间件ProxySQL实现读写分离和主节点故障应用无感应的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MySQL 使用 LOAD DATA 导
下一篇：如何让tomcat只支持ipv4