目錄

• 一 什么是讀寫分離
• 二 讀寫分離的好處
• 三 讀寫分離提高性能之原因
• 四 讀寫分離示意圖
• 五 讀寫分離模擬

一 什么是讀寫分離

雖然知道處理大數(shù)據(jù)量時，數(shù)據(jù)庫為什么要做讀寫分離，原因很簡單：讀寫分離是MySQL優(yōu)化的一方面，它可以提高性能，緩解數(shù)據(jù)庫壓力，緩解服務(wù)器壓力。

MySQL Proxy最強大的一項功能是實現(xiàn)“讀寫分離(Read/Write Splitting)”。基本的原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理SELECT查詢。

數(shù)據(jù)庫復制被用來把事務(wù)性查詢導致的變更同步到集群中 的從數(shù)據(jù)庫。 當然，主服務(wù)器也可以提供查詢服務(wù)。使用讀寫分離最大的作用無非是環(huán)境服務(wù)器壓力。可以看下這張圖：

二 讀寫分離的好處

1.增加冗余

2.增加了機器的處理能力

3.對于讀操作為主的應(yīng)用，使用讀寫分離是最好的場景，因為可以確保寫的服務(wù)器壓力更小，而讀又可以接受點時間上的延遲。

三 讀寫分離提高性能之原因

1.物理服務(wù)器增加，負荷增加
2.主從只負責各自的寫和讀，極大程度的緩解X鎖和S鎖爭用
3.從庫可配置myisam引擎，提升查詢性能以及節(jié)約系統(tǒng)開銷

4.從庫同步主庫的數(shù)據(jù)和主庫直接寫還是有區(qū)別的，通過主庫發(fā)送來的binlog恢復數(shù)據(jù)，但是，最重要區(qū)別在于主庫向從庫發(fā)送binlog是異步的，從庫恢復數(shù)據(jù)也是異步的

5.讀寫分離適用與讀遠大于寫的場景，如果只有一臺服務(wù)器，當select很多時，update和delete會被這些select訪問中的數(shù)據(jù)堵塞，等待select結(jié)束，并發(fā)性能不高。對于寫和讀比例相近的應(yīng)用，應(yīng)該部署雙主相互復制

6.可以在從庫啟動是增加一些參數(shù)來提高其讀的性能，例如--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL。當然這些設(shè)置也是需要根據(jù)具體業(yè)務(wù)需求來定得，不一定能用上

7.分攤讀取。假如我們有1主3從，不考慮上述1中提到的從庫單方面設(shè)置，假設(shè)現(xiàn)在1 分鐘內(nèi)有10條寫入，150條讀取。那么，1主3從相當于共計40條寫入，而讀取總數(shù)沒變，因此平均下來每臺服務(wù)器承擔了10條寫入和50條讀取（主庫不 承擔讀取操作）。

因此，雖然寫入沒變，但是讀取大大分攤了，提高了系統(tǒng)性能。另外，當讀取被分攤后，又間接提高了寫入的性能。所以，總體性能提高了，說白 了就是拿機器和帶寬換性能。MySQL官方文檔中有相關(guān)演算公式：官方文檔 見6.9FAQ之“MySQL復制能夠何時和多大程度提高系統(tǒng)性能”

8.MySQL復制另外一大功能是增加冗余，提高可用性，當一臺數(shù)據(jù)庫服務(wù)器宕機后能通過調(diào)整另外一臺從庫來以最快的速度恢復服務(wù)，因此不能光看性能，也就是說1主1從也是可以的。

四 讀寫分離示意圖

五 讀寫分離模擬

實驗環(huán)境簡介

serv01：代理服務(wù)器 192.168.1.11 serv01.host.com

serv08：主服務(wù)器（主要寫數(shù)據(jù)，可讀可寫） 192.168.1.18 serv08.host.com

serv09：從服務(wù)器（主要讀數(shù)據(jù)） 192.168.1.19 serv09.host.com

操作系統(tǒng)版本

RHEL Server6.1 64位系統(tǒng)

使用到的軟件包版本

mysql-5.5.29-linux2.6-x86_64.tar.gz

mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz

第一步，搭建MySQL服務(wù)器，清空日志。注意：代理服務(wù)器中不需要裝MySQL

第二步，拷貝mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz文件，解壓文件

[root@larrywen 1005]# scp /opt/soft/ule-mysql/mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz 192.168.1.11:/opt [root@serv01 opt]# tar -xvf mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/ [root@serv01 opt]# cd /usr/local/ [root@serv01 local]# mv mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit/ mysql-proxy [root@serv01 local]# ll mysql-proxy/ total 24 drwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 bin drwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 include drwxr-xr-x. 4 7157 wheel 4096 Aug 17 2011 lib drwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 libexec drwxr-xr-x. 3 7157 wheel 4096 Aug 17 2011 licenses drwxr-xr-x. 3 7157 wheel 4096 Aug 17 2011 share --可以查看幫助 [root@serv01 bin]# ./mysql-proxy --help-all

第三步，serv08主服務(wù)器創(chuàng)建用戶，serv09從服務(wù)器創(chuàng)建用戶，注意用戶名和密碼一致

serv08?mysql>?grant?all?on?*.*?to?"larry"@"192.168.1.%"?identified?by?"larry";?Query?OK,?0?rows?affected?(0.00?sec)?serv09?mysql>?grant?all?on?*.*?to?"larry"@"192.168.1.%"?identified?by?"larry";?Query?OK,?0?rows?affected?(0.00?sec)?

第四步，serv09從服務(wù)器更改設(shè)置，開啟slave，查看slave狀態(tài)。創(chuàng)建測試數(shù)據(jù)庫，插入測試數(shù)據(jù)

serv09?mysql>?change?master?to?master_host="192.168.1.18",?master_user="larry",?master_password="larry",?master_port=3306,?master_log_file="mysql-bin.000001",?master_log_pos=107;?Query?OK,?0?rows?affected?(0.01?sec)?mysql>?start?slave;?Query?OK,?0?rows?affected?(0.00?sec)?mysql>?show?slave?status?\G;?***************************?1.?row?***************************?Slave_IO_State:?Waiting?for?master?to?send?event?Master_Host:?192.168.1.18?Master_User:?larry?Master_Port:?3306?Connect_Retry:?60?Master_Log_File:?mysql-bin.000001?Read_Master_Log_Pos:?107?Relay_Log_File:?serv09-relay-bin.000002?Relay_Log_Pos:?253?Relay_Master_Log_File:?mysql-bin.000001??Slave_IO_Running:?Yes?Slave_SQL_Running:?Yes?Replicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0?Last_Error:?Skip_Counter:?0?Exec_Master_Log_Pos:?107?Relay_Log_Space:?410?Until_Condition:?None?Until_Log_File:?Until_Log_Pos:?0?Master_SSL_Allowed:?No?Master_SSL_CA_File:?Master_SSL_CA_Path:?Master_SSL_Cert:?Master_SSL_Cipher:?Master_SSL_Key:?Seconds_Behind_Master:?0?Master_SSL_Verify_Server_Cert:?No?Last_IO_Errno:?0?Last_IO_Error:?Last_SQL_Errno:?0?Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?2?1?row?in?set?(0.00?sec)?ERROR:?No?query?specified?mysql>?select?user,password,host?from?mysql.user;?+------+-------------------------------------------+-----------------+?|?user?|?password?|?host?|?+------+-------------------------------------------+-----------------+?|?root?|?|?localhost?|?|?root?|?|?serv08.host.com?|?|?root?|?|?127.0.0.1?|?|?root?|?|?::1?|?|?|?|?localhost?|?|?|?|?serv08.host.com?|?|?rep?|?*0CDC8D34246E22649D647DB04E7CCCACAB4368B6?|?192.168.1.%?|?+------+-------------------------------------------+-----------------+?7?rows?in?set?(0.00?sec)?mysql>?create?database?larrydb;?Query?OK,?1?row?affected?(0.00?sec)?mysql>?use?larrydb;?Database?changed?mysql>?create?table?user(id?int,?name?varchar(30));?Query?OK,?0?rows?affected?(0.01?sec)?mysql>?insert?into?user?values(1,"larrywen");?Query?OK,?1?row?affected?(0.01?sec)?mysql>?insert?into?user?values(2,"wentasy");?Query?OK,?1?row?affected?(0.00?sec)?mysql>?select?*?from?user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?+------+----------+?2?rows?in?set?(0.00?sec)?serv09?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?+------+----------+?2?rows?in?set?(0.00?sec)?

第五步，為了查看現(xiàn)象，serv09從服務(wù)器關(guān)閉slave

mysql>?stop?slave;Query?OK,?0?rows?affected?(0.01?sec)

第六步，serv 01查看是否有MySQL用戶，修改rw-splitting.lua文件，修改如下幾個參數(shù)

[root@serv01?mysql-proxy]#?id?mysql?uid=500(mysql)?gid=500(mysql)?groups=500(mysql)?[root@serv01?mysql-proxy]#?vim?rw-splitting.lua?[root@serv01?mysql-proxy]#?cat?rw-splitting.lua?|?grep?-e?min_idle_connections?-e?max_idle_connections?-e?is_debug?min_idle_connections?=?1,--最小空閑連接數(shù)，為了測試，這里設(shè)置為1?max_idle_connections?=?1,--最大空閑連接數(shù)，為了測試，這里設(shè)置為1?is_debug?=?true--是否打開Debug調(diào)試，為了查看調(diào)試信息，這里設(shè)置為true?

第七步，啟動mysql-proxy

[root@serv01?mysql-proxy]#?/etc/init.d/mysql-proxy?start?Starting?mysql-proxy:?--先確定是否可以連接?[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.18?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?Your?MySQL?connection?id?is?6?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?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>?exit?Bye?[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.19?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?Your?MySQL?connection?id?is?8?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?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>?exit?Bye?

第八步，查看現(xiàn)象

[root@serv01?~]#?/etc/init.d/mysql-proxy?start?Starting?mysql-proxy:?[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.11?[connect_server]?192.168.1.11:51054?[1].connected_clients?=?0?[1].pool.cur_idle?=?0?[1].pool.max_idle?=?1?[1].pool.min_idle?=?1?[1].type?=?1?[1].state?=?0?[1]?idle-conns?below?min-idle?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?select?@@version_comment?limit?1?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?Your?MySQL?connection?id?is?10?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?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?larrydb;?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?SELECT?DATABASE()??sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?false?Database?changed?mysql>?select?*?from?user;?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?select?*?from?user?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?+------+----------+?2?rows?in?set?(0.00?sec)?mysql>?insert?into?user?values(3,"jsutdb");?[read_query]?192.168.1.11:51644?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?insert?into?user?values(3,"jsutdb")??sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?Query?OK,?1?row?affected?(0.00?sec)?serv08?mysql>?select?*?from?user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?+------+----------+?2?rows?in?set?(0.00?sec)?serv09?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?3?|?jsutdb?|?+------+----------+?3?rows?in?set?(0.00?sec)?

第九步，以上的測試雖有效果，但不是預期。排查原因，重新配置。發(fā)現(xiàn)proxy-read-only-backend-addresses和proxy-backend-addresses參數(shù)配置出錯，proxy-read-only-backend-addresses應(yīng)該配置成從服務(wù)器的IP地址，proxy-backend-addresses應(yīng)該配置成主服務(wù)器的IP地址。

[root@serv01?~]#?vim?/etc/init.d/mysql-proxy?[root@serv01?~]#?cat?/etc/init.d/mysql-proxy?#!/bin/sh?#?#?mysql-proxy?This?script?starts?and?stops?the?mysql-proxy?daemon?#?#?chkconfig:?-?78?30?#?processname:?mysql-proxy?#?description:?mysql-proxy?is?a?proxy?daemon?to?mysql?#?Source?function?library.?.?/etc/rc.d/init.d/functions?#PROXY_PATH=/usr/local/bin?PROXY_PATH=/usr/local/mysql-proxy/bin?prog="mysql-proxy"?#?Source?networking?configuration.?.?/etc/sysconfig/network?#?Check?that?networking?is?up.?[?${NETWORKING}?=?"no"?]?&&?exit?0?#?Set?default?mysql-proxy?configuration.?#PROXY_OPTIONS="--daemon"?PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.1.19:3306?--proxy-backend-addresses=192.168.1.18:3306?--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"?#PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid?PROXY_PID=/var/run/mysql-proxy.pid?#?Source?mysql-proxy?configuration.?if?[?-f?/etc/sysconfig/mysql-proxy?];?then?.?/etc/sysconfig/mysql-proxy?fi?PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH?#?By?default?it"s?all?good?RETVAL=0?#?See?how?we?were?called.?case?"$1"?in?start)?#?Start?daemon.?echo?-n?$"Starting?$prog:?"??$NICELEVEL?$PROXY_PATH/mysql-proxy?$PROXY_OPTIONS?--daemon?--pid-file=$PROXY_PID?--user=mysql?--log-level=debug?--log-file=/var/log/mysql-proxy.log?--proxy-address=192.168.1.11:3306?RETVAL=$??echo?if?[?$RETVAL?=?0?];?then?touch?/var/lock/subsys/mysql-proxy?fi?;;?stop)?#?Stop?daemons.?echo?-n?$"Stopping?$prog:?"?killproc?$prog?RETVAL=$??echo?if?[?$RETVAL?=?0?];?then?rm?-f?/var/lock/subsys/mysql-proxy?rm?-f?$PROXY_PID?fi?;;?restart)?$0?stop?sleep?3?$0?start?;;?condrestart)?[?-e?/var/lock/subsys/mysql-proxy?]?&&?$0?restart?;;?status)?status?mysql-proxy?RETVAL=$??;;?*)?echo?"Usage:?$0?{start|stop|restart|status|condrestart}"?RETVAL=1?;;?esac?exit?$RETVAL?

第十步，測試。插入數(shù)據(jù)，可以發(fā)現(xiàn)連接的是主服務(wù)器，查詢的時候也是主服務(wù)器。說明主服務(wù)器和從服務(wù)器均有讀的的功能。

[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.11?[connect_server]?192.168.1.11:57891?[1].connected_clients?=?0?[1].pool.cur_idle?=?0?[1].pool.max_idle?=?1?[1].pool.min_idle?=?1?[1].type?=?1?[1].state?=?1?[1]?idle-conns?below?min-idle?[read_query]?192.168.1.11:57891?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?select?@@version_comment?limit?1??sending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?mysql>?insert?into?user?values(5,"test");?Query?OK,1?row?affected?(0.01?sec)?[read_query]?192.168.1.11:57893?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?insert?into?user?values(5,"test")??sending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?mysql>?select?*?from?user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?5?|?test?|?+------+----------+?3?rows?in?set?(0.00?sec)?[read_query]?192.168.1.11:57893?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?select?*?from?user??sending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?serv08主服務(wù)器查看數(shù)據(jù)，可以查詢到，說明主服務(wù)器可以寫?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?5?|?test?|?+------+----------+?3?rows?in?set?(0.00?sec)?serv09從服務(wù)器查詢數(shù)據(jù)，發(fā)現(xiàn)不可查詢到，說明從服務(wù)器只讀?mysql>?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?3?|?jsutdb?|?|?4?|?db?|?+------+----------+?4?rows?in?set?(0.00?sec)?

第十一步，開啟slave。發(fā)現(xiàn)數(shù)據(jù)同步成功。

mysql>?start?slave;Query?OK,?0?rows?affected?(0.00?sec)mysql>?select?*?from?larrydb.user;+------+----------+|?id???|?name?????|+------+----------+|????1?|?larrywen?||????2?|?wentasy??||????3?|?jsutdb???||????4?|?db???????||????5?|?test?????|+------+----------+5?rows?in?set?(0.00?sec)

以上就是MySQL做讀寫分離提高性能緩解數(shù)據(jù)庫壓力的詳細內(nèi)容，更多關(guān)于MySQL緩解數(shù)據(jù)庫壓力的資料請關(guān)注其它相關(guān)文章！