大家好， 這次大表哥分享的是 mysql MGR 高可用故障自動識別方案 Proxy SQL.
首先，我們要清楚 原生的 mysql MGR 是 不提供故障自動識別方案的， 市面上TAF的解決方案大致分為如下：
1.官方的mysql router 的組件: 可以參考大表哥之前的文章 http://m.sunline.cc/db/399249
2.開源的路由組件, 像是 Proxy SQL
3.開發自研的自動探測的腳本，可以集成在應用連接池的代碼里面，實現VIP的漂移或者是DNS的域名解析切換。
今天我們要分享的是 用開源的組件 Proxy SQL 實現 MGR 的自動故障轉移。
個人認為 proxy SQL 是一款十分輕量級的，可以實現快速部署的輕量級的路由組件。 我們先看一下 proxy SQL 的官方文檔和下載地址：
https://proxysql.com/
Proxy SQL 下載
根據自己的OS的版本選擇相應的RPM包下載：
https://github.com/sysown/proxysql/releases
安裝：
INFRA [mysql@wqdcsrv3353 ~]# sudo rpm -ivh proxysql2-2.0.14-1.1.el7.x86_64.rpm
warning:proxysql2-2.0.14-1.1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:proxysql2-2.0.14-1.1.el7.x86_64 ################################# [100%]
uat mysql@wqdcsrv3352[09:53:17]:/data/software $ which proxysql
/bin/proxysql
我們接下來要搭建一個 proxy 的集群， 這個集群是為 mysql MGR 提供了路由的服務。 示例圖如下：
對應的測試機器如下：
這里注意的是 proxy 的集群是去中心化的設計，沒有主從角色狀態的概念。
我們先預先準備好一個MGR的mysql 集群：
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 | 3080 | ONLINE | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50 | 3080 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49 | 3080 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)
我們需要在MGR中建立 PROXY 的心跳探測賬戶：
create user proxy_monitor@'10.%' identified by '***********' ;
GRANT REPLICATION CLIENT ON *.* TO `proxy_monitor`@`10.%`;
GRANT SELECT ON `sys`.* TO `proxy_monitor`@`10.%`
另外PROXY SQL 對 mysql mgr 的支持需要創建一些試圖和函數： 這個腳本可以從官方網上拿到：
https://proxysql.com/documentation/main-runtime/#mysql_group_replication_hostgroups
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 ;
執行完腳本之后，我們可以驗證一下創建的對象：
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select gr_member_in_primary_partition();
+----------------------------------+
| gr_member_in_primary_partition() |
+----------------------------------+
| YES |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.01 sec)
下面我們搭建 proxy SQL 的 集群：
1)配置文件參數
完整的參數文件配置： 每個 proxy SQL 的節點的配置文件是一致的， 對于proxy sql 的集群來說，沒有主從的角色之分
datadir="/data/mysql3080/proxySQL" admin_variables= { admin_credentials="admin:admin;proxy_cluster:*****" mysql_ifaces="0.0.0.0:3081" cluster_username="proxy_cluster" cluster_password="******" cluster_check_interval_ms=200 cluster_check_status_frequency=100 cluster_mysql_query_rules_save_to_disk=true cluster_mysql_servers_save_to_disk=true cluster_mysql_users_save_to_disk=true cluster_proxysql_servers_save_to_disk=true cluster_mysql_query_rules_diffs_before_sync=10 cluster_mysql_servers_diffs_before_sync=10 cluster_mysql_users_diffs_before_sync=10 cluster_proxysql_servers_diffs_before_sync=10 } proxysql_servers = ({hostname="10.67.38.50",port=3081,weight=0},{hostname="10.67.39.49",port=3081,weight=0}) mysql_variables= { threads=8 max_connections=5000 default_query_delay=0 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:3082" default_schema="information_schema" stacksize=1048576 server_version="8.0.20" default_query_timeout=7200000 connect_timeout_server=3000 monitor_username="proxy_monitor" monitor_password="admin123" monitor_history=600000 monitor_connect_interval=2000 monitor_ping_interval=2000 monitor_read_only_interval=2000 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 default_charset="utf8mb4" threshold_query_length=524288 threshold_resultset_size=524288 max_allowed_packet=67108864 max_transaction_time=14400000 monitor_replication_lag_interval=10000 eventslog_filename="/data/mysql3080/log/proxysql.log" eventslog_format=2 auditlog_filename="/data/mysql3080/log/audit.log" } mysql_servers = ({address="10.67.38.50",port=3080,hostgroup=20, max_connections=5000,max_replication_lag=2000},{address="10.67.39.49",port=3080,hostgroup=40, max_connections=5000,max_replication_lag=2000},{address="10.67.39.149",port=3080,hostgroup=40, max_connections=5000,max_replication_lag=2000}) mysql_users: ( {username = "db_monitor",password = "admin123",default_hostgroup = 20,max_connections=50,default_schema="information_schema",active = 1}) mysql_query_rules: ({rule_id=1,active=1,match_digest=".",apply=0,log=1} ) scheduler= ( ) mysql_replication_hostgroups= ( ) mysql_group_replication_hostgroups: ( {writer_hostgroup=20, backup_writer_hostgroup=30, reader_hostgroup=40, offline_hostgroup=10,active=1,max_writers=1,writer_is_also_reader=0,max_transactions_behind=4000} )
配置文件中重要的參數含義如下：
a）proxy SQL 集群的配置
admin_credentials="admin:admin;proxy_cluster:*****" ### 1）amdin/admin 是proxy SQL 后臺管理員的賬戶和密碼, 2）proxy SQL 集群是通過 proxy_cluster這個賬戶來實現配置文件的定期同步的
mysql_ifaces="0.0.0.0:3081" --proxy SQL 管理員后臺登錄的端口
cluster_username="proxy_cluster" --proxy SQL 集群的賬戶
cluster_password="*****" --proxy SQL 集群的密碼
--下面是proxy SQL 集群同步檢查相關的一些參數
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=10
cluster_mysql_servers_diffs_before_sync=10
cluster_mysql_users_diffs_before_sync=10
cluster_proxysql_servers_diffs_before_sync=10
--proxy 集群成員的配置
proxysql_servers =
({hostname="10.67.38.50",port=3081,weight=0},{hostname="10.67.39.49",port=3081,weight=0})
b）Mysql server 的配置
mysql_variables=
{
max_connections=5000 --最大連接數
interfaces="0.0.0.0:3082" -- 對應用系統暴露的端口
monitor_username="proxy_monitor" -- 連接mysql mgr 集群的探測心跳賬號
monitor_password="********"
...
...
}
-- MGR 集群的信息，其中 hostgroup = 20 是主庫， hostgroup = 40 表示從庫
mysql_servers =
({address="10.67.38.50",port=3080,hostgroup=20, max_connections=5000,max_replication_lag=2000},
{address="10.67.39.49",port=3080,hostgroup=40, max_connections=5000,max_replication_lag=2000},
{address="10.67.39.149",port=3080,hostgroup=40, max_connections=5000,max_replication_lag=2000}
)
-- 定義 MGR 的 hostgroup: 20：寫組, 30:備份寫組, 40:讀組，10：離線組
mysql_group_replication_hostgroups:
(
{writer_hostgroup=20,
backup_writer_hostgroup=30,
reader_hostgroup=40,
offline_hostgroup=10,active=1,max_writers=1,writer_is_also_reader=0,max_transactions_behind=4000}
)
c）其他的一些基本的配置
datadir="/data/mysql3080/proxySQL" -- proxy SQL 的數據目錄，首次啟動的時候，會在這個目錄下生成相應的數據文件，日志文件
uat mysql@wqdcsrv3352[13:52:26]:/data/mysql3080/proxySQL $ ls -lhtr
total 392K
-rw------- 1 mysql mysql 1.7K Jun 6 13:39 proxysql-key.pem
-rw------- 1 mysql mysql 1.1K Jun 6 13:39 proxysql-ca.pem
-rw------- 1 mysql mysql 1.1K Jun 6 13:39 proxysql-cert.pem
-rw-r--r-- 1 mysql mysql 7 Jun 6 13:39 proxysql.pid
-rw------- 1 mysql mysql 192K Jun 6 13:39 proxysql.db
-rw------- 1 mysql mysql 19K Jun 6 13:49 proxysql.log
-rwxr-xr-x 1 mysql mysql 2.6K Jun 6 13:52 proxy_mysql3080.cnf
-rw------- 1 mysql mysql 160K Jun 6 14:21 proxysql_stats.db
2）創建proxy SQL 的路徑并啟動 （2個HA的節點都需要執行同樣的命令）
mkdir -p /data/mysql3080/proxySQL
啟動的時候需要制定一下： proxy SQL的data 目錄以及配置文件
/usr/bin/proxysql -c /data/mysql3080/proxySQL/proxy_mysql3080.cnf -D /data/mysql3080/proxySQL
3）登錄并驗證proxy SQL 的集群 默認的管理員賬戶是 admin/admin, 管理員的端口是 3081
uat mysql@wqdcsrv3353[14:40:47]:/data/mysql3080/proxySQL $ /opt/mysql/product/percona8.0/bin/mysql -h 127.0.0.1 -P 3081 -u admin -padmin
查看2個 proxy SQL 集群的信息：
mysql> select * from proxysql_servers;
+-------------+------+--------+---------+
| hostname | port | weight | comment |
+-------------+------+--------+---------+
| 10.67.38.50 | 3081 | 0 | |
| 10.67.39.49 | 3081 | 0 | |
+-------------+------+--------+---------+
2 rows in set (0.00 sec)
查看 mysql MGR 的server 信息 ：
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 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20 | 10.67.38.50 | 3080 | 0 | ONLINE | 1 | 0 | 5000 | 2000 | 0 | 0 | |
| 40 | 10.67.39.49 | 3080 | 0 | ONLINE | 1 | 0 | 5000 | 2000 | 0 | 0 | |
| 40 | 10.67.39.149 | 3080 | 0 | ONLINE | 1 | 0 | 5000 | 2000 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
如果proxy SQL 啟動失敗， 請查看集群的日志信息：
uat mysql@wqdcsrv3352[14:47:42]:/data/mysql3080/proxySQL $ view proxysql.log
如果有proxy SQL 節點掛掉或者網絡問題的話，會報錯：
2022-06-06 14:50:58 ProxySQL_Cluster.cpp:217:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.67.39.49:3081 . Error: Can't connect to MySQL server on '10.67.39.49' (115)
2022-06-06 14:50:58 ProxySQL_Cluster.cpp:217:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.67.39.49:3081 . Error: Can't connect to MySQL server on '10.67.39.49' (115)
接下來，我們用 java (spring boot) 寫一個簡單的小代碼片段，模擬一直在讀寫數據庫：
在數據庫的讀寫過程中，我們模擬2個的故障自動轉移的場景：
1）MGR 主節點down掉
2）Proxy SQL 集群掛掉一個節點
我們來準備一下，我們的小程序片段：
首先我們需要同時在 proxy SQL 和 mysql 中創建我們的賬戶：
登錄 Mysql MGR 的主節點 :
創建數據庫 ：testdb
用戶名/密碼： app_test/app_test
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> create user app_test@'10.%' identified with mysql_native_password by 'app_test';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on testdb.* to app_test@'10.%';
Query OK, 0 rows affected (0.01 sec)
-- 這個加密的密碼字符串是給下面 proxy SQL 手動插入記錄時 準備的值
mysql> select authentication_string from mysql.user where user = 'app_test';
+-------------------------------------------+
| authentication_string |
+-------------------------------------------+
| *8832AB64049091E6C9A1B921E55CC2A8FAF2E31C |
+-------------------------------------------+
1 row in set (0.00 sec)
登錄proxy SQL 客戶端， 創建對應的 proxy SQL的 user :
mysql> insert into mysql_users (username,password,default_schema,default_hostgroup,max_connections) values ('app_test','*8832AB64049091E6C9A1B921E55CC2A8FAF2E31C','testdb',20,200)
-> ;
Query OK, 1 row affected (0.00 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)
接下來，我們需要在 application.properties 配置數據庫的連接信息：
注意這里我們要寫的是 proxy SQL 的連接地址:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://10.67.38.50:3082,10.67.39.49:3082/testdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.datasource.username=app_test
spring.datasource.password=app_test
spring.datasource.maximum-pool-size=200 -- 設置最大連接200
spring.datasource.min-idle=10 -- 最小空閑連接
我們啟動 springboot 的程序之后：
由于我們設置了數據庫連接池的最小連接數是 10， 我們可以觀察到 mysql mgr 的主節點 和 proxy SQL 中間件中均建立了10個連接：
Proxy SQL：
mysql> show processlist;
+-----------+----------+--------+-----------+---------+---------+------+
| SessionID | user | db | hostgroup | command | time_ms | info |
+-----------+----------+--------+-----------+---------+---------+------+
| 5 | app_test | testdb | 20 | Sleep | 1228988 | NULL |
| 6 | app_test | testdb | 20 | Sleep | 1229159 | NULL |
| 7 | app_test | testdb | 20 | Sleep | 1229090 | NULL |
| 8 | app_test | testdb | 20 | Sleep | 1229068 | NULL |
| 9 | app_test | testdb | 20 | Sleep | 1229098 | NULL |
| 10 | app_test | testdb | 20 | Sleep | 1229073 | NULL |
| 11 | app_test | testdb | 20 | Sleep | 1229004 | NULL |
| 12 | app_test | testdb | 20 | Sleep | 1228984 | NULL |
| 13 | app_test | testdb | 20 | Sleep | 1229013 | NULL |
| 14 | app_test | testdb | 20 | Sleep | 1228997 | NULL |
+-----------+----------+--------+-----------+---------+---------+------+
10 rows in set (0.01 sec)
Mysql MGR的主節點：
mysql> select * from PROCESSLIST where user = 'app_test';
+------+----------+-------------------+--------+---------+------+-------+------+---------+-----------+---------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+------+----------+-------------------+--------+---------+------+-------+------+---------+-----------+---------------+
| 7832 | app_test | 10.67.38.50:43890 | testdb | Sleep | 1617 | | NULL | 1616551 | 1 | 1 |
| 7833 | app_test | 10.67.38.50:43892 | testdb | Sleep | 1617 | | NULL | 1616530 | 1 | 1 |
| 7825 | app_test | 10.67.38.50:43874 | testdb | Sleep | 1617 | | NULL | 1616804 | 1 | 1 |
| 7834 | app_test | 10.67.38.50:43894 | testdb | Sleep | 1617 | | NULL | 1616511 | 1 | 1 |
| 7826 | app_test | 10.67.38.50:43876 | testdb | Sleep | 1617 | | NULL | 1616676 | 1 | 1 |
| 7827 | app_test | 10.67.38.50:43878 | testdb | Sleep | 1617 | | NULL | 1616656 | 1 | 1 |
| 7828 | app_test | 10.67.38.50:43880 | testdb | Sleep | 1617 | | NULL | 1616635 | 1 | 1 |
| 7829 | app_test | 10.67.38.50:43882 | testdb | Sleep | 1617 | | NULL | 1616615 | 1 | 1 |
| 7830 | app_test | 10.67.38.50:43886 | testdb | Sleep | 1617 | | NULL | 1616591 | 1 | 1 |
| 7831 | app_test | 10.67.38.50:43888 | testdb | Sleep | 1617 | | NULL | 1616570 | 1 | 1 |
+------+----------+-------------------+--------+---------+------+-------+------+---------+-----------+---------------+
10 rows in set (0.00 sec)
我們寫幾行簡單的代碼，輸出一下 mysql 的 report_host 變量,看一下 當前連接的mysql 的實例的 IP 地址：
瀏覽器的地址是輸入測試 ： http://127.0.0.1:8066/
我們模擬一下主庫 10.67.38.50 down 機， 我們去shutdown 一下 mysql :
mysql> shutdown; Query OK, 0 rows affected (0.00 sec)
我們再次刷新瀏覽器的地址： http://127.0.0.1:8066/
這個時候，我們觀察應用的日志，會有連接失效的警告：正好是連接池里面10個失效的連接，符合我們的預期
2022-06-07 09:41:37,691 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@3a9211b1 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,713 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@72ab37ad (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,731 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@e952d6f (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,750 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@1ca54772 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,768 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@f5b9fe6 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,788 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@4c444391 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,808 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@436dde80 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,827 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@37064435 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,846 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@5c50386a (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:41:37,864 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@6ff2e9f (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
我們登陸數據庫查看： 果然 10.67.39.149 是現在的主庫。
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 | 3080 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49 | 3080 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
接下來我們在測試一下 proxy SQL 的HA， 我們需要手動關閉 proxy SQL 的節點 :
spring.datasource.url=jdbc:mysql://10.67.38.50:3082,10.67.39.49:3082/testdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
我們來關閉一下連接串中 第一個 proxySQL 的節點： 10.67.38.50
uat mysql@wqdcsrv3352[09:51:19]:~ $ ps -ef|grep proxysql | grep 3080
mysql 113953 1 0 Jun06 ? 00:00:00 /usr/bin/proxysql -c /data/mysql3080/proxySQL/proxy_mysql3080.cnf -D /data/mysql3080/proxySQL
mysql 113954 113953 0 Jun06 ? 00:09:42 /usr/bin/proxysql -c /data/mysql3080/proxySQL/proxy_mysql3080.cnf -D /data/mysql3080/proxySQL
uat mysql@wqdcsrv3352[09:51:27]:~ $ kill 113954 113953
uat mysql@wqdcsrv3352[09:51:35]:~ $ ps -ef|grep proxysql | grep 3080
我們嘗試刷新一下瀏覽器： http://127.0.0.1:8066/ 依然可以訪問數據庫。
這個時候，我們再次觀察應用的日志，又會有連接失效的警告：正好是連接池里面10個失效的連接，再次符合我們的預期
2022-06-07 09:54:04,845 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@39d142aa (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,867 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@7aa9eace (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,886 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@18c3b7de (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,904 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@1576383b (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,921 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@1c8dcba4 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,940 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@3822d597 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,960 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@167b150d (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,978 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@6cb3d640 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:04,995 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@2904b822 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2022-06-07 09:54:05,012 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ha.MultiHostMySQLConnection@d07555 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
我們查詢一下現在存活的proxy SQL 的節點上的連接數：符合我們的預期，連接已經漂移到了存活的 proxy SQL 的節點
mysql> show processlist;
+-----------+----------+--------+-----------+---------+---------+------+
| SessionID | user | db | hostgroup | command | time_ms | info |
+-----------+----------+--------+-----------+---------+---------+------+
| 27 | app_test | testdb | 20 | Sleep | 182321 | NULL |
| 28 | app_test | testdb | 20 | Sleep | 180263 | NULL |
| 29 | app_test | testdb | 20 | Sleep | 178193 | NULL |
| 30 | app_test | testdb | 20 | Sleep | 176140 | NULL |
| 31 | app_test | testdb | 20 | Sleep | 174718 | NULL |
| 32 | app_test | testdb | 20 | Sleep | 172338 | NULL |
| 33 | app_test | testdb | 20 | Sleep | 170321 | NULL |
| 34 | app_test | testdb | 20 | Sleep | 168121 | NULL |
| 35 | app_test | testdb | 20 | Sleep | 166129 | NULL |
| 36 | app_test | testdb | 20 | Sleep | 164108 | NULL |
+-----------+----------+--------+-----------+---------+---------+------+
10 rows in set (0.00 sec)
我們總結一下，我們完成了
1） Proxy SQL 集群的搭建
2） HA 故障自動轉移之 MGR 主節點 down 機
3） HA 故障自動轉移之 PROXY SQL 節點 down 機
最后我想說的是，一定要結合自己公司的實際應用系統來測試 mysql HA的自動故障轉移。 是 Java 還是 python, 還是GO？
Java 的話， 用的是什么持久層的協議框架？用的什么連接池（C3PO，Hikari）？
作為DBA必須要了解你的系統的持久層框架和連接池的工作機制。
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
