配置环境：
主服务器：192.168.85.144 node1.a.com
从服务器：192.168.85.145 node2.a.com
proxy服务器：192.168.85.128 proxy.a.com
已经搭建好了mysql复制环境(一主一从);
一.基本配置
1.下载mysql-proxy包
这里使用的是二进制包；
也可以直接安yum install，只需要配置好EPEL源即可；
下载地址：
或者
2.创建使用mysql-proxy的用户
[root@proxy ~]# useradd -r proxy
[root@proxy ~]# id proxy
uid=496(proxy) gid=493(proxy) groups=493(proxy)
3.解压并创建链接
[root@proxy ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local/
[root@proxy ~]# cd /usr/local/
[root@proxy local]# ln -sv mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit mysql-proxy
`mysql-proxy' -> `mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit'
4.关于mysql-proxy用法
[root@proxy mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --help-all
proxy-module代理模块：
-P, --proxy-address=
#监听的mysql-proxy服务器的IP和端口，默认端口为4040；
-r, --proxy-read-only-backend-addresses= #后端只读服务器(从)的IP和地址；
-b, --proxy-backend-addresses= #后端可读可写服务器（主）的IP和地址，默认为127.0.0.1:3306
--proxy-skip-profiling #disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 #fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script= #lua脚本文件路径(default: not set)
--no-proxy #不起用proxy模块(default: enabled)
--proxy-pool-no-change-user #don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
--proxy-connect-timeout #连接超时时长(default: 2.0 seconds)
--proxy-read-timeout #读超时时长(default: 8 hours)
--proxy-write-timeout #写超时时长(default: 8 hours)
Application Options:
--defaults-file= #默认读取的配置文件；可以为/etc/mysql-proxy.conf或者/etc/my.cnf中定义[mysql-proxy]段，当使用配置文件时，可以写入proxy module中的选项，如proxy-address=192.168.85.128:4040等；
--verbose-shutdown Always log the exit code when shutting down
--daemon #以守护进程模式运行mysql-proxy；
--user= #mysql-proxy的使用者；
--basedir= #指定使用的mysql-proxy配置文件的路径；
--pid-file= #PID file in case we are started as daemon
--plugin-dir= #加载的插件的目录路径(加载目录下的所有插件);
--plugins= #加载的指定插件名称；插件位于/usr/local/mysql-proxy/lib/mysql-proxy/plugins/下；
--log-level=(error|warning|info|message|debug) #日志级别；
--log-file= #记录日志到文件中；
--log-use-syslog #记录日志到系统日志文件中；
--log-backtrace-on-crash #try to invoke debugger on crash
--keepalive #如果mysql-proxy服务器宕机，会尝试重启；
--max-open-files #最大打开文件数(ulimit -n)
--event-threads #事件句柄打开的线程数(default: 1)
--lua-path=<...> #set the LUA_PATH
--lua-cpath=<...> #set the LUA_CPATH
5.启动mysql-proxy
[root@proxy mysql-proxy]# mysql-proxy --daemon --plugins=proxy --log-level=debug --log-file=/var/log/mysql-proxy.log --proxy-read-only-backend-addresses=192.168.85.145:3306 --proxy-backend-addresses=192.168.85.144:3306
查看日志：
[root@proxy mysql-proxy]# tail /var/log/mysql-proxy.log
2015-12-31 10:11:27: (critical) plugin proxy 0.8.5 started
2015-12-31 10:11:27: (debug) max open file-descriptors = 1024
2015-12-31 10:11:27: (message) proxy listening on port :4040
2015-12-31 10:11:27: (message) added read/write backend: 192.168.85.144:3306
2015-12-31 10:11:27: (message) added read-only backend: 192.168.85.145:3306
6.主服务器上赋予权限给登录proxy服务器的账户
mysql> grant all on *.* to 'root'@'%' identified by 'redhat';
然后找一个mysql客户端登陆proxy服务器：
[root@node2 ~]# mysql -uroot -p -h 192.168.85.128 -P 4040
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
mysql>
测试：
如果执行一个操作，proxy路由到了从服务器，那么只有从服务器上有；如果路由到了主服务器，那么主从服务器都会有；
proxy上创建一个数据库：
mysql> create database testdb;
主从服务器上显示：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
当然，这只是一个偶然的情况，因为mysql-proxy本身并不能实现读写分离，而是要依靠lua脚本来完成，所以，还需配置脚本才能实现真正的读写分离；
7.使用脚本实现读写分离
先[root@proxy mysql-proxy]# killall mysql-proxy
lua脚本在/usr/local/mysql-proxy/share/doc/mysql-proxy/下，其中rw-splitting.lua脚本是用来实现读写分离的；
[root@proxy ~]# mysql-proxy --daemon --plugins=proxy --log-level=debug --log-file=/var/log/mysql-proxy.log --proxy-read-only-backend-addresses=192.168.85.145:3306 --proxy-backend-addresses=192.168.85.144:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
8.使用脚本提供管理接口
8.1编辑admin.lua文件(内容见附录)；
8.2重新启动mysql-proxy：
[root@proxy mysql-proxy]# mysql-proxy --daemon --plugins=proxy --log-level=debug --log-file=/var/log/mysql-proxy.log --proxy-read-only-backend-addresses=192.168.85.145:3306 --proxy-backend-addresses=192.168.85.144:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username=admini --admin-password=admini --admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
8.3查看日志
[root@proxy mysql-proxy]# tail /var/log/mysql-proxy.log
2015-12-31 10:50:41: (critical) plugin proxy 0.8.5 started
2015-12-31 10:50:41: (critical) plugin admin 0.8.5 started
2015-12-31 10:50:41: (debug) max open file-descriptors = 1024
2015-12-31 10:50:41: (message) proxy listening on port :4040
2015-12-31 10:50:41: (message) added read/write backend: 192.168.85.144:3306
2015-12-31 10:50:41: (message) added read-only backend: 192.168.85.145:3306
2015-12-31 10:50:41: (message) admin-server listening on port :4041 #管理接口为4041
8.4通过管理接口连接
[root@node2 ~]# mysql -u admini -h192.168.85.128 -p -P 4041
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
mysql>
8.5通过管理接口连接上后的mysql只有如下命令才有效
mysql> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
8.6测试
8.6.1 写操作测试：
[root@node1 ~]# mysql -u root -p -h192.168.85.128 -P 4040 -e 'create database hellodb;'
Enter password:
mysql> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | up | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
这说明了，刚刚的写操作一定是在主服务器上执行的；
8.6.2 读操作测试：
[root@node1 ~]# mysql -u root -p -h192.168.85.128 -P 4040 -e 'show tables from test;'
Enter password:
mysql> select * from backends;
+-------------+---------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+-------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | up | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | up | ro | NULL | 0 |
+-------------+---------------------+-------+------+------+-------------------+
如果执行命令后从状态仍未unknown这不代表出错了，多尝试几次，毕竟，主是可读可写的，可能读操作被路由到了主而不是从；
二.简化配置
1.每一次配置proxy都要写那么一大段毕竟是很辛苦的，而且易出错，所以可以采用配置文件的方式来简化配置
[root@proxy ~]# cat /etc/mysql-proxy.cnf
[mysql-proxy]
plugins=proxy
log-level=debug
log-file=/var/log/mysql-proxy.log
proxy-read-only-backend-addresses=192.168.85.145:3306
proxy-backend-addresses=192.168.85.144:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
plugins=admin
admin-username=admini
admin-password=admini
admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
proxy-address=192.168.85.128:4040
[root@proxy ~]# chmod 660 /etc/mysql-proxy.cnf #权限要设置为660
先关闭proxy:
[root@proxy ~]# killall mysql-proxy
查看日志：
[root@proxy ~]# mysql-proxy --daemon --defaults-file=/etc/mysql-proxy.cnf
[root@proxy ~]# tail /var/log/mysql-proxy.log
2015-12-31 11:15:47: (message) Initiating shutdown, requested from signal handler
2015-12-31 11:15:48: (message) shutting down normally, exit code is: 0
2015-12-31 11:15:58: (critical) plugin admin 0.8.5 started
2015-12-31 11:15:58: (debug) max open file-descriptors = 1024
2015-12-31 11:15:58: (message) admin-server listening on port :4041
好吧，这里有些问题，4040端口没打开；而且尝试了多次，都有问题！先记录一下，如果解决了再来更新；
问题地址：http://blog.chinaunix.net/uid-30212356-id-5589069.html
2.为mysql-proxy服务提供启动脚本
[root@proxy ~]# vim /etc/rc.d/init.d/mysql-proxy
[root@proxy ~]# chmod +x /etc/rc.d/init.d/mysql-proxy
[root@proxy ~]# chkconfig --add mysql-proxy
为服务脚本提供配置文件：
[root@proxy ~]# cat /etc/sysconfig/mysql-proxy
ADMIN_USER="admini"
ADMIN_PASSWORD="admini"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS="192.168.85.128:4040"
PROXY_USER="proxy"
PROXY_OPTIONS="--daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-read-only-backend-addresses=192.168.85.145:3306 --proxy-backend-addresses=192.168.85.144:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin"
[root@proxy ]# service mysql-proxy start
Starting /usr/local/mysql-proxy/bin/mysql-proxy: [ OK ]
查看日志：
[root@proxy ~]# tail /var/log/mysql-proxy.log
2015-12-31 11:49:54: (critical) plugin proxy 0.8.5 started
2015-12-31 11:49:54: (critical) plugin admin 0.8.5 started
2015-12-31 11:49:54: (debug) max open file-descriptors = 1024
2015-12-31 11:49:54: (message) proxy listening on port 192.168.85.128:4040
2015-12-31 11:49:54: (message) added read/write backend: 192.168.85.144:3306
2015-12-31 11:49:54: (message) added read-only backend: 192.168.85.145:3306
2015-12-31 11:49:54: (message) admin-server listening on port :4041
2015-12-31 11:49:54: (debug) now running as user: proxy (496/493)
测试：
1.初始状态
mysql> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
执行写操作：
[root@node1 ~]# mysql -u root -p -h192.168.85.128 -P 4040 -e 'create database hellodb2;'
Enter password:
mysql> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | up | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
执行读操作：
mysql> select * from backends;
+-------------+---------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+-------+------+------+-------------------+
| 1 | 192.168.85.144:3306 | up | rw | NULL | 0 |
| 2 | 192.168.85.145:3306 | up | ro | NULL | 0 |
+-------------+---------------------+-------+------+------+-------------------+
三.附录
1.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[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
2.服务启动脚本内容
#!/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="admini"
ADMIN_PASSWD="admini"
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="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
阅读(782) | 评论(0) | 转发(0) |
