MySQL-Proxy实现读写分离部署文档-原创手记-慕课网

实验拓扑图：

wKiom1N-tzDwO7z6AAC4RD5zGcU752.jpg

实验环境说明：MySQL主从已经部署完成，并且授权mysql-proxy主机数据库权限

系统 centos 6.4 _ x86_64

192.168.0.101 mysql-proxy

192.168.0.102 mysql-master

192.168.0.103 mysql-slave

部署开始：

检查依赖包

[root@shell tools]# rpm -q lua

lua-5.1.4-4.1.el6.x86_64

2.下载通用的压缩包 mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz 直接解压使用

useradd -r mysql-proxy

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz

tar zxf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz -C /application

ln -s /application/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit /application/mysql-proxy

3.环境变量配置

echo "export PATH=/application/mysql-proxy/bin:$PATH" >> /etc/profile

source /etc/profile

4.启动mysql-proxy

[root@mysql-proxy ~]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses="192.168.0.102:3306" --proxy-read-only-backend-addresses="192.168.0.103:3306" --proxy-lua-script="/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

5.检查启动结果

[root@mysql-proxy lib]# netstat -nltp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 971/sshd

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1274/mysql-proxy

tcp 0 0 :::22

# 此时只能实现简单的读写分离

# 添加启动脚本和管理查询模块

6.为mysql-proxy服务脚本提供配置文件/etc/sysconfig/mysql-proxy

# Options for mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS=""

#ADMIN_ADDRESS="0.0.0.0:4040"

ADMIN_LUA_SCRIPT="/application/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS=""

PROXY_USER="mysql-proxy"

#PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"

7.编辑mysql-proxy依赖的admin.lua脚本

cat >> /application/mysql-proxy/share/doc/mysql-proxy/admin.lua <<EOF

--[[ $%BEGINLICENSE%$

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] = {

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

EOF

8. 编辑mysql-proxy启动脚本

cat >> /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="/application/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

ADMIN_LUA_SCRIPT="/application/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_OPTIONS="--daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.0.102:3306 --proxy-read-only-backend-addresses=192.168.0.103:3306"

PROXY_PID=/var/run/mysql-proxy.pid

PROXY_USER="mysql-proxy"

PROXY_LUA="/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

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_PASSWO

RD"

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

touch /var/lock/subsys/mysql-proxy

}

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

}

# 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

;;

status)

status -p $PROXY_PID $prog

;;

echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"

RETVAL=1

;;

esac

exit $RETVAL

EOF

chmod 700 /etc/init.d/mysql-proxy

9. 重启mysql-proxy

/etc/init.d/mysql-proxy restart

10.检查启动结果

[root@mysql-proxy application]# netstat -nltpo

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 971/sshd

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1396/mysql-proxy

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 1396/mysql-proxy

tcp 0 0 :::22 :::* LISTEN 971/sshd

说明： 4040 是mysql-proxy 本身监听的端口

4040 是mysql-proxy 管理模块监听的端口

11.此时登陆管理模块，查看后端状态

mysql -uroot -P 4040 -p -h192.168.0.101 -e "select user,host from mysql.user"

mysql> select * from backends;

+-------------+--------------------+---------+------+------+-------------------+

+-------------+--------------------+---------+------+------+-------------------+

| 1 | 192.168.0.102:3306 | unknown | rw | NULL | 0 |

| 2 | 192.168.0.103:3306 | unknown | ro | NULL | 0 |

+-------------+--------------------+---------+------+------+-------------------+

2 rows in set (0.00 sec)

#由于此时没有进行任何操作，所以主从状态都是unknown

12.执行查询操作，查看主从状态变化

mysql -uroot -P 4040 -p -h192.168.0.101 -e "show databases;"

mysql> select * from backends;

+-------------+--------------------+---------+------+------+-------------------+

+-------------+--------------------+---------+------+------+-------------------+

| 1 | 192.168.0.102:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.103:3306 | unknown | ro | NULL | 0 |

+-------------+--------------------+---------+------+------+-------------------+

2 rows in set (0.00 sec)

# 由于主是支持读写的，所以发现主状态变为了up是正常现象，要想观看到从的状态，需要多次查询

mysql -uroot -P 4040 -p -h192.168.0.101 -e "select user,host from mysql.user"

mysql> select * from backends;

+-------------+--------------------+-------+------+------+-------------------+

+-------------+--------------------+-------+------+------+-------------------+

| 1 | 192.168.0.102:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.103:3306 | up | ro | NULL | 0 |

+-------------+--------------------+-------+------+------+-------------------+

2 rows in set (0.00 sec)

#此时发现主从状态全部变成了up状态

附件：http://down.51cto.com/data/2364455

mysql-proxy分离部署mysql