使用MySQL-Proxy讀寫分離時需要注意什么

這篇文章主要介紹“使用MySQL-Proxy讀寫分離時需要注意什么”，在日常操作中，相信很多人在使用MySQL-Proxy讀寫分離時需要注意什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”使用MySQL-Proxy讀寫分離時需要注意什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

 
在動手操作前最好先安裝好MySQL-Proxy，并配置好MySQL主從服務器。補充：新版MySQL已經(jīng)內建支持
延遲問題
讀寫分離不能回避的問題之一就是延遲，可以考慮Google提供的SemiSyncReplicationDesign補丁。
 
端口問題
 
MySQL-Proxy缺省使用的是4040端口，如果你想透明的把3306端口的請求轉發(fā)給4040的話，那么可以：
 
iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 4040
 
如果想刪除這條規(guī)則，可以把上面例子中的-I換成-D。
 
參考鏈接
 
密碼加密方式
 
MySQL-Proxy不支持老的密碼加密方式，所以如果你使用的是老版本的MySQL，或者啟用了old_passwords選項的話，則可能會出現(xiàn)錯誤：
 
ERROR 2013: Lost connection to MySQL server
 
此時最好的修復方法就是使用新的密碼加密方式，如果你的用戶表是老式的，可能需要先運行MySQL源代碼里scripts目錄下的mysql_fix_privilege_tables腳本升級表結構。有時候客觀情況可能不允許立刻進行升級操作，此時可以為MySQL-Proxy專門建立一個密碼為空的用戶（通過主機限制訪問，或者起一個很復雜的用戶名），因為不管是新的密碼加密方式還是舊的密碼加密方式，空密碼都同樣是一個空字符串，這樣就規(guī)避了密碼加密的問題。
  www.2cto.com  
查詢亂碼
 
連接上MySQL-Proxy后，執(zhí)行查詢時，隨機出現(xiàn)亂碼。出現(xiàn)此問題的原因是當我們使用MySQL-Proxy讀寫分離時，通常會有多個后端服務器，客戶端發(fā)出查詢請求時，一般會先發(fā)出一條類似"SET NAME gbk"的語句來聲明客戶端編碼，然后再發(fā)出實際查詢的SQL語句，但MySQL-Proxy可能會把這兩條語句分發(fā)給不同的后端服務器，于是就出現(xiàn)了亂碼。
 
解決方法是強行指定后端服務器的字符編碼：
 
init-connect='SET NAME gbk'
 
default-character-set=gbk
skip-character-set-client-handshake
 
如果使用init-connect，則需要注意操作用戶不能有SUPER權限，否則此選項無效。
 
即便做好了以上的設置后，還有可能會出現(xiàn)亂碼，比如說數(shù)據(jù)庫是gbk的，當我們用PHPMyAdmin連接MySQL-Proxy時，查詢還是會出現(xiàn)亂碼，不過這是正常的！因為PHPMyAdmin使用的是utf8編碼，它發(fā)出的“SET NAMES utf8”語句被skip-character-set-client-handshake屏蔽了，所以出現(xiàn)亂碼。
 
進程崩潰
  www.2cto.com  
MySQL-Proxy偶爾會出現(xiàn)進程崩潰的情況，具體原因不明。
 
新版的MySQL-Proxy為了應付這個問題加入了一個keepalive選項（try to restart the proxy if it crashed），當使用這個選項時，會先后啟動兩個mysql-proxy進程，先啟動的mysql-proxy進程用來監(jiān)控后啟動的mysql-proxy進程，實際提供服務的是后啟動的mysql-proxy進程，一旦后啟動的mysql-proxy進程掛掉（你可以自己kill試試），先啟動的mysql-proxy進程會重新啟動一個mysql-proxy提供服務。
 
不過現(xiàn)在很多人用的還是舊版的MySQL-Proxy，此時可以利用init來實現(xiàn)類似keepalive的效果：
 
編寫腳本/usr/local/sbin/mysql-proxy.sh，加入以下內容（具體寫法視安裝情況而定）：
 
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--proxy-backend-addresses=192.168.0.1:3306 \
--proxy-read-only-backend-addresses=192.168.0.2:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua
 
別忘了加上可執(zhí)行屬性：
 
chmod a+x /usr/local/sbin/mysql-proxy.sh
 
0.7.0版本有一個新的選項：--defaults-file，可以把相關信息都寫到配置文件里：
 
# MySQL Proxy's configuration file (mysql-proxy.cnf)
 
[mysql-proxy]
daemon = true
keepalive = true
proxy-backend-addresses = 192.168.0.1:3306
proxy-read-only-backend-addresses = 192.168.0.2:3306
proxy-lua-script = /usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua
 
啟動時可以使用：mysql-proxy --defaults-file=mysql-proxy.cnf
 
修改inittab：
  www.2cto.com  
vi /etc/inittab
 
加入以下內容：
 
mp:12345:respawn:/usr/local/sbin/mysql-proxy.sh
 
然后讓init重新讀取inittab內容：
 
kill -HUP 1
 
系統(tǒng)會自動檢測/usr/local/sbin/mysql-proxy.sh是否正在運行，如果沒有就自動運行。
 
需要注意的是在編寫mysql-proxy.sh腳本的時候，不要加入--daemon選項，否則/usr/local/sbin/mysql-proxy.sh一運行就結束了，系統(tǒng)會不停的嘗試運行腳本，從而在/var/log/message里留下大量的錯誤信息（init: Id "mp" respawning too fast: disabled for 5 minutes）。
 
init的方法可能顯得有點另類了，可以使用其他的工具，比如svscan。
 
有狀態(tài)的查詢
 
一些有狀態(tài)的特殊的查詢可能失效，比如說：
 
SELECT SQL_CALC_FOUND_ROWS ..
SELECT FOUND_ROWS()
 
這種查詢是有狀態(tài)的，應該保證在同一個后端處理，查看rw-splitting.lua腳本可以看到MySQL-Proxy實際上已經(jīng)對這樣的查詢進行了 判斷，但在實際應用中發(fā)現(xiàn)還是存在問題。估計是腳本寫得不咋地，實際應用中，建議大家不要使用這樣的查詢，一來沒有可移植性，而來效率也不見得好。
 
另一個可能會產生問題的查詢是：
  www.2cto.com  
INSERT ... (AUTO_INCREMENT)
SELECT LAST_INSERT_ID()
 
當系統(tǒng)執(zhí)行完INSERT后，再執(zhí)行SELECT時，可能已經(jīng)被分發(fā)到了不同的后端服務器，如果你使用的編程語言是PHP的話，此時應該通過 mysql_insert_id()來得到最新插入的id，每次INSERT結束后，其實對應的autoincrement值就已經(jīng)計算好返回給PHP 了，你無需再發(fā)出一次獨立的查詢，直接用mysql_insert_id()就可以了。不過很多PHP程序使用的都是SELECT LAST_INSERT_ID()的方式，如AdbDB，CakePHP等等，如果你正在使用它們的話需多加小心。（當使用bigint 時，mysql_insert_id()存在問題，詳情見手冊，不過對于大多數(shù)人而言，bigint基本不會遇到，所以你可以無視這個問題）
 
注：對于這兩個問題，官方BUG庫里有人給出了相應的補丁。
 
腳本問題
 
MySQL-Proxy讀寫分離的功能是通過lua腳本（rw-splitting.lua）實現(xiàn)的，但是這個腳本年久失修，問題多多，比如說使用時可能會出現(xiàn)：
 
ERROR 1105: can't change DB to on slave
 
出現(xiàn)這個問題的原因在于當客戶端發(fā)出查詢時，MySQL-Proxy會比較當前客戶端所處數(shù)據(jù)庫和服務器所處數(shù)據(jù)庫是否一致，如果不一致則會在服務端嘗試執(zhí)行一個"USE 數(shù)據(jù)庫"的操作，一個可能性是主從服務器的數(shù)據(jù)庫結構不同，在USE一個不存在的數(shù)據(jù)庫的時候自然會出錯，還有一個原因有些查詢操作并沒有所處數(shù)據(jù)庫這個上下文，比如說SHOW DATABASES這個查詢，并不需要事先“USE 數(shù)據(jù)庫”，只要連上服務器就可以執(zhí)行，這時候如果還嘗試同步客戶端和服務端所處的數(shù)據(jù)庫，出錯就是無法避免的事了。
 
rw-splitting.lua恰恰沒有屏蔽后者所描述的情況，修復方法如下，在合適的位置加入粗體代碼，
 
276         if cmd.type ~= proxy.COM_INIT_DB and
277            c.default_db and c.default_db ~= "" and c.default_db ~= s.default_db then
if is_debug
278                    print("    server default db: " .. s.default_db)
279                    print("    client default db: " .. c.default_db)
280                    print("    syncronizing")
end
281                 proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db)  www.2cto.com  
282         end
 
在lua中，~=是不等于的意思，另外，lua里空字符串""用在if里被認為是true，所以單靠c.default_db不夠。
 
順手加上is_debug的判斷，不然即使不是debug狀態(tài)，服務器的命令行里也會偶爾冒出一些調試信息。
 
此外，新版MySQL-Proxy里，雖然源代碼包里有rw-splitting.lua腳本，但缺省并沒有安裝，你需要手動拷貝，而且數(shù)據(jù)結構發(fā)生了變化，腳本需要按照數(shù)據(jù)結構的變化做出適當?shù)男薷模梢詤⒖甲髡叩拿枋霾僮?，或者參考官方Bug管理或者直接。值得期待的是現(xiàn)在有了專門的MySQL-Proxy-Lua-Scripts項目，希望開發(fā)進度能跟上來。

到此，關于“使用MySQL-Proxy讀寫分離時需要注意什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>