ITPub博客

首页 > 数据库 > MySQL > MYSQL PROXY搭建手册

MYSQL PROXY搭建手册

原创 MySQL 作者:xuexiaogang 时间:2015-08-10 10:44:55 0 删除 编辑

1.安装包

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib* lua*

(若lua没有的话也可以下载源码包来下载:

http://www.lua.org/download.html

cd /opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4

vi src/Makefile
 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC,更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。

make linux
make install

cp etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

 

2.安装proxy

下载mysql-proxy

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy

3. 创建mysql-proxy服务管理脚本

mkdir /opt/mysql-proxy/init.d/

vim 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="--proxy-backend-addresses=192.168.56.102:3306 --proxy-read-only-backend-addresses=192.168.56.103:3306 --proxy-lua-script=/opt/mysql-proxy/share/doc/mysql-proxy/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: "

        $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=debug --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

 

---------------------------------------------------

脚本参数详解:
==============================================
PROXY_PATH=/opt/mysql-proxy/bin //
定义mysql-proxy服务二进制文件路径

PROXY_OPTIONS=" 
--proxy-read-only-backend-addresses=192.168.10.131:3306 \ //
定义后端只读从服务器地址
--proxy-backend-addresses=192.168.10.130:3306 \ //
定义后端主服务器地址
--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua" \ //
定义lua读写分离脚本路径

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \
--daemon \ //
定义以守护进程模式启动
--keepalive \ //
使进程在异常关闭后能够自动恢复
--pid-file=$PROXY_PID \ //
定义mysql-proxy PID文件路径
--user=mysql \ //
mysql用户身份启动服务
--log-level=warning \ //
定义log日志级别,由高到低分别有(error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log //
定义log日志文件路径
==============================================

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

mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

 

4.配置并使用rw-splitting.lua读写分离脚本

Vi /opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

--------------------------------------

-- connection pool
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
/opt/mysql-proxy/init.d/mysql-proxy start

进入设置的log目录查看log

Cd /opt/mysql-proxy/log

tail -f mysql-proxy.log

2015-06-13 13:00:15: (message) Initiating shutdown, requested from signal handler

2015-06-13 13:00:15: (message) shutting down normally, exit code is: 0

2015-06-13 13:00:19: (critical) plugin proxy 0.8.5 started

2015-06-13 13:00:19: (debug) max open file-descriptors = 1024

2015-06-13 13:00:19: (message) proxy listening on port :4040

2015-06-13 13:00:19: (message) added read/write backend: 192.168.56.102:3306

2015-06-13 13:00:19: (message) added read-only backend: 192.168.56.103:3306

2015-06-13 13:00:19: (debug) now running as user: mysql (27/27)

 

5.验证结果

创建用于读写分离的数据库连接用户

登陆主数据库服务器192.168.56.102

mysql> GRANT ALL ON *.* TO 'proxy1'@'proxy服务器' IDENTIFIED BY ‘111111';

由于我们配置了主从复制功能,因此从数据库服务器192.168.56.103上已经同步了此操作

为了清晰的看到读写分离的效果,需要暂时关闭MySQL主从复制功能

登陆从数据库服务器192.168.56.103

关闭Slave同步进程
mysql> stop slave;

连接MySQL-Proxy

mysql -uproxy1 -p111111 -P4040 -h192.168.56.101

mysql> use xx

mysql> show tables;

+--------------+

| Tables_in_xx |

+--------------+

| t1           |

| t2           |

+--------------+

mysql> insert into t1 values ('8');

mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

| 8    |

+------+

5 rows in set (0.00 sec)

 

slave服务器里去看:

mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

+------+

4 rows in set (0.00 sec)

并没有插入数据。

 

此时在slave上开启主从同步:

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

此时mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

| 8    |

+------+

5 rows in set (0.00 sec)

数据同步过来了。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/637517/viewspace-1766825/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Amoeba for mysql
下一篇: HBase简易迁移
请登录后发表评论 登录
全部评论
工学学士,工程硕士 。从业10余年,设计、实施和运维高并发的数据架构,单机处理超过100亿量级的数据库。目前从事负责高可用、业务连续性和高并发数据架构设计和运维管理。任公司高级系统架构师。Oracle 还MySQL的OCP OCP讲师, ITPUB论坛内存数据库版主;

注册时间:2013-07-01

  • 博文量
    33
  • 访问量
    40026