首页 > 代码库 > 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即可;
其主要应用场景:
- 充当MySQL连接池。
- 通过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. 更多阅读
- MySQL Proxy
- Appendix A MySQL Proxy FAQ
- 转MySQL Proxy的几篇文章
MySQL Proxy简介
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。