ProxySQL！像C羅一樣的強大！

關注我們獲得更多精彩內容！

作者 | 張甦， 資料庫領域的專家和知名人士、圖書《MySQL王者晉級之路》作者，51CTO 專家博主。近10年互聯網線上處理及培訓經驗，專註於 MySQL 資料庫，對 MongoDB、Redis 等 NoSQL 資料庫以及 Hadoop 生態圈相關技術有深入研究，具備非常豐富的理論與實戰經驗。

各位兄弟們，時隔多日老張又與大家見面啦。每次與大家見面，都會有好消息告訴大家，這次也不例外。前段時間出版了《MySQL王者晉級之路》一書，反響還不錯。爭取今年再出版一本 MongoDB 運維實戰的書籍，供給那些想要學習 NoSQL 的同學們作為工作中的參考。

現在正趕上世界盃，老張最喜歡的球隊是葡萄牙——最愛C羅，喜歡他那種在比賽中好強不服輸的精神。我們做技術也是一樣，不要因為一點困難，就放棄了當初的夢想。只有不斷努力，提升自己，才能在更好的平台上實現自我價值。

今兒，老張給大家介紹一款 MySQL 的一款中間件的產品——ProxySQL，它是靈活強大的 MySQL 代理層。像C羅一樣的強大，可以實現讀寫分離，支持 Query 路由功能，支持動態指定某個 SQL 進行 cache，支持動態載入配置、故障切換和一些 SQL的過濾功能。還有一些同類產品比如 DBproxy、MyCAT、OneProxy 等。但經過反覆對比和測試之後，決定給大家介紹一款性能不諳的 MySQL 中間件產品 ProxySQL。

有關 ProxySQL 更多的詳細信息可訪問：https://github.com/sysown/proxysql/wiki。

接下來通過實戰來全面了解一下 ProxySQL 的特性和使用場景，先介紹一下環境，我們的系統是CentOS6.7，MySQL版本是5.7.14，準備一主兩從架構來配合ProxySQL。

註：兩個從庫都要開啟 read_only=on。

實驗架構

ProxySQL的安裝與啟動

首先要安裝一些依賴的軟體包，配置好 Yum 源進行安裝即可。

在 192.168.56.103 上執行如下操作：

yum -y install perl-DBD-MySQLyum -y install perl-DBIyum -y install perl-Time-HiResyum -y install perl-IO-Socket-SSL

ProxySQL 軟體包的兩個下載地址。

GitHub官網：https://github.com/sysown/proxysql/releases。

percona官網：https://www.percona.com/downloads/proxysql/。

安裝ProxySQL：

rpm -ivh proxysql-1.3.9-1-centos67.x86_64.rpm

配置文件路徑為 /etc/proxysql.cnf。

啟動 ProxySQL：

註：6032 是 ProxySQL 的管理埠號，6033 是對外服務的埠號。

管理用戶名和密碼都是默認的 admin。

關閉 ProxySQL：

service proxysql stop

查看安裝版本：

管理員登錄命令：

/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

可見有四個庫：main、disk、stats和monitor。分別說明一下這四個庫的作用。

main：內存配置資料庫，即MEMORY，表裡存放後端db實例、用戶驗證、路由規則等信息。main庫中有如下信息：

庫下的主要表：

mysql_servers—後端可以連接MySQL伺服器的列表。

mysql_users—配置後端資料庫的賬號和監控的賬號。

mysql_query_rules—指定Query路由到後端不同伺服器的規則列表。

註：表名以runtime_開頭的表示ProxySQL當前運行的配置內容，不能通過DML語句修改。只能修改對應的不以 runtime開頭的表，然後「LOAD」使其生效，「SAVE」使其存到硬碟以供下次重啟載入。

disk庫—持久化磁碟的配置。

stats庫—統計信息的匯總。

monitor庫—一些監控的收集信息，包括資料庫的健康狀態等。

配置 ProxySQL 監控

首先在 master（192.168.56.100）上創建 ProxySQL 的監控賬戶和對外訪問賬戶並賦予許可權。

命令如下：

create user "monitor"@"192.168.56.%" identified by "monitor";grant all privileges on *.* to "monitor"@"192.168.56.%" with grant option;create user "zs"@"192.168.56.%" identified by "zs";grant all privileges on *.* to "zs"@"192.168.56.%" with grant option;flush privileges;

ProxySQL的多層配置系統

ProxySQL 有一套很完整的配置系統，方便 DBA 對線上的操作。整套配置系統分為三層，最頂層為 RUNTIME，中間層為 MEMORY 和最底層，也就是持久層的 DISK和 CONFIG FILE。

配置結構：

RUNTIME：代表 ProxySQL 當前生效的正在使用的配置，無法直接修改這裡的配置，必須要從下一層 「load」 進來。

MEMORY：MEMORY這一層上面連接 RUNTIME 層，下面連接持久化層。在這層可以正常操作 ProxySQL 配置，隨便修改，不會影響生產環境。修改一個配置一般都是先在 MEMORY 層完成，然後確認正常之後再載入到 RUNTIME 和持久化到磁碟上。

DISK 和 CONFIG FILE：持久化配置信息，重啟後內存中的配置信息會丟失，所以需要將配置信息保留在磁碟中。重啟時，可以從磁碟快速載入回來。

介紹完這三層配置系統之後，用超管用戶登錄 ProxySQL 來添加主從伺服器列表。

命令如下：

insert into mysql_servers(hostgroup_id,hostname,port) values (10,"192.168.56.100",3306);insert into mysql_servers(hostgroup_id,hostname,port) values (10,"192.168.56.101",3306);insert into mysql_servers(hostgroup_id,hostname,port) values (10,"192.168.56.102",3306);load mysql servers to runtime;save mysql servers to disk;

登錄 ProxySQL 之後，凡是進行任何操作，都需要運行 loadto runtime，從memory 載入到 runtime。然後再執行 saveto disk 持久化到磁碟。

載入完成之後，三台機器都是 ONLINE 狀態。

接下來繼續為 ProxySQL 配置監控賬號，命令如下：

set mysql-monitor_username="monitor";set mysql-monitor_password="monitor";load mysql variables to runtime;save mysql variables to disk;

之後驗證監控信息：

監控信息都已正常，沒有任何報錯。

配置 ProxySQL 主從分組信息

這裡會用到一張表 mysql_replication_hostgroups：

裡面的 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;

ProxySQL會根據 server 的 read_only 的取值將伺服器進行分組。read_only=0的server，master 被分到編號為 10 的寫組，read_only=1 的 server，slave 則被分到編號為 20 的讀組。

配置對外訪問賬號，默認指定主庫，並對該用戶開啟事務持久化保護。

註：mysql_users 表中的 transaction_persistent 欄位默認為 0

建議在創建完用戶之後設置為1，避免發生臟讀、幻讀等現象命令如下：

insert into mysql_users(username,password,default_hostgroup) values ("zs","zs",10);update mysql_users set transaction_persistent=1 where username="zs";load mysql users to runtime;save mysql users to disk;

驗證登錄的伺服器就是主庫：

註：對外埠號需要指定為6033。

配置讀寫分離策略

配置讀寫分離策略需要使用 mysql_query_rules 表。表中的 match_pattern 欄位就是代表設置的規則，destination_hostgroup 欄位代表默認指定的分組，apply 代表真正執行應用規則。

把所有以 select 開頭的語句全部分配到編號為 20 的讀組中。select for update 會產生一個寫鎖，對數據查詢的實效性要求高，把它分配到編號為 10 的寫組中，其他所有操作都會默認路由到寫組中。

命令如下：

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;```****測試讀寫分離**通過創建的對外賬戶zs，連接ProxySQL登錄資料庫。命令如下：`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033``**查看zs庫下tt的數據:**然後再登錄管理埠，通過查詢stats_mysql_query_digest這張表來監控查詢狀態,命令如下：select * from stats_mysql_query_digest;可見這條select語句自動路由到編號為20的讀組，即slave庫上。然後繼續測試，通過ProxySQL登錄到資料庫：`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033`執行select * from zs.tt for update和update tt set name="ff" where score=100的語句操作： 這時再登錄管理埠，監控查詢狀態，發現都已經成功路由到了編號為10的寫組，即主庫，證明讀寫分離設置成功。  讀寫分離設置成功之後，我們可以調整權重，讓某台機器承受更多的讀操作。這些技巧都可以用在運維ProxySQL上面。調整192.168.56.102 node2節點的查詢權重，讓更多的讀請求路由到這台機器上面。命令如下：update mysql_servers set weight=10 where hostname="192.168.56.102";load mysql servers to runtime;save mysql servers to disk;作為苦逼DBA的我們，無論是初學者還是已經從業多年的「老司機」，都不要急於去把每個MySQL集群架構搭建出來。在學習的過程中，一些同學總是存在一個誤區，就是覺得我會搭建所有的資料庫架構就非常厲害了。其實並不是這樣的，架構搭建並不是我們的最終目的，作為DBA要先了解清楚自己公司的現有業務，看看公司的業務場景適合什麼樣的架構，要做好相應的資料庫架構設計。了解好該架構的優缺點，以及在今後應用中可能出現的問題，提前做好能解決問題的預案。知己知彼，注重細節，才能避免沒日沒夜地加班熬夜處理那些不該發生的問題。下面總結了五條MySQL架構設計中的經驗。（1）根據公司現有業務設計合理架構。（2）選擇成熟的架構方案。（3）因地制宜，根據實際設備情況做出選擇。（4）考慮方案的可行性。（5）越簡單越好，越適合公司越好。

更多精彩關注「數據和雲」公眾號

資源下載

關注公眾號：數據和雲（OraNews）回復關鍵字獲取

2018DTCC, 資料庫大會PPT

2017DTC，2017 DTC 大會 PPT

DBALIFE，「DBA 的一天」海報

DBA04，DBA 手記4 電子書

122ARCH，Oracle 12.2體系結構圖

2017OOW，Oracle OpenWorld 資料

PRELECTION，大講堂講師課程資料

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！