MySQL做讀寫分離提高性能緩解數(shù)據(jù)庫壓力

更新時間：2023年05月09日 15:54:49 作者：PHP自學中心

這篇文章主要為大家介紹了MySQL做讀寫分離提高性能緩解數(shù)據(jù)庫壓力的技巧詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

一什么是讀寫分離

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

MySQL Proxy最強大的一項功能是實現(xiàn)“讀寫分離(Read/Write Splitting)”?；镜脑硎亲屩鲾?shù)據(jù)庫處理事務性查詢，而從數(shù)據(jù)庫處理SELECT查詢。

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

二讀寫分離的好處

1.增加冗余

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

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

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

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

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

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

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

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

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

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

四讀寫分離示意圖

五讀寫分離模擬

實驗環(huán)境簡介

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

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

serv09：從服務器（主要讀數(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服務器，清空日志。注意：代理服務器中不需要裝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主服務器創(chuàng)建用戶，serv09從服務器創(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從服務器更改設(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從服務器關(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應該配置成從服務器的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?

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

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

您可能感興趣的文章: