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)
分享名称:Mysql数据库读写分离简单配置
链接分享:http://myzitong.com/article/gioohe.html