介绍一下mysql-proxy:
MySQL-Proxy
MySQL-Proxy是一个处于你的client端和MySQL server端之间的简单程序,它可以监测、分析或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分析,查询过滤和修改等等。
MySQL-Proxy就是这么一个中间层代理,简单的说,MySQL-Proxy就是一个连接池,负责将前台应用的连接请求转发给后台的数据库,并且通过使用lua脚本,可以实现复杂的连接控制和过滤,从而实现读写分离和负载平衡。对于应用来说,MySQL-Proxy是完全透明的,应用则只需要连接到MySQL-Proxy的监听端口即可。当然,这样proxy机器可能成为单点失效,但完全可以使用多个proxy机器做为冗余,在应用服务器的连接池配置中配置到多个proxy的连接参数即可。
MySQL-Proxy更强大的一项功能是实现“读写分离”,基本原理是让主数据库处理事务性查询,让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。
环境准备:
操作系统:redhat 6.4
MySQL版本:mysql-5.6.10-linux-glibc2.5-i686.tar.gz
mysql-proxy版本:mysql-proxy-0.8.3-linux-rhas4-x86.tar.gz
lua版本:lua-5.2.0.tar.gz
mysql-proxy服务器:10.10.5.100
mysql读写服务器:10.10.5.10
mysql读服务器:10.10.5.11
mysql-proxy的配置选项
mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类
–help
–help-admin
–help-proxy
–help-all ———— 以上四个选项均用于获取帮助信息;
–proxy-address=host:port ———— 代理服务监听的地址和端口;
–admin-address=host:port ———— 管理模块监听的地址和端口;
–proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口;
–proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口;
–proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本;
–daemon ———— 以守护进程模式启动mysql-proxy;
–keepalive ———— 在mysql-proxy崩溃时尝试重启之;
–log-file=/path/to/log_file_name ———— 日志文件名称;
–log-level=level ———— 日志级别;
–log-use-syslog ———— 基于syslog记录日志;
–plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件;
–user=user_name ———— 运行mysql-proxy进程的用户;
–defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径;其配置段使用[mysql-proxy]标识;
–proxy-skip-profiling ———— 禁用profile;
–pid-file=/path/to/pid_file_name ———— 进程文件名;
安装过程:
1.安装配置mysql-proxy,首先要安装lua
#yum -y install readline-level ncursea-dvel //解决依赖关系
#tar zxf lua-5.2.0.tar.gz -C /usr/local
#cd /usr/local/lua-5.2.0
#make
#make linux
#make install
2.安装mysql-proxy
#useradd -r mysql-proxy
#tar zxf mysql-proxy-0.8.3-linux-rhas4-x86.tar.gz -C /usr/local/
#cd /usr/local/
#ln -sv mysql-proxy-0.8.3-linux-rhas4-x86 mysl-proxy
#cd mysql-proxy
#vim /etc/profile.d/mysql-proxy.sh //修改PATH环境变量,让系统可以直接使用mysql的相关命令
export PATH=$PATH:/usr/local/mysql-proxy/bin
#. /etc/profile.d/mysql-proxy.s
#ls /usr/local/mysql-proxy
bin include lib libexec licenses share
#ls /usr/local/nysql-proxy/share/doc/mysql-proxy //存放脚本目录
active-transations.lua commit-obfuscator.lua rw-splitting.lua ………
3.启动mysql-proxy
#mysql-proxy –daemon –log-level=debug –log-file=/var/log/mysql-proxy.log –plugins=”proxy” –proxy-backend-addresses=”10.10.5.10:3306″ –proxy-read-only-backend-addresses=”10.10.5.11:3306″
#tail /var/log/mysql-proxy.log //查看日志是否启动成功
………….
2014-10-15 15:29:20: (message) proxy listioning on port :4040
2014-10-15 15:29:20: (message) added read/write backend :10.10.5.10:3306
2014-10-15 15:29:20: (message) added read-only backend :10.10.5.11:3306
4.主服务器上创建授权账号和密码
MySQL>grant all on *.* to proxyuser@’10.10.5.%’identfied by ‘proxyuser’;
mysql>fiush privileges;
5.测试是否能连接到mysql-proxy代理服务器
#MySQL -uproxyuser -p -h 10.10.5.100 –prot=4040
Enter password:
welcome to the mysql momitor. commands end with ; or \g.
……….
mysql> //成功连接
到此mysql-proxy完成,但是还不具备读写分离能力,读写分离功能主要靠lua脚本实现,接下来通过lua脚本实现读写分离
6.通过lua脚本实现读写分离,首先在proxy服务器上
#killall mysql-proxy
7.通过其他MySQL服务器,健在lua脚本来连接mysql-proxy代理服务器
#mysql-proxy –daemon –log-level=debug –log-file=/var/log/mysql-proxy.log –plugins=”proxy” –proxy-backend-addresses=”10.10.5.10:3306″ –proxy-read-only-backend-addresses=”10.10.5.11:3306″ –proxy-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua”
#tail /var/log/mysql-proxy.log
2014-10-15 15:29:20: (message) proxy listioning on port :4040
2014-10-15 15:29:20: (message) added read/write backend :10.10.5.10:3306
2014-10-15 15:29:20: (message) added read-only backend :10.10.5.11:3306
#netstat -anput |grep 4040 //查看日志和端口确认服务是否启动
8.可以为mysql-proxy提供一个管理接口,方便以后查看后端MySQL服务器的状态和访问类型,也是一个lua脚本,要放在和读写分离脚本同一个目录下
# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
function
set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or
"error"
}
end
function
read_query(packet)
if
packet:byte() ~= proxy.COM_QUERY then
set_error(
"[admin] we only handle text-based queries (COM_QUERY)"
)
return
proxy.PROXY_SEND_RESULT
end
local query = packet:sub(
2
)
local rows = { }
local fields = { }
if
query:lower() ==
"select * from backends"
then
fields = {
{ name =
"backend_ndx"
,
type = proxy.MYSQL_TYPE_LONG },
{ name =
"address"
,
type = proxy.MYSQL_TYPE_STRING },
{ name =
"state"
,
type = proxy.MYSQL_TYPE_STRING },
{ name =
"type"
,
type = proxy.MYSQL_TYPE_STRING },
{ name =
"uuid"
,
type = proxy.MYSQL_TYPE_STRING },
{ name =
"connected_clients"
,
type = proxy.MYSQL_TYPE_LONG },
}
for
i =
1
, #proxy.global.backends
do
local states = {
"unknown"
,
"up"
,
"down"
}
local types = {
"unknown"
,
"rw"
,
"ro"
}
local b = proxy.global.backends
rows[#rows +
1
] = {
i,
b.dst.name, -- configured backend address
states[b.state +
1
], -- the C-id
is
pushed down starting at
0
types[b.type +
1
], -- the C-id
is
pushed down starting at
0
b.uuid, -- the MySQL Server's UUID
if
it
is
managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() ==
"select * from help"
then
fields = {
{ name =
"command"
,
type = proxy.MYSQL_TYPE_STRING },
{ name =
"description"
,
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows +
1
] = {
"SELECT * FROM help"
,
"shows this help"
}
rows[#rows +
1
] = {
"SELECT * FROM backends"
,
"lists the backends and their state"
}
else
set_error(
"use 'SELECT * FROM help' to see the supported commands"
)
return
proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return
proxy.PROXY_SEND_RESULT
end
10.关闭proxy进程添加提供管理接口的选项
#killall mysql-proxy
#mysql-proxy –daemon –log-level=debug –log-file=/var/log/mysql-proxy.log –plugins=”proxy” –proxy-backend-addresses=”10.10.5.10:3306″ –proxy-read-only-backend-addresses=”10.10.5.11:3306″ –proxy-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua” –plugins=”admin” –admin-username=”admin” –admin-password=”admin” –admin-lus-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua”
新添加的管理接口选项
–plugins=admin ———— 在mysql-proxy启动时加载的插件;
–admin-username=”admin” 运行mysql-proxy进程管理的用户;
–admin-password=”admin” 密码
–admin-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua” 插件使用的配置文件路径;
#tail /var/log/mysql-proxy.log
2014-10-15 15:29:20: (message) proxy listioning on port :4040
2014-10-15 15:29:20: (message) added read/write backend :10.10.5.10:3306
2014-10-15 15:29:20: (message) added read-only backend :10.10.5.11:3306
#netstat -anput |grep 4040 //查看日志和端口确认服务是否启动
tcp 0 0.0.0.0:4040 0.0.0.0:*
tcp 0 0.0.0.0:4040 0.0.0.0:*
#mysql -uadmin -p -h10.10.5.100 –port=4041 //登录管理接口
mysql>selset * from backends; //查看读写状态
+————————+—————————+———–+————-+————+———————————+
| backend_ndx |addrese | state | type | uuid |connected_clients |
+————————+—————————+——— –+————+————-+———————————+
| 1 | 10.10.5.10:3306 |unknown | rw | null | 0 |
+————————+————————–+————+————+————-+———————————+
| 2 | 10.10.5.11:3306 |unkoown | ro | null | 0 |
+————————+—————————+———–+————-+————+———————————+
2 rows in set (0.00 sec)
11.创建mysql-proxy启动脚本和配置文件
#vim //etc/sysconfig/mysql-proxy
[mysql-proxy]
log-level=debug
log-file=/
var
/log/mysql-proxy.log
plugins=
"proxy"
proxy-backend-addresses=
"10.10.5.10:3306"
proxy-read-only-backend-addresses=
"10.10.5.11:3306"
proxy-lua-script=
"/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
plugins=admin
admin-username=
"admin"
admin-password=
"admin"
admin-lua-script=
"/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
# vim /etc/rc.d/init.d/mysql-proxy
12.赋予脚本执行权限
# chmod +x /etc/rc.d/init.d/mysql-proxy
# chkconfig --add mysql-proxy
#service mysql-proxy (start| stop | restart )
转载于:https://blog.51cto.com/9052689/1703401
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/109297.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...