實驗環境】平台 centos6.4IP分配192.168.30.101 node1 mysql-proxy192.168.30.102 node2 mysql主節點192.168.30.103 node3 mysql從節點
192.168.30.104 node4 client
注意：1、所有機器上都已編譯安裝好mysql5.52、實驗的時候需要確保node2和node3的資料一致
3、實驗的時候需要關閉node2和node3的主從複製，以便觀察效果如果不關閉主從複製，新的資料一寫到主伺服器上馬上就通過主從複製複製到了從伺服器，這樣的話難以分辨mysql-proxy是否真的實現了讀寫分離）
實驗拓撲】
650) this.width=650;" src="/e/u/themes/default/images/spacer.gif" style="border:1px solid #ddd;" alt="spacer.gif" />650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/21293540K-0.png" title="clipboard.png" alt="184919541.png" />
實驗步驟】
以下步驟均在node1(mysql-proxy)機器上進行
安裝所需軟體包
[root@node1 ~]# yum install -y readline-devel gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*
編譯安裝lua指令碼
由於mysql-proxy 是通過lua指令碼實現讀寫分離的，因此需要裝上lua
源碼包下載：http://www.lua.org/download.html[root@node1 src]# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz[root@node1 src]# tar xf lua-5.1.4.tar.gz
[root@node1 src]# cd lua-5.1.4
[root@node1 lua-5.1.4]# vi src/Makefile 找到 CFLAGS= -O2 -Wall $(MYCFLAGS) 這一行加上-fPIC，如下所示
CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)
修改INSTALL_TOP，如下所示：指定安裝目錄）
INSTALL_TOP= /usr/local/lua
[root@node1 lua-5.1.4]# make linux
[root@node1 lua-5.1.4]# make install
安裝mysql-proxy：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
[root@node1 src]# wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3.tar.gz
[root@node1 src]# tar xf mysql-proxy-0.8.3.tar.gz [root@node1 src]# cd mysql-proxy-0.8.3[root@node1 mysql-proxy-0.8.3]# ./configure LDFLAGS="-lm -ldl" LUA_CFLAGS="/usr/local/lua/bin/lua -I/usr/local/lua/include" LUA_LIBS="/usr/local/lua/lib -llua" --prefix=/usr/local/mysql-proxy --with-lua
[root@node1 mysql-proxy-0.8.3]# make && make install
確儲存在rw-spitting.lua 這個指令碼
[root@node1 ~]# cp /usr/local/src/mysql-proxy-0.8.3/lib/rw-splitting.lua /usr/local/mysql-proxy/share/doc/mysql-proxy/
啟動mysql-proxy[root@node1 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --proxy-backend-addresses=192.168.30.102:3306 --proxy-read-only-backend-addresses=192.168.30.103:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &[3] 30840[2] Done /usr/local/mysql-proxy/bin/mysql-proxy --proxy-backend-addresses=192.168.30.102:3306 --proxy-read-only-backend-addresses=192.168.30.103:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua
[root@node1 ~]# 2013-09-17 00:14:46: (critical) plugin proxy 0.8.3 started
啟動時注意上述指令碼路徑別寫錯，否則會報檔案不存在的錯誤
串連mysql授權
Mysql主伺服器(node2)[root@node2 src]# mysql -uroot -pmysql> grant all on *.* to 'proxy'@'192.168.30.101' identified by 'password';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql從伺服器(node3)[root@node3 src]# mysql -uroot -pmysql> grant all on *.* to 'proxy'@'192.168.30.101' identified by 'password';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在Mysql1，Mysql2 上面分別測試連接mysql-proxy確保都能連通mysql-proxy）[root@node2 src]# mysql -uproxy -ppassword -P4040 -h192.168.30.101
[root@node3 src]# mysql -uproxy -ppassword -P4040 -h192.168.30.101
測試讀寫分離效果
確保node2和node3 節點的mysql 資料一致mysql> insert into t1 values(1,100);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;+------+------+| id | ip |+------+------+| 1 | 100 |+------+------+
1 row in set (0.08 sec)
client機器上[root@node4 src]# mysql -uroot -pmysql> grant all on *.* to 'proxy'@'192.168.30.101' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@node4 src]# mysql -uproxy -ppassword -h192.168.30.101 -P4040mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || proxy || test |+--------------------+
5 rows in set (0.01 sec)
mysql> use proxy;
Database changed
mysql> select * from t1;+------+------+| id | ip |+------+------+| 1 | 100 |+------+------+
1 row in set (0.01 sec)
添加記錄mysql> insert into t1 values(2,101);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(3,102);
Query OK, 1 row affected (0.04 sec)
查詢記錄
mysql> select * from t1;+------+------+| id | ip |+------+------+| 1 | 100 |+------+------+
1 row in set (0.01 sec)
可以看到通過讀操作，並沒有讀到剛更新的記錄
回到node2mysql主節點），發現已更新寫入的記錄mysql> select * from t1;+------+------+| id | ip |+------+------+| 1 | 100 || 2 | 101 || 3 | 102 |+------+------+
3 rows in set (0.00 sec)
在node3mysql從節點），並沒有發現剛才更新的記錄mysql> select * from t1;+------+------+| id | ip |+------+------+| 1 | 100 |+------+------+
1 row in set (0.00 sec)
由此驗證，我們已經實現了MySQL讀寫分離，目前所有的寫操作都全部在Master主伺服器上，用來避免資料的不同步；另外，所有的讀操作都分攤給了其它各個Slave從伺服器上，用來分擔資料庫壓力。
a
