Ubuntu10下如何搭建MySQL Proxy讀寫分離探討

一、MySQL-Proxy基礎

MySQL Proxy是一個處於你的Client端和MySQL server端之間的簡單程式，它可以監測、分析或改變它們的通訊。它使用靈活，沒有限制，常見的用途包括：Server Load Balancer，故障、查詢分析，查詢過濾和修改等等。

（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 commands

One 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資料庫客戶和服務端之間的程式，它還支援嵌入性指令碼語言Lua。這個代理可以用來分析、監控和變換（transform）通訊資料，它支援非常廣泛的使用情境：

Server Load Balancer和容錯移轉處理 查詢分析和日誌 SQL宏（SQL macros） 查詢重寫（query rewriting） 執行shell命令

MySQL Proxy更強大的一項功能是實現“讀寫分離（Read/Write Splitting）”。基本的原理是讓主要資料庫處理事務性查詢，而從資料庫處理SELECT查詢。資料庫複寫被用來把事務性查詢導致的變更同步到叢集中的從資料庫。

二、實戰過程

測試環境：Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log

192.168.1.147 proxy 代理 入口

192.168.1.126 master 主機 唯寫

192.168.1.145 slaver 從機 唯讀

程式上只需要連結到192.168.1.147，而192.168.1.126和192.168.1.145對於程式來說是透明的，你完全不需要理會，也不需要知道192.168.1.126和192.168.1.145，你對資料庫的所有操作都只對192.168.1.147進行操作。

1.安裝指令碼lua

#apt-get install lua5.1

MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua指令碼實現的，因此需要安裝lua。

2.安裝配置MySQL-Proxy

#apt-get mysql-proxy

當前擷取到的版本是：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，修改預設串連數，進行快速測試，如果不修改串連數的話要達到串連數為4時才會啟用讀寫分離。

-- connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1, //預設為4

max_idle_connections = 1, //預設為8

is_debug = false

}

end

這是因為mysql-proxy會檢測用戶端串連，當串連沒有超過min_idle_connections預設值時, 不會進行讀寫分離, 即查詢操作會發生到Master上。

4.建立檔案夾/var/log/mysql-proxy/和檔案mysql-proxy.log

#mkdir /var/log/mysql-proxy

#vi mysql-proxy.log

5.執行讀寫分離

#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 &

參數說明：

192.168.1.147 proxy 代理 入口

192.168.1.126 master 主機 唯寫

192.168.1.145 slaver 從機 唯讀

當運行sudo mysql-proxy 上面語句後，查詢進程沒有4040的時候，需要重啟mysql ( sudo /etc/init.d/mysql restart) 之後再輸入proxy設定。

6.查看進程連接埠

#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.查看資料庫連結

mysql> show processlist\G;

（Figure3：進程）

可以看到，產生了一個新串連。如果想殺掉某個連結，可以使用mysql>help kill查看kill的協助資訊，殺掉36進程的命令：mysql>kill 36;

8.測試讀寫分離

1)在mysql-proxy機子進入MySQL

#mysql -u gaizai -p -P4040 -h 192.168.1.147

必須指定-h參數，不然報下面錯誤：

（Figure4：出錯）

2)顯示資料庫列表：

mysql> show databases;

如果你是搭建MySQL-Proxy成功的話，你上面查看到的資料庫列表應該是192.168.1.145伺服器上的資料庫列表。（可以在145和126分別建立不同的資料庫進行測試）

3)進入測試資料庫：

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;

對比兩次查詢表的記錄，看記錄是否有變化，我們插入了資料（確認插入成功），但兩次的資料是沒有變化的，這就對了，這就是讀寫分離了（我們讀的是145的資料庫，插入的是126的資料庫，而我們的145與126又沒有設定Replication；如果之前設定了，請先停止後進行測試）

註：有時候mysql_proxy(38)庫裡會顯示出資料，重啟系統系統，重新啟動mysql後就沒有此現象了。

7)進入主寫伺服器(192.168.1.126) 查看資料

#mysql -u gaizai -p -h 192.168.1.126

mysql> use weibo;

mysql>select * from blog;

可以查看已經寫入了一條記錄。

8)進入從讀伺服器(192.168.1.145)

#mysql -u gaizai -p -h 192.168.1.145

mysql> use weibo;

mysql>select * from blog;

因為沒有資料顯示，說明只能讀，不能寫。

在使用工具SQLyog執行查詢時，在Proxy伺服器上會自動顯示下面的資訊：

（Figure5：資訊）

9.MySQL-Proxy+Replication

上面的測試只是測試了插入資料後，在沒有進行Master與Slave的Replication設定的情況下，讀取Master與Slave的資料是不同，如果想達到Figure1的效果，我們還需要設定Master與Slave之間的資料複製（Replication），詳情請參考：Ubuntu10下MySQL搭建Master Slave

三、MySQL-Proxy命令

協助命令：$mysql-proxy --help-all

查看下MySQL Proxy的版本：$ mysql-proxy -V

編譯啟動指令碼：$vi /etc/init.d/mysql-proxy

啟動命令：$ /etc/init.d/mysql-proxy start

停止命令：$ /etc/init.d/mysql-proxy stop

重啟命令：$ /etc/init.d/mysql-proxy restart

四、注意事項

1.在啟動mysql-proxy的時候，可以把啟動命令儲存為檔案：

建議使用設定檔的形式啟動, 注意設定檔必須是660許可權, 否則無法啟動. 如果有多個Slave的話, proxy-read-only-backend-addresses參數可以配置多個以逗號分隔的IP:Port從庫列表。

殺掉mysql-proxy進程：# killall mysql-proxy

建立一個檔案：# vi /etc/mysql-proxy.cnf

在檔案中輸入兩個分隔字元中間的內容：

------------------------------------------------------

[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

------------------------------------------------------

設定許可權：# chmod 660 /etc/mysql-proxy.cnf

或者#chmod +x /etc/init.d/mysql-proxy

設定開機檔案：# 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參數

--admin-address=host:port 指定一個mysqo-proxy的管理連接埠, 預設是4041;

-P, --proxy-address=<host:port> 是mysql-proxy 伺服器端的監聽連接埠, 預設是4040;

-r, --proxy-read-only-backend-addresses=<host:port> 唯讀Slave的地址和連接埠, 預設為不設定;

-b, --proxy-backend-addresses=<host:port> 遠程Master地址和連接埠, 可設定多個做failover和load balance, 預設是127.0.0.1:3306;

--defaults-file=<file>設定檔, 可以把mysql-proxy的參數資訊置入一個設定檔裡;

--daemon mysql-proxy以守護進程方式運行

--keepalive try to restart the proxy if it crashed, 保持串連啟動進程會有2個, 一號進程用來監視二號進程, 如果二號進程死掉自動重啟proxy。

–log-level=debug定義log記錄層級，由高到低分別有

(error|warning|info|message|debug)

--proxy-lua-script=file指定一個Lua指令碼程式來控制mysql-proxy的運行和設定，這個指令碼在每次建立串連和指令碼發生修改的的時候將重新調用。

--max-open-files：指定最大檔案開啟數為1024，否則會有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log訊息出現。

3.當MySQL主從複製在 show slave status\G 時出現Slave_IO_Running或Slave_SQL_Running 的值不為YES時，，需要首先通過 stop slave 來停止從伺服器，然後再進行測試讀寫分離。

4.MySQL-Proxy的rw-splitting.lua指令碼在網上有很多版本，但是最準確無誤的版本仍然是源碼包中所附帶的lib/rw-splitting.lua指令碼，如果有lua指令碼編程基礎的話，可以在這個指令碼的基礎上再進行最佳化；

5.MySQL-Proxy實際上非常不穩定，在高並發或有錯誤串連的情況下，進程很容易自動關閉，因此開啟–keepalive參數讓進程自動回復是個比較好的辦法，但還是不能從根本上解決問題，因此通常最穩妥的做法是在每個從伺服器上安裝一個MySQL-Proxy供自身使用，雖然比較低效但卻能保證穩定性；

6.一主多從的架構並不是最好的架構，通常比較優的做法是通過程式碼和中介軟體等方面，來規劃，比如單雙server-id號分開寫入等方式來實現兩個或多個主伺服器；

7.MySQL-Cluster 的穩定性也不是太好；

8.Amoeba for MySQL 是一款優秀的中介軟體軟體，同樣可以實現讀寫分離，負載平衡等功能，並且穩定性要大大超過MySQL-Proxy，建議大家用來替代MySQL-Proxy，甚至MySQL-Cluster。

9.mysql proxy不支援old_password。另外也可以通過查看密碼長度的方式來判斷:select length(password) from mysql.user如果長度為16位則是old_password無疑。

10. 安裝了mysql-proxy實現讀寫分離，有master x 1, slave x 2。為了測試failover，停掉了一個slave，然後mysql-proxy會一直報錯，提示無法串連。這個情況比單點的mysql還糟糕，掛掉一個就全掛掉！mysql的工程師給提供了一段代碼，替換掉：

src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函數可以解決這個問題。network-mysqld-proxy-function.c檔案。

（經過測試：我停止掉slave資料庫，proxy的查詢就會轉移到master上，當把slave啟動後，proxy依然在讀master，當有新的連結進來的時候才會去讀取slave的資料）

11. 如果在mysql-proxy的機器上也安裝了mysql的話，新手就會在這個時候混亂了，到底要如何進行測試和連結呢？比如使用命令：#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陸原生4040連接埠，使用gaizai帳號，這個帳號可以不是本地mysql的帳號，這樣就比較容易區分了。

12. 在上述環境中，mysql-proxy、mysql-master、mysql-slave三台伺服器均存在單點故障。為了避免mysql-proxy單點隱患有兩種方法：一種方法是mysql-proxy配合keepalived做雙機，另一種方法是將mysql-proxy和應用服務安裝到同一台伺服器上；為了避免mysql-master單點故障可以使用DRBD+heartbear做雙機；為了避免mysql-slave單點故障可以添加多台mysql-slave，mysql-proxy會自動屏蔽後端發生故障的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. 關於mysql-proxy的啟動和關閉的shell指令碼的編寫：

15. 讀寫分離不能迴避的問題之一就是延遲，可以考慮Google提供的SemiSyncReplicationDesign補丁。

16. MySQL-Proxy預設使用的是4040連接埠，如果你想透明的把3306連接埠的請求轉寄給4040的話，那麼可以：iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 4040如果想刪除這條規則，可以把上面例子中的-I換成-D。參考連結

17. 當使用bigint 時，mysql_insert_id()存在問題，詳情見手冊，不過對於大多數人而言，bigint基本不會遇到，所以你可以無視這個問題）註：對於這兩個問題，官方BUG庫裡有人給出了相應的補丁。

五、錯誤

在執行命令的時候出現了下面的錯誤：

（Figure6：錯誤資訊）

could not raise RLIMIT_NOFILE to 8192

這個一個警告層級的錯誤,意思是MySQL Proxy在你的系統上不能把open files limit提升到8192，不過沒關係的，MySQL Proxy依然好好的運行在你的電腦上。

可以通過設定啟動--max-open-files參數解決。

MySQL Proxy安裝和使用(一)

mysql proxy master and slave test

加入--max-open-files=8192後報下面的錯誤：

（Figure7：錯誤資訊）

六、疑問與解答

1.當slave宕機後，mysql-proxy是如何讀取的？（經過測試：我停止掉slave資料庫，proxy的查詢就會轉移到master上，當把slave啟動後，proxy依然在讀master，當有新的連結進來的時候才會重新去讀取slave的資料。有時可能需要重啟下mysql-proxy）

2.如何知道mysql-proxy當前執行的select是在哪台機器上執行的？

3.當slave宕機一段時間後，如果再次同步master的缺失的資料？

4.當配置中設定了proxy-read-only-backend-addresses=192.168.1.145:3306

,192.168.1.147:3306類似這樣的兩個slave，如果兩個slave的資料不同步，那麼是怎麼讀取資料的？# tail -50f /var/tmp/mysql-proxy.log測試

5.生產環境中除了進行程式調試外，其它不要開啟mysql查詢日誌，因為查詢日誌記錄了用戶端的所有語句，頻繁的IO操作將會導致mysql整體效能下降。如何設定呢？

6.mysql-proxy.cnf檔案中的管理員帳號和密碼有什麼用？使用命令進入管理

mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密碼是123123

mysql> select * from proxy_connections;

mysql> select * from proxy_config;

（Figure8：資訊）

7.關於mysql-proxy的啟動和關閉的shell指令碼的編寫？測試

8.對於/usr/share/mysql-proxy/rw-splitting.lua指令碼中的

local min_idle_connections = 4 local max_idle_connections = 8應該如何理解？min的話就是要達到這個值的時候才會讀寫分離，那麼max的是什麼意思呢？最大能有8個連結？

9.mysqld是什麼意思？是mysql的守護進程！

10.HAProxy和keepalived怎麼一起搭建使用？能解決什麼問題？