mysql proxy讀寫分流(二)-加入RW splitting

文章来源：%E8%AE%80%E5%AF%AB%E5%88%86%E6%B5%81-%E8%B2%A0%E8%BC%89/

上一篇中提到 後，接下來就是RW splitting(讀寫分流)的部份了

整體的概念圖跟上一篇相同，再補上一個對應port的圖表：

針對上圖的架構，首先需要了解幾點基本的觀念：

• 所有寫入(ADD、UPDATE、DELETE)的動作都是針對MySQL Master
• 所有讀取(SELECT)的動作都是大部分都是在MySQL Slave(部份在MySQL Master，因為涉及到是否同步完成的關係，請見下面 如何解決同步延遲問題 的說明)
• MySQL Master與MySQL Slave已經建立起 同步(Replication)的機制
• 也一個MySQL 的客戶端(Client)，也是一個MySQL的伺服器端(Server)

如果你對於上面的觀念都已經清楚後，對於上圖的流程我想應該很容易了解。就流程架構做個簡單說明：

1. 以往 所有應用程式(PHP)存取MySQL都是直接對應至MySQL Master，有了 MySQL Proxy當代理後，現在所有應用程式的連線都是連接至MySQL Proxy
2. 當MySQL Proxy接收到應用程式的SQL語法後，會自動判斷SQL語法是讀取還是寫入，再分別將寫入的SQL語法對應至MySQL Master、將讀取的SQL語法對應至MySQL Slave
3. 寫入MySQL Master的資料會透過Replication機制(binlog)同步至所有的MySQL Slave

看到這裡好像一切都很順利也沒有任何問題，如果你有意識到【當應用程式透過MySQL Proxy寫入資料到MySQL Master後， 此時應用程式又立即讀取剛寫入的資料；但是，MySQL Master還沒有同步至MySQL Slave，那麼應用程式讀取的資料不就是舊的資料嗎？】沒錯，當你有這樣的疑問時，表示你已經深入了解這個架構了，底下先針對MySQl Proxy如何解決同步延遲問題做說明

MySQL Proxy如何解決同步延遲問題

解決方式是在MySQL Master上新增一個自增表(count table)，當MySQL Master接收到更新資料的動作時便會觸發這個觸發器，這個觸發器會更新自增表(count table)中的記錄，如下圖所示：

(圖片來源：)

因為自增表(count table)也會一起同步(replication)至MySQL Slave，當應用程式透過MySQL Proxy讀取資料時，MySQL Proxy會先向MySQL Master和MySQL Slave的自增表(count table)發送查詢請求，當MySQL Master和MySQL Slave的資料相同時，MySQL Proxy就可以認定MySQL Master和MySQL Slave的資料是一致的，接著把應用程式的請求對應至MySQL Slave主機上，否則就將請求發送至MySQL Master上，如下圖所示：

(圖片來源：)

透過自增表(count table)的方式就可以解決同步延遲的問題，ok~了解整個架構及克服同步延遲的問題後，再來就是實做的部份啦，底下將說明如何啟動MySQL Proxy及設定相關參數

本章應用一個範例架構來說明RW splitting的應用，架構中架設了一台MySQL Porxy、一台MySQL Master以及二台MySQL Slave，詳細訊息如下：

MySQL Proxy：192.168.10.250
MySQL Master：192.168.10.2
MySQL Slave1：192.168.10.3
MySQL Slave2：192.168.10.4

MySQL Proxy開啟後，預設會有2個port(4040及4041)，port 4040為接受應用程式的port，port 4041為MySQL Proxy本身的管理port，詳細help的說明可以執行【/usr/local/mysql-proxy/bin/mysql-proxy –help-all】查看

如何啟動MySQL Proxy

$ /usr/local/mysql-proxy/bin/mysql-proxy \
> –daemon \
> –keepalive \
> –admin-username=root \
> –admin-password=123456 \
> –proxy-backend-addresses=192.168.10.2:3306 \
> –proxy-read-only-backend=192.168.10.3:3306 \
> –proxy-read-only-backend=192.168.10.4:3306 \
> –log-file=/var/log/mysql-proxy.log \
> –log-level=debug \
> –max-open-files=1024 \
> –proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua

要啟動MySQL Proxy就加了那麼多參數，代表的又是什麼意思呢？

• –daemon：指定mysql-proxy為一個daemon(@@)
• –keepalive：丫忠在測試時會有mysql-proxy自動停止服務的情況，加上此參數後就解決這個問題
• –admin-username：指定MySQL Proxy管理者端(port:4041)的登入帳號
• –admin-password：指定MySQL Proxy管理者端(port:4041)的登入密碼
• –proxy-backend-addresses：指定MySQL Master主機的位置及埠號(port)
• –proxy-read-only-backend：指定MySQL Slave主機的位置及埠號(port)，有多台Slave時， 一個 –proxy–read-only-backend 表示一台Slave
• –log-file：指定儲存MySQL Proxy log的檔案位置
• –log-level：指定要記錄log的等級
• –max-open-files：指定最大檔案開啟數為1024，否則為有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log訊息出現
• –proxy-lua-script：指定MySQL Proxy要套用那一個script

上面的啟動方式，你可以用手動方式輸入啟動；另外，官網也提供了一個init script的shell下載，詳細下載請至

再來，啟動MySQL Proxy的參數中有一個 –proxy-lua-script用來指定lua的檔案位置，這個 rw-splitting.lua檔案是 MySQL Proxy重點中的重點，至於要怎麼產生此 rw-splitting.lua呢？rw-splitting.lua檔案在MySQL Proxy原始安裝檔中就已經包含在裡面了，詳細rw-splitting.lua檔案位置在【mysql-proxy-0.8.0/lib/rw- splitting.lua】，請將此檔案拷貝至–proxy-lua-script找的到的位置

rw-splitting.lua目前尚未有一個穩定的版本，至目前0.8.0為止還在持續修正中，詳細的 Bug或修正內容，有興趣的可以至作者的網站查看( )，另外也有一個 以及針對

完成MySQL Proxy的啟動後，再來是測試是否MySQL Proxy可以真的達到讀寫分流的功能了，在測試前丫忠建議將 rw-splitting.lua的Debug功能打開，這樣才能清楚了解到 MySQL Proxy的流程及讀寫流程，請直接修改 rw-splitting.lua，將 is_debug = false 改成 is_debug = true，debug的log 會在應用程式執行時顯示出來，詳細修改後，如下圖所示：

MySQL Proxy測試寫入動作是否同步

這裡可以寫一個簡單的PHP程式，針對MySQL Proxy做寫入的動作，再至MySQL Master、MySQL Slave1及MySQL Slave2是否有同步更新即可驗證

$link = mysql_connect(‘192.168.10.250:4040′, ‘root’, ‘root_password’);
mysql_select_db(‘mytest’);
$sql=’UPDATE t1 SET t_name=’12345′ WHERE id=1;
$result = mysql_query( $sql);

MySQL Proxy測試讀取動作

丫忠剛開始的測試方式為直接在command下指令 做SELECT的動作；但是，這樣的方式是測試不出來的，錯誤的測試方式如下：

$ mysql -u root -p -P 4040 -h 192.168.10.250
mysql>SELECT * FROM t1;

丫忠再次強調，這樣的方式無法明顯測試出是否有從MySQL Slave讀取資料；而且，debug的記錄不會顯示出來

丫忠建議寫個簡單的PHP程式，跑個小迴圈，在debug log中就會顯示出 SELECT的資料是從那一台機器提供

$link = mysql_connect(‘192.168.10.250:4040′, ‘root’, ‘root_password’);
mysql_select_db(‘mytest’);
for($i=0;$i<20;$i++){
$sql=’SELECT * FROM t1′;
$result = mysql_query( $sql);
}

詳細 debug log的訊息就要請網友查看

另外，rw-splitting.lua 跟讀取較直接相關的參數有2個(min_idle_connections 及 max_idle_connections)，可以直接修改這2個參數的值，看看有什麼變化，此部份及詳細分流的部份就要查看 rw-splittinglua 這隻lua程式了

MySQL Proxy 讀取出來的資料是亂碼

丫忠在測試過程式有遇到此問題，最後是修改 /etc/my.cnf 設定檔中，加入底下設定，即可解決亂碼問題(當然您要依據您的環境做修改)

default-character-set = utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8