溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
各位兄弟們，時隔多日老張又與大家見面啦。每次與大家見面，都會有好消息告訴大家，這次也不例外。前段時間出版了《MySQL王者晉級之路》一書，反響還不錯。爭取今年再出版一本MongoDB運維實戰(zhàn)的書籍，供給那些想要學(xué)習(xí)NoSQL的同學(xué)們作為工作中的參考。
現(xiàn)在正趕上世界杯，老張最喜歡的球隊是葡萄牙——最愛C羅，喜歡他那種在比賽中好強不服輸?shù)木?。我們做技術(shù)也是一樣，不要因為一點困難，就放棄了當初的夢想。只有不斷努力，提升自己，才能在更好的平臺上實現(xiàn)自我價值。
今兒，老張給大家介紹一款MySQL的一款中間件的產(chǎn)品——ProxySQL，它是靈活強大的MySQL代理層。像C羅一樣的強大，可以實現(xiàn)讀寫分離，支持Query路由功能，支持動態(tài)指定某個SQL進行cache，支持動態(tài)加載配置、故障切換和一些SQL的過濾功能。還有一些同類產(chǎn)品比如DBproxy、MyCAT、OneProxy等。但經(jīng)過反復(fù)對比和測試之后，決定給大家介紹一款性能不諳的MySQL中間件產(chǎn)品ProxySQL。
有關(guān)ProxySQL更多的詳細信息可訪問：
https://github.com/sysown/proxysql/wiki。
接下來通過實戰(zhàn)來全面了解一下ProxySQL的特性和使用場景，先介紹一下環(huán)境，我們的系統(tǒng)是CentOS6.7，MySQL版本是5.7.14，準備一主兩從架構(gòu)來配合ProxySQL。
環(huán)境配置：
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。
實驗架構(gòu)：
ProxySQL的安裝與啟動
首先要安裝一些依賴的軟件包，配置好Yum源進行安裝即可。
在192.168.56.103上執(zhí)行如下操作：
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官網(wǎng)：https://github.com/sysown/proxysql/releases。
percona官網(wǎng)：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是對外服務(wù)的端口號。
管理用戶名和密碼都是默認的admin。
關(guān)閉ProxySQL：
service proxysql stop
查看安裝版本： 
管理員登錄命令：/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
可見有四個庫：main、disk、stats和monitor。分別說明一下這四個庫的作用。
main：內(nèi)存配置數(shù)據(jù)庫，即MEMORY，表里存放后端db實例、用戶驗證、路由規(guī)則等信息。main庫中有如下信息：
庫下的主要表：
mysql_servers—后端可以連接MySQL服務(wù)器的列表。
mysql_users—配置后端數(shù)據(jù)庫的賬號和監(jiān)控的賬號。
mysql_query_rules—指定Query路由到后端不同服務(wù)器的規(guī)則列表。
注：表名以runtime_開頭的表示ProxySQL當前運行的配置內(nèi)容，不能通過DML語句修改。只能修改對應(yīng)的不以 runtime開頭的表，然后“LOAD”使其生效，“SAVE”使其存到硬盤以供下次重啟加載。
disk庫—持久化磁盤的配置。
stats庫—統(tǒng)計信息的匯總。
monitor庫—一些監(jiān)控的收集信息，包括數(shù)據(jù)庫的健康狀態(tài)等。
配置ProxySQL監(jiān)控
首先在master（192.168.56.100）上創(chuàng)建ProxySQL的監(jiān)控賬戶和對外訪問賬戶并賦予權(quán)限。
命令如下：
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的多層配置系統(tǒng)
ProxySQL有一套很完整的配置系統(tǒng)，方便DBA對線上的操作。整套配置系統(tǒng)分為三層，最頂層為RUNTIME，中間層為MEMORY和最底層，也就是持久層的DISK和CONFIG FILE。
配置結(jié)構(gòu)：
RUNTIME：代表ProxySQL當前生效的正在使用的配置，無法直接修改這里的配置，必須要從下一層“l(fā)oad”進來。
MEMORY：MEMORY這一層上面連接RUNTIME層，下面連接持久化層。在這層可以正常操作ProxySQL配置，隨便修改，不會影響生產(chǎn)環(huán)境。修改一個配置一般都是先在MEMORY層完成，然后確認正常之后再加載到RUNTIME和持久化到磁盤上。
DISK和CONFIG FILE：持久化配置信息，重啟后內(nèi)存中的配置信息會丟失，所以需要將配置信息保留在磁盤中。重啟時，可以從磁盤快速加載回來。
介紹完這三層配置系統(tǒng)之后，用超管用戶登錄ProxySQL來添加主從服務(wù)器列表。
命令如下：
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之后，凡是進行任何操作，都需要運行l(wèi)oad to runtime，從memory加載到runtime。然后再執(zhí)行save to disk持久化到磁盤。
加載完成之后，三臺機器都是ONLINE狀態(tài)。
接下來繼續(xù)為ProxySQL配置監(jiān)控賬號，命令如下：
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;之后驗證監(jiān)控信息： 
監(jiān)控信息都已正常，沒有任何報錯。
配置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會根據(jù)server的read_only的取值將服務(wù)器進行分組。read_only=0的server，master被分到編號為10的寫組，read_only=1的server，slave則被分到編號為20的讀組。
配置對外訪問賬號，默認指定主庫，并對該用戶開啟事務(wù)持久化保護。
注：mysql_users表中的transaction_persistent字段默認為0
建議在創(chuàng)建完用戶之后設(shè)置為1，避免發(fā)生臟讀、幻讀等現(xiàn)象命令如下：
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;驗證登錄的服務(wù)器就是主庫：
注：對外端口號需要指定為6033。
配置讀寫分離策略
配置讀寫分離策略需要使用mysql_query_rules表。表中的match_pattern字段就是代表設(shè)置的規(guī)則，destination_hostgroup字段代表默認指定的分組，apply代表真正執(zhí)行應(yīng)用規(guī)則。
把所有以select開頭的語句全部分配到編號為20的讀組中。select for update 會產(chǎn)生一個寫鎖，對數(shù)據(jù)查詢的實效性要求高，把它分配到編號為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;
```**
**測試讀寫分離**
通過創(chuàng)建的對外賬戶zs，連接ProxySQL登錄數(shù)據(jù)庫。
命令如下：
`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033``
**查看zs庫下tt的數(shù)據(jù):**
然后再登錄管理端口，通過查詢stats_mysql_query_digest這張表來監(jiān)控查詢狀態(tài),命令如下：
select * from stats_mysql_query_digest;
可見這條select語句自動路由到編號為20的讀組，即slave庫上。
然后繼續(xù)測試，通過ProxySQL登錄到數(shù)據(jù)庫：
`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033`
執(zhí)行select * from zs.tt for update和update tt set name='ff' where score=100的語句操作：
這時再登錄管理端口，監(jiān)控查詢狀態(tài)，發(fā)現(xiàn)都已經(jīng)成功路由到了編號為10的寫組，即主庫，證明讀寫分離設(shè)置成功。
讀寫分離設(shè)置成功之后，我們可以調(diào)整權(quán)重，讓某臺機器承受更多的讀操作。這些技巧都可以用在運維ProxySQL上面。
調(diào)整192.168.56.102 node2節(jié)點的查詢權(quán)重，讓更多的讀請求路由到這臺機器上面。
命令如下：
update mysql_servers set weight=10 where hostname='192.168.56.102';
load mysql servers to runtime;
save mysql servers to disk;
作為苦逼DBA的我們，無論是初學(xué)者還是已經(jīng)從業(yè)多年的“老司機”，都不要急于去把每個MySQL集群架構(gòu)搭建出來。在學(xué)習(xí)的過程中，一些同學(xué)總是存在一個誤區(qū)，就是覺得我會搭建所有的數(shù)據(jù)庫架構(gòu)就非常厲害了。其實并不是這樣的，架構(gòu)搭建并不是我們的最終目的，作為DBA要先了解清楚自己公司的現(xiàn)有業(yè)務(wù)，看看公司的業(yè)務(wù)場景適合什么樣的架構(gòu)，要做好相應(yīng)的數(shù)據(jù)庫架構(gòu)設(shè)計。了解好該架構(gòu)的優(yōu)缺點，以及在今后應(yīng)用中可能出現(xiàn)的問題，提前做好能解決問題的預(yù)案。知己知彼，注重細節(jié)，才能避免沒日沒夜地加班熬夜處理那些不該發(fā)生的問題。
下面總結(jié)了五條MySQL架構(gòu)設(shè)計中的經(jīng)驗。
（1）根據(jù)公司現(xiàn)有業(yè)務(wù)設(shè)計合理架構(gòu)。
（2）選擇成熟的架構(gòu)方案。
（3）因地制宜，根據(jù)實際設(shè)備情況做出選擇。
（4）考慮方案的可行性。
（5）越簡單越好，越適合公司越好。
老張我就希望寫任何東西，都可以給大家一些啟示。在工作中，可以幫助到各位就夠了！技術(shù)需要分享，我們一起努力工作，讓家人過得更好！
最后說句題外話，老張近期應(yīng)邀在51CTO博客上線專欄《練一套正宗的MySQL降龍十八掌》，大家感興趣可以戳鏈接了解一下。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼