Mysql-proxy realization of MySQL read/write separation

MySQL Proxy server function, the implementation of MySQL read and write separation, the user different requests sent to different servers.

mysql-server1:192.168.70.129

mysql-server2:192.168.70.132

mysql-proxy:192.168.70.133

Unzip the Mysql-proxy

[[email protected]  Desktop]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz - c /usr/local/[[email protected]  Desktop]# cd /usr/local/[[email protected] local ]# lsbin  games    lib    libexec                                    sbin   srcetc   include  lib64  mysql-proxy-0.8.3-linux-rhel5-x86-64bit  share[[ Email protected] local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit mysqlproxy[[email  protected] local]# cd mysqlproxy/[[email protected] mysqlproxy]# lsbin   include  lib  libexec  licenses  share[[email  Protected] mysqlproxy]# lS binmysql-binlog-dump  mysql-myisam-dump  mysql-proxy (startup script) [[email protected]  mysqlproxy]# ls share/doc/mysql-proxy/(a script that distinguishes a user from reading and writing requests) is written in the Lua language active-queries.lua        ro-balance.lua            tutorial-resultset.luaactive-transactions.lua  ro-pooling.lua            tutorial-rewrite.luaadmin-sql.lua             rw-splitting.lua (read/write separation) tutorial-routing.luaanalyze-query.lua         tutorial-basic.lua        tutorial-scramble.luaauditing.lua              tutorial-constants.lua   tutorial-states.luacommit-obfuscator.lua     Tutorial-inject.lua      tutorial-tokenize.luacommit-obfuscator.msc    tutorial-keepalive.lua    tutorial-union.luacopying                   tutorial-monitor.lua     tutorial-warnings.luahistogram.lua             tutorial-packets.lua      xtab.luaload-multi.lua            tutorial-prep-stmts.luareadme                    tutorial-query-time.lua

Permissions for read-write and read-write detached file execution

[[email protected] mysql-proxy]# ls-l rw-splitting.lua-rw-r--r--1 7157 wheel 11341 August 6 Rw-splitting.lua[[email Protected] mysql-proxy]# chmod +x Rw-splitting.lua

directory where the startup script is located

[Email protected] bin]# lsmysql-binlog-dump mysql-myisam-dump mysql-proxy

./mysql-proxy Options

- P: Specify IP Address, port for agent listening

- R : Specifies the IP address, port of the read server

- B: Specifies the IP address, port of the write server

- s: Specify the path to the Lua script file

--keepalive: Automatically restarts this process if the process crashes

Start the Mysql-proxy service and check that the native 3306 port is in use at startup.

[Email protected] bin]#/mysql-proxy-p 192.168.70.133:3306-r 192.168.70.132:3306-b 192.168.70.129:3306-s/usr/loc Al/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua--keepalive &[[email protected] bin]# 2014-03-21 11:00:50: ( Critical) Plugin proxy 0.8.3 started

To see if the service is started

[[email protected] bin]# jobs
[1]+ Running./mysql-proxy-p 192.168.70.133:3306-r 192.168.70.132:3306-b 192.168.70.129:3306-s/usr/loca L/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua--keepalive &
[email protected] bin]# NETSTAT-ANUTP | grep:3306
TCP 0 0 192.168.70.133:3306 0.0.0.0:* LISTEN 5140/mysql-proxy

Verify that a table is inserted on two MySQL servers to insert different data, and do the same authorization on two servers, perform mysql-uroot-h192.168.70.133-p on the client, and verify whether the read-write separation is successful through the obtained data.

Mysql-proxy realization of MySQL read/write separation