mysql-proxy篇二

来源：互联网发布：mysql 函数编辑：程序博客网时间：2024/06/05 18:46

MySQL-proxy篇二之读写分离。

实现MySQL的读写分离，在主从的架构中，既可以实现从服务器对主服务器的数据备份又可以分摊负载，降低服务器的压力。下面简单的介绍下应用。

master-server 192.168.5.1（既可以读又可以写），slave-server192.168.5.2（只读的MySQL-server），192.168.5.3 MySQL-proxy所在服务器。

1、在官网可以下载MySQL-proxy。

现在好之后直接解压。

tar xf mysql-proxy****.tar.gz –C /usr/local

ln –sv mysql-proxy*** mysql-proxy

chown root.mysql-proxy /usr/local/mysql-proxy/*

这运行MySQL-proxy的过程中。我们需要用到相应的用户，所以在这里需要提前创建好。

useradd –r mysql-proxy

解压完之后就可以直接应用，将mysql-proxy添加到相应的目录中去。

vi /etc/profile.d/mysql-proxy.sh

exportPATH=$PATH:/usr/local/mysql-proxy/bin

MySQL-proxy是一个可以解压就直接用的一个工具，现在我们就可以跟上相应的参数，然后进行应用了。

mysql-proxy

--daemon

--log-level=debug

--log-file=/var/log/mysql-proxy.log

--plugins=proxy

--proxy-backend-addresses=192.168.5.1

--proxy-read-only-backend-addresses=192.168.5.2

--proxy-lua-script=/usr/loca/mysql-proxy/share/doc/mysql-proxy/rw-spliting.lua

--plugins=admin

--admin-username=admin

--admin-password=admin

--admin-lua-script=/usr/loca/mysql-proxy/share/doc/mysql-proxy/admin.lua

mysql-proxy并没有给我们提供admin的lua脚本但是给我们提供了读写分离的脚本。也就是说MySQL的读写分离并不是由mysql-proxy实现的。他是由lua脚本实现的。

下面自己建立admin的lua脚本

vi/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)

ifpacket:byte() ~= proxy.COM_QUERY then

set_error("[admin]we only handle text-based queries (COM_QUERY)")

returnproxy.PROXY_SEND_RESULT

end

localquery = packet:sub(2)

localrows = { }

localfields = { }

ifquery: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 },

}

fori = 1, #proxy.global.backends do

localstates = {

"unknown",

"up",

"down"

}

localtypes = {

"unknown",

"rw",

"ro"

}

localb = 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 downstarting at 0

b.uuid, -- the MySQL Server's UUID if itis managed

b.connected_clients -- currently connected clients

}

end

elseifquery: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 theirstate" }

else

set_error("use'SELECT * FROM help' to see the supported commands")

returnproxy.PROXY_SEND_RESULT

end

proxy.response= {

type= proxy.MYSQLD_PACKET_OK,

resultset= {

fields= fields,

rows= rows

}

returnproxy.PROXY_SEND_RESULT

end

如果启动成功的话，会有两个端口启动分别是4040端口和4041端口，其中4040端口mysql-proxy的工作端口，4041端口是admin的端口。端口启动之后，在mysql的master端建立一个可以供mysql-proxy登录的账号。

mysql>grant all on *.* to root@’192.168.%.%’ identified by‘redhat’;

mysql>flush privileges;

登录尝试：

mysql –uroot –h192.168.5.3 –port=4040 –p //连接上之后就可以进行mysql的读写操作了。

mysql –uadmin –h192.168.5.3 –port=4041 –p//连接之后是mysql-proxy的管理端口

mysql>select * from backends;

显示的是读写服务器是否已经启动并且被激活参加了工作。如果被激活那么显示的是up的状态。

下面介绍下用脚本启动mysql-proxy

vi /etc/init.d/mysql-proxy

#!/bin/bash

# mysql-proxy This script starts and stopsthe mysql-proxy daemon

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxydaemon 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

# 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

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_PASSWD"

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

vi /etc/sysconfig/mysql-proxy 编辑脚本的配置文件，为脚本传递参数。

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

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

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

PROXY_USER="mysql-proxy"

PROXY_OPTIONS=” –daemon –log-level=debug–log-file=/var/log/mysql-proxy.log –plugins=proxy–proxy-backend-addresses=192.168.5.1:3306–proxy-read-only-backend-addresses=192.168.5.2:3306 –plugins=admin–admin-username=admin –admin-password=admin–proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-spliting.lua”

chmod +x /etc/init.d/mysql-proxy

chmod 660 /etc/sysconfig/mysql-proxy

service mysql-proxy start //启动服务

这样我们就可以使用mysql-proxy了。

0 0