mysql-proxy读写分离
时间:2016-09-11 18:43 来源:未知 作者:liangzh 点击:次
读写分离是将前端的读写请求，分离到不同的服务器。配置mysql的读写分离，首先要做主从同步。写入数据一般是主服务器，读取数据是从服务器，从服务器可以有多台，这样可以实现读取数据库的
负载均衡，同事降低了主数据的磁盘的压力。
1、源码安装时，MySQL proxy的依赖关系：
libevent 1.x or higher (1.3b or later is preferred).
lua 5.1.x or higher.lua-devel
glib2 2.6.0 or higher. glib2-devel
pkgconfig.
libtool 1.5 or higher. libtool-devel
MySQL 5.0.x or higher developer files.
2、安装
# tar zxf mysql-proxy-0.8.2.tar.gz
# cd mysql-proxy-0.8.2
# ./configure
# ./configure --prefix=/usr/local/mysql-proxy/
# make
#make install
默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。
vim /etc/profile #添加环境变量
PATH=/usr/local/mysq-proxyl/bin:$PATH export PATH
soure /etc/profile
3、配置指令
mysql proxy的各配置参数请参见官方文档，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html
1>添加配置文件
vim /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS="172.18.109.233:3306"
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --keepalive --event-threads=8 --log-level=debug --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=172.18.109.236:3306 --proxy-read-only-backend-addresses=172.18.109.235:3306 --proxy-read-only-backend-addresses=172.18.109.239:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
注意：默认的日志文件是在/var/log/meassage中，要更改日志路径可以添加--log-file="/var/log/mysql-proxy.log"
添加admin功能时要先添加用户名和密码以及admin.lua脚本，要按顺序写，否则会出错。
设置变量值是要用引号，而直接设置参数时可不加引号。
2>为mysql-proxy提供SysV服务脚本
vim /etc/init.d/mysql-proxy
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL
给予执行权限，而后加入到服务列表。
# chmod +x /etc/rc.d/init.d/mysql-proxy
# chkconfig --add mysql-proxy
4、复制如下内容建立admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中。
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ --]]
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[i]
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
6.设置脚本参数
（1）、如果日志中提示 (debug) [network-mysqld.c:1134]: error on a connection (fd: -1 event: 0). closing client connection.
vim /usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua
可以修改 rw-splitting.lua中的min_idle_connections = 4和max_idle_connections = 8的只，将其调大。初始测试时可以调小，方便查看读写分离效果。
（2）、如果遇到乱码需要调整后端mysql的设置的字符集
[mysqld]
skip-character-set-client-handshake
init-connect = 'SET NAMES utf8'
character_set_server = utf8
7.启动mysql-proxy
[root@node1 mysql-proxy]# service mysql-proxy start
正在启动 /usr/local/mysql-proxy/bin/mysql-proxy： [确定]
[root@node1 mysql-proxy]# tail /var/log/messages
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (debug) chassis-unix-daemon.c:121: we are the child: 8961
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (critical) plugin proxy 0.8.3 started
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (critical) plugin admin 0.8.3 started
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (debug) max open file-descriptors = 1024
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (message) proxy listening on port 172.18.109.233:3306
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (message) added read/write backend: 172.18.109.236:3306
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (message) added read-only backend: 172.18.109.235:3306
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (message) added read-only backend: 172.18.109.239:3306
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (debug) now running as user: mysql-proxy (495/495)
Aug 28 15:00:58 node1 mysql-proxy: 2016-08-28 15:00:58: (message) chassis-event-thread.c:475: starting 7 threads
[root@node1 mysql-proxy]# netstat -tnlp |grep mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 8961/mysql-proxy
tcp 0 0 172.18.109.233:3306 0.0.0.0:* LISTEN 8961/mysql-proxy
8.测试读写分离效果
多准备结果客户端分别登录管理端口和mysql服务端口，如果客户端不够可以编写网页脚本测试。
[root@node1 ~]# mysql -u admin -h 172.18.109.233 -P 4041 -p # 登录管理端口，密码账号为配置脚本中的。
mysql> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 172.18.109.236:3306 | unknown | rw | NULL | 0 |
| 2 | 172.18.109.235:3306 | unknown | ro | NULL | 0 |
| 3 | 172.18.109.239:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
[root@node1 ~]# mysql -u admin -h 172.18.109.233 -p 登录代理mysql服务器，用户名和授权要在后端mysql上创建
在后端主mysql创建授权用户：mysql> grant all on *.* to admin@'172.18.109.%' identified by 'admin';
mysql> select * from slave.person;
+----+--------+-----+-----------+
| id | name | age | info |
+----+--------+-----+-----------+
| 1 | lzh | 21 | Lawyer |
| 2 | proxy | 27 | secretary |
| 3 | Dale | 22 | cook |
| 4 | Edison | 28 | singer |
| 5 | xie | 23 | dfl |
| 6 | team | 23 | dfl |
| 7 | Ekanns | 27 | zhj |
| 8 | node3 | 27 | zhj |
+----+--------+-----+-----------+
查询询的结果是后端服务器的数据，说明代理成功。
接下来测试读写分离的效果
注意：如果数据库没有开启忽略域名解析可能会出现登录异常。
Enter password:
ERROR 1045 (28000): Access denied for user 'admin'@'node1.test.com' (using password: YES)
mysql> grant all on *.* to admin@172.18.109.% identified by 'admin';
在主数据库配置文件添加：skip_name_resolve=on
mysql> show global variables like '%name%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| hostname | target.test.com |
| lc_time_names | en_US |
| lower_case_table_names | 0 |
| skip_name_resolve | ON |
+------------------------+-----------------+
编写网页测试脚本
[root@node3 html]# vim test.php
<?php
//连接函数
function mysql_proxy_connect($is_close=true){
$link = mysql_connect("172.18.109.233", "admin", "admin", true);
if (!$link){
die('Could not connect: ' . mysql_error());
}
if ($is_close){
echo ' close connection ... ';
mysql_close($link);
}
return true;
}
//测试函数
function test($sum, $is_close=true){
for($i=0; $i<$sum; $i++){
echo "Connect mysql-proxy ". $i .".. ";
if (mysql_proxy_connect($is_close)){
echo 'Succeed</br>';
} else {
echo "Failed ";
}
}
}
test(10000);
?>
通过浏览器访问测试
http://172.18.109.239/test.php
Connect mysql-proxy 0.. close connection ... Succeed
Connect mysql-proxy 1.. close connection ... Succeed
Connect mysql-proxy 2.. close connection ... Succeed
Connect mysql-proxy 3.. close connection ... Succeed
Connect mysql-proxy 4.. close connection ... Succeed
Connect mysql-proxy 5.. close connection ... Succeed
Connect mysql-proxy 6.. close connection ... Succeed
Connect mysql-proxy 7.. close connection ... Succeed
Connect mysql-proxy 8.. close connection ... Succeed
Connect mysql-proxy 9.. close connection ... Succeed
Connect mysql-proxy 10.. close connection ... Succeed
Connect mysql-proxy 11.. close connection ... Succeed
Connect mysql-proxy 12.. close connection ... Succeed
Connect mysql-proxy 13.. close connection ... Succeed
查看读写分离效果：
mysql> select * from backends;
+-------------+---------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+-------+------+------+-------------------+
| 1 | 172.18.109.236:3306 | up | rw | NULL | 0 |
| 2 | 172.18.109.235:3306 | up | ro | NULL | 0 |
| 3 | 172.18.109.239:3306 | up | ro | NULL | 0 |
+-------------+---------------------+-------+------+------+-------------------+
说明读写分离成功。
9.其他配置方法
1>命令配置法
示例：
mysql-proxy --daemon --log-level=info --plugins=proxy --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" --log-file="/var/log/mysql-proxy.log" --proxy-address="172.18.109.239:3306" --proxy-read-only-backend-addresses="172.18.109.235:3306" --proxy-backend-addresses="172.18.109.236:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
# mysql-proxy --daemon --log-level=info --log-use-syslog --proxy-address="172.18.109.239:3306" --proxy-read-only-backend-addresses="172.18.109.235:3306" --proxy-backend-addresses="172.18.109.236:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=proxy --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"--log-file="/var/log/mysql-proxy.log"
mysql-proxy --daemon --log-level=info --plugins=proxy --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" --log-file="/var/log/mysql-proxy.log" --proxy-address="172.18.109.233:3306" --proxy-read-only-backend-addresses="172.18.109.235:3306" --proxy-backend-addresses="172.18.109.236:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
添加admin插件的顺序：--plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
注意：添加命令可以不用引号，但要注意顺序，尤其是admin插件。
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 ———— 进程文件名；
2>配置文件载入
vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=mysql-proxy
keepalive=true
daemon=true
log-level=info
plugins=admin
admin-address=172.18.109.233:4041
admin-username=admin
admin-password=admin
admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
proxy-address=172.18.109.233:3306
proxy-backend-addresses=172.18.109.236:3306
proxy-read-only-backend-addresses=172.18.109.235:3306
plugins=proxy
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/var/log/mysql-proxy.log
#chmod 660 /etc/mysql-proxy.cnf
#mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
（注意mysql-proxy.cnf的权限要配置为660，否则启动会报错。）
启动配置文件内容：mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
通过文件配置的方式同时启动admin和proxy插件会发生异常，可能只能启动其中一个，建议不启用admin插件。
生产环境中也不建议用配置文件的方式启动。
(责任编辑：liangzh) |