MySQL Proxy 安裝與讀寫分離體驗

2009-02-19 10:40:23

一直想等到BETA版出來再實驗的，可還是經不住誘惑阿,下午終於有時間測試一下了。 （本文參考地址：http://blog.chinaunix.net/u/8111/showart.php?id=451420） 一、必備軟體： 1、LUA    可以去LUA的官方下載：www.lua.org 2、MySQL Proxy    這裡有好多二進位版本。     http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/    或者去MYSQL官方下載原始碼。 3、測試過程中取消了B和C的REPLICATION。這樣SQL語句一下子就看出來從哪裡來的。 如果是M-S（可以先在SLAVE上進行STOP SLAVE） 二、測試主機地址： 1、MySQL Proxy 安裝地址：192.168.0.234(簡稱A) 2、MySQL 伺服器位址：192.168.0.235(簡稱B)/236(簡稱C) 三、安裝體驗 如果是按照二進位包安裝的，跳過這一步。 1、 LUA的安裝 [root@localhost ~]#tar zxvf lua-5.1.2.tar.gz -C /usr/local [root@localhost ~]# cd /usr/local/ [root@localhost local]# mv lua-5.1 lua [root@localhost lua]# cd lua [root@localhost lua]#make local;make install;     匯出環境變數： [root@localhost lua]#export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm" 2、MySQL Proxy 安裝   [root@localhost ~]#tar -zxvf mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz -C /usr/local/mysql/ [root@localhost ~]#cd /usr/local/mysql [root@localhost mysql]#mv mysql-proxy-0.6.1-linux-rhel4-x86-32bit/ mysql-proxy [root@localhost sbin]# export PATH=$PATH:/usr/local/mysql/mysql-proxy/sbin/ 四、使用MySQL Proxy 1、查看協助選項 [root@localhost ~]# mysql-proxy --help-all 2、對MySQL 操作 MySQL伺服器假設已經安裝。(安裝步驟這裡就不寫了) 兩台機器上的表初始結構和資料都是一樣的，而且都有t_girl_user這個使用者。 mysql> desc t; +-------+----------+------+-----+---------+----------------+ | Field | Type     | Null | Key | Default | Extra          | +-------+----------+------+-----+---------+----------------+ | id    | int(11) | NO   | PRI | NULL    | auto_increment | | c_str | char(64) | NO   |     |         |                | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) 2 rows in set (0.00 sec) 我在B上插入一條記錄 mysql> insert into t(c_str) values('B'); Query OK, 1 row affected (0.00 sec) 在C上同樣插入一條記錄 mysql> insert into t(c_str) value('C'); Query OK, 1 row affected (0.00 sec) mysql> 3、啟動MySQL-Proxy（測試讀寫分離） [root@localhostsbin]# mysql-proxy--proxy-read-only-backend-addresses=192.168.0.236:3306--proxy-backend-addresses=192.168.0.235:3306 --proxy-lua-script=/usr/local/mysql/mysql-proxy/share/mysql-proxy/rw-splitting.lua& [1] 32554 讓MYSQL PROXY自動啟動的簡單指令碼 #!/bin/sh # export PATH=$PATH:/usr/local/mysql-proxy cd /usr/local/mysql-proxy ./mysql-proxy--proxy-read-only-backend-addresses=192.168.0.236:3306--proxy-backend-addresses=192.168.0.235:3306--proxy-lua-script=rw-splitting.lua >> /tmp/log ~ 這個例子中限制192.168.0.236為唯讀,192.168.0.235為可寫。 4、下來我們來看實驗結果。 我們用幾台用戶端開啟4個串連。 [root@localhost ~]# /usr/local/mysql/bin/mysql -ut_girl_user -p123456 -P4040 -h192.168.0.234 -Dt_girl 我這邊已經啟動了好幾個用戶端，這裡就不貼了，命令和上面的一樣。 寫資料。 mysql> insert into t(c_str) values ('wangwang'); Query OK, 1 row affected (0.01 sec) mysql> show processlist; +----+-------------+---------------------+--------+---------+------+-------+------------------+ | Id | User        | Host                | db     | Command | Time | State | Info             | +----+-------------+---------------------+--------+---------+------+-------+------------------+ | 12 | t_girl_user | 192.168.0.234:44975 | t_girl | Sleep   |   28 |       | NULL             | | 13 | t_girl_user | 192.168.0.234:44976 | t_girl | Sleep   |   15 |       | NULL             | | 14 | t_girl_user | 192.168.0.234:44977 | t_girl | Sleep   |   19 |       | NULL             | | 15 | t_girl_user | 192.168.0.234:44978 | t_girl | Query   |    0 | NULL | show processlist | +----+-------------+---------------------+--------+---------+------+-------+------------------+ 4 rows in set (0.00 sec) 4 rows in set (0.00 sec) 讀資料（現在還是寫和讀都在B上） mysql> select * from t; +----+----------+ | id | c_str    | +----+----------+ | 1 | B        | | 2 | wangwang | +----+----------+ 2 rows in set (0.00 sec) 再增加一個用戶端串連。 1 row in set (0.00 sec) mysql> show processlist; +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ |Id | User        | Host                | db     | Command | Time |State                                                                 |Info             | +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ | 2 | system user |                     | NULL   | Connect | 1842 | Hasread all relay log; waiting for the slave I/O thread to update it |NULL             | | 5 | root        | localhost           |t_girl | Query   |    0 |NULL                                                                  |show processlist | | 12 | t_girl_user | 192.168.0.234:44975 |t_girl | Sleep   | 446|                                                                       | NULL             | | 13 | t_girl_user | 192.168.0.234:44976 |t_girl | Sleep   | 188|                                                                       | NULL             | | 14 | t_girl_user | 192.168.0.234:44977 |t_girl | Sleep   | 206|                                                                       | NULL             | | 15 | t_girl_user | 192.168.0.234:44978 |t_girl | Sleep   | 203|                                                                       | NULL             | | 16 | t_girl_user | 192.168.0.234:44979 |t_girl | Sleep   | 164|                                                                       | NULL             | | 17 | t_girl_user | 192.168.0.234:44980 |t_girl | Sleep   | 210|                                                                       | NULL             | +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ 8 rows in set (0.00 sec) 現在我們來讀資料。 mysql> select * from t; +----+-------+ | id | c_str | +----+-------+ | 1 | C     | +----+-------+ 1 row in set (0.00 sec) 這個資料很明顯是來自C的。 再插入一條記錄 mysql> insert into t(c_str) values ('wangwei'); Query OK, 1 row affected (0.00 sec) mysql> select * from t; +----+-------+ | id | c_str | +----+-------+ | 1 | C     | +----+-------+ 1 row in set (0.00 sec) C上的資料沒有變。 還是沒有資料。 現在跑到B上看看。 mysql> show processlist; +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ |Id | User        | Host                | db     | Command | Time |State                                                                 |Info             | +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ | 2 | system user |                     | NULL   | Connect | 1842 | Hasread all relay log; waiting for the slave I/O thread to update it |NULL             | | 5 | root        | localhost           | t_girl | Query   |    0 |NULL                                                                  |show processlist | |12 | t_girl_user | 192.168.0.234:44975 | t_girl | Sleep   | 446|                                                                       | NULL             | |13 | t_girl_user | 192.168.0.234:44976 | t_girl | Sleep   | 188|                                                                       | NULL             | |14 | t_girl_user | 192.168.0.234:44977 | t_girl | Sleep   | 206|                                                                       | NULL             | |15 | t_girl_user | 192.168.0.234:44978 | t_girl | Sleep   | 203|                                                                       | NULL             | |16 | t_girl_user | 192.168.0.234:44979 | t_girl | Sleep   | 164|                                                                       | NULL             | |17 | t_girl_user | 192.168.0.234:44980 | t_girl | Sleep   | 210|                                                                       | NULL             | +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+ 8 rows in set (0.00 sec) mysql> select * from t; +----+----------+ | id | c_str    | +----+----------+ | 1 | B        | | 2 | wangwang | | 3 | wangwei | +----+----------+ 3 rows in set (0.00 sec) 資料被成功插入到B 這個讀寫分離應該看得很清楚。其他的功能等我測試了再總結吧