MySQL read-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/

tar  zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit. tar .gz mv  mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit  /usr/local/mysql-proxy  2. Configure Mysql-proxy to create the master configuration file cd  /usr/local/mysql-proxy mkdir  lua  #创建脚本存放目录 mkdir  logs  #创建日志目录 cp  share /doc/mysql-proxy/rw-splitting .lua . /lua  #复制读写分离配置文件 cp  share /doc/mysql-proxy/admin-sql .lua . /lua  #复制管理脚本 vi  /etc/mysql-proxy .cnf    #创建配置文件 [mysql-proxy] user=root  #运行mysql-proxy用户 admin-username=proxy  #主从mysql共有的用户 admin-password=123.com  #用户的密码 proxy-address=192.168.0.204:4040  # 如果不写端口号，默认端口号3306，与Mysql的端口号冲突，所以会报错。*************************！！！！ proxy- read -only-backend-addresses=192.168.0.203  #指定后端从slave读取数据 proxy-backend-addresses=192.168.0.202  #指定后端主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日志级别，由高到低分别有(error|warning|info|message|debug) daemon= true     #以守护进程方式运行 keepalive= true  #mysql-proxy崩溃时，尝试重启 保存退出！ chmod 660 /etc/mysql-proxy.cnf 3, modify read-write separation configuration file vi  /usr/local/mysql-proxy/lua/rw-splitting .lua if  not proxy.global.config.rwsplit  then   proxy.global.config.rwsplit = {    min_idle_connections = 1,  #默认超过4个连接数时，才开始读写分离，改为1    max_idle_connections = 1,  #默认8，改为1    is_debug =  false   } end 4. Start Mysql-proxy /usr/local/mysql-proxy/bin/mysql-proxy  --defaults- file = /etc/mysql-proxy .cnf netstat  -tupln |  grep  4040  #已经启动 tcp 0 0 192.168.0.204:4040 0.0.0.0:* LISTEN 1264 /mysql-proxy 关闭mysql-proxy使用：killall -9 mysql-proxy 5. Test read/write separation1>. Create a proxy user in the master server for Mysql-proxy use, and the operation will be synchronized from the server mysql> grant all on *.* to  ‘proxy‘ @ ‘192.168.0.204‘  identified by  ‘123.com‘ ; 2> shutting down the slave from the serverslave stop; 3> Using Client connection Mysql-proxymysql-u proxy-p 4040-h 192.168.0.0-p 4> The following operations are logged in to the database using Mysql-proxy's account. mysql> create table user (number INT(10),name VARCHAR(255)); mysql> insert into  user  values(01, ‘zhangsan‘ ); mysql> insert into user values(02, ‘lisi‘ ); 5> Login to master server, view:SELECT * from userThe primary database has data, no data from the database 6> opening slave from the vaultstart SlaveExecute SELECT * from user in a few the data from the library will also be synchronized slowly. 

Reference: http://lizhenliang.blog.51cto.com/7876557/1305083

Reference 2:http://www.cnblogs.com/luckcs/articles/2543607.html

 ======================== the data of the master-slave library is sometimes inconsistent, but still needs to study doubt, when the test read and write separation, the execution slave stop Time is long, has not slave start whether data is difficult to synchronize? slave stop a little bit, after a while, the data will still be synced to the slave library.  

  

 

MySQL read-write separation