Mysql-proxy for read-write separation

Article from collation: http://blog.jobbole.com/94606/
Where amoeba for MySQL also implements read-write separation

Environment Description:
Operating system: CentOS6.5 32-bit
Primary server master:192.168.179.146
From server slave:192.168.179.147
Dispatch Server mysql-proxy:192.168.179.142
Due to the computer configuration is not, installed three virtual machines, the card is dead, only for a moment, because it is a master
One from, so that leads to read and write are on master, have the opportunity to get two more slave to test
I. MySQL master-slave replication, reference: http://www.cnblogs.com/lin3615/p/5679828.html

Second, mysql-proxy to achieve read and write separation
1, installation Mysql-proxy
The implementation of read-write separation is implemented with LUA scripting, now mysql-proxy inside is integrated, no need to install

Download: http://dev.mysql.com/downloads/mysql-proxy/Be sure to download the corresponding version

tar zxvf mysql-proxy-0.8. 5-LINUX-GLIBC2. 3-x86-32bit. Tar . GZ MV mysql-proxy-0.8. 5-LINUX-GLIBC2. 3-x86-32bit/usr/local/mysql-proxy

2. Configure Mysql-proxy to create the master configuration file

cd/usr/local/mysql-ProxymkdirLua #创建脚本存放目录mkdirlogs #创建日志目录CPShare/doc/mysql-proxy/rw-splitting.lua./Lua #复制读写分离配置文件CPShare/doc/mysql-proxy/admin-sql.lua./Lua #复制管理脚本VI/etc/mysql-proxy.cnf #创建配置文件 [MySQL-Proxy]user=root #运行mysql-Proxy User admin-username=lin3615 #主从mysql共有的用户admin-password=123456#用户的密码proxy-address=192.168.179.142:4040#mysql-proxy run IP and port, no port, default 4040proxy-read-only-backend-addresses=192.168.179.147#指定后端从slave读取数据proxy-backend-addresses=192.168.179.146#指定后端主master写入数据proxy-lua-script=/usr/local/mysql-proxy/lua/rw-Splitting.lua #指定读写分离配置文件位置admin-lua-script=/usr/local/mysql-proxy/lua/admin-Sql.lua #指定管理脚本log-file=/usr/local/mysql-proxy/logs/mysql-Proxy.log #日志位置log-level=Info#定义log日志级别, from high to low respectively (error|warning|Info|message|Debug) Daemon=true#以守护进程方式运行keepalive=true#mysql-proxy crashes, try restarting # Save to exit! chmod 660/etc/mysql-porxy.cnf

3, modify read-Write separation configuration file

vim/usr/local/mysql-proxy/lua/rw-Splitting.luaifthen = {  1  , #默认超过4个连接数时, began to read and write separation,  changed to 11, #默认8  , changed to 1false  }end

4. Start Mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/etc/mysql-grep4000 #已经启动 killall -9 mysql-proxy #关闭mysql-proxy use

5. Test read/write separation
(1). Create a proxy user on the primary server for Mysql-proxy use, and the operation will be synchronized from the server

' lin3615 ' @'192.168.179.142'123456';

(2). Using Client Connection Mysql-proxy

192.168. 179.142 4040 -p123456

Next, according to the basic curd execution can, because there is only one slave, test, each read and write from master, the computer performance can not open four virtual machines, so there is a chance to test more than one slave, see if OK

Mysql-proxy for read-write separation