目錄

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

一 什么是讀寫分離

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

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

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

二 讀寫分離的好處

1.增加冗余

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

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

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

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

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

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

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

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

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

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

四 讀寫分離示意圖

五 讀寫分離模擬

實驗環境簡介

serv01：代理服務器 192.168.1.11 serv01.host.com

serv08：主服務器（主要寫數據，可讀可寫） 192.168.1.18 serv08.host.com

serv09：從服務器（主要讀數據） 192.168.1.19 serv09.host.com

操作系統版本

RHEL Server6.1 64位系統

使用到的軟件包版本

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

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

第一步，搭建MySQL服務器，清空日志。注意：代理服務器中不需要裝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主服務器創建用戶，serv09從服務器創建用戶，注意用戶名和密碼一致

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從服務器更改設置，開啟slave，查看slave狀態。創建測試數據庫，插入測試數據

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)?

第五步，為了查看現象，serv09從服務器關閉slave

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

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

[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,--最小空閑連接數，為了測試，這里設置為1?max_idle_connections?=?1,--最大空閑連接數，為了測試，這里設置為1?is_debug?=?true--是否打開Debug調試，為了查看調試信息，這里設置為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?

第八步，查看現象

[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)?

第九步，以上的測試雖有效果，但不是預期。排查原因，重新配置。發現proxy-read-only-backend-addresses和proxy-backend-addresses參數配置出錯，proxy-read-only-backend-addresses應該配置成從服務器的IP地址，proxy-backend-addresses應該配置成主服務器的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?

第十步，測試。插入數據，可以發現連接的是主服務器，查詢的時候也是主服務器。說明主服務器和從服務器均有讀的的功能。

[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主服務器查看數據，可以查詢到，說明主服務器可以寫?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?5?|?test?|?+------+----------+?3?rows?in?set?(0.00?sec)?serv09從服務器查詢數據，發現不可查詢到，說明從服務器只讀?mysql>?mysql>?select?*?from?larrydb.user;?+------+----------+?|?id?|?name?|?+------+----------+?|?1?|?larrywen?|?|?2?|?wentasy?|?|?3?|?jsutdb?|?|?4?|?db?|?+------+----------+?4?rows?in?set?(0.00?sec)?

第十一步，開啟slave。發現數據同步成功。

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做讀寫分離提高性能緩解數據庫壓力的詳細內容，更多關于MySQL緩解數據庫壓力的資料請關注其它相關文章！