MySQL的讀寫分離的幾種選擇

MySQL主從複制（Master-Slave）與讀寫分離（MySQL-Proxy）實踐

原址如下：

http://heylinux.com/archives/1004.html

Mysql作為目前世界上使用最廣泛的免費資料庫，相信所有從事系統運維的工程師都一定接觸過。但在實際的生産環境中，由單台Mysql作為獨立的資料庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高并發等各個方面。

是以，一般來說都是通過 主從複制（Master-Slave）的方式來同步資料，再通過讀寫分離（MySQL-Proxy）來提升資料庫的并發負載能力 這樣的方案來進行部署與實施的。

如下圖所示：

下面是我在實際工作過程中所整理的筆記，在此分享出來，以供大家參考。

一、MySQL的安裝與配置

具體的安裝過程，建議參考我的這一篇文章：http://heylinux.com/archives/993.html

值得一提的是，我的安裝過程都是源碼包編譯安裝的，并且所有的配置與資料等都統一規劃到了/opt/mysql目錄中，是以在一台伺服器上安裝完成以後，可以将整個mysql目錄打包，然後傳到其它伺服器上解包，便可立即使用。

二、MySQL主從複制

場景描述：

主資料庫伺服器：192.168.10.130，MySQL已經安裝，并且無應用資料。

從資料庫伺服器：192.168.10.131，MySQL已經安裝，并且無應用資料。

2.1 主伺服器上進行的操作

啟動mysql服務

/opt/mysql/init.d/mysql start

通過指令行登入管理MySQL伺服器

/opt/mysql/bin/mysql -uroot -p'new-password'

授權給從資料庫伺服器192.168.10.131

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.10.131' identified by ‘password’;

查詢主資料庫狀态

Mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.000005 | 261 | | |

記錄下 FILE 及 Position 的值，在後面進行從伺服器操作的時候需要用到。

2.2 配置從伺服器

修改從伺服器的配置檔案/opt/mysql/etc/my.cnf

将 server-id = 1修改為 server-id = 10，并確定這個ID沒有被别的MySQL服務所使用。

執行同步SQL語句

mysql> change master to

master_host=’192.168.10.130’,

master_user=’rep1’,

master_password=’password’,

master_log_file=’mysql-bin.000005’,

master_log_pos=261;

正确執行後啟動Slave同步程序

mysql> start slave;

主從同步檢查

mysql> show slave status\G

==============================================

**************** 1. row *******************

Slave_IO_State:

Master_Host: 192.168.10.130

Master_User: rep1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 415

Relay_Log_File: localhost-relay-bin.000008

Relay_Log_Pos: 561

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: YES

Slave_SQL_Running: YES

Replicate_Do_DB:

……………省略若幹……………

Master_Server_Id: 1

1 row in set (0.01 sec)

其中Slave_IO_Running 與 Slave_SQL_Running 的值都必須為YES，才表明狀态正常。

如果主伺服器已經存在應用資料，則在進行主從複制時，需要做以下處理：

(1)主資料庫進行鎖表操作，不讓資料再進行寫入動作

mysql> FLUSH TABLES WITH READ LOCK;

(2)檢視主資料庫狀态

mysql> show master status;

(3)記錄下 FILE 及 Position 的值。

将主伺服器的資料檔案（整個/opt/mysql/data目錄）複制到從伺服器，建議通過tar歸檔壓縮後再傳到從伺服器解壓。

(4)取消主資料庫鎖定

mysql> UNLOCK TABLES;

2.3 驗證主從複制效果

主伺服器上的操作

在主伺服器上建立資料庫first_db

mysql> create database first_db;

Query Ok, 1 row affected (0.01 sec)

在主伺服器上建立表first_tb

mysql> create table first_tb(id int(3),name char(10));

Query Ok, 1 row affected (0.00 sec)

在主伺服器上的表first_tb中插入記錄

mysql> insert into first_tb values (001,’myself’);

在從伺服器上檢視

mysql> show databases;

=============================

+--------------------+

| Database |

| information_schema |

| first_db |

| mysql |

| performance_schema |

| test |

5 rows in set (0.01 sec)

資料庫first_db已經自動生成

mysql> use first_db

Database chaged

mysql> show tables;

| Tables_in_first_db |

| first_tb |

1 row in set (0.02 sec)

資料庫表first_tb也已經自動建立

mysql> select * from first_tb;

+------+------+

| id | name |

| 1 | myself |

1 rows in set (0.00 sec)

記錄也已經存在

由此，整個MySQL主從複制的過程就完成了，接下來，我們進行MySQL讀寫分離的安裝與配置。

三、MySQL讀寫分離

資料庫Master主伺服器：192.168.10.130

資料庫Slave從伺服器：192.168.10.131

MySQL-Proxy排程伺服器：192.168.10.132

以下操作，均是在192.168.10.132即MySQL-Proxy排程伺服器 上進行的。

3.1 MySQL的安裝與配置

具體的安裝過程與上文相同。

3.2 檢查系統所需軟體包

通過 rpm -qa | grep name 的方式驗證以下軟體包是否已全部安裝。

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*

libevent* glib*

若缺少相關的軟體包，可通過yum -y install方式線上安裝，或直接從系統安裝CD光牒中找到并通過rpm -ivh方式安裝。

3.3 編譯安裝lua

MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua腳本實作的，是以需要安裝lua。

lua可通過以下方式獲得

從http://www.lua.org/download.html下載下傳源碼包

從rpm.pbone.net搜尋相關的rpm包

download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm

download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

這裡我們建議采用源碼包進行安裝

cd /opt/install

wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

tar zvfx lua-5.1.4.tar.gz

cd lua-5.1.4

vi src/Makefile

在 CFLAGS= -O2 -Wall $(MYCFLAGS) 這一行記錄裡加上-fPIC，更改為 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 來避免編譯過程中出現錯誤。

make linux

make install

cp etc/lua.pc /usr/lib/pkgconfig/

export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

3.4 安裝配置MySQL-Proxy

MySQL-Proxy可通過以下網址獲得：

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

推薦采用已經編譯好的二進制版本，因為采用源碼包進行編譯時，最新版的MySQL-Proxy對automake，glib以及libevent的版本都有很高的要求，而這些軟體包都是系統的基礎套件，不建議強行進行更新。

并且這些已經編譯好的二進制版本在解壓後都在統一的目錄内，是以建議選擇以下版本：

32位RHEL5平台：

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

64位RHEL5平台：

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz

測試平台為RHEL5 32位，是以選擇32位的軟體包

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy

建立mysql-proxy服務管理腳本

mkdir /opt/mysql-proxy/init.d/

vim mysql-proxy

01

#!/bin/sh

02

#

03	# mysql-proxy This script starts and stops the mysql-proxy daemon

04

#

05	# chkconfig: - 78 30

06	# processname: mysql-proxy

07	# description: mysql-proxy is a proxy daemon to mysql

08

09	# Source function library.

10	. /etc/rc.d/init.d/functions

11

12	#PROXY_PATH=/usr/local/bin

13	PROXY_PATH=/opt/mysql-proxy/bin

14

15	prog= "mysql-proxy"

16

17	# Source networking configuration.

18	. /etc/sysconfig/network

19

20	# Check that networking is up.

21	[ ${NETWORKING} =  "no" ] &&  exit

22

23	# Set default mysql-proxy configuration.

24	#PROXY_OPTIONS="--daemon"

25	PROXY_OPTIONS= "--admin-username=root --admin-password=password --proxy-read-only-backend-addresses=192.168.10.131:3306 --proxy-backend-addresses=192.168.10.130:3306  --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"

26	PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid

27

28	# Source mysql-proxy configuration.

29	if [ -f /etc/sysconfig/mysql-proxy ];  then

30	. /etc/sysconfig/mysql-proxy

31

fi

32

33	PATH=$PATH:/usr/bin:/usr/ local /bin:$PROXY_PATH

34

35	# By default it's all good

36

RETVAL=0

37

38	# See how we were called.

39	case "$1" in

40

start)

41	# Start daemon.

42	echo -n $ "Starting $prog: "

43	$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid- file =$PROXY_PID --user=mysql --log-level=warning --log- file =/opt/mysql-proxy/log/mysql-proxy.log

44

RETVAL=$?

45

echo

46	if [ $RETVAL = 0 ];  then

47	touch /var/lock/subsys/mysql-proxy

48

fi

49

;;

50

stop)

51	# Stop daemons.

52	echo -n $ "Stopping $prog: "

53	killproc $prog

54

RETVAL=$?

55

echo

56	if [ $RETVAL = 0 ];  then

57	rm -f /var/lock/subsys/mysql-proxy

58	rm -f $PROXY_PID

59

fi

60

;;

61

restart)

62

$0 stop

63

sleep 3

64

$0 start

65

;;

66	condrestart)

67	[ -e /var/lock/subsys/mysql-proxy ] && $0 restart

68

;;

69

status)

70	status mysql-proxy

71

RETVAL=$?

72

;;

73

*)

74	echo "Usage: $0 {start|stop|restart|status|condrestart}"

75

RETVAL=1

76

;;

77

esac

78

79	exit $RETVAL

腳本參數詳解：

PROXY_PATH=/opt/mysql-proxy/bin //定義mysql-proxy服務二進制檔案路徑

PROXY_OPTIONS="--admin-username=root \ //定義内部管理伺服器賬号

--admin-password=password \ //定義内部管理伺服器密碼

--proxy-read-only-backend-addresses=192.168.10.131:3306 \ //定義後端隻讀從伺服器位址

--proxy-backend-addresses=192.168.10.130:3306 \ //定義後端主伺服器位址

--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \ //定義lua管理腳本路徑

--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua" \ //定義lua讀寫分離腳本路徑

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定義mysql-proxy PID檔案路徑

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \

--daemon \ //定義以守護程序模式啟動

--keepalive \ //使程序在異常關閉後能夠自動恢複

--pid-file=$PROXY_PID \ //定義mysql-proxy PID檔案路徑

--user=mysql \ //以mysql使用者身份啟動服務

--log-level=warning \ //定義log日志級别，由高到低分别有(error|warning|info|message|debug)

--log-file=/opt/mysql-proxy/log/mysql-proxy.log //定義log日志檔案路徑

cp mysql-proxy /opt/mysql-proxy/init.d/

chmod +x /opt/mysql-proxy/init.d/mysql-proxy

mkdir /opt/mysql-proxy/run

mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

配置并使用rw-splitting.lua讀寫分離腳本

最新的腳本我們可以從最新的mysql-proxy源碼包中擷取

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz

tar xzvf mysql-proxy-0.8.1.tar.gz

cd mysql-proxy-0.8.1

cp lib/rw-splitting.lua /opt/mysql-proxy/scripts

修改讀寫分離腳本rw-splitting.lua

修改預設連接配接，進行快速測試，不修改的話要達到連接配接數為4時才啟用讀寫分離

vim /opt/mysql-proxy/scripts/rw-splitting.lua

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

/opt/mysql-proxy/init.d/mysql-proxy start

3.5 測試讀寫分離效果

建立用于讀寫分離的資料庫連接配接使用者

登陸主資料庫伺服器192.168.10.130，通過指令行登入管理MySQL伺服器

mysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.10.132' IDENTIFIED BY 'password';

由于我們配置了主從複制功能，是以從資料庫伺服器192.168.10.131上已經同步了此操作。

為了清晰的看到讀寫分離的效果，需要暫時關閉MySQL主從複制功能

登陸從資料庫伺服器192.168.10.131，通過指令行登入管理MySQL伺服器

關閉Slave同步程序

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

連接配接MySQL-Proxy

/opt/mysql/bin/mysql -uproxy1 -p'password' -P4040 -h192.168.10.132

登陸成功後，在first_db資料的first_tb表中插入兩條記錄

mysql> use first_db;

Database changed

mysql> insert into first_tb values (007,’first’);

mysql> insert into first_tb values (110,’second’);

查詢記錄

通過讀操作并沒有看到新記錄

mysql> quit

退出MySQL-Proxy

下面，分别登陸到主從資料庫伺服器，對比記錄資訊

首先，檢查主資料庫伺服器

| 007 | first |

| 110 | second |

3 rows in set (0.00 sec)

兩條新記錄都已經存在

然後，檢查從資料庫伺服器

沒有新記錄存在

由此驗證，我們已經實作了MySQL讀寫分離，目前所有的寫操作都全部在Master主伺服器上，用來避免資料的不同步；

另外，所有的讀操作都分攤給了其它各個Slave從伺服器上，用來分擔資料庫壓力。

經驗分享：

1.當MySQL主從複制在 show slave status\G 時出現Slave_IO_Running或Slave_SQL_Running 的值不為YES時，需要首先通過 stop slave 來停止從伺服器，然後再執行一次本文 2.1與2.2 章節中的步驟即可恢複，但如果想盡可能的同步更多的資料，可以在Slave上将master_log_pos節點的值在之前同步失效的值的基礎上增大一些，然後反複測試，直到同步OK。因為MySQL主從複制的原理其實就是從伺服器讀取主伺服器的binlog，然後根據binlog的記錄來更新資料庫。

2.MySQL-Proxy的rw-splitting.lua腳本在網上有很多版本，但是最準确無誤的版本仍然是源碼包中所附帶的lib/rw-splitting.lua腳本，如果有lua腳本程式設計基礎的話，可以在這個腳本的基礎上再進行優化；

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

4.一主多從的架構并不是最好的架構，通常比較優的做法是通過程式代碼和中間件等方面，來規劃，比如設定對表資料的自增id值差異增長等方式來實作兩個或多個主伺服器，但一定要注意保證好這些主伺服器資料的完整性，否則效果會比多個一主多從的架構還要差；

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

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

詳解Mysql Proxy Lua讀寫分離設定

http://mobile.51cto.com/iphone-287937.htm

Mysql Proxy Lua讀寫分離設定是本文要介紹的内容，主要是來了解Mysql Proxy的Mysql 分離設定，為了未來MySQL讀寫分離的需要, 先行對MySQL官方的Mysql Proxy産品進行了初步測試. 以下是測試過程，二進制版Mysql Proxy可以去下載下傳。

1、設定說明

1. Master伺服器: 192.168.41.196  
2. Slave伺服器: 192.168.41.197  
3. Proxy伺服器: 192.168.41.203 

2、安裝Mysql Proxy

在Proxy伺服器上安裝即可. 如果源碼方式安裝, 需提前安裝pkg-config,libevent,glibc,lua等依賴包, 非常麻煩, 建議直接使用二進制版.

1. # cd /u01/software/mysql  
2. # tar -zxvf Mysql Proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz -C /usr/local  
3. # cd /usr/local  
4. # ln -s Mysql Proxy-0.8.1-linux-rhel5-x86-32bit Mysql Proxy  
5. # vi + ~/.bash_profile  
6. export PATH=$PATH:/usr/local/Mysql Proxy/bin/  
7. # . ~/.bash_profile 

3、Mysql Proxy選項說明

1. # Mysql Proxy help-all 

管理功能選項:

1. admin-address=host:port 指定一個mysqo-proxy的管理端口, 預設是4041;  
2. admin-username=<string> username to allow to log in  
3. admin-password=<string> password to allow to log in  
4. admin-lua-script=<filename> script to execute by the admin plugin 

代理功能選項:

1. -P, proxy-address=<host:port> 是Mysql Proxy 伺服器端的監聽端口, 預設是4040;  
2. -r, proxy-read-only-backend-addresses=<host:port> 隻讀Slave的位址和端口, 預設為不設定;  
3. -b, proxy-backend-addresses=<host:port> 遠端Master位址和端口, 可設定多個做failover和load balance, 預設是127.0.0.1:3306;  
4. proxy-skip-profiling 關閉查詢分析功能, 預設是打開的;  
5. proxy-fix-bug-25371 修正 mysql的libmysql版本大于5.1.12的一個#25371号bug;  
6. -s, proxy-lua-script=<file> 指定一個Lua腳本來控制Mysql Proxy的運作和設定, 這個腳本在每次建立連接配接和腳本發生修改的的時候将重新調用; 

其他選項:

1. defaults-file=<file>配置檔案, 可以把Mysql Proxy的參數資訊置入一個配置檔案裡;  
2. daemon Mysql Proxy以守護程序方式運作  
3. pid-file=file 設定Mysql Proxy的存儲PID檔案的路徑  
4. keepalive try to restart the proxy if it crashed, 保持連接配接啟動程序會有2個, 一号程序用來監視二号程序, 如果二号程序死掉自動重新開機proxy. 

4、資料庫準備工作

（1）安裝半同步更新檔(建議)

讀寫分離不能回避的問題之一就是延遲, 可以考慮Google提供的SemiSyncReplication更新檔.

（2）給使用者授權

在Master/Slave建立一個測試使用者, 因為以後用戶端發送的SQL都是通過Mysql Proxy伺服器來轉發, 是以要確定可以從Mysql Proxy伺服器上登入MySQL主從庫.

1. mysql> grant all privileges on *.* to 'u_test'@'192.168.41.203' identified by 'xxx' with grant option; 

（3）在Master建立測試表

1. mysql> create table db_test.t_test (col varchar(10));  
2. mysql> insert into db_test.t_test values ('testA');  
3. mysql> select * from db_test.t_test;  
4. +-+  
5. | col   |  
7. | testA |  
8. +-+ 

5、Mysql Proxy啟動

（1）修改讀寫分離lua腳本

預設最小4個最大8個以上的用戶端連接配接才會實作讀寫分離, 現改為最小1個最大2個:

1. # vi +40 /usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  
2.  connection pool  
3. if not proxy.global.config.rwsplit then  
4.         proxy.global.config.rwsplit = {  
5.                 min_idle_connections = 1,  
6.                 max_idle_connections = 2,  
7.                 is_debug = true   
8.         }         
9. end 

這是因為Mysql Proxy會檢測用戶端連接配接, 當連接配接沒有超過min_idle_connections預設值時, 不會進行讀寫分離, 即查詢操作會發生到Master上.

（2）啟動Mysql Proxy

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

1. # killall Mysql Proxy   
2. # vi /etc/Mysql Proxy.cnf  
3. [Mysql Proxy]  
4. admin-username=wangnc 
5. admin-password=iamwangnc 
6. admin-lua-script=/usr/local/Mysql Proxy/lib/Mysql Proxy/lua/admin.lua  
7. proxy-backend-addresses=192.168.41.196:3351  
8. proxy-read-only-backend-addresses=192.168.41.197:3351  
9. proxy-lua-script=/usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  
10. log-file=/var/tmp/Mysql Proxy.log  
11. log-level=debug 
12. daemon=true 
13. keepalive=true 
14. # chmod 660 /etc/Mysql Proxy.cnf  
15. # Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
16. # ps -ef | grep Mysql Proxy | grep -v grep  
17. root      1869     1  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
18. root      1870  1869  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
19. # tail -50f /var/tmp/Mysql Proxy.log 

6、用戶端連接配接測試

（1）先停止Slave的複制程序

1. mysql> stop slave; 

（2）連接配接Proxy端口, 插入資料

1. # mysql -uu_test -pxxx -h192.168.41.203 -P4040 -Ddb_test  
2. mysql> insert into db_test.t_test values ('testB');  
8. | testB |  

（3）多開幾個用戶端, 連接配接Proxy端口, 查詢資料

如果查詢不到上步新插入的資料, 說明連接配接到了Slave, 讀寫分離成功. 在同一線程再插入資料并驗證:

1. mysql> insert into db_test.t_test values ('testC');  

發現insert操作成功, 但是select不出剛插入的資料, 說明同一線程也讀寫分離成功. 從日志中可以驗證:

1. # tail -50f /var/tmp/Mysql Proxy.log  
2. ...  
3. [read_query] 192.168.41.203:45481  
4.   current backend   = 0 
5.   client default db = db_test 
6.   client username   = u_test 
7.   query             = select * from db_test.t_test  
8.   sending to backend : 192.168.41.197:3351  
9.     is_slave         : true  
10.     server default db: db_test  
11.     server username  : u_test  
12.     in_trans        : false  
13.     in_calc_found   : false  
14.     COM_QUERY       : true  
19.   query             = insert into db_test.t_test values ('testC')  
20.   sending to backend : 192.168.41.196:3351  
21.     is_slave         : false  
26.     COM_QUERY       : true 

（4）測試完畢後, 啟動Slave的複制程序

1. mysql> start slave; 

7、正式環境說明

1、Mysql Proxy目前還隻是個測試版, MySQL官方還不建議用到生産環境中;

2、Mysql Proxy的rw-splitting.lua腳本在網上有很多版本, 但是最準确無誤的版本仍然是源碼包中所附帶的rw-splitting.lua腳本, 如果有lua腳本程式設計基礎的話, 可以在這個腳本的基礎上再進行優化；

3、Mysql Proxy實際上非常不穩定, 在高并發或有錯誤連接配接的情況下, 程序很容易自動關閉, 是以打開keepalive參數讓程序自動恢複是個比較好的辦法, 但還是不能從根本上解決問題, 是以通常最穩妥的做法是在每個從伺服器上安裝一個Mysql Proxy供自身使用, 雖然比較低效但卻能保證穩定性；

4、Amoeba for MySQL是一款優秀的中間件軟體, 同樣可以實作讀寫分離, 負載均衡等功能, 并且穩定性要大大超過Mysql Proxy, 建議大家用來替代Mysql Proxy, 甚至MySQL-Cluster.

mysql的讀寫分離amoeba

http://freeze.blog.51cto.com/1846439/860111

此文凝聚筆者不少心血請尊重筆者勞動，轉載請注明出處http://freeze.blog.51cto.com/ 

  一、關于讀寫分離

1. 讀寫分離（Read/Write Splitting），基本的原理是讓主資料庫處理事務性增、改、删操作（INSERT、UPDATE、DELETE），而從資料庫處理SELECT查詢操作。資料庫複制被用來把事務性操作導緻的變更同步到叢集中的從資料庫。 

二、同類産品比較 

1. 雖然大多數都是從程式裡直接實作讀寫分離的，但對于分布式的部署和水準和垂直分割，一些代理的類似中間件的軟體還是挺實用的，Amoeba for Mysql 與MySQL Proxy比較 在MySQL proxy 6.0版本 上面如果想要讀寫分離并且 讀叢集、寫叢集 機器比較多情況下，用mysql proxy 需要相當大的工作量，目前mysql proxy沒有現成的 lua腳本。mysql proxy根本沒有配置檔案， lua腳本就是它的全部，當然lua是相當友善的。那麼同樣這種東西需要編寫大量的腳本才能完成一 個複雜的配置。而Amoeba for Mysql隻需要進行相關的配置就可以滿足需求。 

三、關于Amoeba

1. Amoeba(變形蟲)項目,該開源架構于2008年 開始釋出一款 Amoeba for Mysql軟體。這個軟體緻力于MySQL的分布式資料庫前端代理層，它主要在應用層通路MySQL的 時候充當SQL路由功能，專注于分布式資料庫代理層（Database Proxy）開發。座落與 Client、DB Server(s)之間,對用戶端透明。具有負載均衡、高可用性、SQL 過濾、讀寫分離、可路由相關的到目标資料庫、可并發請求多台資料庫合并結果。 通過Amoeba你能夠完成多資料源的高可用、負載均衡、資料切片的功能，目前Amoeba已在很多 企業的生産線上面使用。 

四、Amoeba的安裝

4.1下載下傳 

1. wget http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz 

4.2安裝amoeba 

1. mkdir /usr/local/amoeba 
2. #mv amoeba-mysql-binary-2.1.0-RC5.tar.gz /usr/local/amoeba 
3. #tar  xvf amoeba-mysql-binary-2.1.0-RC5.tar.gz  

4.3安裝JDK 

1. 因為Amoeba是java開發的，需要JDK支援。 Amoeba架構是基于Java SE1.5開發的，建議使用Java SE 1.5版本。 
2. % java -version 
3. java version "1.6.0_18" 
4. Java(TM) SE Runtime Environment (build 1.6.0_18-b07) 
5. Java HotSpot(TM) Client VM (build 16.0-b13, mixed mode, sharing) 
6. 目前Amoeba經驗證在JavaTM SE 1.5和Java SE 1.6能正常運作，（可能包括其他未經驗證的版本）。 如果你的機器上沒有安裝JavaTM環境，可以通路http://www.oracle.com/technetwork/java/javase/downloads/index.html進行下載下傳。可以根據你的作業系統等詳情安裝JavaTM環境。 
7. 去oracle官網下載下傳jdk安裝包後，安裝jdk 
8. # chmod 755 jdk-6u25-linux-i586.bin 
9. # ./jdk-6u25-linux-i586.bin 
10. # mv jdk1.6.0_25/ /usr/local/jdk 
11. 聲明路徑，修改/etc/profile，在末尾加上以下代碼 
12. export AMOEBA_HOME=/usr/local/amoeba 
13. export JAVA_HOME=/usr/local/jdk 
14. export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin 

 五、Amoeba配置

1. cd /usr/local/amoeba/conf 主要配置以下2個配置檔案：  
2. dbServers.xml  #定義連接配接資料庫資訊 
3. amoeba.xml     #定義讀寫分離節點管理資訊 

5.1 配置dbServers.xml

1. <?xml version="1.0" encoding="gbk"?> 
2. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd"> 
3. <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/"> 
4.                 <!--  
5.                         Each dbServer needs to be configured into a Pool, 
6.                          such as 'multiPool' dbServer    
7.                 --> 
8.         <dbServer name="abstractServer" abstractive="true"> 
9.                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> 
10.                         <property name="manager">${defaultManager}</property> 
11.                         <property name="sendBufferSize">64</property> 
12.                         <property name="receiveBufferSize">128</property> 
13.                         <!-- mysql port --> 
14.                         <property name="port">3306</property>         __ ** ##後端資料庫端口**__ 
15.                         <!-- mysql schema --> 
16.                         <property name="schema">test</property>        __ ** ##後端資料庫預設庫**__ 
17.                         <!--  mysql password 
18.                         <property name="password">password</property> 
19.                         --> 
20.                 </factoryConfig> 
21.                 <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool"> 
22.                         <property name="maxActive">500</property> 
23.                         <property name="maxIdle">500</property> 
24.                         <property name="minIdle">10</property> 
25.                         <property name="minEvictableIdleTimeMillis">600000</property> 
26.                         <property name="timeBetweenEvictionRunsMillis">600000</property> 
27.                         <property name="testOnBorrow">true</property> 
28.                         <property name="testWhileIdle">true</property> 
29.                 </poolConfig> 
30.         </dbServer> 
31.         <dbServer name="master"  parent="abstractServer">                          __ ** ##定義主的寫的節點**__ 
32.                 <factoryConfig> 
33.                         <property name="ipAddress">192.168.1.1</property>      __ ** ##主masterIP**__ 
34.                         <property name="user">test1</property>                 __ ** ##與主mysql通信，連接配接資料庫的帳号，以下是密碼**__ 
35.                         <property name="password">test1</property> 
38.         <dbServer name="slave"  parent="abstractServer"> 
40.                         <property name="ipAddress">192.168.1.2</property> 
41.                         <property name="user">test2</property>                 __ ** ##與從mysql通信，連接配接資料庫的帳号，以下是密碼**__ 
42.                         <property name="password">test2</property> 
45.         <dbServer name="server1" virtual="true"> 
46.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">    __ ** ##定義寫的池，把master節點加入**__ 
47.                         <property name="loadbalance">1</property> 
48.                         <property name="poolNames">master</property> 
51.         <dbServer name="server2" virtual="true"> 
52.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">    __ ** ##定義讀的池，把slave節點加入**__ 
54.                         <property name="poolNames">slave</property> 
57. </amoeba:dbServers> 

5.2 配置amoeba.xml

2. <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"> 
3.         <proxy> 
4.                 <!-- service class must implements com.meidusa.amoeba.service.Service --> 
5.                         <!-- port --> 
6.                          <property name="port">6666</property>                   __ ** ##定義amoeba讀寫分離proxy對外代理的端口**__ 
7.                         <!-- bind ipAddress --> 
8.                         <!--  
9.                         <property name="ipAddress">127.0.0.1</property> 
10.                          --> 
11.                         <property name="manager">${clientConnectioneManager}</property> 
12.                         <property name="connectionFactory"> 
13.                                         <property name="sendBufferSize">128</property> 
14.                                         <property name="receiveBufferSize">64</property> 
15.                                 </bean> 
16.                         </property> 
17.                         <property name="authenticator"> 
18.                                          <property name="user">dbproxy</property>     __ ** ##定義proxy的管理帳号密碼，用戶端和程式隻需要連接配接proxy的帳号密碼即可，相當于中間接封裝**__ 
19.                                         <property name="password">123456</property> 
20.                                         <property name="filter"> 
21.                                                 </bean> 
22.                                         </property> 
25.                 </service> 
26.                 <!-- server class must implements com.meidusa.amoeba.service.Service --> 
28.                         <!--  default value: random number 
29.                         <property name="port">9066</property> 
33.                         <property name="daemon">true</property> 
36.                 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"> 
37.                         <!-- proxy server net IO Read thread size --> 
38.                         <property name="readThreadPoolSize">20</property> 
39.                         <!-- proxy server client process thread size --> 
40.                         <!-- per connection cache prepared statement size  --> 
41.                         <property name="statementCacheSize">500</property> 
42.                         <!-- query timeout( default: 60 second , TimeUnit:second) --> 
43.                         <property name="queryTimeout">60</property> 
44.                 </runtime> 
45.         </proxy> 
46.         <!--  
47.                 Each ConnectionManager will start as thread 
48.                 manager responsible for the Connection IO read , Death Detection 
49.         --> 
50.         <connectionManagerList> 
52.                           default value is avaliable Processors  
53.                         <property name="processors">5</property> 
55.                 </connectionManager> 
61.         </connectionManagerList> 
62.                 <!-- default using file loader --> 
63.         <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader"> 
64.                 <property name="configFile">${amoeba.home}/conf/dbServers.xml</property> 
65.         </dbServerLoader> 
66.         <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> 
67.                 <property name="ruleLoader"> 
68.                         <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> 
69.                                 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> 
70.                                 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> 
71.                         </bean> 
72.                 </property> 
73.                 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> 
74.                 <property name="LRUMapSize">1500</property> 
75.                 <property name="defaultPool">server1</property>                   __ ** ##定義預設的池，一些除了SELECT\UPDATE\INSERT\DELETE的語句都會在defaultPool執行。**__ 
76.                 <property name="writePool">server1</property>                      __ ** ##定義寫的池**__ 
77.                 <property name="readPool">server2</property>                      __ ** ##定義讀的池**__ 
78.                 <property name="needParse">true</property> 
79.         </queryRouter> 
80. </amoeba:configuration> 

六、啟動Amoeba

1. /usr/local/amoeba/bin/amoeba start & 
2. 開機自動啟動可加入到 /etc/rc.local内 
3. echo "/usr/local/amoeba/bin/amoeba start &" >> /etc/rc.local 

七、日志排錯

1. 日志檔案在/usr/local/amoeba/log下   

   PS：amoeba雖然是JAVA寫的，看似效率不高，但功能異常強大，支援讀寫分離，表和庫級别的讀寫分離，資料庫水準分割，垂直分割，還有叢集。是淘寶的得力作品。喜歡的童鞋可以嘗試下。mysql-proxy 隻是輕量級的讀寫分離程式，雖然C寫的，但是驅動是需要lua的腳本跑，而且在高并發下經常挂掉。程式還忽略了一些字元設定，如果資料庫不是同一編碼還會出現亂碼,amoeba就不存在。就簡單介紹到這裡吧。

 使用Amoeba for mysql實作mysql讀寫分離

http://www.centos.bz/2012/05/amoeba-for-mysql/

Amoeba for MySQL緻力于MySQL的分布式資料庫前端代理層，它主要在應用層通路MySQL的時候充當query 路由功能，專注 分布式資料庫 proxy 開發。座落與Client、DB Server(s)之間。對用戶端透明。具有負載均衡、高可用性、Query過濾、讀寫分離、可路由相關的query到目标資料庫、可并發請求多台資料庫合并結果。 在Amoeba上面你能夠完成多資料源的高可用、負載均衡、資料切片的功能。目前在很多企業的生産線上面使用。

www.centos.bz這裡使用Amoeba for mysql來實作mysql的讀寫分離，起到緩解主資料庫伺服器的壓力，下面是實作這一方案的架構圖：

mysql主從複制配置

因為讀寫分離，是以一台負責mysql的寫操作，另一台負責mysql的讀操作，是以我們這裡使用mysql的主從複制再合适不過了。關于這一配置，請移步：

http://www.centos.bz/2011/07/linux-mysql-replication-sync-configure/

java環境配置

Amoeba架構是基于Java SE1.5開發的，建議使用Java SE 1.5版本。目前Amoeba經驗證在JavaTM SE 1.5和Java SE 1.6能正常運作，（可能包括其他未經驗證的版本）。

Java SE 1.6下載下傳位址：http://www.oracle.com/technetwork/java/javase/downloads/jdk-6u32-downloads-1594644.html

我這裡下載下傳jdk-6u32-linux-i586-rpm.bin。

下載下傳完成後執行sh jdk-6u32-linux-i586-rpm.bin開始安裝，将會安裝到/usr/java/jdk1.6.0_32目錄。

Amoeba的安裝

Amoeba下載下傳位址：http://sourceforge.net/projects/amoeba/

下面是安裝步驟：

1. cd /tmp
2. mkdir /usr/local/amoeba
3. wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz
4. tar xzf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba

配置使用者環境變量

1. vi ~/.bash_profile

設定為：

1. PATH=$PATH:$HOME/bin:/usr/local/amoeba/bin
2. JAVA_HOME=/usr/java/jdk1.6.0_32
3. export JAVA_HOME
4. export PATH

立即生效：

1. source  ~/.bash_profile

Amoeba for mysql配置

配置Amoeba for mysql的讀寫分離主要涉及兩個檔案：

1、/usr/local/amoeba/conf/dbServers.xml

此檔案定義由Amoeba代理的資料庫如何連接配接，比如最基礎的：主機IP、端口、Amoeba使用的使用者名和密碼等等。

2、/usr/local/amoeba/conf/amoeba.xml

此檔案定義了Amoeba代理的相關配置。

dbServers.xml檔案配置

abstractServer配置:

1. <dbServer name="abstractServer" abstractive="true">
2.                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
3.                         <property name="manager">${defaultManager}</property>
4.                         <property name="sendBufferSize">64</property>
5.                         <property name="receiveBufferSize">128</property>
6.                         <!-- mysql port -->
7.                         <property name="port">3306</property>
8.                         <!-- mysql schema -->
9.                         <property name="schema">dbname</property>
10.                         <!-- mysql user -->
11.                         <property name="user">root</property>
12.                         <!--  mysql password -->
13.                         <property name="password">root</property>
14.                 </factoryConfig>
15.                 <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
16.                         <property name="maxActive">500</property>
17.                         <property name="maxIdle">500</property>
18.                         <property name="minIdle">10</property>
19.                         <property name="minEvictableIdleTimeMillis">600000</property>
20.                         <property name="timeBetweenEvictionRunsMillis">600000</property>
21.                         <property name="testOnBorrow">true</property>
22.                         <property name="testWhileIdle">true</property>
23.                 </poolConfig>
24.         </dbServer>

此部分定義真實mysql伺服器的端口，資料庫名稱，mysql使用者及密碼。

主從資料庫定義：

1. <dbServer name="Master"  parent="abstractServer">
2.                 <factoryConfig>
3.                         <!-- mysql ip -->
4.                         <property name="ipAddress">192.168.0.1</property>
7. <dbServer name="Slave1"  parent="abstractServer">
10.                         <property name="ipAddress">192.168.0.2</property>
13. <dbServer name="Slave2"  parent="abstractServer">
16.                         <property name="ipAddress">192.168.0.3</property>
19.         <dbServer name="virtualSlave" virtual="true">
20.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
21.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
22.                         <property name="loadbalance">1</property>
23.                         <!-- Separated by commas,such as: server1,server2,server1 -->
24.                         <property name="poolNames">Slave1,Slave2</property>

此部分定義主伺服器，從伺服器，及從伺服器連接配接池。這裡隻定義資料庫位址，它們的使用者及密碼就是上面的abstractServer裡的設定。注意用來連接配接真實mysql伺服器的使用者必須擁有遠端連接配接權限。

amoeba.xml配置

amoeba連接配接驗證配置：

1. <property name="authenticator">
2.                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
3.                                         <property name="user">root</property>
4.                                         <property name="password">root</property>
5.                                         <property name="filter">
6.                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">
7.                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
8.                                                 </bean>
9.                                         </property>
10.                                 </bean>
11.                         </property>

這裡定義連接配接amoeba時用來驗證的使用者及密碼。

讀寫分離配置：

1. <property name="defaultPool">Master</property>
2.                 <property name="writePool">Master</property>
3.                 <property name="readPool">virtualSlave</property>

defaultPool:配置了預設的資料庫節點，一些除了SELECTUPDATEINSERTDELETE的語句都會在defaultPool執行。

writePool :配置了資料庫寫庫，通常配為Master，如這裡就配置為之前定義的Master資料庫。

readPool :配置了資料庫讀庫，通常配為Slave或者Slave組成的資料庫池，如這裡就配置之前的virtualSlave資料庫池。

amoeba啟動

啟動指令：

1. amoeba start

此指令以前台的方式啟動，會輸出啟動時的資訊，檢查沒有錯誤資訊後，中斷，并背景運作：

1. amoeba start &

FAQ

1、無法正常連接配接？

首先根據執行amoeba start輸出的資訊排除配置檔案沒有問題，之後确認mysql使用者是否有遠端連接配接的權限，然後檢查網站的資料庫配置檔案是否設定正确。

2、如何配置網站資料庫連接配接檔案?

預設的端口應該為8066,使用者及密碼在amoeba.xml裡設定。