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)
