I always wanted to wait for the beta version to come out and try again, but I still couldn't resist the temptation. In the afternoon, I finally had time to test it.
(Reference address: http://blog.chinaunix.net/u/8111/showart.php? Id = 451420)
I. necessary software:
1. Lua
You can download it from Lua Official Website: www.lua.org.
2. MySQL proxy
There are many binary versions.
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
Or download the source code from the MySQL official website.
3. Replication of B and C is canceled during the test. In this way, the SQL statement shows where it comes from.
For M-S (you can stop slave on slave first)
Ii. Test host address:
1. MySQL proxy installation address: 192.168.0.234 ()
2. MySQL server address: 192.168.0.235 (B)/236 (c)
Iii. installation experience
Skip this step if the installation is based on the binary package.
1. Lua Installation
[Root @ localhost ~] # Tar zxvf lua-5.1.2.tar.gz-C/usr/local
[Root @ localhost ~] # Cd/usr/local/
[Root @ localhost local] # Music Video lua-5.1 Lua
[Root @ localhost Lua] # cd Lua
[Root @ localhost Lua] # make local; make install;
Export environment variables:
[Root @ localhost Lua] # export lua_cflags = "-I/usr/local/include" lua_libs = "-l/usr/local/lib-llua-LDL" ldflags = "- lm"
2. Install 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] # Music mysql-proxy-0.6.1-linux-rhel4-x86-32bit/MySQL-proxy
[Root @ localhost sbin] # export Path = $ path:/usr/local/MySQL-proxy/sbin/4. Use MySQL proxy
1. View help options
[Root @ localhost ~] # Mysql-proxy -- Help-all
2. MySQL operations
Assume that the MySQL server has been installed. (The installation steps will not be written here)
The initial structure and data of the tables on the two machines are the same, and both have the user 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)
I insert a record on B.
Mysql> insert into t (c_str) values ('B ');
Query OK, 1 row affected (0.00 sec)
Insert a record on C.
Mysql> insert into t (c_str) value ('C ');
Query OK, 1 row affected (0.00 sec)
Mysql>
3. Start mysql-proxy (test read/write splitting)
[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-proxy/share/mysql-proxy/rw-splitting.lua &
[1] 32554
Simple script for mysql proxy to automatically start
#! /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
~
In this example, 192.168.0.236 is read-only and 192.168.0.235 is writable.
4. Let's look at the test results.
We use several clients to enable four connections.
[Root @ localhost ~] #/Usr/local/mysql/bin/mysql-ut_girl_user-p123456-P4040-h192.168.0.234-Dt_girl
I have already started several clients, so I will not post them here. The commands are the same as those above.
Write Data.
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)
Read data (both write and read are on B)
Mysql> select * from T;
+ ---- + ---------- +
| ID | c_str |
+ ---- + ---------- +
| 1 | B |
| 2 | Wangwang |
+ ---- + ---------- +
2 rows in SET (0.00 Sec)
Add a client connection.
1 row in set (0.00 sec)
Mysql> show processlist;
+ ---- + ------------- + --------------------- + -------- + --------- + ------ + Accept + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------------- + --------------------- + -------- + --------- + ------ + Accept + ------------------ +
| 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 |
+ ---- + ------------- + --------------------- + -------- + --------- + ------ + Accept + ------------------ +
8 rows in set (0.00 sec)
Now let's read the data.
Mysql> select * from t;
+ ---- + ------- +
| Id | c_str |
+ ---- + ------- +
| 1 | C |
+ ---- + ------- +
1 row in set (0.00 sec)
This data is obviously from C.
Insert another record
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)
The data on C has not changed.
Still no data.
Run to B now.
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)
Data is successfully inserted to B.
This read/write splitting should be clear. Let's summarize other functions after I test them. |
