heiyeluren的blog（黑夜路人的开源世界）

PHPCup.cn - 中国新锐LAMP论坛

[原创] MySQL Proxy 学习笔记收藏

[原创] MySQL Proxy 学习笔记

作者：heiyeluren
时间：2008-1-28
博客：http://blog.csdn.net/heiyeshuwu

【测试平台】

服务器端：
OS：FreeBSD 6.2
Lua： 5.1.1
MySQL Server：4.1.22-log
MySQL Proxy： 0.6.0

客户端：
OS：Windows XP Pro SP2
PHP： PHP 5.2.4 (cli)
MySQL Client： Ver 14.13 Distrib 5.1.19-beta for Win32

注意：本文使用的是最新版本 MySQL Proxy alpha 0.6.0 ，如果是0.5.0版本可能部分选项和操作不一致，同样的，以后如果除了新版本，请参照新版本文档。

【 MySQL Proxy 安装】

$ fetch http://mirror.mysql-partners-jp.biz/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-freebsd6-x86.tar.gz
$ tar zxvf mysql-proxy-0.6.0-freebsd6-x86.tar.gz
$ cd mysql-proxy-0.6.0-freebsd6-x86
$ cp sbin/mysql-proxy /usr/local/sbin/

如果无法正常使用，请检查相应的其他组件是否安装了，mysql-proxy 需要依赖 lua、libtool、glib、libevent 等库。

【初步使用】

执行 mysql-proxy --help-all 查看所有的设置选项，比较重要的选项大致描述一下功能：

管理功能选项

--admin-address=host:port — 指定一个mysqo-proxy的管理端口，缺省是 4041

代理功能选项

--proxy-address=host:port — 这个是mysql-proxy 服务器端的监听端口，缺省是 4040
--proxy-read-only-backend-addresses=<host:port> — 远程只读Slave服务器的地址和端口，缺省为不设置（本选项在mysql-proxy0.5.0版本中没有）
--proxy-backend-addresses=host:port — 指定远程MySQL服务器地址和端口，可以设置多个，缺省是 127.0.0.1:3306
--proxy-skip-profiling — 关闭查询分析功能，缺省是打开的
--proxy-fix-bug-25371 — 修正 mysql的libmysql版本大于5.1.12的一个#25371号bug
--proxy-lua-script=file — 指定一个Lua脚本程序来控制mysql-proxy的运行和设置，这个脚本在每次新建连接和脚本发生修改的的时候将重新调用

其他选项

--daemon — mysql-proxy以守护进程方式运行
--pid-file=file — 设置mysql-proxy的存储PID文件的路径

我们执行试试，监听本地MySQL是3000端口：
/usr/local/sbin/mysql-proxy --proxy-backend-addresses=127.0.0.1:3000

然后从我的windows机器使用mysql客户端连接过去：
mysql -uroot -h 192.168.0.2 -P 4040

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 4.1.22-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

远程的mysql-proxy缺省对外监听的是4040端口，它会对连接4040端口的连接做处理后丢给后端3000端口的mysql服务器处理，把结果返回。
我们执行一条sql试试：

mysql> select version();
+------------+
| version() |
+------------+
| 4.1.22-log |
+------------+
1 row in set (0.01 sec)

【对两台/多台MySQL的负载均衡】

首先我们目前MySQL Proxy 是支持多台后端MySQL数据库的，它可以支持同时设置多个 --proxy-backend-addresses 选项来使用多个MySQL，比如：
/usr/local/sbin/mysql-proxy --proxy-address=192.168.0.2:3306 --proxy-backend-addresses=127.0.0.1:3000 --proxy-backend-addresses=192.168.0.2:4000
就分别连接了两台MySQL，一台是3000端口，一台是4000端口。

假设有两台Slave的读数据服务器，我们使用mysql_proxy多个数据库的轮询技术来进行两台Slave数据库的负载均衡分散访问措施，mysql-proxy可以做到，缺省就具有了简单的均衡功能，它自带的均衡功能是当有多台MySQL服务器的时候，进行逐个访问的原则，比如有A、B两台MySQL服务器，那么第一个连接就访问A，第二个连接访问B，第三个连接访问A，一次分布，达到A、B两台MySQL访问均衡。

对于mysql-proxy这个功能，我们进行检测一下。增加一个Lua检测脚本 test-conn.lua，就一个函数：

function read_query( packet )
print("read_query: connection.backend_ndx: ", proxy.connection.backend_ndx)
end

执行：
/usr/local/sbin/mysql-proxy --proxy-address=192.168.0.2:3306 --proxy-backend-addresses=127.0.0.1:3000 --proxy-backend-addresses=192.168.0.2:4000 --proxy-lua-script=./test-conn.lua

在windows上调用mysql客户端连接过去，打开多个cmd窗口进行连接：mysql -u root -h 192.168.0.2 -P 3306

看到mysql-proxy服务器端输出结果：

read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2

很明显是按照逐个来轮询，这样能够达到平均分配连接的目的。

但是一个问题就是如果一台机器硬件设备比另外一台设备差，那么对数据库的连接就不能使用平均的方式了，那么完全使用Lua脚本构建自己的轮询连接算法，mysql-proxy的强大之处就在于能够支持Lua脚本进行各种控制，包括连接服务器(connect_server)、连接握手(read_handshake)、提交验证(read_auth)、验证结果(read_auth_result)、提交查询(read_query)、查询结果(read_query_result) 等控制都是通过简单的在 --proxy-lua-script 选项中指定脚本来进行控制。

现在我们来简单改进一下连接服务器连接分配的算法，就在Lua脚本中增加一个 connect_server 函数就行。
比如按照当前时间进行分布连接到两台MySQL上：

function connect_server()
    if (tonumber(os.date("%M")) % 2 == 0) then
        proxy.connection.backend_ndx = 2
    else
        proxy.connection.backend_ndx = 1
    end
end

或者我们采取随机方式，在两台MySQL之间随机挑选一台：

function connect_server()
local server_index = math.random(1, 2)
proxy.connection.backend_ndx
end

我们客户端继续连接多次，监测连接的服务器，发现连接的机器是随机展开的：

read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     2
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     2

【压力测试】

1. 连接数据库压力测试

测试代码使用PHP5.2，测试文件 php_mysql_proxy.php 代码:

<?php
//连接函数
function mysql_proxy_connect($is_close=true){
    $link = mysql_connect("192.168.0.2:3306", "root", "", true);
    if (!$link){
        die('Could not connect: ' . mysql_error());
    }
    if ($is_close){
        echo ' close connection ... ';
        mysql_close($link);
    }
    return true;
}

//测试函数
function test($sum, $is_close=true){
    for($i=0; $i<$sum; $i++){
        echo "Connect mysql-proxy ". $i .".. ";
        if (mysql_proxy_connect($is_close)){
            echo "Succeed ";
        } else {
            echo "Failed ";
        }

    }
}
?>

//测试1 （连续连接1w次，每次连接后不关闭连接）
test(10000);

Server: mysql-proxy 的CPU占用是从0.1%一直升到 12.4% 左右，然后结束后慢慢减下来，mysqld 的cpu占用率很低，%0.2左右
Client: 客户端在连接后马上关闭连接的状态下，一直持续到3948个连接的时候无法再连接到mysql-proxy
客户端输出：

...
Connect mysql-proxy 3945.. close connection ... Succeed
Connect mysql-proxy 3946.. close connection ... Succeed
Connect mysql-proxy 3947.. close connection ... Succeed
Connect mysql-proxy 3948..
Warning: mysql_connect(): Can't connect to MySQL server on '192.168.0.2' (10048)
in C:php_mysql_proxy.php on line 4
Could not connect: Can't connect to MySQL server on '192.168.0.2' (10048)

//测试2（连续连接1w次，每次连接后不关闭连接）
test(10000, false);

Server: mysql-proxy 的CPU占用是从0.1%一直升到 14.5% 左右，然后结束后慢慢减下来, mysqld 的cpu占用率也很低，%0.2左右
Client: 客户端在连接后马上不关闭连接的状态下，一直持续到3946个连接的时候无法再连接到mysql-proxy，基本跟关闭连接的最终处理数量差不多
输出信息：

Connect mysql-proxy 3944.. Succeed
Connect mysql-proxy 3945.. Succeed
Connect mysql-proxy 3946..
Warning: mysql_connect(): Can't connect to MySQL server on '192.168.0.2' (10048)
in C:php_mysql_proxy.php on line 4
Could not connect:

2. 执行查询测试

测试脚本，php_mysql_proxy.php，代码如下：

<?php
//时间函数
function microtime_float(){
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

//测试函数1（简单查询）
function test1(){
    $link = mysql_connect("192.168.0.2:3306", "root", "", true);
    if (!$link){
        die('Could not connect: ' . mysql_error());
    }
    for($i=0; $i<10000; $i++){
        $res = mysql_query("select now()",$link);
        echo "Query ".$i." ... ";
        if (!$res){
            echo('Could not query: ' . mysql_error());
            return false;
        }
        $row = mysql_fetch_row($res);
        echo " Succeed ";
    }
}

//测试函数2（复杂查询）
function test2(){
    $link = mysql_connect("192.168.0.2:3306", "root", "", true);
    if (!$link){
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("replaction", $link);
    for($i=0; $i<10000; $i++){
        $res = mysql_query("select * from t1 where id in (select id from t1)",$link);
        echo "Query ".$i." ... ";
        if (!$res){
            echo('Could not query: ' . mysql_error());
            return false;
        }
        while($row = mysql_fetch_row($res)){}
        echo " Succeed ";
    }
}
?>

//执行测试1
$start_time = microtime_float();
test1();
$end_time = microtime_float();
echo "\nExecute Time: ". ($end_time - $start_time) ."\n";

测试结果：PHP执行时间和服务器端cpu占用率
Execute Time: 39.4633390903
mysql-proxy: 17.56%
mysqld-1: 5.26%
mysqld-2: 2.12%

//执行测试2
$start_time = microtime_float();
test2();
$end_time = microtime_float();
echo "\nExecute Time: ". ($end_time - $start_time) ."\n";

测试结果：PHP执行时间和服务器端cpu占用率
Execute Time: 62.6189789772
mysql-proxy: 19.09%
mysqld-1: 6.25%
mysqld-2: 3.08%

可以基本看出两者差别不是非常大，也可能是因为数据太少的缘故。

【管理MySQL Proxy 】

在启动 mysql-proxy 之后，就会打开两个端口，一个是4040，这个是缺省用于提供给MySQL客户端连接使用的端口，可以通过 --proxy-address 选项修改为其他的，比如设置为 --proxy-address=192.168.0.2:3306，就完全模拟成了一台MySQL数据库了。另外默认开放 4041 的端口，这个是用来提供给管理 mysql-proxy 使用的，可以使用标准的mysql客户端来连接，同样能够通过 --admin-address 选项来设置为其他管理端口。

目前版本的管理功能还比较弱，只能够查看两个貌似是数据表的关于 mysql-proxy 的一些信息：
mysql -u root -h 192.168.0.2 -P 4041

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

说明这是一个版本为5.1.20的MySQL的代理程序，查看目前mysql-proxy的运行信息：

mysql> select * from proxy_connections;
+------+--------+-------+------------+
| id   | type   | state | db         |
+------+--------+-------+------------+
|    0 | server | 0     |            |
|    1 | proxy  | 0     |            |
|    2 | proxy  | 10    | replaction |
|    3 | server | 10    |            |
+------+--------+-------+------------+
4 rows in set (0.02 sec)

mysql> select * from proxy_config;
+----------------------------+------------------+
| option                     | value            |
+----------------------------+------------------+
| admin.address              | :4041            |
| proxy.address              | 192.168.0.2:3306 |
| proxy.lua_script           | NULL             |
| proxy.backend_addresses[0] | 127.0.0.1:3000   |
| proxy.fix_bug_25371        | 0                |
| proxy.profiling            | 1                |
+----------------------------+------------------+
6 rows in set (0.02 sec)

除了这两个状态信息，我貌似没有发现别的，不过基本的运行信息都包含了。

【总结】

先说说优点，使用简单，安装方便，各种平台的二进制版都有（这个是MySQL的传统优点），同时性能不错。
再说说缺点，缺点同样很明显，说两个典型的缺点：

1. 无法 Master/Slave 的区分
我一开始兴冲冲的以为 --proxy-read-only-backend-addresses 选项来构建一个支持Master/Slave的环境进行测试，但是我反复测试，发现无法实现，每次都是连接到Master，或者是做到每次都是按照连接算法来进行，mysql-proxy无法区分本次操作是写入操作还是读取操作而且划分到不同的MySQL机器。我后来又尝试也力求通过Lua脚本的方式来解决问题，自己定义连接情况，不过目前来看，定义的基本Lua接口，connect_server 是连接服务器，read_query 是整理SQL语句，我发现不论如何都是先执行了 connect_server 才去执行 read_query 的，就是是先连接MySQL，然后执行的查询，这样就无法实现先拿到SQL语句，然后进行分析，发现是 insert/update/delete 全部拿到Master去执行，剩余的拿到Slave去执行，但是目前貌似无法实现，可能是我文档读的不够，或者是操作方法不对，非常希望在日后新版本中有比较方便简单的方式实现，因为一般使用 mysql-proxy 的人来说，都是因为负载高，数据连接太多或者是需要管理多台Slave机器，目前让 mysql-proxy 管理多台服务器是没有问题的，负载均衡也可以通过自行写Lua脚本控制。

2. mysql-proxy admin的管理功能太弱
目前发现除了能够查看两项信息之外，mysql-proxy admin 功能没有别的，比如重启服务呀，或者是查看目前执行连接和执行的SQL语句等等都没有，稍微弱了一点，希望在后期版本中加强。

大致功能都比较清楚了，测试速度也比较快，基本上目前来说，用于一些应用问题是不大的，稳定性也还好，毕竟目前是alpha版本，我们完全有热情去期待在正式版中会有很多新功能和对目前功能的改进，我无比的期待着。

【相关URL 】

[ MySQL Proxy 相关URL ]
下载地址：http://dev.mysql.com/downloads/mysql-proxy/index.html
官方手册：http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html
官方wiki：http://forge.mysql.com/wiki/MySQL_Proxy

[ Lua 相关URL ]
Lua官方网站：http://www.lua.org/
Lua英文手册：http://www.lua.org/manual/5.1/
Lua中文手册：http://www.codingnow.com/2000/download/lua_manual.html

（转载请著名来源：http://blog.csdn.net/heiyeshuwu，本文如有纰漏或者不正确的地方，欢迎指正，谢谢！）

【补充】

added 2008-01-29：今天在InfoQ上面看到一篇文章《用MySQL-Proxy实现读写分离》文章使用了一个Lua脚本实现了读写分离的实现，大家可以参考。

发表于 @ 2008年01月28日 00:27:00|评论(loading...)|举报|收藏