mysql读写分离

1、LUA

2、MySQL Proxy

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

这个读写分离应该看得很清楚。其他的功能等我测试了再总结吧。