使用MySQL Proxy告終讀寫離別的操作實(shí)例
作者：arango_d
本文主要介紹了使用MySQL Proxy告終讀寫離別的操作過程，希望能對(duì)您有所幫助。
MySQL Proxy最壯大的一項(xiàng)功能是告終“讀寫離別(Read/Write Splitting)”。它的原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理SELECT查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的改變同步到集群中的從數(shù)據(jù)庫。
- max_conns = s.connected_clients
- max_conns_ndx = i
- end
- end
- end
至此，我們找到了一個(gè)具有安逸連接的從數(shù)據(jù)庫
- if max_conns_ndx > 0 then
- proxy.connection.backend_ndx = max_conns_ndx
- end
- else
發(fā)送到主數(shù)據(jù)庫
- end
- return proxy.PROXY_SEND_QUERY
始終想等到BETA版出來再試探的，可還是經(jīng)不住勾引阿,下午終于有工夫測(cè)驗(yàn)一下了。
（本文參看地址：blog.chinaunix.net/u/8111/showart.php?id=451420）
一、必備軟件：
1、LUA
能夠去LUA的官方下載：dpa.nsysu.edu.tw/Downloads/MySQL-Proxy/。
可能去MYSQL官方下載源代碼。
我這里下載了：
mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gz
3、測(cè)驗(yàn)過程中廢止了B和C的REPLICATION。這么SQL語句一下子就看出來從哪里來的。
萬一是M-S（能夠先在SLAVE上舉行STOP SLAVE）
二、測(cè)驗(yàn)主機(jī)地址：
1、MySQL Proxy 安裝地址：192.168.0.234(簡(jiǎn)稱A)
2、MySQL 服務(wù)器地址：192.168.0.235(簡(jiǎn)稱B)/236(簡(jiǎn)稱C)
三、安裝經(jīng)驗(yàn)：
萬一是按照二進(jìn)制包安裝的，跳過這一步。
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;
導(dǎo)出環(huán)境變量：
- [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、察看幫助選項(xiàng)
- [root@localhost ~]# mysql-proxy --help-all
2、對(duì)MySQL 壟斷
MySQL服務(wù)器假想曾經(jīng)安裝。(安裝環(huán)節(jié)這里就不寫了)
兩臺(tái)機(jī)器上的表初始構(gòu)造和數(shù)據(jù)都是一樣的，而且都有t_girl_user這個(gè)用戶。
- 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（測(cè)驗(yàn)讀寫離別）
- [root@localhost sbin]# 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
這個(gè)例子中局限192.168.0.236為只讀,192.168.0.235為可寫。
#p#
4、下來我們來看試探收獲。
我們用幾臺(tái)客戶端開啟4個(gè)連接。
- [root@localhost ~]# /usr/local/mysql/bin/mysql -ut_girl_user -p123456 -P4040 -h192.168.0.234 -Dt_girl
我這邊曾經(jīng)啟用了好幾個(gè)客戶端，這里就不貼了，號(hào)召和上面的一樣。
寫數(shù)據(jù)。
- 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)
讀數(shù)據(jù)（目前還是寫和讀都在B上）
- mysql> select * from t;
- +----+----------+
- | id | c_str |
- +----+----------+
- | 1 | B |
- | 2 | wangwang |
- +----+----------+
- 2 rows in set (0.00 sec)
再添置一個(gè)客戶端連接。
- 1 row in set (0.00 sec)
- mysql> show processlist;
- +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+
- | 2 | system user | | NULL | Connect | 1842 | Has read 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)
目前我們來讀數(shù)據(jù)。
- mysql> select * from t;
- +----+-------+
- | id | c_str |
- +----+-------+
- | 1 | C |
- +----+-------+
- 1 row in set (0.00 sec)
這個(gè)數(shù)據(jù)很顯明是來自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上的數(shù)據(jù)未曾變，還是未曾數(shù)據(jù)。
目前跑到B上看看kaibanla.com。
- mysql> show processlist;
- +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+
- | 2 | system user | | NULL | Connect | 1842 | Has read 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)
數(shù)據(jù)已成功插入到B中。
關(guān)于使用MySQL Proxy告終讀寫離別的知識(shí)就介紹到這里，希望能對(duì)您有所幫助。
【編輯推薦】
- 簡(jiǎn)單介紹一下SQL Profiler的過濾設(shè)置
- 如何顯示與隱藏Sharepoint列表中的指定字段
- 在SQL觸發(fā)器或存儲(chǔ)過程中獲取登錄用戶信息
- 檢測(cè)局域網(wǎng)電腦是否有安裝SQL Server數(shù)據(jù)庫
- 局域網(wǎng)所有機(jī)器都能連接MySQL數(shù)據(jù)庫的設(shè)置命令
責(zé)任編輯：趙鵬
來源：
網(wǎng)易博客
