溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
各位兄弟們，時隔多日老張又與大家見面啦。每次與大家見面，都會有好消息告訴大家，這次也不例外。前段時間出版了《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。
環境配置：
192.168.56.100 Master（node1） server-id：3306100
192.168.56.101 Slave1（node2） server-id：3306101
192.168.56.102 Slave2（node3） server-id：3306102
192.168.56.103 Proxysql中間件 server-id：3306103注：兩個從庫都要開啟read_only=on。
實驗架構：
ProxySQL的安裝與啟動
首先要安裝一些依賴的軟件包，配置好Yum源進行安裝即可。
在192.168.56.103上執行如下操作：
yum -y install perl-DBD-MySQL
yum -y install perl-DBI
yum -y install perl-Time-HiRes
yum -y install perl-IO-Socket-SSLProxySQL軟件包的兩個下載地址。
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：
service proxysql start
注：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之后，凡是進行任何操作，都需要運行load to runtime，從memory加載到runtime。然后再執行save to 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）越簡單越好，越適合公司越好。
老張我就希望寫任何東西，都可以給大家一些啟示。在工作中，可以幫助到各位就夠了！技術需要分享，我們一起努力工作，讓家人過得更好！
最后說句題外話，老張近期應邀在51CTO博客上線專欄《練一套正宗的MySQL降龍十八掌》，大家感興趣可以戳鏈接了解一下。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
