MySQL Proxy实现读写分离及负载均衡Posted on by Jose
1. MySQL Proxy安装 MySQL Proxy安装之前需要的依赖包: 1.1 安装一些依赖包： yum -y install gcc libevent libevent-devel readline readline-devel ncurses ncurses-devel glib2 glib2-devel 1.2 安装Lua wget -c http://www./ftp/lua-5.1.5.tar.gz tar zxvf lua-5.1.5.tar.gz cd lua-5.1.5 vi Makefile #将INSTALL_TOP=/usr/local修改为:INSTALL_TOP=/usr/local/lua vi ./src/Makefile #将CFLAGS= -O2 -Wall $(MYCFLAGS)修改为:CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) make linux make install 1.3 安装MySQL Proxy wget -c http://dev./get/Downloads/MySQL-Proxy/mysql-proxy-0.8.3.tar.gz/from/http://cdn./ tar zxvf mysql-proxy-0.8.3.tar.gz cd mysql-proxy-0.8.3 ./configure LDFLAGS="-lm -ldl" LUA_CFLAGS="/usr/local/lua/bin/lua -I/usr/local/lua/include" LUA_LIBS="/usr/local/lua/lib/liblua.a" --prefix=/usr/local/mysql-proxy --with-lua make && make install 2. MySQL Proxy配置与启动 2.1 配置MySQL Proxy MySQL Proxy的配置选项既可以作为MySQL Proxy启动命令行的参数,也可以放到配置文件当中使用. mkdir /var/log/mysql-proxy mkdir /usr/local/mysql-proxy/lua-scripts mkdir /usr/local/mysql-proxy/conf 启动使用的配置文件mysql-proxy.conf: [root@centos190 conf]# cd /usr/local/mysql-proxy/conf [root@centos190 conf]# cat mysql-proxy.conf [mysql-proxy] daemon=true pid-file=/usr/local/mysql-proxy/mysql-proxy.pid log-file=/var/log/mysql-proxy/mysql.log log-level=debug LUA_PATH="/usr/local/mysql-proxy/lua-scripts/?.lua" proxy-lua-script=/usr/local/mysql-proxy/lua-scripts/rw-splitting.lua proxy-address=192.168.1.190:3306 proxy-read-only-backend-addresses=192.168.1.192:3306,192.168.1.189:3306 proxy-backend-addresses=192.168.1.191:3306 keepalive=true proxy-skip-profiling=true #proxy-connect-timeout=2 #proxy-read-timeout=60 #proxy-write-timeout=30 admin-address=:4041 admin-lua-script=/usr/local/mysql-proxy/lua-scripts/admin.lua admin-username=admin admin-password=admin plugins=proxy,admin 修改mysql-proxy.conf的权限为0600 [root@centos190 conf]# chmod 660 mysql-proxy.conf 2.2 修改MySQL Proxy启动时加载的lua脚本 修改实现读写分离功能的rw-splitting.lua脚本连接池部分: --- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 3,
is_debug = false
}
end
2.3 启动MySQL Proxy [root@centos190 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 查看启动日志： [root@centos190 conf]# tail -f /var/log/mysql-proxy/mysql.log 2013-02-24 08:25:41: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=31827 alive 2013-02-24 08:25:41: (debug) chassis-unix-daemon.c:157: waiting for 31827 2013-02-24 08:25:41: (debug) chassis-unix-daemon.c:121: we are the child: 31827 2013-02-24 08:25:41: (critical) plugin proxy 0.8.3 started 2013-02-24 08:25:41: (critical) plugin admin 0.8.3 started 2013-02-24 08:25:41: (debug) max open file-descriptors = 1024 2013-02-24 08:25:41: (message) proxy listening on port 192.168.1.190:4040 2013-02-24 08:25:41: (message) added read/write backend: 192.168.1.191:3306 2013-02-24 08:25:41: (message) added read-only backend: 192.168.1.192:3306 2013-02-24 08:25:41: (message) added read-only backend: 192.168.1.189:3306 2.4 使用MySQL Proxy命令行 代理界面登陆： root@centos190 conf]# mysql -uroot -p -h 192.168.1.190 -P 4040 管理界面登陆： root@centos190 conf]# mysql -uadmin -p -h 192.168.1.190 -P 4041 3. 负载均衡和读写分离测试 3.1 分别在189(read),191(write),192(read)三台MySQL上建test表 mysql>CREATE TABLE `test` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.62 sec) 3.2 使用PHP脚本测试 <?php
// mysql-proxy.php
$mysqli = new MySQLi('192.168.1.190', 'root', '123456', 'test', 4040);
if ($mysqli->connect_errno) {
exit($mysqli->connect_error);
}
//$mysqli->query("insert into test values (10007),(10008);");
$result = $mysqli->query("select * from test");
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
var_dump($rows);
3.3 测试发现多个问题 - 可以实现读写分离和负载均衡,但是不太稳定,刷新PHP脚本太快就会返回warning错误信息 MySQL Proxy FAQ：https://wikis.oracle.com/display/mysql/MySQL+Proxy+FAQ |
|
|
