導讀：
在之前，我們搭建了MySQL組複製叢集環境，MySQL組複製叢集環境解決了MySQL叢集內部的自動故障轉移，但是，組複製並沒有解決外部業務的故障轉移。舉個例子，在A、B、C 3臺機器上搭建了組複製環境，且執行在單主模式下，這裡假設A為主節點，應用程式連線A寫資料，如果A節點發生當機，主節點切換到B機器上，此時，應用程式是不會自動連線到B伺服器上的，需要人工進行切換。
在這篇文章中，我們要介紹的ProxySQL就能夠解決上面的問題，ProxySQL能夠實現業務層面故障轉移、讀寫分離功能，當然ProxySQL不僅僅只有這兩項功能，還有更多的其它功能。其架構如下：
我們不妨來了解一下。
ProxySQL是一款MySQL代理軟體，其核心特點為讀寫分離、故障轉移，詳細其功能如下：
- 應用層代理。ProxySQL不僅能夠實現負載均衡，還可提供端到端連線處理、實時資訊統計和資料庫流量檢查；
- 零停機時間變更。ProxySQL在記憶體中直接進行配置修改，然後可以持久儲存到磁碟和推送到執行中；
- 資料庫防火牆。可充當應用程式與資料庫之間的防火牆，使DBA可以保護資料庫免受惡意活動或有問題的程式的影響；
- 高階查詢規則。使用ProxySQL豐富的查詢規則定義查詢路由，有效的分發和快取資料，從而最大的提高資料庫服務快取效率；
- 資料分片與轉換
- 故障轉移檢測。ProxySQL通過連續監視資料庫後端並在拓撲更改時將流量重新路由到執行正常的節點。
這裡，我們使用ProxySQL來對MySQL組複製環境實現讀寫分離以及故障轉移。我的環境如下：
| IP地址 | 主機名 | 用途 |
| 192.168.10.11 | mgr-node1 | MySQL組複製成員 |
| 192.168.10.12 | mgr-node2 | MySQL組複製成員 |
| 192.168.10.13 | mgr-node3 | MySQL組複製成員 |
| 192.168.10.10 | proxysql | ProxySQL代理伺服器 |
MySQL採用多主模式，搭建過程見文件：《MySQL組複製MGR（二）-- 組複製搭建》，本文把重點放在ProxySQL的搭建與配置上。
（二）安裝ProxySQL
安裝ProxySQL，有2種方法，如果有網路，可以直接使用yum安裝，如果沒有網路，可以下載ProxySQL發行包安裝，下載地址為：https://github.com/sysown/proxysql/releases。這裡為了方便，直接使用yum線上安裝。
新增yum源，使用Linux root使用者執行如下配置：
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key EOF
安裝proxysql：
yum install -y proxysql OR yum install proxysql-version
如果要檢視安裝的檔案在哪，可以使用如下命令：
[root@proxysql yum.repos.d]# rpm -ql proxysql /etc/logrotate.d/proxysql /etc/proxysql.cnf /etc/systemd/system/proxysql-initial.service /etc/systemd/system/proxysql.service /usr/bin/proxysql /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl
檢視ProxySQL程式：
[root@proxysql yum.repos.d]# ps -ef|grep proxy avahi 740 1 0 15:52 ? 00:00:00 avahi-daemon: registering [proxysql-65.local] root 761 1 0 15:52 ? 00:00:00 /usr/sbin/gssproxy -D proxysql 2058 1 0 16:09 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf proxysql 2059 2058 1 16:09 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf root 2087 1589 0 16:09 pts/0 00:00:00 grep --color=auto proxy
檢視埠，6032是proxysql的管理埠，6033是對外服務埠
[root@proxysql yum.repos.d]# netstat -anlp | grep proxysql tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 2059/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 2059/proxysql
（三）啟動關閉ProxySQL
啟動ProxySQL
service proxysql start
關閉ProxySQL
service proxysql stop
重啟ProxySQL
service proxysql restart
檢視ProxySQL的狀態
service proxysql status
（四）ProxySQL基礎知識瞭解
ProxySQL的配置，相對而言還是比較複雜的。因此，在配置ProxySQL之前，我們需要對其架構有一些瞭解，這樣在配置的時候，才不會一臉懵逼。
(4.1)ProxySQL多層配置系統
前面我們說到ProxySQL具有“零停機時間變更”功能，它是通過3層配置來實現的，3層配置包括：Runtime、Memory、Disk & Configuration File。
- Runtime層表示ProxySQL工作執行緒使用的記憶體資料結構；
- Memory(也被稱為main)層經由一個MySQL相容介面露出的記憶體資料庫，使用者可以使用MySQL客戶端連線到管理介面，檢視、編輯ProxySQL配置表；
- Disk & Configuration File。Disk層是一個存放在磁碟上的SQLite3資料庫，Disk層可將記憶體中的配置資訊儲存到磁碟，以便ProxySQL重新啟動後配置還可用。
3個層面的資訊有什麼區別呢？我個人的理解是：3個層面儲存的都是ProxySQL的配置資訊，如果管理員未作修改，那麼3個層面的配置資訊是相同的。如果管理員要修改配置資訊，首先需要修改Memory層，要讓修改的資訊立刻生效，則需要把Memory層的變更資訊推到Runtime層；要讓修改的配置資訊在ProxySQL重啟後還能儲存下來，則需要把Memory層的資訊推到Disk層。Runtime層是ProxySQL正在使用的配置資訊，Memory層是使用者可以編輯的資訊，Disk層可以把配置資訊永久儲存在磁碟上。
各層之間資料如何同步呢？我們可以看上圖的箭頭部分，通過load/save命令來實現同步。具體命令如下：
[1] LOAD <item> FROM MEMORY/LOAD <item> TO RUNTIME 將配置項從記憶體資料庫載入到執行時資料結構 [2] SAVE <item> TO MEMORY/SAVE <item> FROM RUNTIME 將配置項從執行時儲存到記憶體資料庫中 [3] LOAD <item> TO MEMORY/LOAD <item> FROM DISK 將永續性配置專案從磁碟資料庫載入到記憶體資料庫 [4] SAVE <item> FROM MEMORY/SAVE <item> TO DISK 將配置項從記憶體資料庫儲存到磁碟資料庫 [5] LOAD <item> FROM CONFIG 將配置項從配置檔案載入到記憶體資料庫中
常用的配置有：
# 啟用使用者配置到RUNTIME LOAD MYSQL USERS TO RUNTIME; # 儲存使用者資訊到磁碟上 SAVE MYSQL USERS TO DISK; --------------------------------- # 啟用MySQL伺服器資訊到RUNTIME LOAD MYSQL SERVERS TO RUNTIME; # 儲存MySQL伺服器資訊到磁碟 SAVE MYSQL SERVERS TO DISK; --------------------------------- # 啟用查詢路由規則到RUNTIME LOAD MYSQL QUERY RULES TO RUNTIME; # 儲存查詢路由規則到磁碟 SAVE MYSQL QUERY RULES TO DISK; ---------------------------------- # 啟用MySQL變數到RUNTIME LOAD MYSQL VARIABLES TO RUNTIME; # 儲存MySQL變數到磁碟 SAVE MYSQL VARIABLES TO DISK; ---------------------------------- # 啟用proxySQL admin變數到RUNTIME LOAD ADMIN VARIABLES TO RUNTIME; # 儲存proxySQL admin變數到磁碟 SAVE ADMIN VARIABLES TO DISK;
(4.2)ProxySQL的配置管理介面
ProxySQL有2種配置方式：
- 使用ProxySQL的命令列管理介面進行配置
- 使用配置檔案進行配置
通常使用第一種方法進行配置，這裡我們只瞭解第1種方法。
ProxySQL管理介面使用的是MySQL協議的介面，通過使用mysql客戶端連線到SQLite3進行配置的查詢、管理。可以使用預設的admin使用者連線到proxySQL資料庫。
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 mysql> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+
這些資料庫作用如下：
- main：記憶體配置資料庫，使用此資料庫，可以方便的查詢和更新ProxySQL的配置。與上面所曾配置系統的Memory層對應；
- disk：“main”資料庫的磁碟映象。重新啟動ProxySQL時，main中的資料就是從該資料庫載入的。與上面所曾配置系統的disk層對應；
- stats：ProxySQL收集的一些指標。如每個查詢規則的匹配次數，當前正在執行的查詢等；
- monitor：包含於ProxySQL連線的後端伺服器的指標。如連線帶後端伺服器對其進行ping操作的最小、最大時間；
ProxySQL設定了2個使用者來管理配置資料庫：
- 賬號admin 密碼admin : 該使用者具有全部標的讀寫許可權；
- 賬號stats 密碼stats : 該使用者具有統計資訊表的只讀許可權；
（五）一步一步配置ProxySQL--基礎配置
（5.1）檢查配置資訊
檢視相關配置表是否存在資訊，因為還沒開始配置，所以是不存在資訊的，如果已經配置過了，可以先刪除資訊。
mysql> select * from mysql_servers; Empty set (0.00 sec) mysql> select * from mysql_users; Empty set (0.01 sec) mysql> select * from mysql_query_rules; Empty set (0.00 sec) mysql> select * from mysql_group_replication_hostgroups; Empty set (0.00 sec)
（5.2）組的配置
所謂組的配置，即定義讀組、寫組等，可以使用如下兩個表來定義讀寫組：
- mysql_replication_hostgroups：該表用於傳統的master/slave的非同步複製或者半同步複製的配置。
- mysql_group_replication_hostgroups:該表用於MySQL Group Replication、InnoDB Cluster or Galera/Percona XtraDB Cluster的配置
因為我們這裡是使用proxySQL來實現MGR叢集業務層面的實現故障轉移以及讀寫分離的，所以配置mysql_group_replication_hostgroups表即可，該表定義如下：
show create table mysql_group_replication_hostgroups; ------------------------------------------------------------ CREATE TABLE mysql_group_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR, UNIQUE (reader_hostgroup), UNIQUE (offline_hostgroup), UNIQUE (backup_writer_hostgroup))
這些欄位含義如下：
- write_hostgroup：預設情況下會將所有流量傳送到這個組。具有read_only=0的節點也將分配到這個組；
- backup_writer_hostgroup：如果叢集有多個寫節點(read_only=0)且超過了max_writers規定數量，則會把多出來的寫節點放到備用寫組裡面；
- reader_hostgroup：讀取的流量應該傳送到該組，只讀節點(read_only=1)會被分配到該組；
- offline_hostgroup：當ProxySQL監視到某個節點不正常時，會被放入該組；
- active：是否啟用主機組，當啟用時，ProxySQL將監視主機在各族之間移動；
- max_writers：最大寫節點的數量，超過該值的節點應該被放入backup_write_hostgroup；
- writer_is_also_reader：一個節點既做寫節點也做讀節點，如果該值為2，則backup_writer_hostgroup的節點做讀寫點，但是writer_hostgroup不會做讀節點；
我們對該表進行如下配置：
mysql> select * from mysql_group_replication_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 1 | 2 | 3 | 4 | 1 | 1 | 0 | 100 | NULL | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
（5.3）檢視新增
如果ProxySQL是與組複製MGR一起使用的，那麼還需要在MGR叢集新增如下檢視：
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 ;
然後授權給監控使用者，這裡需要特別注意，我的監控使用者在5.5.1步才建立，因此這一步需要放到5.5.1後執行：
grant select on sys.* to monitoring_user;
（5.4）MySQL伺服器新增
mysql_server表是用來儲存ProxySQL路由轉換的MySQL節點的資訊。
mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.11',3306); mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.12',3306); mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.13',3306); 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 | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
然後執行下面的命令生效：
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
（5.5）監控配置及檢查
這裡配置監控資訊，用來監控ProxySQL與後端的MySQL通訊是否正常。
（5.5.1）監控使用者配置
在ProxySQL的變數表裡面設定監控使用者密碼，用於ProxySQL監控後端MySQL伺服器的使用者資訊
mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec) mysql> select variable_name,variable_value from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password'); +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_password | monitor | | mysql-monitor_username | monitor | +------------------------+----------------+
需要注意，既然使用該使用者監控後臺MySQL資料庫，那麼後臺MySQL資料庫也需要建立該使用者並授權，monitor使用者需要有usage許可權去連線、ping和檢查read_only資訊，如果要檢測複製延遲，還需要具有replication client許可權。特別注意，不能使用mysql_users裡面的使用者來做監控使用者。
-- 在MySQL伺服器上建立監控使用者 -- 需要注意，這裡MySQL使用的是MGR，所以只需要在一臺節點建立使用者即可，其它節點會自動同步使用者資訊 create user monitor@'%' identified by 'monitor'; grant usage,replication client on *.* to monitor@'%'; flush privileges;
注意：因為ProxySQL+組複製新增了新的檢視，見”5.3 檢視新增”，因此還需授權：
grant select on sys.* to monitor;
(5.5.2)配置監控間隔
這裡把連線、ping、read_only監控間隔改為2s，也可以根據需要改成其它，也可以不做修改
mysql> update global_variables set variable_value='2000' -> where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval'); Query OK, 3 rows affected (0.00 sec) mysql> select * from global_variables where variable_name like 'mysql-monitor%'; +--------------------------------------------------------------+----------------+ | variable_name | variable_value | +--------------------------------------------------------------+----------------+ | mysql-monitor_enabled | true | | mysql-monitor_connect_timeout | 600 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_read_only_max_timeout_count | 3 | | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 | | mysql-monitor_groupreplication_max_transactions_behind_count | 3 | | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | | mysql-monitor_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_threads_min | 8 | | mysql-monitor_threads_max | 128 | | mysql-monitor_threads_queue_maxsize | 128 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-monitor_username | monitor | | mysql-monitor_password | monitor | | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 2000 | | mysql-monitor_ping_interval | 2000 | | mysql-monitor_read_only_interval | 2000 | | mysql-monitor_read_only_timeout | 500 | +--------------------------------------------------------------+----------------+ 30 rows in set (0.01 sec)
在修改完變數之後，一定要載入到記憶體中生效以及永久儲存到磁碟中：
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
(5.5.3)檢查監控資訊是否存在異常
監控配置完成後，我們需要檢查ProxySQL與後端MySQL通訊是否有異常，monitor資料庫中的表用於儲存監視資訊，需要注意的是，這些表並非都已經被使用。
mysql> show tables from monitor; +--------------------------------------+ | tables | +--------------------------------------+ | mysql_server_aws_aurora_check_status | | mysql_server_aws_aurora_failovers | | mysql_server_aws_aurora_log | | mysql_server_connect_log | | mysql_server_galera_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +--------------------------------------+
檢視ProxySQL與後臺伺服器連線是否正常：
mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10; +---------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+---------------+ | 192.168.10.13 | 3306 | 1596263409501584 | 2191 | NULL | | 192.168.10.11 | 3306 | 1596263409480641 | 1911 | NULL | | 192.168.10.12 | 3306 | 1596263409459524 | 3671 | NULL | | 192.168.10.13 | 3306 | 1596263407504677 | 1451 | NULL | | 192.168.10.11 | 3306 | 1596263407481776 | 1398 | NULL | | 192.168.10.12 | 3306 | 1596263407458859 | 1378 | NULL | | 192.168.10.12 | 3306 | 1596263405490389 | 3480 | NULL | | 192.168.10.13 | 3306 | 1596263405474367 | 2804 | NULL | | 192.168.10.11 | 3306 | 1596263405458569 | 1612 | NULL | | 192.168.10.13 | 3306 | 1596263403497485 | 2132 | NULL | +---------------+------+------------------+-------------------------+---------------+ 10 rows in set (0.00 sec)
檢視組複製是否正常，檢查節點是否只讀和交易滯後時間：
mysql> select * from mysql_server_group_replication_log order by time_start_us desc limit 10; +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error | +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | 192.168.10.13 | 3306 | 1596263494597039 | 5671 | YES | NO | 0 | NULL | | 192.168.10.12 | 3306 | 1596263494596052 | 3231 | YES | NO | 0 | NULL | | 192.168.10.11 | 3306 | 1596263494595139 | 3245 | YES | NO | 0 | NULL | | 192.168.10.13 | 3306 | 1596263489596357 | 3027 | YES | NO | 0 | NULL | | 192.168.10.12 | 3306 | 1596263489595491 | 3306 | YES | NO | 0 | NULL | | 192.168.10.11 | 3306 | 1596263489594645 | 3110 | YES | NO | 0 | NULL | | 192.168.10.13 | 3306 | 1596263484595710 | 3680 | YES | NO | 0 | NULL | | 192.168.10.12 | 3306 | 1596263484594839 | 3618 | YES | NO | 0 | NULL | | 192.168.10.11 | 3306 | 1596263484594114 | 3214 | YES | NO | 0 | NULL | | 192.168.10.13 | 3306 | 1596263479595072 | 1887 | YES | NO | 0 | NULL | +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ 10 rows in set (0.01 sec)
檢視ProxySQL ping後端MySQL伺服器是否正常：
mysql> select * from mysql_server_ping_log order by time_start_us desc limit 10; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 192.168.10.12 | 3306 | 1596263541810631 | 496 | NULL | | 192.168.10.11 | 3306 | 1596263541786903 | 612 | NULL | | 192.168.10.13 | 3306 | 1596263541762973 | 749 | NULL | | 192.168.10.12 | 3306 | 1596263539796079 | 565 | NULL | | 192.168.10.13 | 3306 | 1596263539779040 | 403 | NULL | | 192.168.10.11 | 3306 | 1596263539762769 | 1141 | NULL | | 192.168.10.12 | 3306 | 1596263537797512 | 848 | NULL | | 192.168.10.11 | 3306 | 1596263537779520 | 845 | NULL | | 192.168.10.13 | 3306 | 1596263537761840 | 742 | NULL | | 192.168.10.12 | 3306 | 1596263535814945 | 843 | NULL | +---------------+------+------------------+----------------------+------------+ 10 rows in set (0.00 sec)
通過監控資訊，我們可以得出結論，所有配置都是健康的，繼續下一步。
(5.6)使用者配置
（5.6.1）ProxySQL的雙層使用者認證機制
如果使用了ProxySQL來做中間路由，那麼與我們平時登入資料庫有一些區別：平時我們直接使用資料庫的使用者密碼，即可訪問到資料庫，如果使用了ProxySQL，則要先使用賬號密碼訪問到ProxySQL的資料庫，然後再由ProxySQL進行使用者請求的轉發，那麼，ProxySQL中的使用者與資料庫層的使用者有什麼關聯呢？很奇怪，這部分ProxySQL居然沒在文件裡面給出來。
只能自己測試了，經過個人測試，發現：當中介軟體使用者與資料庫使用者以及密碼一致時，才能正常訪問資料庫。測試結果如下：
| MySQL資料庫使用者(mysql.user表) | ProxySQL使用者（main.mysql_users表） | 使用ProxySQL的6033埠訪問資料庫 |
| usera | usera | 正常訪問 |
| userb | 無法登入proxysql | |
| userc | 可以登入proxysql，但是無法讀寫 |
這裡是我的測試過程：
在MySQL資料庫上建立使用者：usera和userb
create user `usera`@`%` identified by '123456'; grant all privileges on *.* to `usera`@`%`; create user `userb`@`%` identified by '123456'; grant all privileges on *.* to `userb`@`%`; flush privileges;
在ProxySQL上建立使用者：usera和userc
insert into mysql_users(username,password,default_hostgroup) values('usera','123456',1); insert into mysql_users(username,password,default_hostgroup) values('userc','123456',1); load mysql users to runtime; save mysql users to disk;
登入測試(分為2步：先登入，再查詢)：
（1）usera使用者登入無問題，查詢無問題
[root@proxysql ~]# mysql -uusera -p123456 -P6033 -h192.168.10.10 mysql> select count(*) from lijiamandb.test03; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.01 sec)
（2）userb無法登入，提示使用者名稱密碼錯誤
[root@proxysql ~]# mysql -uuserb -p123456 -P6033 -h192.168.10.10 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): ProxySQL Error: Access denied for user 'userb'@'192.168.10.10' (using password: YES)
（3）userc可以正常登入，但是查詢的時候提示密碼不對
[root@proxysql ~]# mysql -uuserc -p123456 -P6033 -h192.168.10.10 mysql> mysql> select count(*) from lijiamandb.test03; ERROR 1045 (28000): Access denied for user 'userc'@'192.168.10.10' (using password: YES)
使用者認證小結：只有ProxySQL中的使用者名稱密碼與MySQL中的使用者名稱密碼相同時，才能正常訪問底層MySQL資料庫。因此，如果要使用ProxySQL訪問資料庫，需要在MySQL和ProxySQL中都要建立相同的賬號，並且密碼也要保持一致。
（5.6.2）ProxySQL使用者建立
ProxySQL的使用者儲存在mysql_users表中，使用者建立直接執行insert插入即可。如建立一個使用者名稱為“lijiaman”，密碼為“123456”，預設使用者組為1的使用者：
insert into mysql_users(username,password,default_hostgroup) values('lijiaman','123456',1);
load mysql users to runtime; save mysql users to disk;
mysql_users表最重要的欄位為：
- username
- password
- default_hostgroup：預設組。如果此使用者傳送的查詢沒有匹配的規則，則它生成的流量將傳送到指定的主機組
- transaction_persistent：如果為與MySQL客戶端連線到ProxySQL的使用者設定了此選項，則在主機組內啟動的事務將保留在該主機組內，而不管其他任何規則。例如，一個事務中存在讀與寫操作，如果不指定該選項，可能會把讀與寫請求分發到不同的主機上，造成資料不一致。
（六）故障轉移(failover)測試
在上一節，我們已經配置了：
- 組：寫組、備用寫組、讀組、離線組。並且讀組最多隻有1臺server；
- MySQL Server:配置了3臺Server，並且將其放入到了寫組中；
- 監控資訊：
- 使用者資訊
此時，ProxySQL已經具備故障轉移的能力了，我們進行測試一下。
STEP1：現在的配置如下，192.168.10.13主機是寫節點，其它2個節點是備用寫節點：
-- mysql_serve在memory層r的配置資訊 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 | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) --載入到RUNTIME後，由於組定義中最多隻有1個寫節點，其餘的主節點移動到備用寫組裡面 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 | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec)
使用ProxySQL來訪問MyQSL叢集，發現可以支援讀寫
-- 使用ProxySQL 6033埠訪問MySQL資料庫 [root@proxysql ~]# mysql -uusera -p123456 -P6033 -h192.168.10.10 mysql> use testdb -- 通過主機名，額可以看到，我們訪問到的是寫節點 mysql> select @@hostname; +------------+ | @@hostname | +------------+ | mgr-node3 | +------------+ 1 row in set (0.00 sec) -- 可以這次插入、查詢資料 mysql> insert into test01 values(1,'a'); Query OK, 1 row affected (0.00 sec) mysql> select * from test01; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)
STEP2：關閉寫節點
# 直接關閉主機 [root@mgr-node3 ~]# reboot
Connection closed by foreign host. Disconnected from remote host(mgr-node3) at 19:00:31. Type `help' to learn how to use Xshell prompt.
[c:\~]$ -- 需要注意的是，以前連線在主節點上的會話會斷開，不會轉移到新的主節點，很正常，Oracle也不會 mysql> select * from test01; ERROR 2013 (HY000): Lost connection to MySQL server during query
STEP3：檢視是否會有備用寫節點轉為寫節點，可以看到192.168.10.12伺服器已經轉為寫節點，而已經關閉的192.168.10.13伺服器已經進入離線組。
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 | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 4 | 192.168.10.13 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
STEP4：再次使用ProxySQL來訪問MyQSL叢集，發現可以支援讀寫，業務不會因主節點的改變而受影響。
-- 使用ProxySQL 6033埠訪問MySQL資料庫 [root@proxysql ~]# mysql -uusera -p123456 -P6033 -h192.168.10.10 mysql> use testdb -- 通過主機名，額可以看到，我們訪問到的是新的寫節點 mysql> select @@hostname; +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+ 1 row in set (0.00 sec) -- 可以這次插入、查詢資料 mysql> insert into test01 values(2,'b'); Query OK, 1 row affected (0.00 sec)
通過上面的測試，可以看到，MGR結合ProxySQL已經可以實現業務的自動故障轉移。
接下來，我們開始研究ProxySQL的讀寫分離功能。