MySQL ProxySQL讀寫分離使用初探

目的

在美團點評DBProxy讀寫分離使用說明文章中已經說明了使用目的，本文介紹ProxySQL的使用方法以及和DBProxy的性能差異。具體的介紹可以看官網的相關說明，并且這個中間件也是percona推的一款中間件。其特性和其他讀寫分離的中間件差距不大，具體的會在文中介紹。本文大緻簡單的介紹在使用過程中的一些說明，也可以看官方的wiki獲得使用幫助。

環境：

Distributor ID：    Ubuntu
Description：    Ubuntu 14.04.5 LTS
Release：    14.04
Codename：    trusty

下載下傳

percona站點：

https：//www.percona.com/downloads/proxysql/

github/官網：

https：//github.com/sysown/proxysql/releases

安裝

1）編譯安裝：

1：下載下傳
wget https：//github.com/sysown/proxysql/archive/v1.3.6.tar.gz

2：安裝依賴包
apt-get install automake bzip2 cmake make g++ gcc git openssl debconf-utils

3：解壓、編譯
make
make install
...
 Adding system startup for /etc/init.d/proxysql ...
   /etc/rc0.d/K20proxysql -> ../init.d/proxysql
   /etc/rc1.d/K20proxysql -> ../init.d/proxysql
   /etc/rc6.d/K20proxysql -> ../init.d/proxysql
   /etc/rc2.d/S20proxysql -> ../init.d/proxysql
   /etc/rc3.d/S20proxysql -> ../init.d/proxysql
   /etc/rc4.d/S20proxysql -> ../init.d/proxysql
   /etc/rc5.d/S20proxysql -> ../init.d/proxysql

4：修改非root啟動
添加使用者：
useradd proxysql
修改相關檔案權限：
cd /var/lib/
chown -R proxysql.proxysql proxysql/
cd /etc/
chown -R proxysql.proxysql proxysql.cnf 
修改啟動指令
vi /etc/init.d/proxysql ->75行
proxysql $OPTS
改成
sudo -u proxysql /usr/bin/proxysql $OPTS

5：啟動&關閉
/etc/init.d/proxysql start
/etc/init.d/proxysql stop

2）deb包安裝：

1：下載下傳
wget https：//github.com/sysown/proxysql/releases/download/v1.3.6/proxysql_1.3.6-dbg-ubuntu14_amd64.deb

2：安裝
dpkg -i proxysql_1.3.5-1.1.trusty_amd64.deb

3：非root使用者啟動，參考編譯安裝部分。

檔案說明

通過啟動檔案 /etc/init.d/proxysql裡可以知道ProxySQL安裝好的資料目錄在 /var/lib/proxysql/，配置檔案目錄在 /etc/proxysql.cnf。

root@proxysql：/var/lib/proxysql# pwd
/var/lib/proxysql
root@proxysql：/var/lib/proxysql# ls -lh
total 48K
-rw------- 1 proxysql proxysql  33K  5月  9 11：52 proxysql.db
-rw------- 1 proxysql proxysql 7.4K  5月  9 11：56 proxysql.log

root@proxysql：~# ls -lh /etc/proxysql.cnf 
-rw------- 1 proxysql proxysql 4.2K  5月  9 11：45 /etc/proxysql.cnf

啟動之後，可以看到這些檔案：proxysql.db是SQLITE的資料檔案，proxysql配置，如後端資料庫的賬号、密碼、路由等存儲在這個資料庫裡面，proxysql.log是日志檔案。proxysql.pid這個pid檔案。proxysql.cnf是ProxySQL的一些靜态配置項，比如一些啟動選項，sqlite的資料目錄等等。配置檔案隻在第一次啟動的時候讀取進行初始化，後面隻讀取db檔案。

程序說明

root@proxysql：~# ps -ef | grep proxysql
proxysql  35740      1  0 12：20 ?        00：00：00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql  35741  35740  0 12：20 ?        00：00：00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

和MySQL的很相似，我們啟動一個程序，然後fork出一個子程序，父程序負責監控子程序運作狀況如果挂了則拉起來，子程序負責執行真正的任務。

接口說明

和DBProxy一樣，ProxySQL也是有管理接口和用戶端接口，通過配置檔案/etc/proxysql.cnf可以看到管理和用戶端接口的資訊：

admin_variables=
{
        admin_credentials="admin：admin"
        mysql_ifaces="127.0.0.1：6032;/tmp/proxysql_admin.sock"
#       refresh_interval=2000
#       debug=true
}

mysql_variables=
{
        ...
        ...
        interfaces="0.0.0.0：6033;/tmp/proxysql.sock"
        default_schema="information_schema"        ...
        ...
}

看到管理接口的端口是6032，賬号密碼是admin(可以動态修改)隻能通過本地連接配接，用戶端接口的端口是6033，賬号密碼通過管理接口去設定。

庫、表說明

通過管理接口登入，需要先裝好mysql用戶端，并且設定prompt

[client]
prompt      = \\u@\\h ： \\d \\r：\\m：\\s>

通過管理接口進入ProxySQL

庫：

root@proxysql：~# mysql -uadmin -padmin -h127.0.0.1 -P6032
...
...
[email protected] ： (none) 12：43：08>show databases;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)

main 記憶體配置資料庫，表裡存放後端db執行個體、使用者驗證、路由規則等資訊。表名以 runtime_ 開頭的表示proxysql目前運作的配置内容，不能通過dml語句修改，隻能修改對應的不以 runtime_ 開頭的（在記憶體）裡的表，然後 LOAD 使其生效， SAVE 使其存到硬碟以供下次重新開機加載。
disk 是持久化到硬碟的配置，sqlite資料檔案。
stats 是proxysql運作抓取的統計資訊，包括到後端各指令的執行次數、流量、processlist、查詢種類彙總/執行時間等等。
monitor 庫存儲 monitor 子產品收集的資訊，主要是對後端db的健康/延遲檢查。

表

1）main庫：

[email protected] ： (none) 02：01：54>show tables from main;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                    |
| mysql_replication_hostgroups         |
| mysql_servers                        |
| mysql_users                          |
| runtime_global_variables             |
| runtime_mysql_query_rules            |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers                |
| runtime_mysql_users                  |
| runtime_scheduler                    |
| scheduler                            |
+--------------------------------------+
13 rows in set (0.00 sec)

global_variables

設定變量，包括監聽的端口、管理賬号等，詳細可參考 https：//github.com/sysown/proxysql/wiki/Global-variables。通過set來設定，如：

set mysql-monitor_ping_interval =10001;

mysql_collations：相關字元集和校驗規則。

mysql_query_rules：定義查詢路由規則。

CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT,
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    flagOUT INT,
    replace_pattern VARCHAR,
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED,
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)

rule_id：表主鍵，自增。規則處理是以 rule_id 的順序進行。
active：隻有 active=1 的規則才會參與比對。
username：如果非 NULL，隻有連接配接使用者是 username 的值才會比對
schemaname：如果非 NULL，隻有查詢連接配接使用的db是 schemaname 的值才會比對。注意如果是 NULL，不代表連接配接沒有使用schema，而是不倫任何schema都進一步比對。
client_addr：比對用戶端來源IP
proxy_addr, proxy_port：比對本地proxysql的IP、端口。
digest：精确的比對一類查詢。
match_digest：正則比對一類查詢。query digest 是指對查詢去掉具體值後進行“模糊化”後的查詢。
match_pattern：正則比對查詢。
negate_match_pattern：反向比對，相當于對 match_digest/match_pattern 的比對取反。
re_modifiers：修改正則比對的參數，比如預設的：忽略大小寫CASELESS、禁用GLOBAL。

上面都是比對規則，下面是比對後的行為：
replace_pattern：查詢重寫，預設為空，不rewrite。
destination_hostgroup：路由查詢到這個 hostgroup。當然如果使用者顯式 start transaction 且 transaction_persistent=1，那麼即使比對到了，也依然按照事務裡第一條sql的路由規則去走。
cache_ttl：查詢結果緩存的毫秒數。proxysql這個 Query Cache 與 MySQL 自帶的query cache不是同一個。proxysql query cache也不會關心後端資料是否被修改，它所做的就是針對某些特定種類的查詢結果進行緩存，比如一些曆史資料的count結果。一般不設。
timeout：這一類查詢執行的最大時間（毫秒），逾時則自動kill。這是對後端DB的保護機制，預設mysql-default_query_timeout給的是10h。
retries：語句在執行時失敗時，重試次數。預設由 mysql-query_retries_on_failure變量指定，預設為1 。
delay：查詢延遲執行，這是ProxySQL提供的限流機制，會讓其它的查詢優先執行。
error_msg：預設為NULL，如果指定了則這個查詢直接被 block 掉，馬上傳回這個錯誤資訊。這個功能也很實用，比如線上突然冒出一個 “壞查詢”，應用端不友善馬上發版解決，我們就可以在這配置一個規則，把查詢屏蔽掉，想正常的mysql報錯那樣抛異常。
multiplex：連接配接是否複用。
log：是否記錄查詢日志。
flagIN，flagOUT，apply

flagIN, flagOUT, apply：用來定義路由鍊 chains of rules
首先會檢查 flagIN=0 的規則，以rule_id的順序；如果都沒比對上，則走這個使用者的 default_hostgroup
當比對一條規則後，會檢查 flagOUT
如果不為NULL，并且 flagIN != flagOUT ，則進入以flagIN為上一個flagOUT值的新規則鍊
如果不為NULL，并且 flagIN = flagOUT，則應用這條規則
如果為NULL，或者 apply=1，則結束，應用這條規則
如果最終沒有比對到，則找到這個使用者的 default_hostgroup

mysql_replication_hostgroups：監視指定主機組中所有伺服器的read_only值，并且根據read_only的值将伺服器配置設定給寫入器或讀取器主機組

CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
    comment VARCHAR,
    UNIQUE (reader_hostgroup))

定義 hostgroup 的主從關系。ProxySQL monitor 子產品會監控 HG 後端所有servers 的

read_only

變量，如果發現從庫的 read_only 變為0、主庫變為1，則認為角色互換了，自動改寫 mysql_servers 表裡面 hostgroup 關系，達到自動 Failover 效果。

mysql_servers：設定後端MySQL的表

CREATE TABLE mysql_servers (
    hostgroup_id INT NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
    compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )

hostgroup_id： ProxySQL通過 hostgroup (下稱HG) 的形式組織後端db執行個體。一個 HG 代表同屬于一個角色

該表的主鍵是 (hostgroup_id, hostname, port)，可以看到一個 hostname：port 可以在多個hostgroup裡面，這樣可以避免從庫全都不可用時，依然可以把讀請求發到主庫上。一個 HG可以有多個執行個體，即多個從庫，可以通過 weight 配置設定權重。hostgroup_id 0 是一個特殊的HG，路由查詢的時候，沒有比對到規則則預設選擇 HG 0
status：

ONLINE：目前後端執行個體狀态正常

SHUNNED：臨時被剔除，可能因為後端 too many connections error，或者超過了可容忍延遲閥值 max_replication_lag

OFFLINE_SOFT： “軟離線”狀态，不再接受新的連接配接，但已建立的連接配接會等待活躍事務完成。

OFFLINE_HARD： “硬離線”狀态，不再接受新的連接配接，已建立的連接配接或被強制中斷。當後端執行個體當機或網絡不可達，會出現。
max_connections：允許連接配接到該後端執行個體的最大連接配接數。不要大于MySQL設定的 max_connections，如果後端執行個體 hostname：port 在多個 hostgroup 裡，以較大者為準，而不是各自獨立允許的最大連接配接數。
max_replication_lag：允許的最大延遲，主庫不受這個影響，預設0。如果 > 0， monitor 子產品監控主從延遲大于閥值時，會臨時把它變為 SHUNNED
max_latency_ms： mysql_ping 響應時間，大于這個閥值會把它從連接配接池剔除（即使是ONLINE），預設0。
comment：備注。

mysql_users：配置後端資料庫的賬号和監控的賬号

CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))

username, password：連接配接後端db的使用者密碼。

這個密碼你可以插入明文，也可以插入hash加密後的密文，proxysql會檢查你插入的時候密碼是否以 * 開頭來判斷，而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 裡，都統一變成了密文是以可以明文插入，再 SAVE MYSQL USERS TO MEM，此時看到的也是HASH密文。
active：是否生效該使用者。
default_hostgroup：這個使用者的請求沒有比對到規則時，預設發到這個 hostgroup，預設0
default_schema：這個使用者連接配接時沒有指定 database name 時，預設使用的schema注意表面上看預設為NULL，但實際上受到變量 mysql-default_schema 的影響，預設為 information_schema。
transaction_persistent：如果設定為1，連接配接上ProxySQL的會話後，如果在一個hostgroup上開啟了事務，那麼後續的sql都繼續維持在這個hostgroup上，不倫是否會比對上其它路由規則，直到事務結束。雖然預設是0。
frontend, backend：目前版本這兩個都需要使用預設的1，将來有可能會把 Client -> ProxySQL (frontend) 與 ProxySQL -> BackendDB (backend)的認證分開。從 runtime_mysql_users 表内容看到，記錄數比 mysql_users 多了一倍，就是把前端認證與後端認證獨立出來的結果。
fast_forward：忽略查詢重寫/緩存層，直接把這個使用者的請求透傳到後端DB。相當于隻用它的連接配接池功能，一般不用，路由規則 .* 就行了。

scheduler：排程器是一個類似于cron的實作，內建在ProxySQL中，具有毫秒的粒度。通過腳本檢測來設定ProxySQL。

CREATE TABLE scheduler (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
    filename VARCHAR NOT NULL,
    arg1 VARCHAR,
    arg2 VARCHAR,
    arg3 VARCHAR,
    arg4 VARCHAR,
    arg5 VARCHAR,
    comment VARCHAR NOT NULL DEFAULT '')

id ：排程器唯一辨別，自增主鍵。
active ：是否開啟，預設開啟。
interval_ms ：工作的開始頻率（以毫秒為機關）。最小interval_ms為100毫秒
filename ：執行的可執行檔案的絕對路徑
arg1 to arg5 ：傳遞給執行檔案的參數，最大允許5個參數
comment ：備注

關于main庫下的表的說明可以看這篇文章的說明：http：//seanlook.com/2017/04/10/mysql-proxysql-install-config/

2）disk庫：

show tables from disk;
+------------------------------+
| tables                       |
+------------------------------+
| global_variables             |
| mysql_collations             |
| mysql_query_rules            |
| mysql_replication_hostgroups |
| mysql_servers                |
| mysql_users                  |
| scheduler                    |
+------------------------------+
7 rows in set (0.00 sec)

和上面介紹的main庫一樣。

3）stats庫：

stats_mysql_commands_counters：統計各種SQL類型的執行次數和時間，通過參數mysql-commands_stats控制開關，預設是ture。

CREATE TABLE stats_mysql_commands_counters (
    Command VARCHAR NOT NULL PRIMARY KEY,
    Total_Time_us INT NOT NULL,
    Total_cnt INT NOT NULL,
    cnt_100us INT NOT NULL,
    cnt_500us INT NOT NULL,
    cnt_1ms INT NOT NULL,
    cnt_5ms INT NOT NULL,
    cnt_10ms INT NOT NULL,
    cnt_50ms INT NOT NULL,
    cnt_100ms INT NOT NULL,
    cnt_500ms INT NOT NULL,
    cnt_1s INT NOT NULL,
    cnt_5s INT NOT NULL,
    cnt_10s INT NOT NULL,
    cnt_INFs)

command：已執行的SQL指令的類型。如：FLUSH，INSERT，KILL，SELECT FOR UPDATE等。
Total_Time_us：執行該類型指令的總時間（以微秒為機關）。
total_cnt：執行該類型的指令的總數。
cnt_100us, cnt_500us, ..., cnt_10s, cnt_INFs：在指定的時間限制内執行的給定類型的指令總數和前一個指令的總數。

stats_mysql_connection_pool：連接配接後端MySQL的連接配接資訊。

CREATE TABLE stats_mysql_connection_pool (
    hostgroup VARCHAR,
    srv_host VARCHAR,
    srv_port VARCHAR,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    Queries INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT)

hostgroup：後端伺服器所屬的主機組。請注意，單個後端伺服器可以屬于多個主機組。
srv_host，srv_port：mysqld後端伺服器正在偵聽連接配接的TCP端點的IP和Port。
status：後端伺服器的狀态。可以有ONLINE，SHUNNED，OFFLINE_SOFT，OFFLINE_HARD。有關每個狀态意味着什麼的詳細資訊，請參閱上面的mysql_servers表的描述。
ConnUsed：ProxySQL目前使用多少個連接配接來向後端伺服器發送查詢。
ConnFree：目前有多少個連接配接是空閑。
ConnOK：成功建立了多少個連接配接。
ConnERR：沒有成功建立多少個連接配接。
Queries：路由到此特定後端伺服器的查詢數。
Bytes_data_sent：發送到後端的資料量。
Bytes_data_recv：從後端接收的資料量。
Latency_ms：從Monitor報告的目前ping以毫秒為機關的延遲時間。

stats_mysql_global：代表與MySQL相關的代理級别的全局統計，如：

Client_Connections_aborted：由于無效憑據或max_connections而導緻的前端連接配接數已達到。
Client_Connections_connected - 目前連接配接的前端連接配接數。
Client_Connections_created - 到目前為止建立的前端連接配接數。
Questions ：從前端發送的查詢總數。
Slow_queries：在全局變量中定義的運作時間超過門檻值的毫秒數的查詢數mysql-long_query_time
等等...

stats_mysql_processlist：類似MySQL的show processlist的指令，檢視各線程的狀态。

CREATE TABLE stats_mysql_processlist (
    ThreadID INT NOT NULL,
    SessionID INTEGER PRIMARY KEY,
    user VARCHAR,
    db VARCHAR,
    cli_host VARCHAR,
    cli_port VARCHAR,
    hostgroup VARCHAR,
    l_srv_host VARCHAR,
    l_srv_port VARCHAR,
    srv_host VARCHAR,
    srv_port VARCHAR,
    command VARCHAR,
    time_ms INT NOT NULL,
    info VARCHAR)

ThreadID：ProxySQL内線程的内部ID。
SessionID：ProxySQL會話ID。通過這個ID可以進行kill操作。
user：與MySQL用戶端連接配接到ProxySQL的使用者。
db：目前選擇的資料庫。
cli_host，cli_port：連接配接ProxySQL的IP和TCP端口。
hostgroup：目前主機組。如果正在處理查詢，則是查詢已被路由或将要路由的主機組，或預設主機組。可以通過這個檢視該SQL到底是到哪個HG裡。
l_srv_host，l_srv_port：ProxySQL的IP和TCP端口。
srv_host，srv_port - 後端MySQL伺服器的IP和端口。
command：正在執行的MySQL查詢的類型。
time_ms：指令執行的時間（以毫秒為機關）。
info：正在執行的SQL。

stats_mysql_query_digest：表示SQL的執行次數、時間消耗等。通過變量mysql-query_digests控制開關，預設是開。

stats_mysql_query_digest
Create Table： CREATE TABLE stats_mysql_query_digest (
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    PRIMARY KEY(hostgroup, schemaname, username, digest))

hostgroup：發送查詢的主機組。值-1表示查詢查詢緩存。
schemaname：查詢的資料庫。
user：連接配接ProxySQL的使用者名。
digest：一個十六進制散列，表示其參數剝離的SQL。
digest_text：參數剝離的實際SQL文本。
count_star：執行查詢的總次數（參數的值不同）。
first_seen：unix時間戳，是通過代理路由查詢的第一時刻。
last_seen：unix時間戳，當查詢通過代理路由時的最後一刻（到目前為止）。
sum_time：執行此類查詢的總時間（以微秒為機關）。這對于确定應用程式工作負載中花費的最多時間在哪裡是非常有用的，并為改進的地方提供了一個良好的起點。
min_time，max_time - 執行此類查詢時期望的持續時間範圍。 min_time是到目前為止所看到的最小執行時間，而max_time表示最大執行時間，以微秒為機關。

stats_mysql_query_digest_reset和stats_mysql_query_digest結構一樣，stats_mysql_query_digest_reset可以清空stats_mysql_query_digest的資料，如執行：

select * from stats_mysql_query_digest_reset;
show create table stats.stats_mysql_query_digest_reset;

執行上面的任意一條就可以清空stats_mysql_query_digest表。

stats_mysql_query_rules：路由命中次數統計。

CREATE TABLE stats_mysql_query_rules (
    rule_id INTEGER PRIMARY KEY,
    hits INT NOT NULL)

rule_id：路由規則的ID與main.mysql_query_rules的id對應。
hits：此路由規則的比對總數。如果目前傳入的查詢符合規則，則會記錄一次命中。

4）monitor庫：對後端MySQL的健康檢查。由變量mysql-monitor_enabled來确定是否開啟Monitor子產品。

mysql_server_connect/mysql_server_connect_log：連接配接到所有MySQL伺服器以檢查它們是否可用，該表用來存放檢測連接配接的日志。由變量 mysql-monitor_connect_interval來控制其檢測 的時間間隔，由參數 mysql-monitor_connect_timeout控制連接配接是否逾時（預設200毫秒）。
mysql_server_ping/mysql_server_ping_log：使用mysql_ping API ping後端MySQL伺服器檢查它們是否可用，該表用來存放ping的日志。由變量mysql-monitor_ping_interval控制ping的時間間隔，預設值：10000（毫秒，相當于10秒）。
mysql_server_replication_lag_log：後端MySQL服務主從延遲的檢測。由參數mysql-monitor_replication_lag_interval控制檢測間隔時間，如果複制滞後太大，可以暫時關閉從。由mysql_servers.max_replication_lag列控制。預設值：10000（毫秒，相當于10秒）。

配置

ProxySQL有一個完備的配置系統，持配置修改之後的線上儲存、應用，不需要重新開機之後生效。整個配置系統分三層設計。配置系統分為三層的目的有三個:

(1).自動更新

(2).盡可能的不重新開機proxysql就可以修改配置

(3).友善復原錯誤配置

整個配置系統分為三層，如下所示:

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

RUNTIME 代表的是ProxySQL目前生效的正在使用的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。無法直接修改這裡的配置，必須要從下一層load進來。也就是說RUNTIME這個頂級層，就是proxysql運作過程中實際采用的那一份配置，這一份配置就是要影響實際生産的，是以将你的配置加進RUNTIME層時需要三思而行。

MEMORY 使用者可以将MySQL用戶端連接配接到此接口（admin接口），并查詢不同的表和資料庫是在mysql指令行修改的 main 裡頭配置，可以認為是SQLite資料庫在記憶體的鏡像。也就是說MEMORY這個中間層，上面接着生産配置項RUNTIME，下面接着持久化層DISK,CONFIG FILE。MEMORY也是我們修改proxysql的唯一正常入口。一般的，我們修改一個配置，先改Memory，确認無誤後再接入生産(RUNTIME)，和持久化到磁盤(DISK)。也就是說memeory裡面的配置随便改，不影響生産，也不影響磁盤中儲存的資料。通過此接口可以修改mysql_servers、mysql_users、mysql_query_rules、global_variables等。

DISK / CONFIG FILE 持久存儲的那份配置，一般在

$(DATADIR)/proxysql.db

，在重新開機的時候會從硬碟裡加載。

/etc/proxysql.cnf

檔案隻在第一次初始化的時候用到，完了後，如果要修改監聽端口，還是需要在管理指令行裡修改，再 save 到硬碟。也就是說DISK和CONFIG FILE這一層是持久化層，我們做的任何配置更改，如果不持久化下來，重新開機後，配置都将丢失。

說明：proxysql的每一個配置項在三層中都存在，但是這三層是獨立的，也就是說，proxysql可以同時擁有三份配置，每層都是獨立的，可能三份都不一樣，可能三份都一樣。

當proxysql啟動時，首先讀取配置檔案CONFIG FILE(/etc/proxysql.cnf)，然後從該配置檔案中擷取datadir，datadir中存儲的是sqlite的資料目錄。如果該目錄存在，且sqlite資料檔案存在，那麼正常啟動，将sqlite中的配置項讀進記憶體，并且加載進RUNTIME，用于初始化proxysql的運作。如果datadir目錄下沒有sqlite的資料檔案，proxysql就會使用config file中的配置來初始化proxysql，并且将這些配置儲存至資料庫。

修改配置：需要修改配置時，直接操作的是 MEMORAY（main）

以下指令可用于加載或儲存

users（mysql_users）

：（序号對應上圖）

[1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY --将修改後的配置(在memory層)用到實際生産
[2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME --将生産配置拉一份到memory中
[3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK    --将磁盤中持久化的配置拉一份到memory中來
[4]: SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY    --将memoery中的配置儲存到磁盤中去
[5]: LOAD MYSQL USERS FROM CONFIG                               --将配置檔案中的配置加載到memeory中

以下指令加載或儲存

servers（mysql_servers）

[1]: LOAD MYSQL SERVERS TO RUNTIME --讓修改的配置生效
[2]: SAVE MYSQL SERVERS TO MEMORY
[3]: LOAD MYSQL SERVERS TO MEMORY
[4]: SAVE MYSQL SERVERS TO DISK --将修改的配置持久化
[5]: LOAD MYSQL SERVERS FROM CONFIG

query rules（mysql_query_rules）

[1]: load mysql query rules to run
[2]: save mysql query rules to mem
[3]: load mysql query rules to mem
[4]: save mysql query rules to disk
[5]: load mysql query rules from config

以下指令加載或儲存

mysql variables（global_variables）

[1]: load mysql variables to runtime
[2]: save mysql variables to memory
[3]: load mysql variables to memory
[4]: save mysql variables to disk
[5]: load mysql variables from config

admin variables（select * from global_variables where variable_name like 'admin-%'）

[1]: load admin variables to runtime
[2]: save admin variables to memory
[3]: load admin variables to memory
[4]: save admin variables to disk
[5]: load admin variables from config

到此，PrxoySQL的說明已經大緻介紹完，更多的資訊可以看官方文檔介紹和官方wiki說明，後面接着開始測試ProxySQL的相關功能。

測試說明

1）讀寫分離

環境：

APP：192.168.200.25、192.168.200.64
DB：
   M：192.168.200.202
   S：192.168.200.132
ProxySQL：192.168.200.24

① 配置後端MySQL。登入ProxySQL，把MySQL主從的資訊添加進去。将主庫master也就是做寫入的節點放到HG 100中，salve節點做讀放到HG 1000。

[email protected] : (none) 12:37:05>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.200.202',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)

[email protected] : (none) 12:37:13>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.200.132',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)

[email protected] : (none) 12:37:19>select * from mysql_servers;     
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
| hostgroup_id | hostname        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment       |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
| 100          | 192.168.200.202 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test proxysql |
| 1000         | 192.168.200.132 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test proxysql |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
2 rows in set (0.00 sec)

② 配置後端MySQL使用者。這個使用者需要先在後端MySQL(202,132)裡真實存在，一個是監控賬号、一個是程式賬号：

-- 監控
[email protected] : 12:38:21>GRANT USAGE ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected (0.00 sec)

-- 程式
[email protected] : 12:38:22>GRANT SELECT, INSERT, UPDATE, DELETE ON `sbtest`.* TO 'sbuser'@'192.168.200.24' identified by 'sbuser';
Query OK, 0 rows affected (0.00 sec)

在後端MySQL裡添加完之後再配置ProxySQL：這裡需要注意，default_hostgroup需要和上面的對應。

[email protected] : (none) 12:39:52>insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('sbuser','sbuser',1,100,1);
Query OK, 1 row affected (0.00 sec)

[email protected] : (none) 12:41:07>select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbuser   | sbuser   | 1      | 0       | 100               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

上面看到是一個明文密碼，也可以用password函數加密後的密碼進行代替。

③ 設定健康監測賬号：

[email protected] : (none) 12:42:38>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

[email protected] : (none) 12:42:44>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

或則還行下面的：

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

[email protected] : (none) 12:45:14>set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.01 sec)

其他變量的修改，登入管理接口按照需要可以自行調整：

set mysql-default_charset='utf8mb4';
set mysql-query_retries_on_failure=0;
set mysql-ping_timeout_server=500;
set mysql-monitor_connect_timeout=1000;
set mysql-default_max_latency_ms=2000;
set mysql-monitor_replication_lag_interval=500;
set mysql-ping_interval_server_msec=3000;
set mysql-monitor_ping_interval=5000;
set mysql-connect_timeout_server_max=3000;

④ 加載配置和變量：因為修改了servers、users和variables，是以加載的時候要執行：

-- 應用到線上
[email protected] : (none) 12:51:28>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

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

[email protected] : (none) 12:56:31>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)

-- 持久化
[email protected] : (none) 12:56:37>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

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

[email protected] : (none) 12:56:50>save mysql variables to disk;
Query OK, 72 rows affected (0.00 sec)

要是是用明文密碼設定mysql_users，在這裡可以用save指令來轉換成了hash值的密碼：

[email protected] : (none) 04:25:23>save mysql users to mem;
Query OK, 0 rows affected (0.00 sec)

[email protected] : (none) 04:25:33>select username,password from mysql_users;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| sbuser   | *CA96E56547F43610DDE9EB7B12B4EF4C51CDDFFC |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

⑤ 連接配接資料庫，通過proxysql的用戶端接口通路(6033)：

~$ mysql -usbuser -psbuser -h192.168.200.24 -P6033
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.33 (ProxySQL)
...
[email protected] : (none) 04:41:34>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

寫入測試資料：

[email protected] : sbtest 04:42:35>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[email protected] : sbtest 04:44:12>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

[email protected] : sbtest 04:44:14>begin;
Query OK, 0 rows affected (0.00 sec)

[email protected] : sbtest 04:44:17>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[email protected] : sbtest 04:44:26>commit;
Query OK, 0 rows affected (0.00 sec)

[email protected] : sbtest 04:44:29>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
+------+
6 rows in set (0.00 sec)

通過proxysql接口正常操作資料，從管理接口看看ProxySQL的統計資訊：

-- 檢視各類指令的執行情況
[email protected] : (none) 04:52:59>select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;
+---------+---------------+-----------+
| Command | Total_Time_us | Total_cnt |
+---------+---------------+-----------+
| BEGIN   | 314           | 1         |
| COMMIT  | 493           | 1         |
| INSERT  | 5021          | 2         |
| SELECT  | 2224          | 9         |
| SHOW    | 4577          | 5         |
+---------+---------------+-----------+

-- 檢視各類SQL的執行情況
[email protected] : (none) 04:54:47>select * from stats_mysql_query_digest;

通過上面看到，ProxySQL已經正常啟動，但是在stats_mysql_query_digest的hostgroup中發現，讀和寫全部都是走100這個Master的HG，沒有用到從庫。主要原因就是ProxySQL的核心mysql_query_rules路由表沒有配置。proxysql是通過自定義sql路由規則就可以實作讀寫分離。

⑥ 定義路由規則，如：除select * from tb for update的select全部發送到slave，其他的的語句發送到master。

-- 發送到M
[email protected] : (none) 04:58:11>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.00 sec)
-- 發送到S
[email protected] : (none) 05:08:17>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);
Query OK, 1 row affected (0.00 sec)

-- 加載
[email protected] : (none) 05:09:37>load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
[email protected] : (none) 05:09:57>save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)

說明：active表示是否啟用這個sql路由項，match_pattern就是我們正則比對項，destination_hostgroup表示我們要将該類sql轉發到哪些mysql上面去，apply為1表示該正則比對後，将不再接受其他比對，直接轉發。具體的資訊看上面的表介紹說明。路由規則添加完成，現在來測試下讀寫分離，先清空stats_mysql_query_digest統計表：

[email protected] : (none) 05:16:21>select * from stats_mysql_query_digest_reset;

寫入測試資料：

[email protected] : sbtest 10:20:27>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[email protected] : sbtest 10:20:29>select * from sb;
...

[email protected] : sbtest 10:20:34>start transaction;
Query OK, 0 rows affected (0.01 sec)

[email protected] : sbtest 10:20:41>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

[email protected] : sbtest 10:20:46>commit;
Query OK, 0 rows affected (0.00 sec)

[email protected] : sbtest 10:20:51>select * from sb;
...

[email protected] : sbtest 10:20:56>select * from sb for update;
..

[email protected] : sbtest 10:21:11>insert into sb values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

[email protected] : sbtest 10:21:20>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
+------+
15 rows in set (0.01 sec)

檢視統計資訊：

select * from stats_mysql_query_digest;

看到讀寫分離已經成功。為了更直覺一點，使用sysbench測試讀寫分離是否正常：

./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.24 --mysql-port=6033 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest  --report-interval=10  --max-requests=0 --time=300 --threads=4 --tables=1  --table-size=500000 --skip-trx=on --db-ps-mode=disable --mysql-ignore-errors=1062 prepare/run/clean

為了更好的驗證，可以在sysbench運作期間，可以到後端的從MySQL上檢視是否有查詢進來。執行完之後來看下統計表：

[email protected] : (none) 10:31:52>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
+-----------+------------+----------+--------------------------------------------------------------------+------------+
| hostgroup | schemaname | username | substr(digest_text,120,-120)                                       | count_star |
+-----------+------------+----------+--------------------------------------------------------------------+------------+
| 100       | sbtest     | sbuser   | show processlist                                                   | 15         |
| 100       | sbtest     | sbuser   | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)            | 123932     |
| 100       | sbtest     | sbuser   | DELETE FROM sbtest1 WHERE id=?                                     | 123932     |
| 100       | sbtest     | sbuser   | UPDATE sbtest1 SET c=? WHERE id=?                                  | 123932     |
| 100       | sbtest     | sbuser   | UPDATE sbtest1 SET k=k+? WHERE id=?                                | 123932     |
| 1000      | sbtest     | sbuser   | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                | 123932     |
| 100       | sbtest     | sbuser   | show tables                                                        | 1          |
| 100       | sbtest     | sbuser   | select * from sb for update                                        | 1          |
| 100       | sbtest     | sbuser   | start transaction                                                  | 1          |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          | 123932     |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     | 123932     |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id=?                                   | 1239320    |
| 1000      | sbtest     | sbuser   | select * from sb                                                   | 3          |
| 1000      | sbtest     | sbuser   | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 123932     |
| 100       | sbtest     | sbuser   | drop table sbtest1                                                 | 1          |
| 100       | sbtest     | sbuser   | commit                                                             | 1          |
| 100       | sbtest     | sbuser   | insert into sb values(?),(?),(?)                                   | 3          |
+-----------+------------+----------+--------------------------------------------------------------------+------------+

從上面的結果可知，路由規則已經生效，select語句均到從庫上執行了。

總結：

本文初步介紹了ProxySQL的安裝、相關表說明和讀寫分離。後續還有ProxySQL其本身的特性說明：延遲讀取主庫、重寫查詢以及和DBProxy對比等，由于篇幅原因，放到下一篇文章裡介紹。

參考文檔：

https://github.com/sysown/proxysql/wiki

http://proxysql.blogspot.jp/2015/09/proxysql-tutorial-setup-in-mysql.html

ProxySQL之安裝及配置詳解推薦

ProxySQL之讀寫分離與分庫路由示範推薦

MySQL中間件之ProxySQL安裝部署篇

MySQL中間件之ProxySQL_讀寫分離/查詢重寫配置

~~~~~~~~~~~~~~~

萬物之中,希望至美

~~~~~~~~~~~~~~~

MySQL ProxySQL讀寫分離使用初探

目的

環境：

下載下傳

安裝

檔案說明

程序說明

接口說明

庫、表說明

庫：

表

配置

測試說明

總結：

參考文檔：

繼續閱讀

Linux環境下部署tomcat中間件

常用的中間件

linux 安裝配置redislinux環境下安裝redis

WebSphere安裝配置文檔WebSphere安裝配置文檔一、環境準備二、IIM安裝三、WAS、IHS & PLG 和JDK的安裝及打更新檔（可同時安裝）四、建立概要檔案五、釋出應用六、WAS調優

kafka的入門學習今日大綱1. Kafka簡介2. kafka的分布式安裝3. Kafka的基本操作4. flume和kafka的整合5. Kafka架構之道6. Kafka Leader Election7. Kafka高性能之道

kafka叢集部署安裝

linux下安裝tuxedo

apache & jboss 簡單整合

zk安裝入門

linux下tomcat的安裝，兩個tomcat的配置，并與nginx負載均衡配置

Linux中zookeeper叢集安裝及使用

Linux Tomcat配置jks格式證書

RabbitMQ安裝及配置前言介紹Erlang安裝RabbitMQ安裝

Redis采用的單線程+多路IO複用

軟體業群雄逐鹿 IBM讓微軟蓋茨睡不安枕？

Mysql 網站大全