mysql-proxy读写分离

回复 收藏

1:master:192.168.0.17

    slave:192.168.0.18

    mysql-proxy:192.168.0.21

2:主从复制安装就不叙述了 .

3:下载mysql-proxy:http://dev.mysql.com/downloads/mysql-proxy/

tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 

mv mysql-proxr....... /usr/local/mysql-proxy

useradd -r mysql-proxy

4: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/init.d/mysql-proxy

chkconfig --add mysql-proxy

5: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=""

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon 

                              --log-level=info 

                              --log-use-syslog 

                             --plugins=proxy

                             --plugins=admin 

                            --proxy-backend-addresses=192.168.0.17:3306 

                            --proxy-read-only-backend-addresses=192.168.0.18:3306 

                           --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

#--daemon:以守护进程模式启动mysql-proxy

#--proxy-backend-addresses:后端可读写的mysql服务器的地址和端口

#--proxy-read-only-backend-addresses:后端只读mysql服务器的地址和端口

#--proxy-lua-script:完成mysql代理功能的Lua脚本

6: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

7:vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

--------------------------------------------------------------------------->

if not proxy.global.config.rwsplit then

        proxy.global.config.rwsplit = {

                min_idle_connections = 1,     //默认为4

                max_idle_connections = 1,    //默认为8

                is_debug = false

        }

end

          //mysql-proxy会检测客户端连接,当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。

8:service mysql-proxy start

   ss -tnlp  

   QQ截图20171023150814.jpg

9:在mysql-proxy服务器上

   yum -y install mysql (安装mysql客户端)

  mysql -uadmin -padmin -h192.168.0.21 --port=4041

 ---------------------------------------------------------------------------->

QQ截图20171023151114.jpg

读写分离测试:

10:在master机器上

      GRANT ALL ON *.* TO 'jason'@'192.168.0.%' IDENTIFIED BY '123456';      FLUSH PRIVILEGES;

分别在两台机器上安装tcpdump准备抓包

 master:tcpdump -i eth0 -nn -XX ip dst 192.168.0.17 and tcp dst port 3306

   slave:tcpdump -i eth0 -nn -XX ip dst 192.168.0.18 and tcp dst port 3306

11:mysql-proxy上进行数据库操作

      mysql -ujason -p123456 -h192.168.0.21

        ------------------------------------------------->

CREATE DATABASE hello1;

USE mysql;

SELECT * FROM user;             //可以用额外的主机多执行几次

在master上的抓包信息:

QQ截图20171023151659.jpg

在slave上的抓包信息:

QQ截图20171023151825.jpg

12:查看状态,在proxy上操作,可以看到状态全部为up:

mysql -uadmin -padmin -h192.168.0.21 --port=4041

QQ截图20171023152015.jpg

原文地址:http://www.cnblogs.com/tae44/p/4701226.html

2017-10-23 15:21 举报
已邀请:

回复帖子,请先登录注册

退出全屏模式 全屏模式 回复
评分
可选评分理由: