MySQL中介軟體--ProxySQL
中文翻譯：
https://github.com/malongshuai/proxysql/wiki
ProxySQL
是
MySQL
的一款中介軟體的產品，是靈活的
MySQL
代理層，可以實現讀寫分離，支援
QUERY
路由器的功能，支援動態指定
SQL
進行快取，支援動態載入配置，故障切換和一些
SQL
的過濾功能。
其他產品：Dbproxy,MyCAT,OneProxy
等。
功能強大的MySQL
中介軟體；
官方站點：http://www.proxysql.com
官方的github
：
https://github.com/sysown/proxysql/releases/tag/v1.4.9
Percona ProxySQL
的文件手冊：
https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html
ProxySQL
是使用
C++
語言開發的，輕量級的產品，但效能非常好，功能很多，幾乎能夠滿足中介軟體所需的絕大多數的功能，主要包括：
1.
讀
/
寫分離，且支援多種方式；
2.
可以定義基於使用者、基於
Schema
、基於
SQL
語句的規則對
SQL
語句進行路由轉發；規則很靈活；
3.
基於
schema
和語句級別的規則，可以實現簡單的資料庫分片功能；
(Sharding)
4.
可以通過查詢快取來快取查詢結果；
5.
監控後端伺服器節點：後端伺服器的心跳資訊，後端伺服器的
read-only/read-write
資訊，
Slave
和
Master
的資料同步的延遲性
(replication lag)
；
獲取安裝程式包：
兩個發行版本：
官方的ProxySQL
Percona
的
ProxySQL
一.1
安裝
https://www.percona.com/downloads/
https://www.percona.com/downloads/proxysql2/
https://www.percona.com/downloads/proxysql/
https://github.com/sysown/proxysql/releases
安裝proxysql
[root@rhel6lhr soft]$
rpm -ivh proxysql2-2.0.7-1.2.el6.x86_64.rpm
warning: proxysql2-2.0.7-1.2.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing... ########################################### [100%]
1:proxysql2 ########################################### [100%]
[root@rhel6lhr soft]$
推薦rpm
安裝。
[root@rhel6lhr proxysql]$ proxysql --help
High Performance Advanced Proxy for MySQL
USAGE: proxysql [OPTIONS]
OPTIONS:
-c, --config ARG Configuration file
-D, --datadir ARG Datadir
-e, --exit-on-error Do not restart ProxySQL if crashes
-f, --foreground Run in foreground
-h, -help, --help, --usage Display usage instructions.
-M, --no-monitor Do not start Monitor Module
-n, --no-start Starts only the admin service
-r, --reuseport Use SO_REUSEPORT
-S, --admin-socket ARG Administration Unix Socket
-V, --version Print version
--idle-threads Create auxiliary threads to handle idle connections
--initial Rename/empty database file
--reload Merge config file into database file
--sqlite3-server Enable SQLite3 Server
ProxySQL rev. 2.0.7-percona-1.2 -- Fri Oct 18 05:47:24 2019
Copyright (C) 2013-2019 ProxySQL LLC
This program is free and without warranty
[root@rhel6lhr proxysql]$
[root@rhel6lhr proxysql]$ proxysql -V
ProxySQL version 2.0.7-percona-1.2, codename Truls
一.2
啟動
配置檔案路徑為：/etc/proxysql.cnf
啟動proxysql
service proxysql start
netstat -anlp |grep proxysql
6032
是管理埠，
6033
是對外服務的埠號
:
[root@rhel6lhr ~]$ netstat -anlp |grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 38917/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 38917/proxysql
使用者名稱和密碼預設都是admin
檢視proxysql
安裝庫情況：
mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql -uadmin -padmin -h192.168.59.130 -P6033
一.3
Proxysql
庫說明
[root@rhel6lhr ~]$ mysql -uadmin -padmin -h127.0.0.1 -P6032
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 3
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2014, 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.
(admin@127.0.0.1) [(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:
記憶體配置資料庫，即
memory
，表裡存放後端
db
例項，使用者驗證，路由規則等資訊。
Main
庫中有如下資訊：
mysql_servers--
後端可以連線
mysql
伺服器的列表
mysql_users--
配置後端資料庫的賬號和監控的賬號
mysql_query_rules--
指定
query
路由到後端不同伺服器的規則列表
disk
庫：持續化磁碟的配置。
Stats
庫：統計資訊的彙總。
Monitor
庫：一些監控的收集資訊，包括資料庫的健康狀態。
一、ProxySQL安裝
Proxy官方地址：https://proxysql.com/
proxysql-2.0.8-1-centos7.x86_64.rpm下載地址：https://github.com/sysown/proxysql/releases/tag/v2.0.8
安裝方法：
1、上傳安裝包到伺服器（CentOS7）
2、# yum install
proxysql-2.0.8-1-centos7.x86_64.rpm
二、ProxySQL架構示意圖
通過官方站點或官方的github專案，或Percona官方及Percona資源的映象站點；
安裝ProxySQL：[root@proxysql ~]# yum install proxysql啟動和關閉服務：[root@proxysql ~]# service proxysql start/stop驗證服務啟動的結果：
[root@proxysql ~]# ss -tnlp
LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=35296,fd=23))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=22))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=21))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=20))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=19))
看到上述結果，說明proxysql服務啟動成功；
登入方式：
可以使用mysql客戶端工具連線到proxysql的管理介面，該管理介面有預設的管理員賬戶和密碼，都是admin；
[root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
實驗中所涉及到的庫表的含義：
預設情況下，ProxySQL提供了幾個庫，每個庫都有各自的意義；檢視所有庫的方法：
MySQL [(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 |
+-----+---------------+-------------------------------------+
主要介紹main庫和monitor庫相關的表的功能；
MySQL [main]> show tables from main;
+--------------------------------------------+ | tables |
+--------------------------------------------+ | global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| 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 |
+--------------------------------------------+
MySQL [main]> show tables from monitor;
+------------------------------------+ | tables |
+------------------------------------+ | mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
注意： 1.所有的以"runtime_"開頭的表都是執行時的配置，是不能修改的。要完成ProxySQL的配置，僅能修改那些不是以"runtime_"開頭的表； 2.在執行表的修改之後，必須手動的將修改結果同步至執行時環境和持久化儲存到磁碟；
LOAD ... TO RUNTIME;
將指定的表中的修改結果同步至執行時環境；
SAVE ... TO DISK;
將指定的表中的修改結果同步至磁碟以實現持久化儲存；
注意：“...”表示表名，去掉真實表名中的"_"符號用空白字元代替；
示例：修改了msyql_servers表，
load mysql servers to runtime;
save mysql servers to disk;
mysql_servers表中有很多個欄位，每個欄位都有十分重要的意義；
MySQL [main]> show create table mysql_servers;
欄位 資料型別 是否為空 欄位預設值
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL
port INT NOT NULL DEFAULT 3306,
status VARCHAR NOT NULL DEFAULT 'ONLINE',
weight INT NOT NULL DEFAULT 1,
compression INT NOT NULL DEFAULT 0,
max_connections INT NOT NULL DEFAULT 1000,
max_replication_lag INT NOT NULL DEFAULT 0,
use_ssl INT NOT NULL DEFAULT 0,
max_latency_ms INT NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
各欄位的含義：
hostgroup_id：
後端MySQL例項所在的伺服器所在的主機組的編號；注意：一臺主機可能有多種角色，可能存在於多個主機組中；
hostname：
後端的MySQL例項所在的伺服器監聽的IP地址；該欄位沒有預設值，意味著在新增一個新的後端伺服器節點時，必須指定該欄位的值；
port：
後端的MySQL例項所在的伺服器監聽的埠號；預設值為3306，通常使用預設值即可；
status：
後端MySQL伺服器的執行時狀態，有四種狀態值：'ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'
ONLINE：此狀態表示後端MySQL伺服器節點完全正常；
SHUNNED：此狀態表示後端MySQL伺服器節點暫時被ProxySQL自動忽略，可能的原因是在一個較短時間內發生了大量的連線錯誤，也可能是Slave端與Master端之間的資料延遲過大；
OFFLINE_SOFT：此狀態表示ProxySQL不會再向此伺服器轉發任何請求，但此伺服器上尚未完成的事務會繼續執行，直到所有事務執行完畢之後，會進入"非活躍狀態"；graceful stop； 'OFFLINE_HARD：此狀態表示ProxySQL不會再向此伺服器轉發任何請求，同時此伺服器上正在執行的事務立即中斷結束，ProxySQL也會臨時將其移除出主機組，常用於維護操作；
weight：對應後端伺服器在主機組中的權重，權重值越高，ProxySQL就會向其轉發越多的請求；預設值為1；
compression：標記ProxySQL和後端MySQL伺服器之間建立的連線中，是否會先壓縮資料，再傳輸；預設值為0；
如果該欄位值為0，則不壓縮傳輸；
如果該欄位值大於0，則壓縮後傳輸；
max_connections：表示ProxySQL與後端MySQL伺服器之間允許建立的最大連線數；一旦達到最大值，即使後端伺服器的權重值再大，也不會再建立新的連線；預設值為1000，表示每個後端MySQL伺服器最多同時接受1000個來自於ProxySQL的連線；
通過合理的定義該欄位的值，可以避免後端MySQL伺服器超負荷運轉；
max_replication_lag：用於表示後端的SLave端在複製時延後於Master多長時間，如果超出該欄位定義的上限值，proxySQL會暫時忽略此伺服器，直到Slave的複製內容趕上Master為止(資料一致)；
use_ssl：表示是否允許ProxySQL和後端MySQL伺服器之間基於SSL協議建立連線；
max_latency_ms：監控模組定期向後端MySQL伺服器發起ping(SELECT)檢查操作的延遲時間；
comment：註釋資訊，說明資訊，可以隨意定義的內容，主要起輔助提示作業；
向ProxySQL插入監控節點：
MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.3',3306),(10,'172.16.75.4',3306),(10,'172.16.75.5',3306);
監控後端的MySQL節點：
新增節點完成之後，更重要的是監控各後端節點；後端必須是主從複製的環境；而ProxySQL會讀取後端MySQL伺服器的read_only伺服器引數的值，以區分該伺服器節點是屬於"讀組"還是"寫組"；
在後端的Master伺服器上建立一個用於監控功能的使用者，該使用者僅需要有"USAGE"許可權即可；如果想要監控在複製結構中Slave端是否與Master端存在驗證的延遲或滯後的狀態，即replication lag狀態，還需要讓該監控使用者具備"REPLICARION CLIENT"許可權；
**建立監控使用者的方法：**
在Master端執行下列SQL語句：
MariaDB [(none)]> grant replication client,replication slave on *.* to 'mmonitor'@'172.16.74.%' identified by '123';
在ProxySQL端執行下列SQL語句：
MySQL [main]> set mysql-monitor_username='mmonitor';
MySQL [main]> set mysql-monitor_password='qhdlink';
實際上是在修改global_variables表中對應的變數的值，所以可以直接使用UPDATE語句來修改對應的欄位的值：
UPDATE global_variables SET variable_value='mmonitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='qhdlink' WHERE variable_name='mysql-monitor_password';
讓此前新增的後端MySQL伺服器節點及用於監控各節點的使用者生效：
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
MySQL [main]> load mysql variables to runtime;
MySQL [main]> save mysql variables to disk;
驗證監控的結果：
ProxySQL監控模組的指標都儲存在monitor庫的各log表中；
如果想檢視連線是否正常的監控資訊，在mysql_server_connect_log表中
MySQL [(none)]> select * from mysql_server_connect_log limit 3;
+-------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+---------------+
| 172.16.74.2 | 3306 | 1533700332922373 | 1945 | NULL |
| 172.16.75.1 | 3306 | 1533700332933127 | 1689 | NULL |
| 172.16.75.2 | 3306 | 1533700332944270 | 50012 | NULL |
+-------------+------+------------------+-------------------------+---------------+
3 rows in set (0.00 sec)
可能會看到一些connect_error資訊，這是因為此前並沒有設定正確的用於監控的使用者賬戶資訊；在成功的配置監控使用者之後，connect_error自動的值應該是"NULL"，表示連線正常；
如果想檢視後端MySQL伺服器的心跳資訊的監控，在mysql_server_ping_log表中；
MySQL [(none)]> select * from mysql_server_ping_log limit 3;
+-------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error |
+-------------+------+------------------+----------------------+------------+ | 172.16.74.2 | 3306 | 1533700268376052 | 649 | NULL |
| 172.16.75.1 | 3306 | 1533700268377872 | 602 | NULL |
| 172.16.75.2 | 3306 | 1533700268379770 | 824 | NULL |
+-------------+------+------------------+----------------------+------------+ 3 rows in set (0.00 sec)
由於尚未對後端MySQL伺服器進行明確的節點分組，所以mysql_server_read_only_log和mysql_server_replication_lag_log都是空的；
要想定義後端MySQL伺服器的分組，在mysql_replication_hostgroups表中新增對應組的ID即可；
writer_hostgroup
reader_hostgroup
預設mysql_replication_hostgroups表是空表，想要定義讀組和寫組的ID，需要向該表中插入資料：
MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,2);
** 注意：應該保證插入的組的ID應該與此前插入的主機所定義的組ID相同；**
此時並不能立即生效，檢視mysql_servers表中各主機所屬組的時候，看到的是舊的資料；
MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
+--------------+-------------+------+ | hostgroup_id | hostname | port |
+--------------+-------------+------+ | 10 | 172.16.75.1 | 3306 |
| 10 | 172.16.75.2 | 3306 |
| 10 | 172.16.74.2 | 3306 |
+--------------+-------------+------+
將此前修改的內容載入到runtime之後，才能使其生效；
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
再檢視主機分組的資訊：
MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
+--------------+-------------+------+ | hostgroup_id | hostname | port |
+--------------+-------------+------+ | 10 | 172.16.75.1 | 3306 |
| 2 | 172.16.75.2 | 3306 |
| 2 | 172.16.74.2 | 3306 |
+--------------+-------------+------+
Monitor模組會根據後端MySQL伺服器上的read_only伺服器變數的值判斷並將對應的節點自動移動到讀組或寫組中；
在monitor庫的mysql_server_read_only_log表中就會有後端MySQL伺服器的read_only屬性；
MySQL [main]> select * from mysql_server_read_only_log limit 3;
+-------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error |
+-------------+------+------------------+-----------------+-----------+-------+
| 172.16.75.1 | 3306 | 1533627317448205 | 2403 | 0 | NULL |
| 172.16.75.2 | 3306 | 1533627317449233 | 2670 | 1 | NULL |
| 172.16.74.2 | 3306 | 1533627317450825 | 2213 | 1 | NULL |
+-------------+------+------------------+-----------------+-----------+-------+
管理mysql_users
上述所有配置都是與後端MySQL伺服器節點相關的，除此之外，ProxySQL還可以配置與SQL語句相關的內容，包括：傳送SQL請求的使用者，SQL語句路由規則，SQL的查詢快取，SQL語句的重寫等；
傳送SQL請求的使用者的相關配置：
root
sqlsender
在後端的Master伺服器上建立對應使用者賬戶：
MariaDB [(none)]> grant all on *.* to 'root'@'172.16.74.%' identified by '123';
MariaDB [(none)]> grant all on *.* to 'sqlsender'@'172.16.74.%' identified by '123';
在ProxySQL上，需要向mysql_users表中新增使用者賬戶；
MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('root','123',10),('sqlsender','123',2);
新增使用者之後，需要將修改後的資料載入至runtime及持久化儲存至磁碟：
MySQL [main]> load mysql users to runtime;
MySQL [main]> save mysql users to disk;
注意：在向mysql_users表中新增使用者時，至少要指定username，password和default_hostgroup三個欄位；
username：前端連線到ProxySQL以及ProxySQL將SQL語句路由至MySQL時所使用的使用者名稱；
password：使用者對應的密碼，可以是明文儲存，也可以使用hash加密的密碼存放，如果想要使用hash密碼，需要藉助於PASSWORD('password_string')函式；
default_hostgroup：用於為使用者名稱設定預設的路由目標；
其他的欄位的含義：
active：對應的使用者是否為有效使用者；
transaction_persistent：是否啟用事務持久化，如果值為1，意味著當某連線使用該使用者開啟了一個事務之後，此事務在提交或回滾之前，所有的語句都被路由到同一組中，以避免同一事務的SQL語句分散到不同組，從而引發資料混亂或事務無法正常提交執行；預設值為1；此欄位在較早的proxySQL版本中有預設值為0的情況，如果出現此類情況，在新增使用者時必須手動設定其值為1，而不使用預設值；
檢視mysql_users表的內容：
MySQL [(none)]> select * from mysql_users;
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| sqlsender | 123 | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
使用主機172.16.74段的任意主機（這裡使用172.16.74.1），測試基於使用者賬戶的讀寫分離機制，讀請求訪問由結果可知預設的訪問後臺讀伺服器是隨機訪問而不是輪詢方式訪問：
[root@mysqlrouter ~]# mysql -uroot -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 201 |
+-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+
基於SQL語句實現讀寫分離
需要新增路由規則：
與查詢規則有關的表有兩個：
mysql_query_rules
mysql_query_rules_fast_routing
mysql_query_rules_fast_routing是mysql_query_rules的擴充套件表，在proxysql 1.4.7以後才出現，支援快速路由表
mysql_query_rules常用的配置欄位：
rule_id：規則編號，自動增長的整數，可以不指定；
active：規則是否有效，預設值為0，表示無效，需要在定義規則時，將其設定為1；
match_digest：定義規則的具體匹配內容；由正規表示式元字元組成，用來匹配SQL語句；
destination_hostgroup：對於符合規則的請求，設定目標主機組，從而實現路由轉發；
apply：是否有效提交；預設值為0，表示無效，需要在定義規則時，將其值設定為1；
新增查詢路由規則：
MySQL [none]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',2,1);
檢視新建立的路由規則：
MySQL [(none)]> select * from mysql_query_rules\G;
1. row
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: ^SELECT.*FOR UPDATE$
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 10
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
log: NULL
apply: 1
comment: NULL
2. row
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: ^SELECT
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 2
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
log: NULL
apply: 1
comment: NULL
2 rows in set (0.00 sec)
ERROR: No query specified
將規則的修改載入值runtime並持久化儲存至磁碟：
MySQL [main]> load mysql query rules to runtime;
MySQL [main]> save mysql query rules to disk;
測試事務持久化：
在Master主伺服器的MySQL互動模式中：
先建立資料庫mytest：
MariaDB [mysql]> use mytest;
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
MariaDB [mytest]> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
[root@mysqlrouter ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'set @@autocommit=0;\
start transaction;\
use mytest;\
insert into t1 values (1000),(2000);\
select @@server_id;\
commit;\
'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
測試proxysql基於SQL請求實現讀寫分離：
-P指定6032是管理埠，指定6033是查詢埠
[root@proxysql ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id for update;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
[root@proxysql ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 201 |
+-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+
修改proxysql預設埠的辦法：
proxysql一旦啟動起來，其資料庫檔案已經形成的情況下，配置檔案的內容將會被跳過，要是想修改內容的話，必須在執行時環境設定，並且儲存到檔案才可以。
步驟：
先在執行時環境（proxysql的互動模式中）設定如下：
MySQL [(none)]> set mysql-interfaces='0.0.0.0:3306';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 95 rows affected (0.03 sec)
MySQL [(none)]> select @@mysql-interfaces;
+--------------------+| @@mysql-interfaces |+--------------------+| 0.0.0.0:3306 |+--------------------+1 row in set (0.00 sec)
然後重啟proxysql服務就可以了
[root@mysqlrouter ~]# service proxysql stopShutting down ProxySQL: DONE!
[root@mysqlrouter ~]# service proxysql startStarting ProxySQL: DONE!
檢視監聽埠狀態：
[root@mysqlrouter ~]# ss -tnlpState Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=22))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=21))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=20))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=19))
LISTEN 0 128 *:111 *:* users:(("rpcbind",pid=758,fd=8))
LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=36322,fd=23))
至此，MySQLRouter和Proxysql實現MySQL/MariaDB讀寫分離部署完成。
1、ProxySQL說明
ProxySQL是mysql的一款中介軟體的產品，是靈活的mysql代理層，可以實現讀寫分離，支援query路由器的功能，支援動態指定sql進行快取，支援動態載入配置，故障切換和一些sql的過濾功能。
其他產品：Dbproxy,MyCAT,OneProxy等。
2、MHA+Proxysql 讀寫分離實驗
2.1. 安裝步驟
|
IP |
角色 |
作業系統 |
版本 |
|
172.16.10.21 |
Proxysql |
Redhat6.7 |
1.4.9 |
|
172.16.10.32 |
Master |
Redhat6.7 |
5.7.20 |
|
172.16.10.34 |
Slave1 |
Redhat6.7 |
5.7.20 |
|
172.16.10.36 |
Salve2 |
Redhat6.7 |
5.7.20 |
|
172.16.10.30 |
VIP |
|
|
從庫開啟read_only=1，主庫read_only=0
ProxySQL安裝原始碼包：
yum -y install perl-DBD-MYSQL perl-DBI perl-Time-Hires perl-IO-Socket-ssl
或者簡單粗暴的 ：yum -y install perl*
proxySQL軟體包下載地址：
https://www.percona.com/downloads/proxysql/
安裝proxysql
rpm -ivh proxysql-1.4.9-1.1.el6.x86_64.rpm
配置檔案路徑為：/etc/proxysql.cnf
啟動proxysql
service proxysql start
netstat -anlp |grep proxysql
6032是管理埠,6033是對外服務的埠號
使用者名稱和密碼預設都是admin
使用幫助如下：
檢視proxysql 安裝庫情況：
mysql -uadmin -padmin -h127.0.0.1 -P6032
2.2. Proxysql庫說明
Proxysql 版本1.4.9-percona-1.1例項：
Main:記憶體配置資料庫，即memory，表裡存放後端db例項，使用者驗證，路由規則等資訊。Main庫中有如下資訊：
mysql_servers --後端可以連線mysql伺服器的列表
mysql_users --配置後端資料庫的賬號和監控的賬號
mysql_query_rules --指定query路由到後端不同伺服器的規則列表
disk庫：持續化磁碟的配置。
Stats庫：統計資訊的彙總。
Monitor庫：一些監控的收集資訊，包括資料庫的健康狀態。
2.3. 配置proxysql監控
https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
頂層為runtime,中間層為memory，底層也就是持久層disk和config file。
Runtime:代表Proxysql當前生效的正在使用的配置，無法直接修改這裡的配置，必須要從下一層load進來。
Memory:memory層上面連線runtime層，下面連線持久化層。在這層可以正常操作Proxysql配置，隨便修改，不會影響生產環境。修改一個配置一般都是現在memory層完成，確認正常後在載入到runtime和持久化到磁碟。
Disk和config file：持久化配置資訊，重啟後記憶體的配置資訊會丟失，所以需要將配置資訊保留在磁碟中。重啟時，可以從磁碟快速載入回來。
1為寫組，2為讀組。
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.32',3307);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.34',3307);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.36',3307);
select * from mysql_servers;
配置監控賬戶：
create user 'mon'@'172.16.10.%' IDENTIFIED BY 'mon';
GRANT all privileges ON *.* TO 'mon'@'172.16.10.%' with grant option;
對外訪問賬戶：
create user 'wr'@'172.16.10.%' IDENTIFIED BY 'wr';
GRANT all privileges ON *.* TO ON *.* TO 'wr'@'172.16.10.%' with grant option;
配置Proxysql監控:
set mysql-monitor_username='mon';
set mysql-monitor_password='mon';
load mysql servers to runtime;
save mysql servers to disk;
之後驗證監控資訊：
select * from monitor.mysql_server_connect_log limit 6;
select * from monitor.mysql_server_ping_log order by time_start_us limit 6;
監控資訊提示正常。
2.4. 配置Proxysql主從分割槽資訊
配置主從分割槽需要用到mysql_replication_hostgroups
show create table mysql_replication_hostgroups\G;
writer_hostgroup 寫入組的編號
reader_hostgroup 讀取組的編號
實驗使用10作為寫入組，20作為讀取組。
insert into mysql_replication_hostgroups values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_replication_hostgroups;
Proxysql 會根據server的read_only的取值將服務進行分組，read_only=0的server，master被分到編號為10的組，read_only=1的server，slave則被分到編號為20的讀組。
select * from mysql_servers;
Mysql_users表中的 transaction_persistent欄位預設為0，建議在建立完使用者之後設定為1，避免發生髒讀幻讀等現象：
insert into mysql_users(username,password,default_hostgroup) values('wr','wr',10);
update mysql_users set transaction_persistent=1 where username='wr';
load mysql users to runtime;
save mysql users to disk;
測試登陸（埠6033）：
mysql -uwr -pwr -h 172.16.10.34 -P3307 -e "show slave status\G"
2.5. 配置讀寫分離策略
配置讀寫分離使用的表mysql_query_rules：
match_pattern:欄位就是代表設定的規則。
destination_hostgroup:欄位代表預設指定的分組。
apply代表真正執行應用規則。
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
2.6. 測試讀寫分離
通過wr所建立的賬戶連線Proxysql登陸資料庫。
mysql -uwr -pwr -h172.16.10.21 -P6033
通過管理埠登陸檢視：
mysql -uadmin -padmin -h127.0.0.1 -P6032
select * from stats_mysql_query_digest;
可以得知，select count(*) from t; 這條語句自動編號到20的讀組上，即slave上。
測試update。
測試update語句在10的寫組上。
2.7. 讀寫分離權重調整
讀寫分離設定成功後，可以調節權重，如slave2（172.16.10.36）多進行讀操作。
update mysql_servers set weight=10 where hostname='172.16.10.36';
load mysql servers to runtime;
load mysql variables to runtime;
load mysql users to runtime;
save mysql servers to disk;
save mysql variables to disk;
save mysql users to disk;
select * from mysql_servers;
2.8. MHA failover測試
測試前：
Master 172.16.10.32為master，組數為10，寫組。
Failover後：
新的master為172.16.10.34（原slave1）
select * from runtime_mysql_servers;
新的master為寫組（10），原為20讀組。
進行讀寫分離測試：
發現讀寫分離仍然成功（回切後也成功）。
MySQL中介軟體之ProxySQL(7)：詳述ProxySQL的路由規則
返回
ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
當ProxySQL收到前端app傳送的SQL語句後，它需要將這個SQL語句(或者重寫後的SQL語句)傳送給後端的MySQL Server，然後收到SQL語句的MySQL Server執行查詢，並將查詢結果返回給ProxySQL，再由ProxySQL將結果返回給客戶端(如果設定了查詢快取，則先快取查詢結果)。
ProxySQL可以實現多種方式的路由：基於ip/port、username、schema、SQL語句。其中基於SQL語句的路由是按照規則進行匹配的，匹配 方式有hash高效匹配、正則匹配，還支援更復雜的鏈式規則匹配。
本文將簡單演示基於埠、使用者和schema的路由，然後再詳細介紹基於SQL語句的路由規則。不過需要說明的是，本文只是入門，為後面ProxySQL的高階路由方法做鋪墊。
在閱讀本文之前，請確保：
-
已經理解ProxySQL的多層配置系統，可參考：
ProxySQL的多層配置系統
- 會操作ProxySQL的Admin管理介面，可參考：
ProxySQL的Admin管理介面
- 已經配置好了後端節點、mysql_users等。可參考：
ProxySQL管理後端節點
如果想速成，可參考；
ProxySQL初試讀寫分離
本文涉及到的實驗環境如下：
| Proxysql |
192.168.100.21 |
null |
無 |
| Master |
192.168.100.22 |
110 |
剛安裝的全新MySQL例項 |
| Slave1 |
192.168.100.23 |
120 |
剛安裝的全新MySQL例項 |
| Slave2 |
192.168.100.24 |
130 |
剛安裝的全新MySQL例項 |
該實驗環境已經在前面的文章中搭建好，本文不再贅述一大堆的內容。環境的搭建請參考前面給出的1、2、3。
我前面寫了一篇
通過MySQL Router實現MySQL讀寫分離的文章，MySQL Router實現讀寫分離的方式就是通過監聽不同埠實現的：一個埠負責讀操作，一個埠負責寫操作。這樣的路由邏輯非常簡單，配置起來也很方便。
雖然基於埠實現讀寫分離配置起來非常簡單，但是缺點也很明顯：必須在前端app的程式碼中指定埠號碼。這意味著MySQL的一部分流量許可權被開發人員掌控了，換句話說，DBA無法全域性控制MySQL的流量。此外，修改埠號時，app的程式碼也必須做出相應的修改。
雖說有缺點，但為了我這個ProxySQL系列文章的完整性，本文還是要簡單演示ProxySQL如何基於埠實現讀寫分離。
首先修改ProxySQL監聽SQL流量的埠號，讓其監聽在不同埠上。
admin> set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
admin> save mysql variables to disk;
然後重啟ProxySQL。
[root@xuexi ~]# service proxysql stop
[root@xuexi ~]# service proxysql start
[root@xuexi ~]# netstat -tnlp | grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 27572/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 27572/proxysql
tcp 0 0 0.0.0.0:6034 0.0.0.0:* LISTEN 27572/proxysql
監聽到不同埠，再去修改
mysql_query_rules表。這個表是ProxySQL的路由規則定製表，後文會非常詳細地解釋該表。
例如，插入兩條規則，分別監聽在6033埠和6034埠，6033埠對應的
hostgroup_id=10是負責寫的組，6034對應的
hostgroup_id=20是負責讀的組。
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply)
values(1,1,6033,10,1), (2,1,6034,20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
這樣就配置結束了，是否很簡單？
其實除了基於埠進行分離，還可以基於監聽地址(修改欄位proxy_addr即可)，甚至可以基於客戶端地址(修改欄位client_addr欄位即可，該用法可用於採集資料、資料分析等)。
無論哪種路由方式，其實都是在修改mysql_query_rules表，所以下面先解釋下這個表。
可以通過
show create table mysql_query_rules語句檢視定義該表的語句。
下面是我整理出來的欄位屬性。
| COLUMN | TYPE | NULL? | DEFAULT ||-----------------------|---------|----------|------------|| rule_id (pk) | INTEGER | NOT NULL | || active | INT | NOT NULL | 0 || username | VARCHAR | | || schemaname | VARCHAR | | || flagIN | INT | NOT NULL | 0 || client_addr | VARCHAR | | || proxy_addr | VARCHAR | | || proxy_port | INT | | || digest | VARCHAR | | || match_digest | VARCHAR | | || match_pattern | VARCHAR | | || negate_match_pattern | INT | NOT NULL | 0 || re_modifiers | VARCHAR | | 'CASELESS' || flagOUT | INT | | || replace_pattern | VARCHAR | | || destination_hostgroup | INT | | NULL || cache_ttl | INT | | || reconnect | INT | | NULL || timeout | INT | | || retries | INT | | || delay | INT | | || mirror_flagOU | INT | | || mirror_hostgroup | INT | | || error_msg | VARCHAR | | || sticky_conn | INT | | || multiplex | INT | | || log | INT | | || apply | INT | NOT NULL | 0 || comment | VARCHAR | | |
各個欄位的意義如下：有些欄位不理解也無所謂，後面會分析一部分比較重要的。
-
rule_id：規則的id。
規則是按照rule_id的順序進行處理的。
-
active：只有該欄位值為1的規則才會載入到runtime資料結構，所以只有這些規則才會被查詢處理模組處理。
-
username：使用者名稱篩選，當設定為非NULL值時，只有匹配的使用者建立的連線發出的查詢才會被匹配。
-
schemaname：schema篩選，當設定為非NULL值時，只有當連線使用
schemaname作為預設schema時，該連線發出的查詢才會被匹配。(在MariaDB/MySQL中，schemaname等價於databasename)。
-
flagIN,flagOUT：這些欄位允許我們建立"鏈式規則"(chains of rules)，一個規則接一個規則。
-
apply：當匹配到該規則時，立即應用該規則。
-
client_addr：通過源地址進行匹配。
-
proxy_addr：當流入的查詢是在本地某地址上時，將匹配。
-
proxy_port：當流入的查詢是在本地某埠上時，將匹配。
-
digest：通過digest進行匹配，digest的值在
stats_mysql_query_digest.digest中。
-
match_digest：通過正規表示式匹配digest。
-
match_pattern：通過正規表示式匹配查詢語句的文字內容。
-
negate_match_pattern：設定為1時，表示未被
match_digest或
match_pattern匹配的才算被成功匹配。也就是說，相當於在這兩個匹配動作前加了NOT操作符進行取反。
-
re_modifiers：RE正則引擎的修飾符列表，多個修飾符使用逗號分隔。指定了
CASELESS後，將忽略大小寫。指定了
GLOBAL後，將替換全域性(而不是第一個被匹配到的內容)。為了向後相容，預設只啟用了
CASELESS修飾符。
-
replace_pattern：將匹配到的內容替換為此欄位值。它使用的是RE2正則引擎的Replace。注意，這是可選的，當未設定該欄位，查詢處理器將不會重寫語句，只會快取、路由以及設定其它引數。
-
destination_hostgroup：將匹配到的查詢路由到該主機組。但注意，如果使用者的
transaction_persistent=1(見
mysql_users表)，且該使用者建立的連線開啟了一個事務，則這個事務內的所有語句都將路由到同一主機組，無視匹配規則。
-
cache_ttl：查詢結果快取的時間長度(單位毫秒)。注意，在ProxySQL 1.1中，cache_ttl的單位是秒。
-
reconnect：目前不使用該功能。
-
timeout：被匹配或被重寫的查詢執行的最大超時時長(單位毫秒)。如果一個查詢執行的時間太久(超過了這個值)，該查詢將自動被殺掉。如果未設定該值，將使用全域性變數
mysql-default_query_timeout的值。
-
retries：當在執行查詢時探測到故障後，重新執行查詢的最大次數。如果未指定，則使用全域性變數
mysql-query_retries_on_failure的值。
-
delay：延遲執行該查詢的毫秒數。本質上是一個限流機制和QoS，使得可以將優先順序讓位於其它查詢。這個值會寫入到
mysql-default_query_delay全域性變數中，所以它會應用於所有的查詢。將來的版本中將會提供一個更高階的限流機制。
-
mirror_flagOUT和mirror_hostgroup：
mirroring相關的設定，目前mirroring正處於實驗階段，所以不解釋。
-
error_msg：查詢將被阻塞，然後向客戶端返回
error_msg指定的資訊。
-
sticky_conn：當前還未實現該功能。
-
multiplex：如果設定為0，將禁用multiplexing。如果設定為1，則啟用或重新啟用multiplexing，除非有其它條件(如使用者變數或事務)阻止啟用。如果設定為2，則只對當前查詢不禁用multiplexing。預設值為
NULL，表示不會修改multiplexing的策略。
-
log：查詢將記錄日誌。
-
apply：當設定為1後，當匹配到該規則後，將立即應用該規則，不會再評估其它的規則(注意：應用之後，將不會評估
mysql_query_rules_fast_routing中的規則)。
-
comment：註釋說明欄位，例如描述規則的意義。
基於mysql user的配置方式和基於埠的配置是類似的。
需要注意，在插入mysql user到
mysql_users表中時，就已經指定了預設的路由目標組，這已經算是一個路由規則了(只不過是預設路由目標)。當成功匹配到
mysql_query_rules中的規則時，這個預設目標就不再生效。所以，通過預設路由目標，也能簡單地實現讀寫分離。
例如，在後端MySQL Server上先建立好用於讀、寫分離的使用者。例如，root使用者用於寫操作，reader使用者用於讀操作。
# 在master節點上執行：grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';grant select,show databases,show view on *.* to reader@'192.168.100.%' identified by 'P@ssword1!';
然後將這兩個使用者新增到ProxySQL的
mysql_users表中，並建立兩條規則分別就有這兩個使用者進行匹配。
insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10),('reader','P@ssword1!',20);load mysql users to runtime;save mysql users to disk;delete from mysql_query_rules; # 為了測試，先清空已有規則insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) values(1,1,'root',10,1),(2,1,'reader',20,1);load mysql query rules to runtime;save mysql query rules to disk;
當然，在上面演示的示例中，mysql_query_rules中基於username的規則和mysql_users中這兩個使用者的預設規則是重複了的。
ProxySQL支援基於schemaname進行路由。這在一定程度上實現了簡單的sharding功能。例如，將後端MySQL叢集中的節點A和節點B定義在不同主機組中，ProxySQL將所有對於DB1庫的查詢路由到節點A所在的主機組，將所有對DB2庫的查詢路由到節點B所在的主機組。
只需配置一個schemaname欄位就夠了，好簡單，是不是感覺很爽。但想太多了，ProxySQL的schemaname欄位只是個雞肋，要實現分庫sharding，只能通過正則匹配、查詢重寫的方式來實現。
例如，原語句如下，用於找出浙江省的211大學。
select * from zhongguo.university where prov='Zhejiang' and high=211;
按省份分庫後，通過ProxySQL的正則替換，將語句改寫為如下SQL語句：
select * from Zhejiang.university where 1=1 high=211;
然後還可以將改寫後的SQL語句路由到指定的主機組中，實現真正的分庫。
這些內容比較複雜、也比較高階，在後面的文章中我會詳細解釋。
從這裡開始，開始介紹ProxySQL路由規則的核心：基於SQL語句的路由。
ProxySQL接收到前端傳送的SQL語句後，首先分析語句，然後從
mysql_query_rules表中尋找是否有匹配該語句的規則。如果先被username或ip/port類的規則匹配並應用，則按這些規則路由給後端，如果是被基於SQL語句的規則匹配，則啟動正則引擎進行正則匹配，然後路由給對應的後端組，如果規則中指定了正則替換欄位，則還會重寫SQL語句，然後再傳送給後端。
ProxySQL支援兩種型別的SQL語句匹配 方式：match_digest和match_pattern。在解釋這兩種匹配 方 式之前，有必要先解釋下SQL語句的引數化。
什麼是引數化？
select * from tbl where id=?
這裡將where條件語句中欄位id的值進行了引數化，也就是上面的問號
?。
我們在客戶端發起的SQL語句都是完整格式的語句，但是SQL優化引擎出於優化的目的需要考慮很多事情。例如，如何快取查詢結果、如何匹配查詢快取中的資料並取出，等等。將SQL語句引數化是優化引擎其中的一個行為，對於那些引數相同但引數值不同的查詢語句，SQL語句認為這些是同類查詢，同類查詢的SQL語句不會重複去編譯而增加額外的開銷。
例如，下面的兩個語句，就是同類SQL語句：
select * from tbl where id=10;select * from tbl where id=20;
將它們引數化後，結果如下：
select * from tbl where id=?;
通俗地講，這裡的"?"就是一個變數，任何滿足這個語句型別的值都可以傳遞到這個變數中。
所以，
對引數化進行一個通俗的定義：對於那些引數相同、引數值不同的SQL語句，使用問號"?"去替換引數值，替換後返回的語句就是引數化的結果。
無論是MySQL、SQL Server還是Oracle(這個不確定)，優化引擎內部都會將語句進行引數化。例如，下面是SQL Server的執行計劃，其中"@1"就是所謂的問號"?"。
ProxySQL也支援引數化。當前端傳送SQL語句到達ProxySQL後，ProxySQL會將其引數化並分類。例如，下面是sysbench測試過程中，ProxySQL統計的引數化語句。
+----+----------+------------+-------------------------------------------------------------+| hg | sum_time | count_star | digest_text |+----+----------+------------+-------------------------------------------------------------+| 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? || 1 | 3142041 | 5001 | COMMIT || 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c || 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? || 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? || 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? || 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? || 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? || 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) || 1 | 875343 | 5005 | BEGIN |+----+----------+------------+-------------------------------------------------------------+
ProxySQL的
mysql_query_rules表中有三個欄位，能基於
引數化後的SQL語句進行三種不同方式的匹配：
如果要進行SQL語句的重寫(即正則替換)，或者對引數值匹配，則必須採用match_pattern。如果可以，儘量採用digest匹配 方式，因為它的效率更高。
在ProxySQL的stats庫中，包含了幾個統計表。
admin> show tables from stats;+--------------------------------------+| tables |+--------------------------------------+| global_variables || stats_memory_metrics || stats_mysql_commands_counters | <--已執行查詢語句的統計資訊| stats_mysql_connection_pool | <--連線池資訊| stats_mysql_connection_pool_reset | <--重置連線池統計資料| stats_mysql_global | <--全域性統計資料| stats_mysql_prepared_statements_info || stats_mysql_processlist | <--模擬show processlist的結果| stats_mysql_query_digest | <--本文解釋| stats_mysql_query_digest_reset | <--本文解釋| stats_mysql_query_rules | <--本文解釋| stats_mysql_users | <--各mysql user前端和ProxySQL的連線數| stats_proxysql_servers_checksums | <--ProxySQL叢集相關| stats_proxysql_servers_metrics | <--ProxySQL叢集相關| stats_proxysql_servers_status | <--ProxySQL叢集相關+--------------------------------------+
這些表的內容、解釋我已經翻譯，參見：
ProxySQL的stats庫。本文介紹其中3個和路由、規則相關的表。
這個表對於分析SQL語句至關重要，是分析語句效能、定製路由規則指標的最主要來源。
剛才已經解釋過什麼是SQL語句的引數化，還說明了ProxySQL會將引數化後的語句進行hash計算得到它的digest，這個統計表中記錄的就是每個引數化分類後的語句對應的統計資料，包括該類語句的執行次數、所花總時間、所花最短、最長時間，還包括語句的文字以及它的digest。
如下圖：
以下是各個欄位的意義：
注意，該表中的查詢所花時長是指ProxySQL從接收到客戶端查詢開始，到ProxySQL準備向客戶端傳送查詢結果的時長。因此，這些時間更像是客戶端看到的發起、接收的時間間隔(儘管客戶端到服務端資料傳輸也需要時間)。更精確一點，在執行查詢之前，ProxySQL可能需要更改字符集或模式，可能當前後端不可用(當前後端執行語句失敗)而找一個新的後端，可能因為所有連線都繁忙而需要等待空閒連線，這些都不應該計算到查詢執行所花時間內。
其中hostgroup、digest、digest_text、count_start、{sum,min,max}_time這幾列最常用。
例如：
admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;+----+------------+----------+--------------------+------------------------+| hg | count_star | sum_time | digest | digest_text |+----+------------+----------+--------------------+------------------------+| 10 | 4 | 2412 | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 6 | 4715 | 0x57497F236587B138 | select * from test1.t1 |+----+------------+----------+--------------------+------------------------+
從中分析，兩個語句都路由到了hostgroup=10的組中，第一個語句執行了4次，這4次總共花費了2412微秒(即2.4毫秒)，第二個語句執行了6次，總花費4.7毫秒。還給出了這兩個語句引數化後的digest值，以及引數化後的SQL文字。
這個表的表結構和
stats_mysql_query_digest是完全一樣的，只不過每次從這個表中檢索資料(隨便檢索什麼，哪怕
where 1=0)，都會重置
stats_mysql_query_digest表中已統計的資料。
這個表只有兩個欄位：
digest匹配規則是對digest進行精確匹配。
例如，從
stats_mysql_query_digest中獲取兩個對應的digest值。注意，現在它們的hostgroup_id=10。
admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;+----+------------+----------+--------------------+------------------------+| hg | count_star | sum_time | digest | digest_text |+----+------------+----------+--------------------+------------------------+| 10 | 4 | 2412 | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 6 | 4715 | 0x57497F236587B138 | select * from test1.t1 |+----+------------+----------+--------------------+------------------------+
插入兩條匹配這兩個digest的規則：
insert into mysql_query_rules(rule_id,active,digest,destination_hostgroup,apply) values(1,1,"0xADB885E1F3A7A5C2",20,1),(2,1,"0x57497F236587B138",10,1);
然後測試
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"
再去檢視規則的路由命中情況：
admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 || 2 | 1 |+---------+------+
檢視路由的目標：
admin> select hostgroup hg,count_star cs,digest,digest_text from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest | digest_text |+----+----+--------------------+------------------------+| 20 | 1 | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 1 | 0x57497F236587B138 | select * from test1.t1 |+----+----+--------------------+------------------------+
可見，基於digest的精確匹配規則已經生效。
match_digest是對digest做正則匹配，但注意match_pattern欄位中給的規則不是hash值，而是SQL語句的文字匹配規則。
ProxySQL支援兩種正則引擎：
老版本中預設的正則引擎是RE2，現在預設的正則引擎是PCRE。可從變數
mysql-query_processor_regex獲知當前的正則引擎是RE2還是PCRE：
Admin> select @@mysql-query_processor_regex;+-------------------------------+| @@mysql-query_processor_regex |+-------------------------------+| 1 |+-------------------------------+
其中1代表PCRE，2代表RE2。
在
mysql_query_rules表中有一個欄位
re_modifiers，它用於定義正則引擎的修飾符，預設已經設定
caseless，表示正則匹配時忽略大小寫，所以select和SELECT都能匹配。此外，還可以設定global修飾符，表示匹配全域性，而非匹配第一個，這個在重寫SQL語句時有用。
(RE2引擎無法同時設定caseless和global，即使它們都設定了也不會生效。所以，將預設的正則引擎改為了PCRE)
在進行下面的實驗之前，先把
mysql_query_rules表清空，並將規則的統計資料也清空。
delete from mysql_query_rules;select * from stats_mysql_query_digest_reset;insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);load mysql query rules to runtime;save mysql query rules to disk;
然後分別執行：
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"
檢視規則匹配結果：
admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 || 2 | 1 |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest | dt |+----+----+--------------------+------------------------+| 10 | 1 | 0x57497F236587B138 | select * from test1.t1 || 20 | 1 | 0xADB885E1F3A7A5C2 | select * from test2.t1 |+----+----+--------------------+------------------------+
顯然，命中規則，且按照期望進行路由。
如果想對match_digest取反，即不被正則匹配的SQL語句才命中規則，則設定
mysql_query_rules表中的欄位
negate_match_pattern=1。同樣適用於下面的match_pattern匹配 方式。
和match_digest的匹配 方式類似，但match_pattern是基於原始SQL語句進行匹配的，包括引數值。有兩種情況必須使用match_pattern：
如果想對match_pattern取反，即不被正則匹配的SQL語句才命中規則，則設定
mysql_query_rules表中的欄位
negate_match_pattern=1。
例如：
## 清空規則以及規則的統計資料delete from mysql_query_rules;select * from stats_mysql_query_digest_reset where 1=0;insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);load mysql query rules to runtime;save mysql query rules to disk;
執行查詢：
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"
然後檢視匹配結果：
admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 || 2 | 1 |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest | dt |+----+----+--------------------+------------------------+| 20 | 1 | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 1 | 0x57497F236587B138 | select * from test1.t1 |+----+----+--------------------+------------------------+
再來看看匹配引數值(雖然幾乎不會這樣做)。這裡要測試的語句如下：
mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test1.t1 where name like 'malong%';"mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test2.t1 where name like 'xiaofang%';"
現在插入兩條規則，對引數"malong%"和"xiaofang"進行匹配。
## 清空規則以及規則的統計資料delete from mysql_query_rules;select * from stats_mysql_query_digest_reset where 1=0;insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,"malong",20,1),(2,1,"xiaofang",10,1);load mysql query rules to runtime;save mysql query rules to disk;
執行上面的兩個查詢語句，然後檢視匹配結果：
admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 || 2 | 1 |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------------------------+| hg | cs | digest | dt |+----+----+--------------------+------------------------------------------+| 20 | 1 | 0x0C624EDC186F0217 | select * from test1.t1 where name like ? || 10 | 1 | 0xA38442E236D915A7 | select * from test2.t1 where name like ? |+----+----+--------------------+------------------------------------------+
已按預期進行路由。
一個極簡單卻大有用處的讀、寫分離功能：將預設路由組設定為寫組，然後再插入下面兩個select語句的規則。
# 10為寫組，20為讀組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);
但需要注意的是，這樣的規則只適用於小環境下的讀寫分離，對於稍複雜的環境，需要對不同語句進行開銷分析，對於開銷大的語句需要制定專門的路由規則。在之後的文章中我會稍作分析。
ProxySQL能通過ip、port、client_ip、username、schemaname、digest、match_digest、match_pattern實現不同方式的路由，方式可謂繁多。特別是基於正則匹配的靈活性，使得ProxySQL能滿足一些比較複雜的環境。
總的來說，ProxySQL主要是通過digest、match_digest和match_pattern進行規則匹配的。在本文中，只是介紹了匹配規則的基礎以及簡單的用法，為進軍後面的文章做好鋪墊。
About Me
 |
 |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2676876/，如需轉載，請註明出處，否則將追究法律責任。
