MySQL Proxy简介

作者：zhanhailiang 日期：2014-12-31

MySQL Proxy是介于MySQL Client端和MySQL Server端之间的中间件，可以监测、分析或改变它们的通信。由于其MySQL Proxy实现MySQL C/S通信协议，所以其对应用是透明，即应用把MySQL Proxy当成MySQL Server，只需要将原先直连的MySQL host:port修改成MySQL Proxy host:port即可；

其主要应用场景：

1. 充当MySQL连接池。
2. 通过Lua监测、分析或改变SQL DML，如连接控制，过滤，实现读写分离和负载均衡。其中，MySQL Proxy实现“读写分离”的基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。

接下来介绍如何安装与使用MySQL Proxy。

1. 安装

下载源码包，获取Lua测试脚本

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5.tar.gz
tar zxvf mysql-proxy-0.8.5.tar.gz

下载二进制包，安装

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

2. 配置

添加环境变量到~/.bashrc

PATH=/usr/local/mysql-proxy/bin:$PATH

创建MySQL Proxy配置文件 /usr/local/mysql-proxy/etc/master.conf， 注意将master.conf文件权限修改成0660

[mysql-proxy]
log-file = /var/log/mysql-proxy.log
log-level = message
proxy-backend-addresses = 127.0.0.1:3306

3. 测试

启动Mysql Proxy，添加--daemon将以守护进程方式运行，默认MySQL Proxy监听4040端口：

mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.lua

通过MySQL客户端直连MySQL Proxy即可：

[root@~/software/mysql-proxy-0.8.5/examples]# /usr/local/mysql/bin/mysql --host=127.0.0.1 --port=4040 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 5.5.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

测试Lua脚本，测试脚本test.php如下：

$db = mysql_connect(‘127.0.0.1:4040‘, ‘root‘, ‘******‘);
if (!$db) {
    exit(-1);
}

mysql_select_db(‘test‘, $db);
$result = mysql_query(‘SELECT * FROM test.test‘, $db);

while ($row = mysql_fetch_assoc($result)) {
    var_dump($row);
}

执行test.php：php test.php，其输出如下：

array(1) {
  ["id"]=>
  string(1) "1"
}
array(1) {
  ["id"]=>
  string(1) "2"
}

查看MySQL Proxy日志如下：

[root@/usr/local]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.lua
we got a normal query: SELECT * FROM test.test
query-time: 0.3ms
response-time: 0.31ms

4. 更多阅读

1. MySQL Proxy
2. Appendix A MySQL Proxy FAQ
3. 转MySQL Proxy的几篇文章

原文：http://blog.csdn.net/billfeller/article/details/42304671