mysql讀寫分離——中間件ProxySQL的簡介與配置

mysql實現讀寫分離的方式 mysql 實現讀寫分離的方式有以下幾種：程式修改mysql操作，直接和資料庫通信，簡單快捷的讀寫分離和隨機的方式實現的負載均衡，許可權獨立分配，需要開發人員協助。 amoeba，直接實現讀寫分離和負載均衡，不用修改代碼，有很靈活的數據解決方案，自己分配賬戶，和後端數據 ...

mysql實現讀寫分離的方式

mysql 實現讀寫分離的方式有以下幾種：

程式修改mysql操作，直接和資料庫通信，簡單快捷的讀寫分離和隨機的方式實現的負載均衡，許可權獨立分配，需要開發人員協助。
amoeba，直接實現讀寫分離和負載均衡，不用修改代碼，有很靈活的數據解決方案，自己分配賬戶，和後端資料庫許可權管理獨立，許可權處理不夠靈活。
mysql-proxy，直接實現讀寫分離和負載均衡，不用修改代碼，master和slave用一樣的帳號，效率低
mycat中間件
proxysql中間件（推薦使用）

ProxySQL簡介

ProxySQL 是一款可以實際用於生產環境的 MySQL 中間件，它有官方版和 percona 版兩種。percona版是在官方版的基礎上修改的，添加了幾個比較實用的工具。生產環境建議用官方版。

ProxySQL 是用 C++ 語言開發的，雖然也是一個輕量級產品，但性能很好(據測試，能處理千億級的數據)，功能也足夠，能滿足中間件所需的絕大多數功能，包括：

最基本的讀/寫分離，且方式有多種
可定製基於用戶、基於schema、基於語句的規則對SQL語句進行路由。換句話說，規則很靈活。基於schema和與語句級的規則，可以實現簡單的sharding(分庫分表)
可緩存查詢結果。雖然ProxySQL的緩存策略比較簡陋，但實現了基本的緩存功能，絕大多數時候也夠用了。此外，作者已經打算實現更豐富的緩存策略
監控後端節點。ProxySQL可以監控後端節點的多個指標，包括：ProxySQL和後端的心跳信息，後端節點的read-only/read-write，slave和master的數據同步延遲性(replication lag)

ProxySQL安裝

[root@Lynk ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

[root@Lynk ~]# yum -y install proxysql

ProxySQL的Admin管理介面

當 ProxySQL 啟動後，將監聽兩個埠：

admin管理介面，預設埠為6032。該埠用於查看、配置ProxySQL
接收SQL語句的介面，預設埠為6033，這個介面類似於MySQL的3306埠

ProxySQL 的 admin 管理介面是一個使用 MySQL 協議的介面，所以，可以直接使用 mysql 客戶端、navicat 等工具去連接這個管理介面，其預設的用戶名和密碼均為 admin

例如，使用 mysql 客戶端去連接 ProxySQL 的管理介面：

[root@localhost src]# systemctl start proxysql
[root@localhost src]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

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

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

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

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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

由於 ProxySQL 的配置全部保存在幾個自帶的庫中，所以通過管理介面，可以非常方便地通過發送一些SQL命令去修改 ProxySQL 的配置。 ProxySQL 會解析通過該介面發送的某些對ProxySQL 有效的特定命令，並將其合理轉換後發送給內嵌的 SQLite3 資料庫引擎去運行

ProxySQL 的配置幾乎都是通過管理介面來操作的，通過 Admin 管理介面，可以線上修改幾乎所有的配置並使其生效。只有兩個變數的配置是必須重啟 ProxySQL 才能生效的，它們是：
mysql-threads 和 mysql-stacksize

admin管理介面相關的變數

admin-admin_credentials

admin-admin_credentials 變數控制的是admin管理介面的管理員賬戶。預設的管理員賬戶和密碼為admin:admin，但是這個預設的用戶只能在本地使用。如果想要遠程連接到ProxySQL，例如用windows上的navicat連接Linux上的ProxySQL管理介面，必須自定義一個管理員賬戶。

添加管理員帳戶

#查看當前管理員賬戶的用戶名密碼
mysql> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+
1 row in set (0.00 sec)
#添加一個用戶名為myadmin，密碼為lynk123的管理員賬戶
mysql> set admin-admin_credentials='admin:admin;myadmin:lynk123';
Query OK, 1 row affected (0.00 sec)
#使改變立即生效
mysql> load admin variables to runtime; 
Query OK, 0 rows affected (0.00 sec)
#永久化保存所進行的改變
mysql> save admin variables to disk;
Query OK, 31 rows affected (0.02 sec)

#使用我們剛剛添加的管理員登錄mysql
[root@localhost src]# mysql -umyadmin -plynk123 -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

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

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

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

#所有的配置操作都是在修改main庫中對應的表
mysql> select * from global_variables where variable_name='admin-admin_credentials';
+-------------------------+-----------------------------+
| variable_name           | variable_value              |
+-------------------------+-----------------------------+
| admin-admin_credentials | admin:admin;myadmin:lynk123 |
+-------------------------+-----------------------------+
1 row in set (0.00 sec)

必須要區分admin管理介面的用戶名和mysql_users中的用戶名

admin管理介面的用戶是連接到管理介面(預設埠6032)上用來管理、配置ProxySQL的
mysql_users表中的用戶名是應用程式連接ProxySQL(預設埠6033)，以及ProxySQL連接後端MySQL Servers使用的用戶。它的作用是發送、路由SQL語句，類似於MySQL Server的3306埠。所以，這個表中的用戶必須已經在後端MySQL Server上存在且授權了

admin管理介面的用戶必須不能存在於mysql_users中，這是出於安全的考慮，防止通過admin管理介面用戶猜出mysql_users中的用戶

admin-stats_credentials

admin-stats_credentials 變數控制admin管理介面的普通用戶，這個變數中的用戶沒有超級管理員許可權，只能查看monitor庫和main庫中關於統計的數據，其它庫都是不可見的，且沒有任何寫許可權

預設的普通用戶名和密碼均為 stats ，與admin一樣，它預設也只能用於本地登錄，若想讓人遠程查看則要添加查看的專有用戶

mysql> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats               |
+---------------------------+
1 row in set (0.00 sec)

#添加一個普通用戶
mysql> set admin-stats_credentials='stats:stats;mystats:lynk234';
Query OK, 1 row affected (0.00 sec)

mysql> select @@admin-stats_credentials;
+-----------------------------+
| @@admin-stats_credentials   |
+-----------------------------+
| stats:stats;mystats:lynk234 |
+-----------------------------+
1 row in set (0.00 sec)

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

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

同樣，這個變數中的用戶必須不能存在於mysql_users表中

admin-mysql_ifaces

admin-mysql_ifaces 變數指定admin介面的監聽地址，格式為冒號分隔的hostname:port列表。預設監聽在 0.0.0.0:6032

註意，允許使用UNIX的domain socket進行監聽，這樣本主機內的應用程式就可以直接被處理。
例如：

mysql> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock';
Query OK, 1 row affected (0.00 sec)

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

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

多層配置系統

proxysql中的庫

使用ProxySQL的Admin管理介面連上ProxySQL，可查看ProxySQL擁有的庫

[root@localhost src]# mysql -umyadmin -plynk123 -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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

main庫是ProxySQL最主要的庫，是需要修改配置時使用的庫，它其實是一個記憶體資料庫系統。所以，修改main庫中的配置後，必須將其持久化到disk上才能永久保存
disk庫是磁碟資料庫，該資料庫結構和記憶體資料庫完全一致。當持久化記憶體資料庫中的配置時，其實就是寫入到disk庫中。磁碟資料庫的預設路徑為 $DATADIR/proxysql.db
stats庫是統計信息庫。這個庫中的數據一般是在檢索其內數據時臨時填充的，它保存在記憶體中。因為沒有相關的配置項，所以無需持久化
monitor庫是監控後端MySQL節點相關的庫，該庫中只有幾個log類的表，監控模塊收集到的監控信息全都存放到對應的log表中
stats_history庫是1.4.4版新增的庫，用於存放歷史統計數據。預設路徑為 $DATADIR/proxysql_stats.db

ProxySQL多層配置系統

ProxySQL 的配置系統非常強大，它能線上修改幾乎所有配置(僅有的兩個需要重啟才能生效的變數為 mysql-threads 和 mysql-stacksize )，併在線生效、持久化保存。這得益於它採用的多層配置系統。
多層配置系統結構如下：

       +-------------------------+                      [1] ：將記憶體資料庫中的配置載入到RUNTIME數據結構中
       |         RUNTIME         |                               LOAD XXX FROM MEMORY
       +-------------------------+                               LOAD XXX TO RUNTIME
              /|\          |
               |           |                            [2] ：將RUNTIME數據結構中的配置持久化到記憶體資料庫中
           [1] |       [2] |                                     SAVE XXX FROM RUNTIME
               |          \|/                                    SAVE XXX TO MEMORY
       +-------------------------+
       |         MEMORY          |                      [3] ：將磁碟資料庫中的配置載入到記憶體資料庫中
       +-------------------------+ _                             LOAD XXX FROM DISK
              /|\          |      |\                             LOAD XXX TO MEMORY
               |           |        \
           [3] |       [4] |         \ [5]              [4] ：將記憶體資料庫中的配置持久化到磁碟資料庫中
               |          \|/         \                          SAVE XXX FROM MEMORY
       +-------------------------+  +---------------+            SAVE XXX TO DISK
       |          DISK           |  |  CONFIG FILE  |
       +-------------------------+  +---------------+   [5] ：從傳統配置文件中讀取配置載入到記憶體資料庫中
                                                                 LOAD XXX FROM CONFIG

最底層的是 disk 庫和 config file 。這裡需要註意，這裡的 config file 就是傳統的配置文件，預設為 /etc/proxysql.cnf ， ProxySQL 啟動時，主要是從 disk 庫中讀取配置載入到記憶體並最終載入到 runtime 生效，只有極少的幾個特定配置內容是從 config file 中載入的，除非是第一次初始化 ProxySQL 運行環境(或者disk庫為空)。

中間層的是 memory ，表示的是記憶體資料庫，其實就是 main 庫。通過管理介面修改的所有配置，都保存在記憶體資料庫(main)中。當 ProxySQL 重啟或者崩潰時，這個記憶體資料庫中的數據會丟失，所以需要 save 到 disk 庫中。

最上層的是 runtime ，它是 ProxySQL 有關線程運行時讀取的數據結構。換句話說，該數據結構中的配置都是已生效的配置。所以，修改了 main 庫中的配置後，必須 load 到 runtime 數據結構中才能使其生效。
在上面的多層配置系統圖中，標註了[1]、[2]、[3]、[4]、[5]的序號。每個序號都有兩個操作方向from/to，其實只是所站角度不同而已。

DISK/MEMORY/RUNTIME/CONFIG 可以縮寫，只要能識別即可。例如MEMORY可以縮寫為MEM，runtime可以縮寫為run

另外，上面的XXX是什麼？這表示要載入/保存的是哪類配置。目前的ProxySQL支持以下幾種：

mysql users
mysql servers
mysql variables
mysql query rules
admin variables
scheduler
proxysql_servers：目前ProxySQL集群功能還處於實驗階段，所以該類配置不應該去使用

這些從main庫或disk庫中就可以查看到

MySQL [(none)]> show tables from disk;
+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |  # (1)包含兩類變數，以amdin-開頭的表示admin variables，以mysql-開頭的表示mysql variables。修改哪類變數，前文的XXX就代表哪類
| mysql_collations                   |  # (N)只是一張表，保存的是ProxySQL支持的字元集和排序規則，它是不用修改的
| mysql_group_replication_hostgroups |  # (2)對應mysql servers
| mysql_query_rules                  |  # (3)對應mysql query rules
| mysql_query_rules_fast_routing     |  # (4)對應mysql query rules
| mysql_replication_hostgroups       |  # (5)對應mysql servers
| mysql_servers                      |  # (6)對應mysql servers
| mysql_users                        |  # (7)對應mysql users
| proxysql_servers                   |  # (8)是ProxySQL的集群配置表，該功能目前還處於實驗階段。
| scheduler                          |  # (9)對應scheduler
+------------------------------------+
10 rows in set (0.00 sec)

啟動ProxySQL時如何載入配置

如果 ProxySQL 是剛安裝的，或者磁碟資料庫文件為空(甚至不存在)，或者啟動 ProxySQL 時使用了選項 --initial，這幾種情況啟動 ProxySQL 時，都會從傳統配置文件 config file 中讀取配置載入到記憶體資料庫，並自動 load 到 runtime 數據結構、save到磁碟資料庫，這是初始化 ProxySQL 運行環境的過程。

如果不是第一次啟動 ProxySQL ，由於已經存在磁碟資料庫文件，這時 ProxySQL 會從磁碟資料庫中讀取幾乎所有的配置(即使傳統配置文件中配置了某項，也不會去解析)，但有3項是必須從傳統配置文件中讀取，它們分別是：

datadir：ProxySQL啟動時，必須從配置文件中確定它的數據目錄，因為磁碟資料庫文件、日誌以及其它一些文件是存放在數據目錄下的。如果使用/etc/init.d/proxysql管理ProxySQL，則除了修改/etc/proxysql.cnf的datadir，還需要修改該腳本中的datadir。
restart_on_missing_heartbeats：MySQL線程丟失多少次心跳，就會殺掉這個線程並重啟它。預設值為10。
execute_on_exit_failure：如果設置了該變數，ProxySQL父進程將在每次ProxySQL崩潰的時候執行已經定義好的腳本。建議使用它來生成一些崩潰時的警告和日誌。註意，ProxySQL的重啟速度可能只有幾毫秒，因此很多其它的監控工具可能無法探測到ProxySQL的一次普通故障，此時可使用該變數

不同類型的讀寫分離方案解析

資料庫中間件最基本的功能就是實現讀寫分離， ProxySQL 當然也支持。而且 ProxySQL 支持的路由規則非常靈活，不僅可以實現最簡單的讀寫分離，還可以將讀/寫都分散到多個不同的組，以及實現分庫 sharding (分表sharding的規則比較難寫，但也能實現)。

本文只描述通過規則制定的語句級讀寫分離，不討論通過 ip/port, client, username, schemaname 實現的讀寫分離。

下麵描述了ProxySQL能實現的常見讀寫分離類型

最簡單的讀寫分離

這種模式的讀寫分離，嚴格區分後端的master和slave節點，且slave節點必須設置選項read_only=1

在ProxySQL上，分兩個組，一個寫組HG=10，一個讀組HG=20。同時在ProxySQL上開啟monitor模塊的read_only監控功能，讓ProxySQL根據監控到的read_only值來自動調整節點放在HG=10(master會放進這個組)還是HG=20(slave會放進這個組)

這種模式的讀寫分離是最簡單的，只需在mysql_users表中設置用戶的預設路由組為寫組HG=10，併在mysql_query_rules中加上兩條簡單的規則(一個select for update，一個select)即可

這種讀寫分離模式，在環境較小時能滿足絕大多數需求。但是需求複雜、環境較大時，這種模式就太過死板，因為一切都是monitor模塊控制的

多個讀組或寫組的分離模式

前面那種讀寫分離模式，是通過 monitor 模塊監控 read_only 來調整的，所以每一個後端集群必須只能分為一個寫組，一個讀組。
但如果想要區分不同的 select ，並將不同的 select 路由到不同的節點上。例如有些查詢語句的開銷非常大，想讓它們獨占一個節點/組，其它查詢共用一個節點/組，怎麼實現？

例如，下麵這種模式

看上去非常簡單。但是卻能適應各種需求。例如，後端做了分庫，對某庫的查詢要路由到特定的主機組

至於各個主機組是同一個主從集群(下圖左邊)，還是互相獨立的主從集群環境(下圖右邊)，要看具體的需求，不過這種讀寫分離模式都能應付

在實現這種模式時，前提是不能開啟monitor模塊的read_only監控功能，也不要設置mysql_replication_hostgroup 表

例如，下麵的配置實現的是上圖左邊的結構：寫請求路由給HG=10，對test1庫的select語句路由給HG=20，其它select路由給HG=30

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | host1    | 3306 | ONLINE | 1      |
| 20           | host2    | 3306 | ONLINE | 1      |
| 30           | host3    | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_users: 
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root     | 10                |
+----------+-------------------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT.*test1\..*   |
| 3       | 30                    | ^SELECT              |
+---------+-----------------------+----------------------+

查看表結構的方式：

PRAGMA  table_info("表名");

ProxySQL實現讀寫分離示例

環境：

Cnetos7-Lynk-192.168.26.128-mysql主機-proxysql
Cnetos7-Hyrule-192.168.26.129-mysql從機

#安裝proxySQL
[root@Lynk ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

[root@Lynk ~]# yum -y install proxysql

#啟動proxysql並設置開機自動啟動
#proxysql預設只提供了sysv風格的啟動腳本，所以想設置開機自啟則需藉助於chkconfig工具
[root@Lynk ~]# systemctl start proxysql
[root@Lynk ~]# chkconfig proxysql on

#mysql主庫添加proxysql可以增刪改查的賬號
mysql> grant all on *.* to 'proxysql'@'127.0.0.1' identified by 'pwproxysql';
Query OK, 0 rows affected, 1 warning (0.59 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#登錄進proxy管理
[root@lynk ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

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

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

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

([email protected]:6032) [(none)]> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

資料庫說明：

main 記憶體配置資料庫，表裡存放後端db實例、用戶驗證、路由規則等信息。表名以 runtime開頭的表示proxysql當前運行的配置內容，不能通過dml語句修改，只能修改對應的不以 runtime 開頭的（在記憶體）里的表，然後 LOAD 使其生效， SAVE 使其存到硬碟以供下次重啟載入
disk 是持久化到硬碟的配置，sqlite數據文件
stats 是proxysql運行抓取的統計信息，包括到後端各命令的執行次數、流量、processlist、查詢種類彙總/執行時間等等
monitor 庫存儲 monitor 模塊收集的信息，主要是對後端db的健康/延遲檢查
stats_history 統計信息歷史庫

Proxysql管理端添加後端連接mysql主從資料庫的配置

([email protected]:6032) [(none)]> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |  # ProxySQL的基本配置參數，類似與MySQL
| mysql_collations                           |  # 配置對MySQL字元集的支持
| mysql_group_replication_hostgroups         |  # MGR相關的表，用於實例的讀寫組自動分配
| mysql_query_rules                          |  # 路由表
| mysql_query_rules_fast_routing             |  # 主從複製相關的表，用於實例的讀寫組自動分配
| mysql_replication_hostgroups               |  # 存儲MySQL實例的信息
| mysql_servers                              |  # 存儲MySQL用戶
| mysql_users                                |  # 存儲ProxySQL的信息，用於ProxySQL Cluster同步
| proxysql_servers                           |  # 運行環境的存儲校驗值
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |  # 與上面對應，但是運行環境正在使用的配置
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |  # 定時任務表
+--------------------------------------------+
20 rows in set (0.00 sec)

runtime_開頭的是運行時的配置，這些是不能修改的。要修改 ProxySQL 的配置，需要修改了非 runtime_ 表，修改後必須執行 LOAD ... TO RUNTIME 才能載入到 RUNTIME 生效，執行 save ... to disk 才能將配置持久化保存到磁碟

下麵語句中沒有先切換到 main 庫也執行成功了，因為 ProxySQL 內部使用的 SQLite3 資料庫引擎，和 MySQL 的解析方式是不一樣的。即使執行了 USE main 語句也是無任何效果的，但不會報錯

使用 insert 語句添加 mysql 主機到 mysql_servers 表中，其中：hostgroup_id 1 表示寫組，2表示讀組

([email protected]:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'127.0.0.1',3306,1,'Write Group');
Query OK, 1 row affected (0.01 sec)

([email protected]:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.26.129',3306,1,'Read Group');
Query OK, 1 row affected (0.00 sec)

([email protected]:6032) [(none)]> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1            | 127.0.0.1      | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Write Group |
| 2            | 192.168.26.129 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group  |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.00 sec)

#載入到RUNTIME，並保存到disk
([email protected]:6032) [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.02 sec)

([email protected]:6032) [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)

在 proxysql 主機的 mysql_users 表中添加剛纔在 master 上創建的賬號 proxysql，proxysql 客戶端需要使用這個賬號來訪問資料庫
default_hostgroup 預設組設置為寫組，也就是1;
當讀寫分離的路由規則不符合時，會訪問預設組的資料庫;

([email protected]:6032) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','pwproxysql',1,1);
Query OK, 1 row affected (0.00 sec)

([email protected]:6032) [(none)]> select * from mysql_users \G
*************************** 1. row ***************************
              username: proxysql        # 後端mysql實例的用戶名
              password: pwproxysql      # 後端mysql實例的密碼
                active: 1               # active=1表示用戶生效，0表示不生效
               use_ssl: 0
     default_hostgroup: 1               # 用戶預設登錄到哪個hostgroup_id下的實例
        default_schema: NULL            # 用戶預設登錄後端mysql實例時連接的資料庫，這個地方為NULL的話，則由全局變數mysql-default_schema決定，預設是information_schema
         schema_locked: 0
transaction_persistent: 1       # 如果設置為1，連接上ProxySQL的會話後，如果在一個hostgroup上開啟了事務，那麼後續的sql都繼續維持在這個hostgroup上，不論是否會匹配上其它路由規則，直到事務結束。雖然預設是0
          fast_forward: 0       # 忽略查詢重寫/緩存層，直接把這個用戶的請求透傳到後端DB。相當於只用它的連接池功能，一般不用，路由規則 .* 就行了
               backend: 1
              frontend: 1
       max_connections: 10000   # 該用戶允許的最大連接數
1 row in set (0.00 sec)

([email protected]:6032) [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

([email protected]:6032) [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

在主庫添加健康檢測的帳號

(root@localhost:mysql.sock) [(none)]> GRANT SELECT ON *.* TO 'monitor'@'127.0.0.1' IDENTIFIED BY 'monitor'; 
Query OK, 0 rows affected, 1 warning (0.07 sec)

(root@localhost:mysql.sock) [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

在proxysql主機端修改變數設置健康檢測的賬號

([email protected]:6032) [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.01 sec)

([email protected]:6032) [(none)]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)

([email protected]:6032) [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

([email protected]:6032) [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.01 sec)

添加讀寫分離的路由規則

將 select 查詢語句全部路由至 hostgroup_id=2 的組(也就是讀組)
但是 select * from tb for update 這樣的語句是會修改數據的，所以需要單獨定義，將它路由至 hostgroup_id=1 的組(也就是寫組)
其他沒有被規則匹配到的組將會被路由至用戶預設的組(mysql_users 表中的 default_hostgroup)

([email protected]:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
Query OK, 1 row affected (0.00 sec)

([email protected]:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
Query OK, 1 row affected (0.00 sec)

([email protected]:6032) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     |
| 2       | 1      | ^SELECT              | 2                     | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)

([email protected]:6032) [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.01 sec)

([email protected]:6032) [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

([email protected]:6032) [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.03 sec)

([email protected]:6032) [(none)]> save admin variables to disk;
Query OK, 31 rows affected (0.04 sec)

驗證讀寫分離

登錄用戶是剛纔我們在 mysql_user 表中創建的用戶，埠為6033

([email protected]:6033) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lynk               |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.01 sec)

嘗試修改資料庫和查詢

([email protected]:6033) [information_schema]> create database zelda;
Query OK, 1 row affected (0.00 sec)

([email protected]:6033) [information_schema]> select user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| proxysql      | 192.168.26.128 |
| repl          | 192.168.26.129 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
| zabbix        | localhost      |
+---------------+----------------+
6 rows in set (0.01 sec)

驗證讀寫分離是否成功

([email protected]:6032) [(none)]> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+----------------------------------+-------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                      | count_| max_time |
+-----------+--------------------+----------+--------------------+----------------------------------+-------+----------+
| 1         | information_schema | proxysql | 0x5E0E58962991A0EB | create database zelda            | 2     | 9274     |
| 2         | information_schema | proxysql | 0x0F02B330C823D739 | select user,host from mysql.user | 6     | 10002613 |
+-----------+--------------------+----------+--------------------+----------------------------------+-------+----------+
15 rows in set (0.01 sec)

從以上 hostgroup 和 digest_text 值來看，所有的寫操作都被路由至1組，讀操作都被路由至2組，其中1組為寫組，2組為讀組