首页 > 代码库 > MySQL Proxy简介

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的几篇文章

MySQL Proxy简介