溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文內容主要給大家帶來ProxySQL亮點、安裝、配置及測試等解析，這里所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。
mysql_query_rules表中依據digest,match_pattern,client_addr等維度控制哪類語句可以緩存
rpm包下載地址
https://github.com/sysown/proxysql/releases 推薦rpm形式安裝
Installing from source
Make sure you have installed the equivalent for each of these packages for your operating system:
automake
bzip2
cmake
make
gcc #>4.4版本
gcc-c++
git
openssl
openssl-devel
patchgit clone https://github.com/sysown/proxysql.git
Go to the directory where you cloned the repo (or unpacked the tarball) and run:
make
sudo make installCompilation time should be around a couple of minutes for the first time around. The configuration file will be found at /etc/proxysql.cnf afterwards.
在make這一步遇到了錯誤：
g++ -fPIC -c -o obj/ProxySQL_GloVars.oo ProxySQL_GloVars.cpp -std=c++11 -I../include -I../deps/jemalloc/jemalloc/include/jemalloc -I../deps/mariadb-client-library/mariadb_client/include -I../deps/libconfig/libconfig-1.4.9/lib -I../deps/re2/re2 -I../deps/sqlite3/sqlite3 -O2 -ggdb -Wall
cc1plus: 錯誤：無法識別的命令行選項“-std=c++11”
make[1]: *** [obj/ProxySQL_GloVars.oo] 錯誤 1
make[1]: Leaving directory `/usr/local/src/proxysql-master/lib'
make: *** [build_lib] 錯誤 2網查是由于gcc版本低導致，centos 6的yum源（以及epel源）都只能獲取到4.4.7版本
包 gcc-4.4.7-17.el6.x86_64 已安裝并且是最新版本
包 gcc-c++-4.4.7-17.el6.x86_64 已安裝并且是最新版本
而centos7上為4.8版本換到centos7上，將上述軟件安裝/更新之后，make步驟完成，但是make install步驟又出了問題：
install -m 0755 src/proxysql /usr/local/bin
install -m 0600 etc/proxysql.cnf /etc
install -m 0755 etc/init.d/proxysql /etc/init.d
if [ ! -d /var/lib/proxysql ]; then mkdir /var/lib/proxysql ; fi
update-rc.d proxysql defaults
make: update-rc.d：命令未找到
make: *** [install] 錯誤 127update-rc.d是ubuntu的自啟動腳本管理軟件,未成功安裝不影響使用。
安裝完成后，自動在/etc/init.d/proxysql增加服務管理腳本（需要把/usr/local/bin/加入\$PATH或者軟鏈至 \$PATH目錄下，腳本中直接用到proxysql命令）
配置文件/etc/proxysql.cnf和配置數據庫文件/var/lib/proxysql/proxysql.db，如果存在 “proxysql.db”文件，則啟動過程不解析proxysql.cnf文件；配置文件只在第一次啟動的時候讀取
官方推薦用admin interface方式
登陸admin interface：
mysql -uadmin -padmin -P6032 -h227.0.0.1
登陸成功后，可通過對main庫（默認登陸后即在此庫）的global_variables表中的
admin-admin_credentials
admin-mysql_ifaces
兩個變量進行更改來修改登錄認證注意：admin interface對配置的存儲是基于SQLite的，SQLite支持標準的SQL語法，與mysql也基本兼容。但是無法用use語句切換數據庫，作者對use語句做了兼容（不報錯），但是卻沒有實際效果。
兩種方式，區別在于：
1. 一種是在往mysql_servers表中添加server時就為其劃分好hostgroup_id（例如0表示寫組，1表示讀組）
2. 另一種往mysql_servers表中添加server時不區分hostgroup_id（例如全部設為0），然后通過mysql_replication_hostgroups表中的值，根據proxysql檢測到的各server的read_only變量值來自動為后端server設置hostgroup_id
這里強烈推薦用第一種方式：
因為第一種是完全由我們控制的;而第二種假如我們誤將讀server的read_only屬性設置為0，則proxysql會將其重新分配到寫組，這絕對是不期望的。MySQL [(none)]> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 192.168.1.21 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.1.10 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.1.4 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
#proxysql server的IP為：192.168.1.34配置好一主(db1，hostgroup0)兩從(db2和db3，hostgroup1) ，并且在'mysql_query_rules'表中增加一條路由規則
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (10,1,'^SELECT',1,1)意為所有以select開頭的語句路由到hostgroup1，其余語句路由到hostgroup0
在兩臺server的mysql-client分別連接至proxysql的6033端口，執行'select @@hostname'觀察其分配到的后端server
以mysql -e的形式執行該命令則能夠看到請求在兩臺讀server之間變換
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N.
db1
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N
db5
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N
db5
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N
db1
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N
db1
[root@db1 ~]# mysql -udm -p'dm' -h292.168.1.34 -P6033 -e "select @@hostname" -s -N
db5再實驗下mysql -e跟多條語句會如何
[root@db1 ~]# mysql -udm -p'dm' -P6033 -h292.168.1.34 -e "select @@hostname;select @@hostname;select @@hostname" -s -N
dm-web5
dm-web5
dm-web5由以上結果可能會猜想并可印證：在一個client的一個鏈接周期內，所有query路由到同一臺后端。
但是：這只是個假象(是因為正好用到了select @ 語句。按作者所說： sends a query that implicitly disables multiplexing. For example, if you run "SELECT @a" , ProxySQL will disable multiplexing for that client and will always use the same backend connection.)，proxysql的負載方式目前僅為加權輪詢一種（經過作者確認的），并無其他機制。
對上述架構用sysbench做只讀測試，過程中關閉(service mysqld stop)某一臺server (測試均在mysql-monitor_enabled=false的前提下)
測試命令
alias sysbench_test='sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua\
--mysql-user=dm --mysql-password='dm' --mysql-port=6033\
--mysql-host=192.168.1.34 --oltp-tables-count=16\
--num-threads=8 run --oltp-skip-trx=on --oltp-read-only=on'結果如下
[root@db3 ~]# sysbench_test
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored
Threads started!
ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest16 WHERE id=4964' failed: 2013 Lost connection to MySQL server during query
ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest12 WHERE id=4954' failed: 2013 Lost connection to MySQL server during query
ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest7 WHERE id BETWEEN 4645 AND 4645+99' failed: 2013 Lost connection to MySQL server during query不是說好的自動重連/重執行嗎？為毛報錯了呢（atlas有同樣問題）
但是兩者通過上述的sysbench命令所拋出的錯誤經過多次比較，有不同：
proxysql報錯就一種
failed: 2013 Lost connection to MySQL server during query
atlas報錯則是兩種
failed: 2013 Lost connection to MySQL server during
failed: 1317 Query execution was interrupted
是不是說明re-execute有效呢？(no，其實這個思路就不對)測試方法錯了：其實關閉后端mysql服務來測試“reconnect”特性應該說從本質上就是不對的，mysql正常關閉會kill掉其上的所有processlist，我們可以用mysql-client來做一些驗證
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
**通過別的tty重啟該mysql**
mysql> select @@hostname;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+------------+
| @@hostname |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)可以看到mysql-client是具有重連(reconnect)功能的，然后我們來做一個kill掉mysql-client線程（也就是mysql在關閉時會kill掉所有線程）的操作
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
**在mysql服務端查詢并kill掉這個鏈接**
mysql> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 我們看到了之前sysbenche測試過程中關閉一臺從庫時的報錯。也就是說這個場景下這個報錯不是proxysql的‘reconnect’機制的問題，倒更像是‘re-execute’機制的問題。而kill操作應該理解為是mysql“主動”的動作，而非“異常”，所以這就不符合proxysql 的"re-execute"特性了，故而會報錯。
應該換一種方式來進行這個測試：
模擬網絡層無法通信的異常
我們在兩臺slave上通過iptables阻斷peoxysql到本機的3306端口來模擬無法鏈接和鏈接中斷異常
在sysbench開始之后再重啟iptables已經建立的鏈接會被保留，所以新規則要放到第一條的位置
-A INPUT -s 192.168.1.34 -p tcp -m tcp --dport 3306 -j DROP
首先 ：啟動sysbench只讀測試
然后 ：在測試結束前，修改db1的iptables，禁止來自proxysql的請求進入
然后 ：觀察sysbench的輸出和proxysql.log的輸出
結果 ：sysbench等待很長時間，依然無法完成，同時，proxysql也不會把db1標記為SHUNNED
proxysql.log輸出：
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 133 on 192.168.1.4:3306
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 136 on 192.168.1.4:3306
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 135 on 192.168.1.4:3306
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 137 on 192.168.1.4:3306
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 138 on 192.168.1.4:3306
2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 134 on 192.168.1.4:3306
可以看到在mysql-default_query_timeout=30000，30s之后proxysql確實kill了超時的語句。
30s這個時間可以通過執行‘service iptables restart’的時間和proxysql.log里日志的時間來判定，這個設置30s是有效的然后，直接對后端node（db1）進行同樣的測試，發現結果是一樣的，sysbench等待很長時間依然無法完成也無報錯。
從這一點似乎可以判定在這個測試方法下，未達到預期結果的原因可能不在proxysql，而在sysbench本身。
通過對重啟iptables之后的通信進行抓包（分別對針對proxysql和mysql在這個測試場景下進行抓包）,命令如下：
~]# date; service iptables restart; tcpdump -i em2 host 192.168.1.35 and port 3306 and host not 192.168.1.10 -w /tmp/sysbench-proxysql-network-issue.pacp
~]# date; service iptables restart; tcpdump -i em2 host 192.168.1.34 and port 3306 and host not 192.168.1.10 -w /tmp/sysbench-proxysql-network-issue.pacp
發現，sysbench“一直”在重傳由于iptables新規則而無法返回的幾個請求，所以就成了“無窮盡等待的樣子” (atlas 在這個場景下有同樣問題)
照理說，proxysql kill掉了一些查詢，會返回給sysbench錯誤，但為什么sysbench并未將錯誤展示出來呢(可能是因為re-execute機制)
最后，通過跟作者溝通，發現是由于沒開啟monitor模塊，導致proxysql無法檢測到后端出了什么類型的錯誤，也就無法執行對應各種后端錯誤的一些操作（之前我是特意關掉了monitor模塊）
prepare語句的支持好多框架用prepare語句來避免SQL注入等安全問題，同時能在MySQL解析查詢方面降低一些開銷，所以對于prepare語句支持與否也很重要
首先要從MySQL協議層面了解prepare語句，分為兩種(參考)
- Prepared Statements in Application Programs
或者稱為BINARY protocol
抓包分析一次prepare,set,execute過程，可以觀察到，客戶端發送的是COM_STMT_PREPARE
- Prepared Statements in SQL Scripts
或者稱為TEXT protocol
抓包分析一次prepare,set,execute過程，可觀察到客戶端發送的是COM_QUERY 
關于prepare語句，作者給出的回復和計劃如下：
這是我在實驗1.2.1版本時跟作者的溝通。現在已經發布1.3.5了，從1.3版本開始,兩種協議都支持了。但是在設置字符集方面，對于binary protocol 的prepare set names xxx和普通query(set names 'utf8' collate 'utf8_general_ci')(注意加了校對規則，不加校對規則可以)語句還無法正確處理（例如，php的laravel框架默認就是以第一種形式設置字符集）。
修正：經測試1.3.7版本，以上兩prepare語句的小bug都已解決
MySQL supports two type of prepared statements:
using API
using SQL Further details here
SQL support currently not supported either, because PREPARE doesn't disable multiplexing, so it is possible that ProxySQL sends PREPARE in a connection, and EXECUTE into another.SQL support will be fixed in v1.2.3 , see #684 .
API support is planned in ProxySQL 1.3 .
首先來精確理解一下這兩個詞，依作者回復
They are very related to each other
Connection poolis a cache of connections that can be reused.Multiplexingis a technique to reuse these connections.
后期逐漸了解到了proxysql的multiplexing。連接池是一個共享的池子里面有跟后端創建好的一些連接，一個服務端腳本的執行過程中可能有多次sql請求：
很顯然，對于連接池的使用效率上來說，理論上多數情況下proxysql的方式會更加高效并且與DB間維護的鏈接數量會更少
測試場景：
10條`select * from test.test_table`，10條`select @@hostname`；
ProxySQL/Atlas IP 192.168.1.35；兩個讀節點IP分別為192.168.1.37和192.168.1.38；
每次測試完之前重啟ProxySQL/Atlas；分兩次測試，第一次測試腳本如下（每條命令一次連接）
!#/bin/sh
for i in {1..10};do
mysql -uuser -p'passwd' -P6033 -h292.168.1.35 -e "select @@hostname;" #select @xxx，會禁用multiplexing
done
for i in {1..10};do
mysql -uuser -p'passwd' -P6033 -h292.168.1.35 -e "select id from test.test_table;" #普通查詢
done第二次測腳本如下（一次連接，執行所有命令）
for i in {1..10};do
query1 += 'select @@hostname;' #select @xxx，會禁用multiplexing
query2 += 'select id from test.test_table;' #普通查詢
done
echo $query1$query2 | mysql -uuser -p'passwd' -P6033 -h292.168.1.35對ProxySQL進行測試分析
通過tcpdump抓包wireshark分析，通過ProxySQL對20條查詢的路由情況及其和后端MySQL之間的建立連接情況（通過ProxySQL上的原端口號）來分析連接池和禁用multiplexing的情況。結果匯總如下
第一次測試（每條命令一次連接）
select @xxx #會禁用multiplexing
1.35源端口
1.35---->1.37 42094 42096 42097 42099 42102 （轉發5次）
1.35---->1.38 37971 37974 37976 37977 37979 （轉發5次）
普通查詢
1.35源端口
1.35---->1.37 42105 （轉發3次）
1.35---->1.38 37980 （轉發7次） 第二次測試（一次連接，執行所有命令）
select @xxx #會禁用multiplexing
1.35源端口
1.35---->1.37 （轉發0次）
1.35---->1.38 37817 （轉發10次）
普通查詢
1.35源端口
1.35---->1.37 （轉發0次）
1.35---->1.38 37817 （轉發10次） 對比來看Atlas的分時分析
第一次測試（每條命令一次連接）
select @xxx
1.35源端口
1.35---->1.37 （轉發0次）
1.35---->1.38 38405 38407 38409 38411 38413 （轉發10次）
38415 38417 38419 38421 38423
普通查詢
1.35源端口
1.35---->1.37 （轉發0次）
1.35---->1.38 38385 38387 38389 38391 38393 （轉發10次）
38395 38397 38399 38401 38403第二次測腳本如下（一次連接，執行所有命令）
select @xxx
1.35源端口
1.35---->1.37 42435 （轉發5次）
1.35---->1.38 38312 （轉發5次）
普通查詢
1.35源端口
1.35---->1.37 42435 （轉發5次）
1.35---->1.38 38312 （轉發5次）由上面測試分析結果可以明顯的看到
ProxySQL的負載均衡策略是基于權重的輪詢，但并不是嚴格的逐條輪詢。而且可以看到在一次連接之內：由于某些語句（select @xx 或者prepare語句）導致ProxySQL自動關閉multiplexing之后，在本次鏈接之后的所有語句都會被路由到同一臺MySQL
atlas做的僅僅是輪詢轉發，他不會去區分查詢類型（例如有些查詢是要路由到后端唯一的MySQL）
而且，在`第一次測試（每條命令一次連接）`中，atlas將所有的20條請求都路由到了1.38這臺MySQL，并且每次都要新建連接（并沒有用到其連接池）可以主動
來看一下其相關參數：
| mysql-monitor_enabled | true |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 120000 |
| mysql-monitor_connect_timeout | 200 |
| mysql-monitor_ping_interval | 60000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 100 |這兩種檢測的區別，據作者回復和抓包分析，總結如下：
Ping is done using mysql_ping()
    通過抓包分析，是通過現有的鏈接（連接池中）發送一個Request Ping語句
Connect is done using mysql_real_connect()
    這是一個客戶端到云服務器建立鏈接，登錄，退出登錄，關閉鏈接的完整過程
這兩個函數的返回值不同，可以幫助proxysql理解與后端鏈接除了哪些問題。
模擬場景，驗證以上各設置并加深理解其故障檢測機制：
兩個前提
修改web1 MySQL配置文件中max_connections = 3；重啟web1 MySQL，在其他tty打開幾個MySQL鏈接以確保proxysql無法鏈接該MySQL。同時在proxysql server上打開抓包功能tcpdump -i em2 host 192.168.1.4 and port 3306 -w /tmp/web_shun.pcap，然后通過wireshark對比上面各值進行分析。
附件：數據包文件
通過分析可驗以以下設置的有效性
mysql-monitor_connect_interval
mysql-monitor_ping_interval
mysql-monitor_ping_max_failures
mysql-shun_recovery_time_sec
mysql-ping_interval_server_msec可以被動
被動就是將全局變量‘mysql-monitor_enabled’置為false，這種情況下，后端server故障后，proxysql不會主動探知，而是在有請求被“正常”路由到該server之后才會在runtime層更改該server狀態為‘SHUNNED’ 或者重新變為‘ONLINE’。這個過程，應用無感知（表現為mysql-client命令和sysbench均無報錯）
相關變量為
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 60 |
| mysql-query_retries_on_failure | 1 |
| mysql-connect_retries_on_failure | 5 |
| mysql-connect_retries_delay | 1 |
| mysql-connection_max_age_ms | 0 |
| mysql-connect_timeout_server | 1000 |
| mysql-connect_timeout_server_max | 10000 |###七、關于proxysql和mysql中的最大連接數
首先明確下MySQL中的最大連接數由max_connections變量控制，proxysql中的最大連接數有兩個方面的設置mysql_users.max_connections和mysql_servers.max_connections
下面就直接說下我的結論 摘自我github上的issue
- if the
mysql_servers.max_connectionsis reached, some of the connections will wait until themysql-connect_timeout_server_maxis reached. then proxysql will return error messageSQLSTATE[HY000]: General error: 9001 Max connect timeout reached while reaching hostgroup 1 after 10000ms.- if the
mysql_users.max_connectionsis reached, then the client will see the error message1040: Too many connections- If backend's global variable 'max_connections' is reached and proxysql has no
ConnFreewith the backend, then client can accomplish the connection with proxysql but all queries will returnERROR 1040 (#HY00): Too many connections, and the monitor will consider this backend isshunand will be loged to the proxysql.log
###八、據我所知的bug和不足
bug：
SQLSTATE[HY000]: General error: 2057 A stored procedure returning result sets of different size was called. This is not supported by libmysqlset names xxx語句還不能有效處理set names xxx collate xxxx還不能有效處理###總結
穩定性方面：目前我們已經部分業務切換到了ProxySQL上，運行一直穩定，未遇到cpu負載高或者占用內存多等情況。
運維/DBA友好：借助于ProxySQL的錯誤日志，我們發現了之前沒注意到的一些SQL問題（如主鍵重復類的問題等）。還通過stats庫里的相關表定位了一些問題。
性能方面：也強于atlas（參見文章）
個人覺得，解決掉上述幾點bug和不足的話（且不說可能會出現的其他feature），ProxySQL就會更加強大和完美。
對于以上關于ProxySQL亮點、安裝、配置及測試等解析，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
