mysql proxy配置选项,mysql数据分离（mysql-proxy配置）

mysql数据分离(mysql-proxy配置)

1、安装MySQL

2、安装Lua

# wget https://www.lua.org/ftp/lua-5.1.4.tar.gz

# tar xzf lua-5.1.4.tar.gz

# cd lua-5.1.4

# make

# make linux INSTALL_TOP= /usr/local/lua

# make install

3、安装glib

# wget https://ftp.gnome.org/pub/gnome/sources/glib/2.28/glib-2.28.4.tar.bz2

# tar jxf glib-2.28.4.tar.bz2

# cd glib-2.28.4

# ./configure --prefix=/opt/module/glib2

# make && make install

# echo "/opt/module/glib2/lib" >> /etc/ld.so.conf

# ldconfig

4、安装pkg-config

# wget https://pkg-config.freedesktop.org/releases/pkgconfig-0.18.tar.gz

# tar xzf pkgconfig-0.18.tar.gz

# cd pkgconfig-0.18

# ./configure && make && make install

5、安装libevent

# wget https://monkey.org/%7Eprovos/libevent-2.0.10-stable.tar.gz

# tar zxf libevent-2.0.10-stable.tar.gz

# cd libevent-2.0.10-stable

# ./configure --prefix=/opt/module/libevent

# make; make install

6、安装mysql-proxy

# wget https://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz/from/https://mysql.he.net/

# tar xzf mysql-proxy-0.8.1.tar.gz

# cd mysql-proxy-0.8.1

# export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"

export PKG_CONFIG_PATH="/usr/lib/pkgconfig/"

export GLIB_CFLAGS="-I/opt/module/glib2/include/glib-2.0 -I/opt/module/glib2/lib/glib-2.0/include" GLIB_LIBS="-L/opt/module/glib2/lib -lglib-2.0"

export GMODULE_CFLAGS="-I/opt/module/glib2/include" GMODULE_LIBS="-L/opt/module/glib2/lib"

export GTHREAD_CFLAGS="-I/opt/module/glib2/include"

export GTHREAD_LIBS="-L/opt/module/glib2/lib"

export CPPFLAGS="$CPPFLAGS -I/opt/module/libevent/include"

export CFLAGS="$CFLAGS -I/opt/module/libevent/include"

export LDFLAGS="$LDFLAGS -L/opt/module/libevent/lib -lm"

export LIBS="/opt/module/glib2/lib/libgthread-2.0.so /opt/module/glib2/lib/libgmodule-2.0.so"

# ./configure --with-mysql="/opt/module/mysql" --prefix="/opt/module/mysql-proxy"

查看是否安装成功：

# /opt/module/mysql-proxy/bin/mysql-proxy --help-all | less

配置：

# vi /etc/mysql-proxy.cnf

===================

[mysql-proxy]

admin-username = mysqlproxyadm 主从数据库都需要的数据库用户

admin-password = 123456

daemon = true

keepalive = true

log-file = /opt/module/mysql-proxy/log

log-level = debug

proxy-backend-addresses = 192.168.1.223:3306 主

proxy-read-only-backend-address = 192.168.1.222:3306 从

proxy-read-only-backend-address = 192.168.1.224:3306 从

proxy-lua-script = /opt/module/mysql-proxy/rw-splitting.lua

admin-lua-script  = /opt/module/mysql-proxy/lib/mysql-proxy/lua/admin.lua

===================

# chmod 0660 /etc/mysql-proxy.cnf

制作启动脚本：

# vi /etc/init.d/mysql-proxy

===================

#!/bin/sh

export LUA_PATH=/opt/module/mysql-proxy/?.lua

mode=$1;

if [ -z $mode ] ; then

mode="start"

fi

case $mode in

start)

/opt/module/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf>/opt/module/mysql-proxy/log &

;;

stop)

killall -9 mysql-proxy

;;

*)

echo "Usage: $0 (start|stop)"

exit 1

;;

esac

exit 0;

=====================

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

编写mysql-proxy实现读写分离的脚本

# vi /opt/module/mysql-proxy/rw-splitting.lua

============

发送所有的非事务性select到一个从数据库

if is_in_transaction==0 and packet:byte() == proxy.COM_QUERY and packet:sub(2, 7)=="SELECT" then

local max_conns=-1

local max_conns_ndx=0

for i=1, #proxy.servers do

local s=proxy.servers[i] 需要选择一个拥有空闲连接的从数据库

if s.type==proxy.BACKEND_TYPE_RO and s.idling_connections>0 then

if max_conns==-1 or s.connected_clients0 then

proxy.connection.backend_ndx=max_conns_ndx

end

else

发送到主数据库

end

return proxy.PROXY_SEND_QUERY

============

启动mysql-proxy

# /etc/init.d/mysql-proxy start

注意：copy虚拟机的虚拟硬盘例如219.vdi，重命名为218.vdi，此时用virtualbox来创建一个虚拟主机，直接连接这个硬盘使用，会提示uuid重复的提示，这是用virtualbox的内部命令可以解决：

C:Documents and SettingsAdministrator>"c:Program FilesOracleVirtualBox"VBoxManage internalcommands setvdiuuid E:HardDisks217.vdi