MYSQL-PROXY的安裝與使用

目前MYSQL-PROXY已經有相當的功能, 比如負載平衡,讀寫分離,失敗切換等, 就簡單功能做了測試,,,,,[@more@]

MySQL Proxy 安裝與使用

1. MYSQL 安裝
(略)

2. MYSQL PROXY 安裝

2.0 獲取系統資訊
getconf LONG_BIT
cat /etc/redhat-release

2.1 下載

--二進位制版本 (請下載v0.7.0以上)
wget
# 具體版本以SETP2.0所得資訊而定

2.2 安裝

2.2.1 #必備軟體: LUA

cd /opt/install
wget
tar zxvf lua-5.1.2.tar.gz
cd lua-5.1.2
make linux install

2.2.2 #二進位制版本安裝
tar zxvf mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz
cp mysql-proxy-0.6.1-linux-rhel4-x86-32bit/sbin/mysql-proxy $MYSQL目錄/bin
mysql-proxy & //在後臺啟動,預設啟動時4040和4041埠
mysql -P4040 // 現在這樣也可以連線資料了

3. MYSQL PROXY 使用

手冊:http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-cmdline.html
幫助: mysql-proxy --help-all

3.0 測試環境

10.2.224.236 RHEL4/32/MYSQL 5.0.51a -- 簡稱為S-A
10.2.224.237 RHEL4/32/MYSQL 5.0.51a -- 簡稱為S-B
10.5.232.210 RHEL4/32/MYSQL PROXY 0.6.1 -- 簡稱為PROXY
10.2.226.24 -- 簡稱CLIENT

3.1 測試讀寫分離

要求: 寫在S-A上, 讀在S-B上,

啟動PROXY :
mysql-proxy --proxy-read-only-backend-addresses=10.2.224.237:3306
--proxy-backend-addresses=10.2.224.236:3306
--proxy-lua-script=/opt/install/mysql-proxy-0.6.1-linux-rhel4-x86-32bit/share/mysql-proxy/rw-splitting.lua &

CLENT連線:
mysql -u probe -P4040 -palibaba -h 10.5.232.210 eservice -e 'select count(*) from test';
mysql -u probe -P4040 -palibaba -h 10.5.232.210 eservice -e "insert into test values ('236')";

這時,你在同一個SESSION中,不管你INSERT多少次, 你SELECT的結果都是一樣的.因為你插和查根本不是同一個庫.
這也就表明讀寫分離了!

(注意這裡的LUA檔案,是在連線產生時才會執行.)

3.2 測試負載平衡

mysql-proxy --proxy-backend-addresses=10.2.224.237:3306
--proxy-backend-addresses=10.2.224.236:3306 &
(如果有,還可以寫多個)

在測試的時候,注意只有在五個SESSION以上,PROXY才會考慮平衡,在這裡用了10個連線:
mysql -u probe -P4040 -palibaba -h 10.5.232.210 eservice

在S-A上:
mysql> show processlist ;
+----+-------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+--------------------+----------+---------+------+-------+------------------+
| 56 | root | 127.0.0.1:57794 | eservice | Query | 0 | NULL | show processlist |
| 83 | probe | 10.5.232.210:33770 | eservice | Sleep | 42 | | NULL |
| 84 | probe | 10.5.232.210:33772 | eservice | Sleep | 35 | | NULL |
| 85 | probe | 10.5.232.210:33774 | eservice | Sleep | 29 | | NULL |
| 86 | probe | 10.5.232.210:33776 | eservice | Sleep | 23 | | NULL |
| 87 | probe | 10.5.232.210:33778 | eservice | Sleep | 17 | | NULL |
+----+-------+--------------------+----------+---------+------+-------+------------------+

在S-B上:.
mysql> show processlist ;
+---------+-------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------+--------------------+----------+---------+------+-------+------------------+
| 5685701 | root | 127.0.0.1:58177 | eservice | Query | 0 | NULL | show processlist |
| 5685710 | probe | 10.5.232.210:33769 | eservice | Sleep | 50 | | NULL |
| 5685711 | probe | 10.5.232.210:33771 | eservice | Sleep | 42 | | NULL |
| 5685712 | probe | 10.5.232.210:33773 | eservice | Sleep | 37 | | NULL |
| 5685713 | probe | 10.5.232.210:33775 | eservice | Sleep | 31 | | NULL |
| 5685714 | probe | 10.5.232.210:33777 | eservice | Sleep | 25 | | NULL |
+---------+-------+--------------------+----------+---------+------+-------+------------------+

從這麼看,倒像是負載平衡起作用了!

4. 失敗切換

當我們以多伺服器啟動時:
mysql-proxy --proxy-backend-addresses=10.2.224.237:3306
--proxy-backend-addresses=10.2.224.236:3306 &

只要一個節點(237)DOWN了. 那PROXY就連不上了!

(原來連著236的SESSION不會斷,但新加的連線就報:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 )

這時:
PROXY 會報: network-mysqld-proxy.c.3709: connect(10.2.224.237:3306) failed: Connection refused 並試著重新連線!

V0.6.1 有這個BUG,V0.7.0 解決這個問題

5. PROXY 管理

你可以用MYSQL CLIENT直接連到PROXY管理視窗進行管理查詢:(管理埠可以更改)
mysql -P4041 -h 10.5.232.210

(:)> select * from proxy_connections;
+------+--------+-------+----------+
| id | type | state | db |
+------+--------+-------+----------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
| 3 | proxy | 10 | eservice |
+------+--------+-------+----------+
4 rows in set (0.00 sec)

(:)> select * from proxy_config;
+----------------------------+-------------------+
| option | value |
+----------------------------+-------------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 10.2.224.237:3306 |
| proxy.backend_addresses[1] | 10.2.224.236:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+-------------------+
7 rows in set (0.00 sec)

不過,據說PROXY還不推薦被使用在生產環境,