? 通常一個網站在初期訪問量都比較小，所以一般的小架構足以支撐。但是，當網站逐漸發展起來后，隨之而來的是大量的訪問，這時候最先出現的瓶頸就是數據庫了。因為數據的寫入讀取操作（I/O）是集群中響應速度最慢的，所以在集群建設時就要規劃好后端存儲架構。
? 后端存儲數據庫架構普遍是主從復制，這樣解決了數據備份問題。但是，由前端來的讀寫請求都要經過主庫，量小沒問題，當量大到超過主庫的性能極限時，主庫分分鐘會宕機。
? 為了避免主庫宕機這種災難性事件的發生，讀寫分離的設計誕生了！把寫的請求只交給主庫，讀的請求由主庫和從庫一起分攤，這樣就大大減輕了主庫的負擔。
? 大部分公司會由開發部門在軟件層面實現對數據庫的讀寫分離，其次可以選擇讀寫分離的中間件如：TDDL、amoeba、cobar、MySQL-proxy、Atlas、MyCat。
本次測試采用MySQL官方發布的MySQL-proxy。
官網下載地址（二進制包）：https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
測試環境：
[root@SQL-M ~]# cat /etc/redhat-release?
CentOS release 6.8 (Final)
[root@SQL-M ~]# uname -r
2.6.32-642.el6.x86_64
MySQL-proxy ? IP：192.168.0.86
MySQL-master ?IP：192.168.0.88
MySQL-slave ? IP：192.168.0.90
前提：MySQL主庫與從庫已經實現主從復制。
實現主從復制請參考我的前一篇博文：http://yuyicong.blog.51cto.com/11274530/1919158
接下來的操作在proxy服務器上：
[root@SQL-proxy ~]# cd /usr/local/src/
[root@SQL-proxy src]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@SQL-proxy src]# ll -h
total 12M
-rw-r--r-- 1 root root 12M Aug 19 ?2014 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@SQL-proxy src]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@SQL-proxy src]# cd ..?
[root@SQL-proxy local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy-0.8.5
[root@SQL-proxy local]# ll -d?mysql-proxy-0.8.5
drwxr-xr-x ?8 7161 wheel 4096 Aug 19 ?2014 mysql-proxy-0.8.5 ? ?# 看到所屬用戶和組有問題
[root@SQL-proxy local]# chown -R root:root mysql-proxy-0.8.5/ ?# 改為root
[root@SQL-proxy local]# ln -s mysql-proxy-0.8.5/ mysql-proxy ? ?# 必備軟連接
[root@SQL-proxy local]# cd mysql-proxy
[root@SQL-proxy mysql-proxy]# mkdir etc ? ? ? ? ? ? ?# 創建 lua 腳本存放目錄
[root@SQL-proxy mysql-proxy]# mkdir logs ? ? ? ? ? ?# 創建日志目錄
把實現讀寫分離的 lua 腳本復制到 etc 目錄下
[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua etc/ ??
[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua etc/
創建啟動參數文件，啟動服務時命令行就不用老長老長的。
[root@SQL-proxy ~]# vim /etc/mysql-proxy.cnf
[mysql-proxy] admin-username=proxy?????#?連接主從mysql共有的用戶 admin-password=123???????#?連接用戶的密碼 proxy-address=192.168.0.86:3306???#?mysql-proxy監聽本地工作ip和端口，不加端口默認是4040，為了方便管理這里用3306 proxy-backend-addresses=192.168.0.87:3306????#?指定后端主庫 proxy-read-only-backend-addresses=192.168.0.90:3306??#?指定后端只讀從庫 proxy-lua-script=/usr/local/mysql-proxy/etc/rw-splitting.lua??#?指定實現讀寫分離的lua腳本 admin-lua-script=/usr/local/mysql-proxy/etc/admin-sql.lua?????#?指定管理腳本 pid-file=/var/run/mysql-proxy.pid??? log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log??#?日志位置 log-level=info??#?定義log日志級別，由高到低分別有(error|warning|info|message|debug) daemon=true?????#?以守護進程方式運行 keepalive=true??#?work進程崩潰時，嘗試重啟
啟動服務
[root@SQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
2017-04-24 16:40:19: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf aren't secure (0660 or stricter required)
2017-04-24 16:40:19: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2017-04-24 16:40:19: (message) shutting down normally, exit code is: 1
！！看到無法啟動，文件權限要改，因為文件里面有賬號密碼。
[root@SQL-proxy ~]# chmod 660 /etc/mysql-proxy.cnf ? ? # 改權限
[root@SQL-proxy ~]# ll /etc/mysql-proxy.cnf?
-rw-rw---- 1 root root 432 Apr 24 16:31 /etc/mysql-proxy.cnf
[root@SQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?# ?重新啟動
[root@SQL-proxy ~]# netstat -lntup ? # 查看服務
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address ? ? ? ? ? ? ? Foreign Address ? ? ? ? ? ? State ? ? ? PID/Program name ??
tcp ? ? ? ?0 ? ? ?0 192.168.0.86:3306 ? ? ? ? ? 0.0.0.0:* ? ? ? ? ? ? ? ? ? LISTEN ? ? ?3086/mysql-proxy?
老是命令行敲一長串命令肯定不科學了，直接上服務管理腳本。
[root@SQL-proxy?~]#?vim?/etc/init.d/mysql-proxy
#!/bin/bash
#
#?mysql-proxy?This?script?starts?and?stops?the?mysql-proxy?daemon
#
#?chkconfig:?-?78?30
#?processname:?mysql-proxy
#?description:?mysql-proxy?is?a?proxy?daemon?for?mysql#?Source?function?library.
.?/etc/rc.d/init.d/functionsprog="/usr/local/mysql-proxy/bin/mysql-proxy"#?Source?networking?configuration.
if?[?-f?/etc/sysconfig/network?];?then.?/etc/sysconfig/network
fi#?Check?that?networking?is?up.
[?${NETWORKING}?=?"no"?]?&&?exit?0DEFAULT_FILE=/etc/mysql-proxy.cnf
PROXY_PID=/var/run/mysql-proxy.pidRETVAL=0start()?{echo?-n?$"Starting?MySQL-proxy...??:?"daemon?$prog?--defaults-file=$DEFAULT_FILERETVAL=$?echoif?[?$RETVAL?-eq?0?];?thentouch?/var/lock/subsys/mysql-proxy.lockfi
}stop()?{echo?-n?$"Stopping?MySQL-proxy...??:?"killproc?-p?$PROXY_PID?-d?3?$progRETVAL=$?echoif?[?$RETVAL?-eq?0?];?thenrm?-f?/var/lock/subsys/mysql-proxy.lockrm?-f?$PROXY_PIDfi
}
#?See?how?we?were?called.
case?"$1"?instart)start;;stop)stop;;restart)stopstart;;condrestart|try-restart)if?status?-p?$PROXY_PIDFILE?$prog?>&/dev/null;?thenstopstartfi;;status)status?-p?$PROXY_PID?$prog;;*)echo?"Usage:?$0?{start|stop|restart|reload|status|condrestart|try-restart}"RETVAL=1;;
esacexit?$RETVAL[root@SQL-proxy ~]# chmod +x /etc/init.d/mysql-proxy ? # ?給執行權限
[root@SQL-proxy ~]# chkconfig mysql-proxy on ? ? ? ? ? # 可以加入開機啟動
測試一下腳本：
[root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?status mysql-proxy?(pid??3086)?is?running... [root@SQL-proxy?~]#? [root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?stop Stopping?MySQL-proxy...??:?????????????????????????????????[??OK??] [root@SQL-proxy?~]#? [root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?status mysql-proxy?is?stopped [root@SQL-proxy?~]#?netstat?-lntup Active?Internet?connections?(only?servers) Proto?Recv-Q?Send-Q?Local?Address???????????????Foreign?Address?????????????State???????PID/Program?name??? tcp????????0??????0?0.0.0.0:22??????????????????0.0.0.0:*???????????????????LISTEN??????1079/sshd??????????? tcp????????0??????0?:::22???????????????????????:::*????????????????????????LISTEN??????1079/sshd??????????? [root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?start Starting?MySQL-proxy...??:?????????????????????????????????[??OK??] [root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?restart Stopping?MySQL-proxy...??:?????????????????????????????????[??OK??] Starting?MySQL-proxy...??:?????????????????????????????????[??OK??] [root@SQL-proxy?~]#? [root@SQL-proxy?~]#?netstat?-lntup??????????????? Active?Internet?connections?(only?servers) Proto?Recv-Q?Send-Q?Local?Address???????????????Foreign?Address?????????????State???????PID/Program?name??? tcp????????0??????0?192.168.0.86:3306???????????0.0.0.0:*???????????????????LISTEN??????3143/mysql-proxy
?
以上MySQL-proxy服務器搭建完成，但是用于連接后端數據庫的用戶：proxy還需要在數據庫端創建
數據庫Master端：創建proxy用戶，給增刪改查權限
[root@SQL-M?~]#?mysql?-uroot?-p Enter?password:? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?3 Server?version:?5.5.55-log?MySQL?Community?Server?(GPL)Copyright?(c)?2000,?2017,?Oracle?and/or?its?affiliates.?All?rights?reserved.Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners.Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.Master>? Master>?grant?insert,delete,update,select?on?*.*?to?proxy@'192.168.0.86'?identified?by?'123'; Query?OK,?0?rows?affected?(0.00?sec)Master>?select?user,host?from?mysql.user;???????????????????????????????????????+-------+--------------+ |?user??|?host?????????| +-------+--------------+ |?root??|?127.0.0.1????| |?rep???|?192.168.0.%??| |?proxy?|?192.168.0.86?| |?root??|?localhost????| +-------+--------------+ 4?rows?in?set?(0.00?sec)Master>?show?grants?for?proxy@'192.168.0.86'; +------------------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?proxy@192.168.0.86????????????????????????????????????????????????????????????????????????????????????????????????????????????| +------------------------------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?*.*?TO?'proxy'@'192.168.0.86'?IDENTIFIED?BY?PASSWORD?'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'?| +------------------------------------------------------------------------------------------------------------------------------------------+ 1?row?in?set?(0.00?sec)
Slave端：此時已經與主庫一致擁有了proxy用戶，但是從庫只能讀（select），所以要對proxy回收權限。
Slave>?show?grants?for?proxy@'192.168.0.86'; +------------------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?proxy@192.168.0.86????????????????????????????????????????????????????????????????????????????????????????????????????????????| +------------------------------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?*.*?TO?'proxy'@'192.168.0.86'?IDENTIFIED?BY?PASSWORD?'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'?| +------------------------------------------------------------------------------------------------------------------------------------------+ 1?row?in?set?(0.00?sec)Slave>?revoke?insert,update,delete?on?*.*?from?proxy@'192.168.0.86';?#?只留查詢權限 Query?OK,?0?rows?affected?(0.00?sec)Slave>?show?grants?for?proxy@'192.168.0.86';???????????????????????? +------------------------------------------------------------------------------------------------------------------+ |?Grants?for?proxy@192.168.0.86????????????????????????????????????????????????????????????????????????????????????| +------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?'proxy'@'192.168.0.86'?IDENTIFIED?BY?PASSWORD?'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'?| +------------------------------------------------------------------------------------------------------------------+ 1?row?in?set?(0.00?sec)
測試讀寫分離
為了方便測試，修改 rw-splitting.lua 腳本里觸發讀寫分離參數
[root@SQL-proxy?~]#?vim?/usr/local/mysql-proxy/etc/rw-splitting.lua?
--?connection?pool
if?not?proxy.global.config.rwsplit?thenproxy.global.config.rwsplit?=?{min_idle_connections?=?1,??#?默認超過4個連接數時，才開始讀寫分離，改為1max_idle_connections?=?8,??#?默認最大連接數8，不動它is_debug?=?false}
end[root@SQL-proxy?~]#?/etc/init.d/mysql-proxy?restart???????#?重啟一下
Stopping?MySQL-proxy...??:?????????????????????????????????[??OK??]
Starting?MySQL-proxy...??:?????????????????????????????????[??OK??]數據庫Master端
Master>?create?database?proxy;?????????#?建個庫 Query?OK,?1?row?affected?(0.00?sec)Master>?create?table?master(id?int(2),name?char(10));??#?停掉從庫?IO線程后再建個表 Query?OK,?0?rows?affected?(0.18?sec)Master>?desc?master; +-------+----------+------+-----+---------+-------+ |?Field?|?Type?????|?Null?|?Key?|?Default?|?Extra?| +-------+----------+------+-----+---------+-------+ |?id????|?int(2)???|?YES??|?????|?NULL????|???????| |?name??|?char(10)?|?YES??|?????|?NULL????|???????| +-------+----------+------+-----+---------+-------+ 2?rows?in?set?(0.00?sec)
Slave端
Slave>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?mysql??????????????| |?performance_schema?| |?proxy??????????????|?????#?新建的庫已經有了 |?school?????????????| |?test???????????????| +--------------------+ 6?rows?in?set?(0.00?sec)Slave>?stop?slave?io_thread;??????#?停掉?IO?線程 Query?OK,?0?rows?affected?(0.00?sec)Slave>?show?slave?status\G?? ***************************?1.?row?***************************Slave_IO_State:?Master_Host:?192.168.0.88Master_User:?repMaster_Port:?3306Connect_Retry:?60Master_Log_File:?mysql-bin.000019Read_Master_Log_Pos:?351Relay_Log_File:?relay-bin.000011Relay_Log_Pos:?497Relay_Master_Log_File:?mysql-bin.000019Slave_IO_Running:?NoSlave_SQL_Running:?YesSlave>?use?proxy; Database?changedSlave>?create?table?slave(id?int(2),name?char(10));??#?建個表 Query?OK,?0?rows?affected?(0.00?sec)Slave>?desc?slave; +-------+----------+------+-----+---------+-------+ |?Field?|?Type?????|?Null?|?Key?|?Default?|?Extra?| +-------+----------+------+-----+---------+-------+ |?id????|?int(2)???|?YES??|?????|?NULL????|???????| |?name??|?char(10)?|?YES??|?????|?NULL????|???????| +-------+----------+------+-----+---------+-------+ 2?rows?in?set?(0.00?sec)Slave>?insert?into?slave?values(90,'slave');????#?插入一條數據 Query?OK,?1?row?affected?(0.00?sec)Slave>?select?*?from?slave; +------+-------+ |?id???|?name??| +------+-------+ |???90?|?slave?| +------+-------+ 1?row?in?set?(0.00?sec)
模擬遠程連接MySQL-proxy服務器進行讀寫操作
[root@SQL-proxy?~]#?mysql?-h192.168.0.86?-uproxy?-P3306?-p Enter?password:? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?12 Server?version:?5.5.55-log?MySQL?Community?Server?(GPL)Copyright?(c)?2000,?2017,?Oracle?and/or?its?affiliates.?All?rights?reserved.Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners.Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.mysql>? mysql>?use?proxy; Database?changed mysql>? mysql>?show?tables;???#?show?出來的只有master這個表 +-----------------+ |?Tables_in_proxy?| +-----------------+ |?master??????????| +-----------------+ 1?row?in?set?(0.01?sec)mysql>?desc?master; +-------+----------+------+-----+---------+-------+ |?Field?|?Type?????|?Null?|?Key?|?Default?|?Extra?| +-------+----------+------+-----+---------+-------+ |?id????|?int(2)???|?YES??|?????|?NULL????|???????| |?name??|?char(10)?|?YES??|?????|?NULL????|???????| +-------+----------+------+-----+---------+-------+ 2?rows?in?set?(0.00?sec)mysql>?insert?into?master?values(88,'master');??#?往master表里插入數據 Query?OK,?1?row?affected?(0.00?sec)????????<<---?寫入是?OK?的mysql>?select?*?from?master; ERROR?1146?(42S02):?Table?'proxy.master'?doesn't?exist??#?明顯查不到的，因為去找從庫了，從庫上沒有這表。mysql>?show?tables;???#?我們show時只能看到?master?表，好像show只能找主庫。 +-----------------+ |?Tables_in_proxy?| +-----------------+ |?master??????????| +-----------------+ 1?row?in?set?(0.00?sec)mysql>?select?*?from?slave;??#?我們直接查?slave?表 +------+-------+ |?id???|?name??| +------+-------+ |???90?|?slave?|?????????<<---?查到了，讀操作成功 +------+-------+ 1?row?in?set?(0.00?sec)mysql>
恢復 slave IO 線程
Slave>?start?slave?io_thread; Query?OK,?0?rows?affected?(0.00?sec)Slave>?show?slave?status\G??? ***************************?1.?row?***************************Slave_IO_State:?Waiting?for?master?to?send?eventMaster_Host:?192.168.0.88Master_User:?repMaster_Port:?3306Connect_Retry:?60Master_Log_File:?mysql-bin.000019Read_Master_Log_Pos:?1566Relay_Log_File:?relay-bin.000012Relay_Log_Pos:?1468Relay_Master_Log_File:?mysql-bin.000019Slave_IO_Running:?YesSlave_SQL_Running:?Yes
再次模擬遠程連接MySQL-proxy服務器查?master 表
[root@SQL-proxy?~]#?mysql?-h192.168.0.86?-uproxy?-P3306?-p Enter?password:? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?12 Server?version:?5.5.55-log?MySQL?Community?Server?(GPL)Copyright?(c)?2000,?2017,?Oracle?and/or?its?affiliates.?All?rights?reserved.Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners.Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.mysql>? mysql>?use?proxy; Database?changed mysql>? mysql>?select?*?from?master; +------+--------+ |?id???|?name???| +------+--------+ |???88?|?master?|????????<<----由于從庫恢復后開始復制主庫數據，所以可以查到了。 +------+--------+ 1?row?in?set?(0.00?sec)
以上MySQL-proxy讀寫分離完成。
存在的問題：
? ? 當模擬前端連接MySQL-proxy服務器并進行數據庫操作時，MySQL-proxy服務器端屏幕有信息輸出，不知道為什么？
信息如下：
[root@SQL-proxy ~]# ? ? server default db: proxy
? ? client default db: school
? ? syncronizing
? ? server default db: proxy
? ? client default db: school
? ? syncronizing
? ? server default db: school
? ? client default db: proxy
? ? syncronizing
轉載于:https://blog.51cto.com/yuyicong/1919799
)
![BZOJ3172: [Tjoi2013]單詞](http://pic.xiahunao.cn/BZOJ3172: [Tjoi2013]單詞)