mysql读写分离升级脚本_MySql之读写分离

前言

本篇是以MySql之主从复制为基础做的拓展

在主从复制中，我们设置了两台数据库服务器(master，slave)

接下来我们需要做到在master进行写操作(insert,update,delete)

在slave上进行读操作(select)

一般情况下，会有多台slave，因为数据库的读写操作比基本是4:1

所以我们使用多台slave服务器来进行负载均衡，分担数据库的压力

安装lua

要完成读写分离，我们还要继续准备一台数据库服务器，作为其集群的中间件

Mysql-Proxy是基于rw-splitting.lua实现的

首先安装rw-splitting.lua：

wget wget http://www.lua.org/ftp/lua-5.2.4.tar.gz

tar zvfx lua-5.2.4.tar.gz

cd lua-5.2.4

配置安装文件：

vi src/Makefile

找到

CFLAGS= -O2 -Wall (MYCFLAGS)

修改为：

CFLAGS=−O2−Wall−fPIC (MYCFLAGS)

:wq

安装

make

make install

配置系统变量：

cp etc/lua.pc /usr/lib/pkgconfig/

export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

安装中间件Mysql-Proxy

下载地址：

https://downloads.mysql.com/archives/proxy/

解压安装：

tar zxvf mysql-proxy-0.8.0-linux-rhel5-x86-32bit.tar.gz

mv mysql-proxy-0.8.0-linux-rhel5-x86-32bit mysql-proxy

配置mysql-proxy

mkdir /mysql-proxy/init.d

cd /mysql-proxy/init.d

vi mysql-proxy

#!/bin/sh

#

# mysql-proxy This script starts and stops the mysql-proxy daemon

#

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon to mysql

# Source function library.

. /etc/rc.d/init.d/functions

#PROXY_PATH=/usr/local/bin

PROXY_PATH=/opt/mysql-proxy/bin

prog="mysql-proxy"

# Source networking configuration.

. /etc/sysconfig/network

# Check that networking is up.

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

# Set default mysql-proxy configuration.

#PROXY_OPTIONS="--daemon"

PROXY_OPTIONS="--admin-username=root --admin-password=password --proxy-read-only-backend-addresses=192.168.10.131:3306 --proxy-backend-addresses=192.168.10.130:3306 --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"

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

# Source mysql-proxy configuration.

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

. /etc/sysconfig/mysql-proxy

fi

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it's all good

RETVAL=0

# See how we were called.

case "$1" in

start)

# Start daemon.

echo -n $"Starting $prog: "

NICELEVELNICELEVELPROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=warning --log-file=/opt/mysql-proxy/log/mysql-proxy.log

RETVAL=$?

echo

if [ $RETVAL = 0 ]; then

touch /var/lock/subsys/mysql-proxy

fi

;;

stop)

# Stop daemons.

echo -n $"Stopping $prog: "

killproc $prog

RETVAL=$?

echo

if [ $RETVAL = 0 ]; then

rm -f /var/lock/subsys/mysql-proxy

rm -f $PROXY_PID

fi

;;

restart)

$0 stop

sleep 3

$0 start

;;

condrestart)

[ -e /var/lock/subsys/mysql-proxy ] && $0 restart

;;

status)

status mysql-proxy

RETVAL=$?

;;

*)

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

RETVAL=1

;;

esac

exit $RETVAL

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

mkdir /opt/mysql-proxy/run

mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

安装rw-splitting.lua读写分离脚本

拿到最新的rw-splitting.lua读写分离脚本

从https://downloads.mysql.com/archives/proxy/下载最新的源码包

解压并cd到根目录，把脚本考到我们本地mysql-proxy的scripts文件夹下

cp lib/rw-splitting.lua /mysql-proxy/scripts

修改rw-splitting.lua：

vi /mysql-proxy/scripts/rw-splitting.lua

min_idle_connections = 1, //默认为4

max_idle_connections = 1, //默认为8

保存后启动

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

以上便是MySql之读写分离