Ubuntu10下如何搭建MySQL Proxy讀寫分離探討
一、MySQL-Proxy基礎(chǔ)
MySQL Proxy是一個(gè)處于你的Client端和MySQL server端之間的簡單程序,它可以監(jiān)測、分析或改變它們的通信。它使用靈活,沒有限制,常見的用途包括:負(fù)載平衡,故障、查詢分析,查詢過濾和修改等等。
(Figure1:MySQL Proxy)
MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. The proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:
load balancing and fail over handling query analysis and logging SQL macros query rewriting executing shell commandsOne of the more powerful features of MySQL Proxy is the ability to do "Read/Write Splitting". The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.
MySQL-Proxy是處在你的MySQL數(shù)據(jù)庫客戶和服務(wù)端之間的程序,它還支持嵌入性腳本語言Lua。這個(gè)代理可以用來分析、監(jiān)控和變換(transform)通信數(shù)據(jù),它支持非常廣泛的使用場景:
負(fù)載平衡和故障轉(zhuǎn)移處理 查詢分析和日志 SQL宏(SQL macros) 查詢重寫(query rewriting) 執(zhí)行shell命令MySQL Proxy更強(qiáng)大的一項(xiàng)功能是實(shí)現(xiàn)“讀寫分離(Read/Write Splitting)”?;镜脑硎亲屩鲾?shù)據(jù)庫處理事務(wù)性查詢,而從數(shù)據(jù)庫處理SELECT查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫。
二、實(shí)戰(zhàn)過程
測試環(huán)境:Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log
192.168.1.147 proxy 代理 入口
192.168.1.126 master 主機(jī) 只寫
192.168.1.145 slaver 從機(jī) 只讀
程序上只需要鏈接到192.168.1.147,而192.168.1.126和192.168.1.145對(duì)于程序來說是透明的,你完全不需要理會(huì),也不需要知道192.168.1.126和192.168.1.145,你對(duì)數(shù)據(jù)庫的所有操作都只對(duì)192.168.1.147進(jìn)行操作。
1.安裝腳本lua
#apt-get install lua5.1
MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua腳本實(shí)現(xiàn)的,因此需要安裝lua。
2.安裝配置MySQL-Proxy
#apt-get mysql-proxy
當(dāng)前獲取到的版本是:mysql-proxy 0.8.0(查看版本命令:#mysql-proxy -V)
3.修改rw-splitting.lua
#vim /usr/share/mysql-proxy/rw-splitting.lua
配置并使用rw-splitting.lua讀寫分離腳本,腳本目錄是 /usr/share/mysql-proxy,修改讀寫分離腳本rw-splitting.lua,修改默認(rèn)連接數(shù),進(jìn)行快速測試,如果不修改連接數(shù)的話要達(dá)到連接數(shù)為4時(shí)才會(huì)啟用讀寫分離。
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默認(rèn)為4
max_idle_connections = 1, //默認(rèn)為8
is_debug = false
}
end
這是因?yàn)閙ysql-proxy會(huì)檢測客戶端連接,當(dāng)連接沒有超過min_idle_connections預(yù)設(shè)值時(shí), 不會(huì)進(jìn)行讀寫分離, 即查詢操作會(huì)發(fā)生到Master上。
4.新建文件夾/var/log/mysql-proxy/和文件mysql-proxy.log
#mkdir /var/log/mysql-proxy
#vi mysql-proxy.log
5.執(zhí)行讀寫分離
#sudo mysql-proxy --proxy-read-only-backend-addresses=192.168.1.145:3306 --proxy-backend-addresses=192.168.1.126:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua >/var/log/mysql-proxy/mysql-proxy.log &
參數(shù)說明:
192.168.1.147 proxy 代理 入口
192.168.1.126 master 主機(jī) 只寫
192.168.1.145 slaver 從機(jī) 只讀
當(dāng)運(yùn)行sudo mysql-proxy 上面語句后,查詢進(jìn)程沒有4040的時(shí)候,需要重啟mysql ( sudo /etc/init.d/mysql restart) 之后再輸入proxy設(shè)置。
6.查看進(jìn)程端口
#netstat -ant
#netstat –ntl
(Figure2:端口)
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN
7.查看數(shù)據(jù)庫鏈接
mysql> show processlist\G;
(Figure3:進(jìn)程)
可以看到,產(chǎn)生了一個(gè)新連接。如果想殺掉某個(gè)鏈接,可以使用mysql>help kill查看kill的幫助信息,殺掉36進(jìn)程的命令:mysql>kill 36;
8.測試讀寫分離
1)在mysql-proxy機(jī)子進(jìn)入MySQL
#mysql -u gaizai -p -P4040 -h 192.168.1.147
必須指定-h參數(shù),不然報(bào)下面錯(cuò)誤:
(Figure4:出錯(cuò))
2)顯示數(shù)據(jù)庫列表:
mysql> show databases;
如果你是搭建MySQL-Proxy成功的話,你上面查看到的數(shù)據(jù)庫列表應(yīng)該是192.168.1.145服務(wù)器上的數(shù)據(jù)庫列表。(可以在145和126分別創(chuàng)建不同的數(shù)據(jù)庫進(jìn)行測試)
3)進(jìn)入測試數(shù)據(jù)庫:
mysql> use weibo;
4)查詢表記錄:
mysql>select * from blog;
5)插入一條記錄:
mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES('10','fefef','fefef','efef',NOW(),'http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html',NULL,'0','0','33333333',NOW());
6)查詢表記錄:
mysql>select * from blog;
對(duì)比兩次查詢表的記錄,看記錄是否有變化,我們插入了數(shù)據(jù)(確認(rèn)插入成功),但兩次的數(shù)據(jù)是沒有變化的,這就對(duì)了,這就是讀寫分離了(我們讀的是145的數(shù)據(jù)庫,插入的是126的數(shù)據(jù)庫,而我們的145與126又沒有設(shè)置Replication;如果之前設(shè)置了,請(qǐng)先停止后進(jìn)行測試)
注:有時(shí)候mysql_proxy(38)庫里會(huì)顯示出數(shù)據(jù),重啟系統(tǒng)系統(tǒng),重新啟動(dòng)mysql后就沒有此現(xiàn)象了。
7)進(jìn)入主寫服務(wù)器(192.168.1.126) 查看數(shù)據(jù)
#mysql -u gaizai -p -h 192.168.1.126
mysql> use weibo;
mysql>select * from blog;
可以查看已經(jīng)寫入了一條記錄。
8)進(jìn)入從讀服務(wù)器(192.168.1.145)
#mysql -u gaizai -p -h 192.168.1.145
mysql> use weibo;
mysql>select * from blog;
因?yàn)闆]有數(shù)據(jù)顯示,說明只能讀,不能寫。
在使用工具SQLyog執(zhí)行查詢時(shí),在Proxy服務(wù)器上會(huì)自動(dòng)顯示下面的信息:
(Figure5:信息)
9.MySQL-Proxy+Replication
上面的測試只是測試了插入數(shù)據(jù)后,在沒有進(jìn)行Master與Slave的Replication設(shè)置的情況下,讀取Master與Slave的數(shù)據(jù)是不同,如果想達(dá)到Figure1的效果,我們還需要設(shè)置Master與Slave之間的數(shù)據(jù)復(fù)制(Replication),詳情請(qǐng)參考:Ubuntu10下MySQL搭建Master Slave
三、MySQL-Proxy命令
幫助命令:$mysql-proxy --help-all
查看下MySQL Proxy的版本:$ mysql-proxy -V
編譯啟動(dòng)腳本:$vi /etc/init.d/mysql-proxy
啟動(dòng)命令:$ /etc/init.d/mysql-proxy start
停止命令:$ /etc/init.d/mysql-proxy stop
重啟命令:$ /etc/init.d/mysql-proxy restart
四、注意事項(xiàng)
1.在啟動(dòng)mysql-proxy的時(shí)候,可以把啟動(dòng)命令保存為文件:
建議使用配置文件的形式啟動(dòng), 注意配置文件必須是660權(quán)限, 否則無法啟動(dòng). 如果有多個(gè)Slave的話, proxy-read-only-backend-addresses參數(shù)可以配置多個(gè)以逗號(hào)分隔的IP:Port從庫列表。
殺掉mysql-proxy進(jìn)程:# killall mysql-proxy
新建一個(gè)文件:# vi /etc/mysql-proxy.cnf
在文件中輸入兩個(gè)分隔符中間的內(nèi)容:
------------------------------------------------------
[mysql-proxy]
admin-username=viajarchen
admin-password=123123
admin-lua-script = /usr/share/mysql-proxy//admin-sql.lua
proxy-backend-addresses=192.168.1.126:3306
proxy-read-only-backend-addresses=192.168.1.145:3306
proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua
log-file=/var/tmp/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true
max-open-files=1024
------------------------------------------------------
設(shè)置權(quán)限:# chmod 660 /etc/mysql-proxy.cnf
或者#chmod +x /etc/init.d/mysql-proxy
設(shè)置啟動(dòng)文件:# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
查看信息:# ps -ef | grep mysql-proxy | grep -v grep
root 1869 1 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 1870 1869 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
查看日志:# tail -50f /var/tmp/mysql-proxy.log
2.mysql-proxy參數(shù)
--admin-address=host:port 指定一個(gè)mysqo-proxy的管理端口, 缺省是4041;
-P, --proxy-address=<host:port> 是mysql-proxy 服務(wù)器端的監(jiān)聽端口, 缺省是4040;
-r, --proxy-read-only-backend-addresses=<host:port> 只讀Slave的地址和端口, 缺省為不設(shè)置;
-b, --proxy-backend-addresses=<host:port> 遠(yuǎn)程Master地址和端口, 可設(shè)置多個(gè)做failover和load balance, 缺省是127.0.0.1:3306;
--defaults-file=<file>配置文件, 可以把mysql-proxy的參數(shù)信息置入一個(gè)配置文件里;
--daemon mysql-proxy以守護(hù)進(jìn)程方式運(yùn)行
--keepalive try to restart the proxy if it crashed, 保持連接啟動(dòng)進(jìn)程會(huì)有2個(gè), 一號(hào)進(jìn)程用來監(jiān)視二號(hào)進(jìn)程, 如果二號(hào)進(jìn)程死掉自動(dòng)重啟proxy。
–log-level=debug定義log日志級(jí)別,由高到低分別有
(error|warning|info|message|debug)
--proxy-lua-script=file指定一個(gè)Lua腳本程序來控制mysql-proxy的運(yùn)行和設(shè)置,這個(gè)腳本在每次新建連接和腳本發(fā)生修改的的時(shí)候?qū)⒅匦抡{(diào)用。
--max-open-files:指定最大檔案開啟數(shù)為1024,否則會(huì)有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log訊息出現(xiàn)。
3.當(dāng)MySQL主從復(fù)制在 show slave status\G 時(shí)出現(xiàn)Slave_IO_Running或Slave_SQL_Running 的值不為YES時(shí),,需要首先通過 stop slave 來停止從服務(wù)器,然后再進(jìn)行測試讀寫分離。
4.MySQL-Proxy的rw-splitting.lua腳本在網(wǎng)上有很多版本,但是最準(zhǔn)確無誤的版本仍然是源碼包中所附帶的lib/rw-splitting.lua腳本,如果有l(wèi)ua腳本編程基礎(chǔ)的話,可以在這個(gè)腳本的基礎(chǔ)上再進(jìn)行優(yōu)化;
5.MySQL-Proxy實(shí)際上非常不穩(wěn)定,在高并發(fā)或有錯(cuò)誤連接的情況下,進(jìn)程很容易自動(dòng)關(guān)閉,因此打開–keepalive參數(shù)讓進(jìn)程自動(dòng)恢復(fù)是個(gè)比較好的辦法,但還是不能從根本上解決問題,因此通常最穩(wěn)妥的做法是在每個(gè)從服務(wù)器上安裝一個(gè)MySQL-Proxy供自身使用,雖然比較低效但卻能保證穩(wěn)定性;
6.一主多從的架構(gòu)并不是最好的架構(gòu),通常比較優(yōu)的做法是通過程序代碼和中間件等方面,來規(guī)劃,比如單雙server-id號(hào)分開寫入等方式來實(shí)現(xiàn)兩個(gè)或多個(gè)主服務(wù)器;
7.MySQL-Cluster 的穩(wěn)定性也不是太好;
8.Amoeba for MySQL 是一款優(yōu)秀的中間件軟件,同樣可以實(shí)現(xiàn)讀寫分離,負(fù)載均衡等功能,并且穩(wěn)定性要大大超過MySQL-Proxy,建議大家用來替代MySQL-Proxy,甚至MySQL-Cluster。
9.mysql proxy不支持old_password。另外也可以通過查看密碼長度的方式來判斷:select length(password) from mysql.user如果長度為16位則是old_password無疑。
10. 安裝了mysql-proxy實(shí)現(xiàn)讀寫分離,有master x 1, slave x 2。為了測試failover,停掉了一個(gè)slave,然后mysql-proxy會(huì)一直報(bào)錯(cuò),提示無法連接。這個(gè)情況比單點(diǎn)的mysql還糟糕,掛掉一個(gè)就全掛掉!mysql的工程師給提供了一段代碼,替換掉:
src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函數(shù)可以解決這個(gè)問題。network-mysqld-proxy-function.c文件。
(經(jīng)過測試:我停止掉slave數(shù)據(jù)庫,proxy的查詢就會(huì)轉(zhuǎn)移到master上,當(dāng)把slave啟動(dòng)后,proxy依然在讀master,當(dāng)有新的鏈接進(jìn)來的時(shí)候才會(huì)去讀取slave的數(shù)據(jù))
11. 如果在mysql-proxy的機(jī)器上也安裝了mysql的話,新手就會(huì)在這個(gè)時(shí)候混亂了,到底要如何進(jìn)行測試和鏈接呢?比如使用命令:#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陸本機(jī)的4040端口,使用gaizai帳號(hào),這個(gè)帳號(hào)可以不是本地mysql的帳號(hào),這樣就比較容易區(qū)分了。
12. 在上述環(huán)境中,mysql-proxy、mysql-master、mysql-slave三臺(tái)服務(wù)器均存在單點(diǎn)故障。為了避免mysql-proxy單點(diǎn)隱患有兩種方法:一種方法是mysql-proxy配合keepalived做雙機(jī),另一種方法是將mysql-proxy和應(yīng)用服務(wù)安裝到同一臺(tái)服務(wù)器上;為了避免mysql-master單點(diǎn)故障可以使用DRBD+heartbear做雙機(jī);為了避免mysql-slave單點(diǎn)故障可以添加多臺(tái)mysql-slave,mysql-proxy會(huì)自動(dòng)屏蔽后端發(fā)生故障的mysql-slave。
13. 用sysbench (或者super-smack)測試mysql性能:
#sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000 --mysql-socket=/tmp/mysql.sock --mysql-password=123456 --mysql-user=gaizai --mysql-host=192.168.1.126 --mysql-db=weibo --num-threads=15 prepare
#sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000 --mysql-socket=/tmp/mysql.sock --mysql-password=123456 --mysql-user=gaizai --mysql-host=192.168.1.126 --mysql-db=weibo --oltp-test-mode=complex run
14. 關(guān)于mysql-proxy的啟動(dòng)和關(guān)閉的shell腳本的編寫:
15. 讀寫分離不能回避的問題之一就是延遲,可以考慮Google提供的SemiSyncReplicationDesign補(bǔ)丁。
16. MySQL-Proxy缺省使用的是4040端口,如果你想透明的把3306端口的請(qǐng)求轉(zhuǎn)發(fā)給4040的話,那么可以:iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 4040如果想刪除這條規(guī)則,可以把上面例子中的-I換成-D。參考鏈接
17. 當(dāng)使用bigint 時(shí),mysql_insert_id()存在問題,詳情見手冊(cè),不過對(duì)于大多數(shù)人而言,bigint基本不會(huì)遇到,所以你可以無視這個(gè)問題)注:對(duì)于這兩個(gè)問題,官方BUG庫里有人給出了相應(yīng)的補(bǔ)丁。
五、錯(cuò)誤
在執(zhí)行命令的時(shí)候出現(xiàn)了下面的錯(cuò)誤:
(Figure6:錯(cuò)誤信息)
could not raise RLIMIT_NOFILE to 8192
這個(gè)一個(gè)警告級(jí)別的錯(cuò)誤,意思是MySQL Proxy在你的系統(tǒng)上不能把open files limit提升到8192,不過沒關(guān)系的,MySQL Proxy依然好好的運(yùn)行在你的電腦上。
可以通過設(shè)置啟動(dòng)--max-open-files參數(shù)解決。
MySQL Proxy安裝和使用(一)
mysql proxy master and slave test
加入--max-open-files=8192后報(bào)下面的錯(cuò)誤:
(Figure7:錯(cuò)誤信息)
六、疑問與解答
1.當(dāng)slave宕機(jī)后,mysql-proxy是如何讀取的?(經(jīng)過測試:我停止掉slave數(shù)據(jù)庫,proxy的查詢就會(huì)轉(zhuǎn)移到master上,當(dāng)把slave啟動(dòng)后,proxy依然在讀master,當(dāng)有新的鏈接進(jìn)來的時(shí)候才會(huì)重新去讀取slave的數(shù)據(jù)。有時(shí)可能需要重啟下mysql-proxy)
2.如何知道m(xù)ysql-proxy當(dāng)前執(zhí)行的select是在哪臺(tái)機(jī)器上執(zhí)行的?
3.當(dāng)slave宕機(jī)一段時(shí)間后,如果再次同步master的缺失的數(shù)據(jù)?
4.當(dāng)配置中設(shè)置了proxy-read-only-backend-addresses=192.168.1.145:3306
,192.168.1.147:3306類似這樣的兩個(gè)slave,如果兩個(gè)slave的數(shù)據(jù)不同步,那么是怎么讀取數(shù)據(jù)的?# tail -50f /var/tmp/mysql-proxy.log測試
5.生產(chǎn)環(huán)境中除了進(jìn)行程序調(diào)試外,其它不要開啟mysql查詢?nèi)罩?,因?yàn)椴樵內(nèi)罩居涗浟丝蛻舳说乃姓Z句,頻繁的IO操作將會(huì)導(dǎo)致mysql整體性能下降。如何設(shè)置呢?
6.mysql-proxy.cnf文件中的管理員帳號(hào)和密碼有什么用?使用命令進(jìn)入管理
mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密碼是123123
mysql> select * from proxy_connections;
mysql> select * from proxy_config;
(Figure8:信息)
7.關(guān)于mysql-proxy的啟動(dòng)和關(guān)閉的shell腳本的編寫?測試
8.對(duì)于/usr/share/mysql-proxy/rw-splitting.lua腳本中的
local min_idle_connections = 4 local max_idle_connections = 8應(yīng)該如何理解?min的話就是要達(dá)到這個(gè)值的時(shí)候才會(huì)讀寫分離,那么max的是什么意思呢?最大能有8個(gè)鏈接?
9.mysqld是什么意思?是mysql的守護(hù)進(jìn)程!
10.HAProxy和keepalived怎么一起搭建使用?能解決什么問題?
- php實(shí)現(xiàn)帶讀寫分離功能的MySQL類完整實(shí)例
- MySQL5.6 Replication主從復(fù)制(讀寫分離) 配置完整版
- MySQL的使用中實(shí)現(xiàn)讀寫分離的教程
- Yii實(shí)現(xiàn)MySQL多數(shù)據(jù)庫和讀寫分離實(shí)例分析
- Thinkphp實(shí)現(xiàn)MySQL讀寫分離操作示例
- 通過mysql-proxy完成mysql讀寫分離
- 使用PHP實(shí)現(xiàn)Mysql讀寫分離
- MySQL主從同步、讀寫分離配置步驟
- mysql 讀寫分離(實(shí)戰(zhàn)篇)
- mysql 讀寫分離(基礎(chǔ)篇)
- MySQL 讀寫分離實(shí)例詳解
相關(guān)文章
具有負(fù)載均衡功能的MySQL服務(wù)器集群部署及實(shí)現(xiàn)
MySQL是一個(gè)高速度、高性能、多線程的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),適用平臺(tái)多,可擴(kuò)展性強(qiáng)。2011-05-05Mysql錯(cuò)誤Every derived table must have its own alias解決方法
這篇文章主要介紹了Mysql錯(cuò)誤Every derived table must have its own alias解決方法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-08-08MySQL創(chuàng)建和刪除表操作命令實(shí)例講解
這篇文章主要介紹了MySQL創(chuàng)建和刪除表操作命令實(shí)例講解,本文講解了創(chuàng)建表、創(chuàng)建臨時(shí)表、查看已經(jīng)創(chuàng)建的mysql表等內(nèi)容,需要的朋友可以參考下2014-12-12MySQL用B+樹作為索引結(jié)構(gòu)有什么好處
這篇文章主要介紹了MySQL用B+樹作為索引結(jié)構(gòu)有什么好處,幫助大家更好的理解和使用MySQL 索引,感興趣的朋友可以了解下2021-01-01親手教你怎樣創(chuàng)建一個(gè)簡單的mysql數(shù)據(jù)庫
數(shù)據(jù)庫是存放數(shù)據(jù)的“倉庫”,維基百科對(duì)此形象地描述為“電子化文件柜”,這篇文章主要介紹了親手教你怎樣創(chuàng)建一個(gè)簡單的mysql數(shù)據(jù)庫,需要的朋友可以參考下2022-11-11