MySQL read-write separation based on Mysql-proxy

1. mysql-proxy:192.168.20.109

   master:192.168.20.135

   slave;192.168.20.137

2. Download Msql-proxy:wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz

useradd -g mysql-proxy -s /sbin/nologin

   tar zxvf mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local

   mv /usr/local/mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

   export PATH=$PATH:/usr/local/mysql-proxy/bin/

   echo "PATH=$PATH:/usr/local/mysql-proxy/bin" >> /etc/profile




3.查看mysql-proxy --help

  

--help-all   ：获取全部帮助信息； --proxy-address=host:port  ：代理服务监听的地址和端口； --admin-address=host:port  ：管理模块监听的地址和端口； --proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口； --proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口； --proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本； --daemon  ：以守护进程模式启动mysql-proxy； --keepalive  ：在mysql-proxy崩溃时尝试重启之； --log-file=/path/to/log_file_name ：日志文件名称； --log-level=level ：日志级别； --log- use -syslog ：基于syslog记录日志； --plugins=plugin：在mysql-proxy启动时加载的插件； --user=user_name  ：运行mysql-proxy进程的用户； --defaults-file=/path/to/conf_file_name ： 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识； --proxy-skip-profiling ： 禁用profile； --pid-file=/path/to/pid_file_name ：进程文件名；

4. mysql> grant all on *.* to [email protected] ‘%‘identified by ‘密码‘;




5.mysql-proxy--daemon--log-level=debug--user=mysql-proxy-- KeepAlive--log-file=/ var /log/mysql-proxy.log- -plugins= --proxy-backend-addresses= "192.168.182.135:3306" -- proxy-read-only-backend-addresses= "192.168.182.137:3306" --proxy-lua-script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin--admin-username= "admin" --admin-password= "admin" --admin-lua-script= "/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

6.ps-ef | grep mysql-proxy

You can see that 495 is the core boot process for mysql-proxy.

NETSTAT-TLUNP | grep mysql-proxy

You can see that 4040 is Mysql-proxy Port 4041 is admin port

7. Then go to the slave side to verify that you can connect to the Mysql-proxy via the proxy port 4040 Port

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/76/wKiom1VJwhKRgqzzAAGkGD1_pGU968.jpg "title=" QQ picture 20150506152328.jpg "alt=" Wkiom1vjwhkrgqzzaagkgd1_pgu968.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/72/wKioL1VJxG7RX9ayAAH9sfHTTbA025.jpg "title=" QQ picture 20150506152722.jpg "alt=" Wkiol1vjxg7rx9ayaah9sfhttba025.jpg "/>

8.mysql-uadmin-pyzg1314520-h192.168.20.109--port=4041-padmin

Verify the login view of admin

SELECT * from Backends;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/72/wKioL1VJxWLRnADHAADiCIsOk1A694.jpg "title=" QQ picture 20150506153133.jpg "alt=" Wkiol1vjxwlrnadhaadicisok1a694.jpg "/>

Because rw-splitting.lu a script by default has 4 links to enable separation, so open several terminals, multiple tests, you can also modify the relevant values inside to achieve state all up.

MySQL read-write separation based on Mysql-proxy