MySQL中介軟體之ProxySQL(3)：初試讀寫分離

easydb發表於2020-10-14

實現一個簡單的讀寫分離

這裡通過一個簡單的示例實現ProxySQL的讀寫分離功能，算是ProxySQL的快速入門。即使是快速入門，需要配置的內容也很多，包括：後端MySQL配置、監控配置、傳送SQL語句的使用者、SQL語句的路由規則。所以，想要實現一個ProxySQL+MySQL，即使只實現最基本的功能，步驟也是挺多的，不過配置的邏輯都很簡單。

實驗環境：
OS：centos7.8
ProxySQL：2.0.14
MySQL：8.0.20

角色	主機IP	埠	server_id
ProxySQL	192.168.80.54	6032/6033	null
Master	192.168.80.54	21021	21021
Slave1	192.168.80.54	21022	21022
Slave2	192.168.80.54	21023	21023

1. 準備MySQL的主從環境

這是我是使用dbdeployer快速部署的，詳情可以去看我之前的檔案

dbdeployer deploy replication 8.0.20 --gtid --bind-address='0.0.0.0'

mysql 8.0 預設的認證外掛是caching_sha2_password，需要改回之前版本的default_authentication_plugin

echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/master/my.sandbox.cnf
echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/node1/my.sandbox.cnf
echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/node2/my.sandbox.cnf

從庫設定read_only

echo 'read_only=1' >>  ~/sandboxes/rsandbox_8_0_20/node1/my.sandbox.cnf
echo 'read_only=1' >>  ~/sandboxes/rsandbox_8_0_20/node2/my.sandbox.cnf

重啟所有例項

[root@mysql8 ~]# ~/sandboxes/rsandbox_8_0_20/restart_all 
# executing 'stop' on /root/sandboxes/rsandbox_8_0_20
stop /root/sandboxes/rsandbox_8_0_20/node1
stop /root/sandboxes/rsandbox_8_0_20/node2
stop /root/sandboxes/rsandbox_8_0_20/master
# executing 'start' on /root/sandboxes/rsandbox_8_0_20
executing 'start' on master
.. sandbox server started
executing 'start' on slave 1
.. sandbox server started
executing 'start' on slave 2
.. sandbox server started

[root@mysql8 ~]# ~/sandboxes/rsandbox_8_0_20/status_all 
REPLICATION  /root/sandboxes/rsandbox_8_0_20
master : master on  -  port	21021 (21021)
node1 : node1 on  -  port	21022 (21022)
node2 : node2 on  -  port	21023 (21023)

2.檢查ProxySQL環境

啟動proxysql

systemctl start proxysql.service

登陸到proxysql管理介面，–prompt重置提示符

mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

確認proxysql中沒有任何資料。mysql_servers，mysql_replication_hostgroups或mysql_query_rules表中為空。

admin> SELECT * FROM mysql_servers;
Empty set (0.000 sec)

admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.000 sec)

admin> SELECT * from mysql_query_rules;
Empty set (0.000 sec)

3.將後端mysql節點新增到ProxySQL中

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21021);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21022);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21023);
Query OK, 1 row affected (0.000 sec)

檢視這3個節點是否插入成功，以及它們的狀態。

admin> select * from mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21021
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21022
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21023
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
3 rows in set (0.000 sec)

新增成功後，載入到RUNTIME，並儲存到disk。ProxySQL修改表後必須執行LOAD ... TO RUNTIME才能載入到RUNTIME生效，執行save ... to disk才能將配置持久化儲存到磁碟。

admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.003 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.040 sec)

4.監控後端MySQL節點

新增節點之後，還需要監控後端節點。對於後端是主從複製的環境來說，這是必須的，因為ProxySQL需要通過每個節點的read_only值來自動調整它們是屬於讀組還是寫組。

首先在後端master節點上建立一個用於監控的使用者名稱(只需在master上建立即可，因為會複製到slave上)，這個使用者名稱只需具有USAGE許可權即可。如果還需要監控複製結構中slave是否嚴重延遲於master(先混個眼熟：這個俗語叫做"拖後腿"，術語叫做"replication lag")，則還需具備replication client許可權。這裡直接賦予這個許可權。

# 在master例項上執行
[root@mysql8 ~]# mysql -uroot -pmsandbox -h127.0.0.1 -P21021

MySQL [(none)]> create user monitor@'192.168.80.%' identified by 'monitor';
Query OK, 0 rows affected (0.005 sec)

MySQL [(none)]> grant replication client on *.* to monitor@'192.168.80.%';
Query OK, 0 rows affected (0.003 sec)

然後回到ProxySQL上配置監控

admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.001 sec)

admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.001 sec)

新增後，載入到RUNTIME，並儲存到disk

admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)

admin> save mysql variables to disk;
Query OK, 136 rows affected (0.004 sec)

驗證監控結果：ProxySQL監控模組的指標都儲存在monitor庫的log表中。

以下是連線是否正常的監控(對connect指標的監控)：(在前面可能會有很多connect_error，這是因為沒有配置監控資訊時的錯誤，配置後如果connect_error的結果為NULL則表示正常)

admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
+---------------+-------+------------------+-------------------------+---------------+
| hostname      | port  | time_start_us    | connect_success_time_us | connect_error |
+---------------+-------+------------------+-------------------------+---------------+
| 192.168.80.54 | 21023 | 1602662330240468 | 675                     | NULL          |
| 192.168.80.54 | 21021 | 1602662329832310 | 548                     | NULL          |
| 192.168.80.54 | 21022 | 1602662329424482 | 381                     | NULL          |
| 192.168.80.54 | 21021 | 1602662270567369 | 709                     | NULL          |
| 192.168.80.54 | 21023 | 1602662269995663 | 489                     | NULL          |
+---------------+-------+------------------+-------------------------+---------------+
5 rows in set (0.000 sec)

以下是對心跳資訊的監控(對ping指標的監控)：

admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
+---------------+-------+------------------+----------------------+------------+
| hostname      | port  | time_start_us    | ping_success_time_us | ping_error |
+---------------+-------+------------------+----------------------+------------+
| 192.168.80.54 | 21021 | 1602662429416400 | 137                  | NULL       |
| 192.168.80.54 | 21023 | 1602662429308793 | 187                  | NULL       |
| 192.168.80.54 | 21022 | 1602662429201595 | 180                  | NULL       |
| 192.168.80.54 | 21022 | 1602662419430132 | 133                  | NULL       |
| 192.168.80.54 | 21021 | 1602662419315822 | 187                  | NULL       |
+---------------+-------+------------------+----------------------+------------+
5 rows in set (0.000 sec)

5.對主從節點進行分組

通過監控我們可以看到，monitor.mysql_server_read_only_log日誌表為空的

admin> SELECT * FROM monitor.mysql_server_read_only_log;
Empty set (0.000 sec)

這是因為還沒有對ProxySQL中的節點分組：writer_hostgroup、reader_hostgroup。設定分組資訊，需要修改的是main庫中的mysql_replication_hostgroups表，該表只有3個欄位：第一個欄位名為writer_hostgroup，第二個欄位為reader_hostgroup，第三個欄位為註釋欄位，可隨意寫。

例如，指定寫組的id為10，讀組的id為20。

如果read_only=0，它們將被移至主機組10
如果read_only=1，它們將被移至主機組20

admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (10,20,'cluster1');
Query OK, 1 row affected (0.000 sec)

在該配置載入到RUNTIME生效之前，先檢視下各mysql server所在的組。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+-------+--------+--------+
| hostgroup_id | hostname      | port  | status | weight |
+--------------+---------------+-------+--------+--------+
| 10           | 192.168.80.54 | 21021 | ONLINE | 1      |
| 10           | 192.168.80.54 | 21022 | ONLINE | 1      |
| 10           | 192.168.80.54 | 21023 | ONLINE | 1      |
+--------------+---------------+-------+--------+--------+
3 rows in set (0.000 sec)
# 目前3個節點都在hostgroup_id=10的組中。

現在，將剛才mysql_replication_hostgroups表的修改載入到RUNTIME生效。

admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.067 sec)

一載入，Monitor模組就會開始監控後端的read_only值，當監控到read_only值後，就會按照read_only的值將某些節點自動移動到讀/寫組。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+-------+--------+--------+
| hostgroup_id | hostname      | port  | status | weight |
+--------------+---------------+-------+--------+--------+
| 10           | 192.168.80.54 | 21021 | ONLINE | 1      |
| 20           | 192.168.80.54 | 21023 | ONLINE | 1      |
| 20           | 192.168.80.54 | 21022 | ONLINE | 1      |
+--------------+---------------+-------+--------+--------+
3 rows in set (0.000 sec)

admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+-------+------------------+-----------------+-----------+-------+
| hostname      | port  | time_start_us    | success_time_us | read_only | error |
+---------------+-------+------------------+-----------------+-----------+-------+
| 192.168.80.54 | 21023 | 1602667034693600 | 266             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667034681432 | 222             | 0         | NULL  |
| 192.168.80.54 | 21022 | 1602667034668657 | 249             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667033204309 | 172             | 0         | NULL  |
| 192.168.80.54 | 21023 | 1602667033186378 | 210             | 1         | NULL  |
| 192.168.80.54 | 21022 | 1602667033169659 | 489             | 1         | NULL  |
| 192.168.80.54 | 21022 | 1602667031692632 | 269             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667031680421 | 236             | 0         | NULL  |
| 192.168.80.54 | 21023 | 1602667031667752 | 226             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667030198377 | 265             | 0         | NULL  |
+---------------+-------+------------------+-----------------+-----------+-------+
10 rows in set (0.000 sec)

6.配置mysql_users

上面的所有配置都是關於後端MySQL節點的，現在可以配置關於SQL語句的，包括：傳送SQL語句的使用者、SQL語句的路由規則、SQL查詢的快取、SQL語句的重寫等等。

本小節是SQL請求所使用的使用者配置，例如root使用者。這要求我們需要先在後端MySQL節點新增好相關使用者。這裡以root和dev兩個使用者名稱為例。

首先，在master節點上執行：(只需master執行即可，會複製給兩個slave)

MySQL [(none)]> create user root@'192.168.80.%' identified by '123';
Query OK, 0 rows affected (0.007 sec)

MySQL [(none)]> create user dev@'192.168.80.%' identified by '123';
Query OK, 0 rows affected (0.003 sec)

MySQL [(none)]> grant all on *.* to 'root'@'192.168.80.%';
Query OK, 0 rows affected (0.004 sec)

MySQL [(none)]> grant all on *.* to 'dev'@'192.168.80.%';
Query OK, 0 rows affected (0.004 sec)

然後回到ProxySQL，配置mysql_users表，將剛才的兩個使用者新增到該表中。

admin> insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_users(username,password,default_hostgroup) values('dev','123',10);
Query OK, 1 row affected (0.000 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)

admin> save mysql users to disk;
Query OK, 0 rows affected (0.007 sec)

mysql_users表有不少欄位，最主要的三個欄位為username、password和default_hostgroup

username：前端連線ProxySQL，以及ProxySQL將SQL語句路由給MySQL所使用的使用者名稱。
password：使用者名稱對應的密碼。可以是明文密碼，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節點上執行select password(PASSWORD)，然後將加密結果複製到該欄位。
default_hostgroup：該使用者名稱預設的路由目標。例如，指定root使用者的該欄位值為10時，則使用root使用者傳送的SQL語句預設情況下將路由到hostgroup_id=10組中的某個節點。

admin> select * from mysql_users \G
*************************** 1. row ***************************
              username: root
              password: 123
                active: 1            #  注意本行
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1            #  注意本行
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
*************************** 2. row ***************************
              username: dev
              password: 123
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
2 rows in set (0.000 sec)

雖然本文不詳細介紹mysql_users表，但上面標註了"注意本行"的兩個欄位必須要引起注意。

只有active=1的使用者才是有效的使用者。
至於transaction_persistent欄位，當它的值為1時，表示事務持久化：當某連線使用該使用者開啟了一個事務後，那麼在事務提交/回滾之前，所有的語句都路由到同一個組中，避免語句分散到不同組(更進一步的，它會自動禁用multiplexing，讓同一個事務的語句從同一個連線路由出去，保證路由到同一個組的同一個節點)。在以前的版本中，預設值為0，不知道從哪個版本開始，它的預設值為1。我們期望的值為1，所以在繼續下面的步驟之前，先檢視下這個值，如果為0，則執行下面的語句修改為1。

update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;

7.讀寫分離：配置路由規則

ProxySQL的路由規則非常靈活，可以基於使用者、基於schema以及基於每個語句實現路由規則的定製。

本文作為入門文章，實現一個最簡單的語句級路由規則，從而實現讀寫分離。必須注意，這只是實驗，實際的路由規則絕不應該僅根據所謂的讀、寫操作進行分離，而是從各項指標中找出壓力大、執行頻繁的語句單獨寫規則、做快取等等。

和查詢規則有關的表有兩個：mysql_query_rules和mysql_query_rules_fast_routing，後者是前者的擴充套件表，1.4.7之後才支援該快速路由表。本文只介紹第一個表。

插入兩個規則，目的是將select語句分離到hostgroup_id=20的讀組，但由於select語句中有一個特殊語句SELECT…FOR UPDATE它會申請寫鎖，所以應該路由到hostgroup_id=10的寫組。

admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
    -> VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
    ->        (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.000 sec)

admin> 
admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)

admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.007 sec)

select ... for update規則的rule_id必須要小於普通的select規則的rule_id，因為ProxySQL是根據rule_id的順序進行規則匹配的。

8.測試讀寫分離

測試讀：

[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21022 |
+-------------+
[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21023 |
+-------------+

測試寫：（begin開啟事務，commit結束提交）

[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "begin;select @@server_id;commit;select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21021 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|       21022 |
+-------------+

顯然，一切都按照預期進行。

最後，如果想檢視路由的資訊，可查詢stats庫中的stats_mysql_query_digest表。以下是該表的一個輸出格式示例(和本文無關)。

admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 20 | 10675    | 14         | select @@server_id               |
| 10 | 2042     | 3          | begin                            |
| 10 | 534      | 3          | select @@server_id               |
| 10 | 349      | 3          | commit                           |
| 10 | 0        | 1          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
5 rows in set (0.002 sec)

因為有悔，所以披星戴月；因為有夢，所以奮不顧身！個人部落格首發：`easydb.net` 微信公眾號：`easydb` 關注我，不走丟！

MySQL cetus 中介軟體讀寫分離
2020-04-10
MySql
MySQL中介軟體之ProxySQL
2019-07-29
MySql
使用proxysql 1.4.14中介軟體實現mysql 5.7.26主從的讀寫分離
2019-11-04
MySql
MySQL 中介軟體Atlas 實現讀寫分離
2016-08-05
MySql
ProxySQL實現MySQL讀寫分離
2019-03-04
MySql
MySQL中介軟體之ProxySQL(14)：ProxySQL+PXC
2018-07-26
MySql
MySQL中介軟體--ProxySQL
2020-02-23
MySql
Mycat中介軟體實現Mysql主從讀寫分離
2019-11-02
MySql
MysqL讀寫分離的實現-Mysql proxy中介軟體的使用
2017-12-27
MySql
MySQL中介軟體之ProxySQL(7)：詳述ProxySQL的路由規
2021-09-09
MySql路由
ProxySQL實現Mysql讀寫分離 - 部署手冊
2018-07-02
MySql
Mycat中介軟體實現Percona Cluster讀寫分離
2019-11-14
MySQL中介軟體之ProxySQL(1)：簡介和安裝
2018-07-07
MySql
mysql優化之讀寫分離
2018-10-12
MySql優化
ProxySQL簡介原理及讀寫分離應用
2020-06-06
SQL
MHA+ProxySQL實現讀寫分離高可用
2020-12-16
SQL
使用ProxySQL實現MySQL Group Replication的故障轉移、讀寫分離（一）
2020-08-01
MySql
資料庫中介軟體sharding-jdbc實現讀寫分離
2020-10-06
資料庫JDBC
高可用Mysql架構_Mysql主從複製、Mysql雙主熱備、Mysql雙主雙從、Mysql讀寫分離（Mycat中介軟體）、Mysql分庫分表架構（Mycat中介軟體）的演變
2017-05-17
MySql架構
Mysql之讀寫分離架構-Atlas
2021-04-23
MySql架構
使用Consul和ProxySQL實現MySQL HA讀寫分離高可用性
2016-09-25
MySql
MySQL Amoeba讀寫分離
2015-01-29
MySql
Amoeba for mysql讀寫分離
2013-10-12
MySql
MySQL讀寫分離Atlas
2015-09-07
MySql
博學谷 - mysql資料庫效能優化筆記05 - 讀寫分離中介軟體MaxScale
2020-09-28
MySql資料庫優化筆記
【Mysql】mysql-proxy讀寫分離
2016-03-07
MySql
MyCat 讀寫分離資料庫分庫分表中介軟體安裝部署，及簡單使用
2019-03-01
資料庫
Centos8.3、proxysql2.0讀寫分離實戰記錄
2021-06-22
CentOSSQL
MySQL中介軟體之ProxySQL(5)：執行緒、執行緒池、連線池
2018-07-09
MySql執行緒
MySQL + Atlas --- 部署讀寫分離
2017-09-18
MySql
Amoeba for MySQL讀寫分離配置
2014-11-12
MySql
mysql讀寫分離(PHP類)
2015-07-06
MySqlPHP
Linux下MySQL主從複製(GTID)+讀寫分離(ProxySQL)-實施筆記
2021-08-30
LinuxMySql筆記
proxysql配置讀寫分離策略和權重調整一例
2018-10-09
SQL
MySQL 讀寫分離的好處
2019-07-15
MySql
探究MySQL MGR的讀寫分離
2018-07-16
MySql
Amoeba 實現 MySQL 讀寫分離
2016-06-28
MySql
mysql讀寫分離Amoeba的部署
2015-10-30
MySql