MySQL Proxy 安装与读写分离体验

1、mysql-5.1的安装
mysql-5.1刚出来rc版，原先持续好久的beta版
# wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.1/mysql-5.1.22-rc.tar.gz
# tar zxf mysql-5.1.22-rc.tar.gz
# cd mysql-5.1.22-rc
# ./configure –prefix=/usr/local/mysql-5.1.22 –with-charset=utf8 –with-extra-charsets=all –with-big-tables –with-comment –with-mysqld-ldflags=-all-static –enable-assembler –disable-shared –with-pthread
# make && make install
# ln -s /usr/local/mysql-5.1.22 /usr/local/mysql
# cd /usr/local/mysql
# mkdir etc ; cp share/mysql/my-large.cnf etc/my.cnf
# useradd mysql -s /sbin/nologin
# chown -R mysql.mysql .
# su -m mysql -c ‘./bin/mysql_install_db’ //安装授权表
# su -m mysql -c ‘./bin/mysqld_safe &’ //启动mysql服务
# /usr/local/mysql/bin/mysql //进入msyql

2、mysql-proxy安装
编译mysql-proxy 0.6.0需要Lua 5.1和glib 2.6.0以上版本
下载安装Lua 5.1
# wget http://www.lua.org/ftp/lua-5.1.2.tar.gz
# tar zxf lua-5.1.2.tar.gz && cd lua-5.1.2 && make linux install
centos4.5本身的glib是2.4的，glib升级非常麻烦，mysql-proxy 0.6.0源码的配置无法通过。
# tar zxf mysql-proxy-0.6.0.tar.gz && cd mysql-proxy-0.6.0
# LUA_CFLAGS=”-I/usr/local/include” LUA_LIBS=”-L/usr/local/lib -llua -ldl” LDFLAGS=”-lm” ./configure –prefix=/usr/local/mysql-proxy –with-mysql=/usr/local/mysql-5.1.22/bin/mysql_config
checking for LUA… yes
checking for GLIB… Requested ‘glib-2.0 >= 2.6.0’ but version of GLib is 2.4.7
configure: error: Package requirements (glib-2.0 >= 2.6.0) were not met:

后来直接下载了一个RHEL4的二进制版本
# wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
# tar mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
# cp mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy /usr/local/mysql/bin
# /usr/local/mysql/bin/mysql-proxy & //在后台启动,默认启动时4040和4041端口
# /usr/local/mysql/bin/mysql -P4040 //一样能进入mysql
mysql-proxy用法
–proxy-address= 代理的ip:port，默认是0.0.0.0:4040
–proxy-read-only-backend-addresses= 代理的只读连接ip:port 默认0.0.0.0:4042(试了一下好像没有作用)
–proxy-backend-addresses= db server的地址,默认是127.0.0.1:3306，可以指定多个来做failover和load balance
–proxy-profiling enable profiling of queries (没试)
–proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions (没试)
–proxy-lua-script= lua脚本文件

3、测试

分别在192.168.1.122和192.168.189上安装mysql5.1数据库，启动并添加一个用户

mysql>grant all privileges on test.* to abc@'%' identified by '123456';

在192.168.1.122的test数据库建立一个表hehe

create table hehe(id int(5),hehe char(255));

在192.168.1.189的test数据库建立一个表hehe

create table hehe(id int(5),name char(255));

两个表明一样却有不同的字段。
在192.168.1.104启动mysql-proxy
# LUA_PATH=”/usr/local/share/mysql-proxy/?.lua” /usr/local/mysql/bin/mysql-proxy –proxy-read-only-backend-addresses=192.168.1.189:3306 –proxy-backend-addresses=192.168.1.122:3306 –proxy-lua-script=share/mysql-proxy/rw-splitting.lua &
假设104也有mysql客户端
# mysql -uabc -p123456 -h192.168.1.104 -P4040 test
对表进行查询

mysql> desc hehe;

出现的表描述是189上的就对了。
对数据进行写操作

mysql> create table jiji(id int(5),name char(255));

然后去122上看看，如果122上多了一张表就对了

总结：这个mysql数据库前端代理能对用户的mysql操作进行读写分离，对程序是透明的，减少了开发上的难度。

4、MySQL Proxy 安装与读写分离体验
一直想等到BETA版出来再试验的，可还是经不住诱惑阿,下午终于有时间测试一下了。
（本文参考地址：http://blog.chinaunix.net/u/8111/showart.php?id=451420）
4.1 必备软件：
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）
4.2 测试主机地址：
1. MySQL Proxy 安装地址：192.168.0.234(简称A)
2. MySQL 服务器地址：192.168.0.235(简称B)/236(简称C)
4.3 安装体验
如果是按照二进制包安装的，跳过这一步。
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/
4.4 使用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@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-proxycd 
/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 | 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 | 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 | 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)
数据被成功插入到B
这个读写分离应该看得很清楚。其他的功能等我测试了再总结吧。
如果要知道为什么连接多了才会进行分离的话，看看手册
还有其他我以前收藏的地址，可以参考以下。
http://www.infoq.com/news/2007/10/mysqlproxyrwsplitting;jsessionid=F9E0B91935E82033BAFAC067A326DC4B
http://blogs.mysql.com/kaj/2007/12/10/combining-mysql-proxy-with-mysql-cluster/

flyinweb's blog

不积跬步，无以致千里；不积小流，无以成江海。

MySQL Proxy 安装与读写分离体验

发表回复取消回复

相关文章：

发表回复 取消回复

发表回复取消回复