Mysql数据库读写分离简单配置

环境：

Master：192.168.71.128      mysql-sql-node1

Slave：192.168.71.140       mysql-data-node1

Mysql-Proxy：192.168.71.138 mysql-mgm-node

 

 

rpm -q libevent glib2 pkgconfig libtool mysql-devel 查看系统是否已安装以上包，如未安装，使用yum install 安装。

yum install -y libtermcap-devel ncurses-devel libevent-devel readline-devel gcc-c++

 

一、下载程序：

 

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

wget http://gd.tuwien.ac.at/db/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gz

wget http://www.monkey.org/~provos/libevent-1.4.13-stable.tar.gz

 

二、安装lua：

 

[root@mailtest /software]# tar zxvf lua-5.1.4.tar.gz

[root@mailtest lua-5.1.4]#  cd lua-5.1.4

[root@mailtest lua-5.1.4]# # vi Makefile

修改：

INSTALL_TOP= /usr/local/lua

 

[root@mailtest lua-5.1.4]#  make && make linux && make install

 

设置环境变量

[root@mailtest lua-5.1.4]# export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"

[root@mailtest lua-5.1.4]# export CPPFLAGS="-I/usr/local/libevent/include"

[root@mailtest lua-5.1.4]# export CFLAGS="-I/usr/local/libevent/include"

 

三、安装 libevent：

 

tar zvxf libevent-1.4.13-stable.tar.gz

cd libevent-1.4.13-stable

./configure --prefix=/usr/local/libevent

make && make install

 

四、安装mysql-proxy：

./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua

make && make install

 

 

启动：

/usr/local/mysql-proxy/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.71.140:3306 --proxy-backend-addresses=192.168.71.128:3306 --proxy-lua-

 

script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &

 

查看进程：

netstat -ntlp | grep mysql

编辑启动脚本：

vi /etc/init.d/mysql-proxy

#!/bin/bash

PRODIR=/usr/local/mysql-proxy

LUA_PATH=$PRODIR/share/mysql-proxy

start(){

$PRODIR/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.71.140:3306 --proxy-backend-addresses=192.168.71.128:3306 --proxy-lua-

 

script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &

>>$PRODIR/mysql-proxy.log &

}

stop(){

kill $(pidof mysql-proxy)

if [ $? -ne 0 ];then

kill -9 $(pidof mysql-proxy)

fi

}

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

*)

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

esac

 

 

 

修改权限：

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

/etc/init.d/mysql-proxy start

 

测试：

在MySQL-Master 上创建测试数据库和用户。

mysql> create database unixhot;

mysql> GRANT ALL PRIVILEGES ON king.* to proxytest@'%' identified by 'bobo365';

连接测试

[root@MySQL-Proxy ~]# mysql -h 192.168.71.138 -P 4040 -u proxytest -p

输入密码即可登录。

 

 

mysql> show processlist;

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

| Id | User      | Host                 | db   | Command | Time | State | Info             |

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

| 14 | proxytest | 192.168.71.138:17350 | NULL | Sleep   |    2 |       | NULL             |

| 15 | proxytest | 192.168.71.138:17355 | NULL | Query   |    0 | NULL  | show processlist |

| 16 | proxytest | 192.168.71.138:17358 | NULL | Sleep   |    2 |       | NULL             |

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

3 rows in set (0.01 sec)