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之读写分离