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