Ubuntu10下如何搭建MySQL Proxy读写分离探讨
发布时间:2024-01-20 08:04:43
一、MySQL-Proxy基础
MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序,它可以监测、分析或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分析,查询过滤和修改等等。
(Figure1:MySQL Proxy)
MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. The proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:
load balancing and fail over handling query analysis and logging SQL macros query rewriting executing shell commands
One of the more powerful features of MySQL Proxy is the ability to do "Read/Write Splitting". The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.
MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序,它还支持嵌入性脚本语言Lua。这个代理可以用来分析、监控和变换(transform)通信数据,它支持非常广泛的使用场景:
负载平衡和故障转移处理 查询分析和日志 SQL宏(SQL macros) 查询重写(query rewriting) 执行shell命令
MySQL Proxy更强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询,而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。
二、实战过程
测试环境:Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log
192.168.1.147 proxy 代理 入口
192.168.1.126 master 主机 只写
192.168.1.145 slaver 从机 只读
程序上只需要链接到192.168.1.147,而192.168.1.126和192.168.1.145对于程序来说是透明的,你完全不需要理会,也不需要知道192.168.1.126和192.168.1.145,你对数据库的所有操作都只对192.168.1.147进行操作。
1.安装脚本lua
#apt-get install lua5.1
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的,因此需要安装lua。
2.安装配置MySQL-Proxy
#apt-get mysql-proxy
当前获取到的版本是:mysql-proxy 0.8.0(查看版本命令:#mysql-proxy -V)
3.修改rw-splitting.lua
#vim /usr/share/mysql-proxy/rw-splitting.lua
配置并使用rw-splitting.lua读写分离脚本,脚本目录是 /usr/share/mysql-proxy,修改读写分离脚本rw-splitting.lua,修改默认连接数,进行快速测试,如果不修改连接数的话要达到连接数为4时才会启用读写分离。
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
is_debug = false
}
end
这是因为mysql-proxy会检测客户端连接,当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。
4.新建文件夹/var/log/mysql-proxy/和文件mysql-proxy.log
#mkdir /var/log/mysql-proxy
#vi mysql-proxy.log
5.执行读写分离
#sudo mysql-proxy --proxy-read-only-backend-addresses=192.168.1.145:3306 --proxy-backend-addresses=192.168.1.126:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua >/var/log/mysql-proxy/mysql-proxy.log &
参数说明:
192.168.1.147 proxy 代理 入口
192.168.1.126 master 主机 只写
192.168.1.145 slaver 从机 只读
当运行sudo mysql-proxy 上面语句后,查询进程没有4040的时候,需要重启mysql ( sudo /etc/init.d/mysql restart) 之后再输入proxy设置。
6.查看进程端口
#netstat -ant
#netstat –ntl
(Figure2:端口)
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN
7.查看数据库链接
mysql> show processlist\G;
(Figure3:进程)
可以看到,产生了一个新连接。如果想杀掉某个链接,可以使用mysql>help kill查看kill的帮助信息,杀掉36进程的命令:mysql>kill 36;
8.测试读写分离
1)在mysql-proxy机子进入MySQL
#mysql -u gaizai -p -P4040 -h 192.168.1.147
必须指定-h参数,不然报下面错误:
(Figure4:出错)
2)显示数据库列表:
mysql> show databases;
如果你是搭建MySQL-Proxy成功的话,你上面查看到的数据库列表应该是192.168.1.145服务器上的数据库列表。(可以在145和126分别创建不同的数据库进行测试)
3)进入测试数据库:
mysql> use weibo;
4)查询表记录:
mysql>select * from blog;
5)插入一条记录:
mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES('10','fefef','fefef','efef',NOW(),'http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html',NULL,'0','0','33333333',NOW());
6)查询表记录:
mysql>select * from blog;
对比两次查询表的记录,看记录是否有变化,我们插入了数据(确认插入成功),但两次的数据是没有变化的,这就对了,这就是读写分离了(我们读的是145的数据库,插入的是126的数据库,而我们的145与126又没有设置Replication;如果之前设置了,请先停止后进行测试)
注:有时候mysql_proxy(38)库里会显示出数据,重启系统系统,重新启动mysql后就没有此现象了。
7)进入主写服务器(192.168.1.126) 查看数据
#mysql -u gaizai -p -h 192.168.1.126
mysql> use weibo;
mysql>select * from blog;
可以查看已经写入了一条记录。
8)进入从读服务器(192.168.1.145)
#mysql -u gaizai -p -h 192.168.1.145
mysql> use weibo;
mysql>select * from blog;
因为没有数据显示,说明只能读,不能写。
在使用工具SQLyog执行查询时,在Proxy服务器上会自动显示下面的信息:
(Figure5:信息)
9.MySQL-Proxy+Replication
上面的测试只是测试了插入数据后,在没有进行Master与Slave的Replication设置的情况下,读取Master与Slave的数据是不同,如果想达到Figure1的效果,我们还需要设置Master与Slave之间的数据复制(Replication),详情请参考:Ubuntu10下MySQL搭建Master Slave
三、MySQL-Proxy命令
帮助命令:$mysql-proxy --help-all
查看下MySQL Proxy的版本:$ mysql-proxy -V
编译启动脚本:$vi /etc/init.d/mysql-proxy
启动命令:$ /etc/init.d/mysql-proxy start
停止命令:$ /etc/init.d/mysql-proxy stop
重启命令:$ /etc/init.d/mysql-proxy restart
四、注意事项
1.在启动mysql-proxy的时候,可以把启动命令保存为文件:
建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表。
杀掉mysql-proxy进程:# killall mysql-proxy
新建一个文件:# vi /etc/mysql-proxy.cnf
在文件中输入两个分隔符中间的内容:
------------------------------------------------------
[mysql-proxy]
admin-username=viajarchen
admin-password=123123
admin-lua-script = /usr/share/mysql-proxy//admin-sql.lua
proxy-backend-addresses=192.168.1.126:3306
proxy-read-only-backend-addresses=192.168.1.145:3306
proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua
log-file=/var/tmp/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true
max-open-files=1024
------------------------------------------------------
设置权限:# chmod 660 /etc/mysql-proxy.cnf
或者#chmod +x /etc/init.d/mysql-proxy
设置启动文件:# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
查看信息:# ps -ef | grep mysql-proxy | grep -v grep
root 1869 1 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 1870 1869 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
查看日志:# tail -50f /var/tmp/mysql-proxy.log
2.mysql-proxy参数
--admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;
-P, --proxy-address=<host:port> 是mysql-proxy 服务器端的监听端口, 缺省是4040;
-r, --proxy-read-only-backend-addresses=<host:port> 只读Slave的地址和端口, 缺省为不设置;
-b, --proxy-backend-addresses=<host:port> 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;
--defaults-file=<file>配置文件, 可以把mysql-proxy的参数信息置入一个配置文件里;
--daemon mysql-proxy以守护进程方式运行
--keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy。
–log-level=debug定义log日志级别,由高到低分别有
(error|warning|info|message|debug)
--proxy-lua-script=file指定一个Lua脚本程序来控制mysql-proxy的运行和设置,这个脚本在每次新建连接和脚本发生修改的的时候将重新调用。
--max-open-files:指定最大档案开启数为1024,否则会有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log讯息出现。
3.当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时,,需要首先通过 stop slave 来停止从服务器,然后再进行测试读写分离。
4.MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本,但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本,如果有lua脚本编程基础的话,可以在这个脚本的基础上再进行优化;
5.MySQL-Proxy实际上非常不稳定,在高并发或有错误连接的情况下,进程很容易自动关闭,因此打开–keepalive参数让进程自动恢复是个比较好的办法,但还是不能从根本上解决问题,因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用,虽然比较低效但却能保证稳定性;
6.一主多从的架构并不是最好的架构,通常比较优的做法是通过程序代码和中间件等方面,来规划,比如单双server-id号分开写入等方式来实现两个或多个主服务器;
7.MySQL-Cluster 的稳定性也不是太好;
8.Amoeba for MySQL 是一款优秀的中间件软件,同样可以实现读写分离,负载均衡等功能,并且稳定性要大大超过MySQL-Proxy,建议大家用来替代MySQL-Proxy,甚至MySQL-Cluster。
9.mysql proxy不支持old_password。另外也可以通过查看密码长度的方式来判断:select length(password) from mysql.user如果长度为16位则是old_password无疑。
10. 安装了mysql-proxy实现读写分离,有master x 1, slave x 2。为了测试failover,停掉了一个slave,然后mysql-proxy会一直报错,提示无法连接。这个情况比单点的mysql还糟糕,挂掉一个就全挂掉!mysql的工程师给提供了一段代码,替换掉:
src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函数可以解决这个问题。network-mysqld-proxy-function.c文件。
(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会去读取slave的数据)
11. 如果在mysql-proxy的机器上也安装了mysql的话,新手就会在这个时候混乱了,到底要如何进行测试和链接呢?比如使用命令:#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陆本机的4040端口,使用gaizai帐号,这个帐号可以不是本地mysql的帐号,这样就比较容易区分了。
12. 在上述环境中,mysql-proxy、mysql-master、mysql-slave三台服务器均存在单点故障。为了避免mysql-proxy单点隐患有两种方法:一种方法是mysql-proxy配合keepalived做双机,另一种方法是将mysql-proxy和应用服务安装到同一台服务器上;为了避免mysql-master单点故障可以使用DRBD+heartbear做双机;为了避免mysql-slave单点故障可以添加多台mysql-slave,mysql-proxy会自动屏蔽后端发生故障的mysql-slave。
13. 用sysbench (或者super-smack)测试mysql性能:
#sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000 --mysql-socket=/tmp/mysql.sock --mysql-password=123456 --mysql-user=gaizai --mysql-host=192.168.1.126 --mysql-db=weibo --num-threads=15 prepare
#sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000 --mysql-socket=/tmp/mysql.sock --mysql-password=123456 --mysql-user=gaizai --mysql-host=192.168.1.126 --mysql-db=weibo --oltp-test-mode=complex run
14. 关于mysql-proxy的启动和关闭的shell脚本的编写:
15. 读写分离不能回避的问题之一就是延迟,可以考虑Google提供的SemiSyncReplicationDesign补丁。
16. MySQL-Proxy缺省使用的是4040端口,如果你想透明的把3306端口的请求转发给4040的话,那么可以:iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 4040如果想删除这条规则,可以把上面例子中的-I换成-D。参考链接
17. 当使用bigint 时,mysql_insert_id()存在问题,详情见手册,不过对于大多数人而言,bigint基本不会遇到,所以你可以无视这个问题)注:对于这两个问题,官方BUG库里有人给出了相应的补丁。
五、错误
在执行命令的时候出现了下面的错误:
(Figure6:错误信息)
could not raise RLIMIT_NOFILE to 8192
这个一个警告级别的错误,意思是MySQL Proxy在你的系统上不能把open files limit提升到8192,不过没关系的,MySQL Proxy依然好好的运行在你的电脑上。
可以通过设置启动--max-open-files参数解决。
MySQL Proxy安装和使用(一)
mysql proxy master and slave test
加入--max-open-files=8192后报下面的错误:
(Figure7:错误信息)
六、疑问与解答
1.当slave宕机后,mysql-proxy是如何读取的?(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会重新去读取slave的数据。有时可能需要重启下mysql-proxy)
2.如何知道mysql-proxy当前执行的select是在哪台机器上执行的?
3.当slave宕机一段时间后,如果再次同步master的缺失的数据?
4.当配置中设置了proxy-read-only-backend-addresses=192.168.1.145:3306
,192.168.1.147:3306类似这样的两个slave,如果两个slave的数据不同步,那么是怎么读取数据的?# tail -50f /var/tmp/mysql-proxy.log测试
5.生产环境中除了进行程序调试外,其它不要开启mysql查询日志,因为查询日志记录了客户端的所有语句,频繁的IO操作将会导致mysql整体性能下降。如何设置呢?
6.mysql-proxy.cnf文件中的管理员帐号和密码有什么用?使用命令进入管理
mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密码是123123
mysql> select * from proxy_connections;
mysql> select * from proxy_config;
(Figure8:信息)
7.关于mysql-proxy的启动和关闭的shell脚本的编写?测试
8.对于/usr/share/mysql-proxy/rw-splitting.lua脚本中的
local min_idle_connections = 4 local max_idle_connections = 8应该如何理解?min的话就是要达到这个值的时候才会读写分离,那么max的是什么意思呢?最大能有8个链接?
9.mysqld是什么意思?是mysql的守护进程!
10.HAProxy和keepalived怎么一起搭建使用?能解决什么问题?
猜你喜欢
- 技术背景在上一篇博客中,我们介绍了使用量子计算模拟器ProjectQ去生成一个随机数,也介绍了随机数的应用场景等。但是有些时候我们希望可以打
- 这里就不给大家废话了,直接上代码,代码的解释都在注释里面,看不懂的也别来问我,好好学学基础知识去!# -*- coding: utf-8 -
- 本文实例讲述了Python实现按特定格式对文件进行读写的方法。分享给大家供大家参考,具体如下:#! /usr/bin/env python#
- 在进行数据库的查询时,会经常遇到这样的情况:例如想在一个用户数据库中查询他的用户名和他的密码,但恰好该用户使用的名字和密码中有特殊的字符,例
- 看到了很多关于如何读出图片的长度的高度的方法,其实都不实用,大多都是通过图片的大小来判断的,图片的种类众多,通过大小来判断难免要制造很多的代
- 要做好一个HTML邮件,说简单,还真不怎么复杂,说它复杂,其实也不难。-_-!!以前写过类似的[ 关于HTML邮件的总结 ],最近又犯愁,就
- 1. 游戏循环和游戏时钟之前几篇文章中,飞机大战游戏中的英雄飞机已经被绘制到屏幕上了,怎么能够让飞机移动呢?1.1 游戏中的动画实现原理跟电
- 一 、精灵(Sprite),屏幕上的对象。精灵组是精灵的组合。创建空的精灵组对象:精灵组可以对其中的所有精灵调用它们各自的更新方法(self
- 一、安装PILPIL是Python Imaging Library简称,用于处理图片。PIL中已经有图片高斯模糊处理类,但有个bug(目前最
- 本来是只用Tenorflow的,但是因为TF有些Numpy特性并不支持,比如对数组使用列表进行切片,所以只能转战Pytorch了(pytor
- 需 求 分 析 1、读取指定目录下的所有文件2、读取指定文件,输出文件内容3、创建一个文件并保存到指定目录实 现 过 程Python写代码简
- 通过观察执行计划,发现之前的执行计划在很多大表连接的部分使用了Hash Join,由于涉及的表中数据众多,因此查询优化器选择使用并行执行,速
- 前面的话发布一个版本时,我们通常先在版本库中打一个标签(tag)。这样,就唯一确定了打标签时刻的版本。将来无论什么时候,取某个标签的版本,就
- 前言学学Python中操纵JSON的知识。学完本文,你可以学到如下内容:1、JSON是什么?2、JSON与XML的优劣差异?3、将Pytho
- PDOStatement::executePDOStatement::execute — 执行一条预处理语句(PHP 5 >= 5.1
- [摘要]了解如何充分利用SQL Server 2000的全文搜索功能。本文包含有关实现最大吞吐量和最佳性能的几点提示和技
- 目录它有什么作用?安装方法简介它有什么作用?它提供了一种将包括Python对象在内的结构化数据打包为JSON可序列化格式的机制。通过向相应的
- 一、引言属性将值与类,结构体,枚举进行关联。Swift中的属性分为存储属性和计算属性两种,存储属性用于存储一个值,其只能用于类与结构体,计算
- 1.Python3读取hdf文件最开始使用Python导入pyhdf包的时候是可以的,但是当导入pyhdf.SD的时候就出现了以下问题:我查
- 最近在做的一个项目中需要使用到HTML5中引入的WebSocket技术,本来以为应该很容易就能搞定,谁知道在真正上手开发了以后才发现有很多麻