MySQL Learning note ten: Using Mysql-proxy for MySQL read-write separation

MySQL read and write separation refers to the master to handle the write operation, so that slave processing read operations, is very suitable for reading a large number of scenes, can reduce the pressure of master.

This paper uses Mysql-proxy to realize the read and write separation of MySQL,Mysql-proxy is actually as the back-end MySQL master-slave Server Agent, it directly accept the client's request, the SQL statement analysis, to determine whether read or write operations, It is then distributed to the corresponding MySQL server . For multi-node slave clusters, you can also play a load-balancing effect.

First, prepare the experimental environment

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/82/58/wKioL1dRjO_gsNNHAABFkEPtk2o985.png "title=" 2016-06-03_215319.png "width=" "height=" 293 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:600PX;HEIGHT:293PX; " alt= "Wkiol1drjo_gsnnhaabfkeptk2o985.png"/>

MySQL's master-slave replication architecture is described in http://9124573.blog.51cto.com/9114573/1785454

Second, installation configuration Mysql-proxy

1. The Mysql-proxy version downloaded here is mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

# Tar XF mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz-c/usr/local

# cd/usr/local

# LN-SV Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit Mysql-proxy

# Useradd-r Mysql-proxy

[[email protected] ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz  -c /usr/local/[[email protected] ~]# ln -s /usr/local/ Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy[[email protected] ~]# cd  /usr/local/mysql-proxy[[email protected] mysql-proxy]# lsbin  include   lib  libexec  licenses  share[[email protected] mysql-proxy]#  ls binmysql-binlog-dump  mysql-myisam-dump  mysql-proxy[[email protected]  mysql-proxy]# ls share/doc/mysql-proxy/active-queries.lua        commit-obfuscator.lua  README               tutorial-constants.lua  tutorial-prep-stmts.lua   Tutorial-scramble.lua  xtab.luaactive-transactions.lua  commit-obfuscator.msc  ro-balance.lua       tutorial-inject.lua     tutorial-query-time.lua   tutorial-states.luaadmin-sql.lua             copying                 Ro-pooling.lua      tutorial-keepalive.lua  tutorial-resultset.lua    tutorial-tokenize.luaanalyze-query.lua         histogram.lua          rw-splitting.lua     tutorial-monitor.lua    tutorial-rewrite.lua      tutorial-union.luaauditing.lua              load-multi.lua         tutorial-basic.lua  tutorial-packets.lua    tutorial-routing.lua      Tutorial-warnings.lua[[email protected] mysql-proxy]# useradd -r mysql-proxy

2. Provide SYSV service script for Mysql-proxy

# Vim/etc/rc.d/init.d/mysql-proxy

... (Content slightly)

# chmod +x/etc/rc.d/init.d/mysql-proxy

# chkconfig--add Mysql-proxy

[Email protected] ~]# vim/etc/rc.d/init.d/mysql-proxy ... [Email protected] ~]# chmod +x! $chmod +x/etc/rc.d/init.d/mysql-proxy[[email protected] ~]# chkconfig--add mysql-proxy

3. Provide the configuration file/etc/sysconfig/mysql-proxy for the service script, as follows:

# Options for Mysql-proxy

Admin_user= "Admin" #mysql-proxy Admin Account

admin_password= "Admin"

Admin_address= ""

admin_lua_script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" #完成mysql scripts for-proxy management functions

Proxy_address= ""

Proxy_user= "Mysql-proxy" #以什么身份运行mysql-proxy

proxy_options= "--daemon--log-level=info--log-use-syslog"

The last line needs to be modified according to the actual scenario, for example:

proxy_options= "--daemon--log-level=info--log-use-syslog --plugins=proxy--plugins=admin -- Proxy-backend-addresses=192.168.30.10:3306 --proxy-read-only-backend-addresses=192.168.30.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "

The proxy-backend-addresses option and the proxy-read-only-backend-addresses option can be reused multiple times to achieve a specified number of read-write servers or read-only servers;

Rw-splitting.lua is a script file that implements the read-write separation function

[[email protected] ~]# vim/etc/sysconfig/mysql-proxy# Options for mysql-proxyadmin_user= "admin" admin_password= "admin "Admin_address=" "admin_lua_script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua "PROXY_ADDRESS=" " Proxy_user= "Mysql-proxy" proxy_options= "--daemon--log-level=info--log-use-syslog--plugins=proxy--plugins=admin- -proxy-backend-addresses=192.168.30.10:3306--proxy-read-only-backend-addresses=192.168.30.20:3306-- Proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "

4. Configuration options for the Mysql-proxy command

We can also start the service process with the Mysql-proxy command, which can be broadly divided into help options, administrative options, proxy options, and application options:

--help

--help-admin

--help-proxy

--help-all #以上四个选项均用于获取帮助信息;

--proxy-address=host:port #代理服务监听的地址和端口

--admin-address=host:port # The address and port that the management module listens to, the default is 0.0.0.0:4041

--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 crashes when attempting to restart;

--log-file=/path/to/log_file_name #日志文件名称;

--log-level=level #日志级别;

--log-use-syslog #基于syslog记录日志;

--plugins=plugin,.. #在mysql plug-ins loaded at-proxy startup;

--user=user_name #运行mysql The user of the-proxy process;

--defaults-file=/path/to/conf_file_name #默认使用的配置文件路径; Its configuration segment uses [mysql-proxy] identification;

--proxy-skip-profiling #禁用profile;

--pid-file=/path/to/pid_file_name #进程文件名;

5. Create a Admin.lua file and save it to the/usr/local/mysql-proxy/share/doc/mysql-proxy/directory (slightly)

[Email protected] ~]# Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua ...

6. Start Mysql-proxy

Service Mysql-proxy Start

      Internet says Mysql-proxy the default proxy service port is 4040, in this case 3306, no reason is found, there may be a change in the new version, or if you want to use a different port, Use the option--proxy-address to modify it;

[[email protected] ~]# service mysql-proxy startstarting /usr/local/mysql-proxy/ BIN/MYSQL-PROXY:           [  OK  ] [[email protected] ~]# netstat -tanp...tcp         0      0 0.0.0.0:4041                 0.0.0.0:*                    listen      52446/ mysql-proxy   tcp        0       0 0.0.0.0:3306                 0.0.0.0:*                  &nbsP; listen      52446/mysql-proxy 

7. Management Mysql-proxy

Mysql-u admin-h 192.168.30.14-p 4041-p
Mysql> SELECT * from Backends; #查看后端mysql服务器状态

[[email protected] ~]# mysql -u admin -h 192.168.30.14 -p 4041  -pEnter password: ERROR 1045  (28000):  password doesn ' T match[[email  protected] ~]# mysql -u admin -h 192.168.30.14 -p 4041 - penter password: ...mysql> select * from backends;+-------------+------------ --------+-------+------+------+-------------------+| backend_ndx | address             | state | type | uuid |  connected_clients |+-------------+--------------------+-------+------+------+-------------------+|            1 | 192.168.30.10:3306 |  up    | rw   | NULL |                  0 | |            2 | 192.168.30.20:3306 |  up    | ro   | NULL |                  0 |+-------------+--------------- -----+-------+------+------+-------------------+2 rows in set  (0.00 sec)

Third, testing

1. Create a user on the main library

Grant Select,insert,create,delete on * * to [e-mail protected] ' 192.168.30.% ' identified by ' proxypass ';

Flush privileges;

The operation is synchronized to the slave node, so it is no longer necessary to create it from the library

2, ready to grab the bag

# tcpdump-i ETH0-NN-XX IP DST host 192.168.30.10 and TCP DST Port 3306

# tcpdump-i ETH0-NN-XX IP DST host 192.168.30.20 and TCP DST Port 3306

3, connected Mysql-proxy, read, write and other operations

Mysql-u proxytest-h 192.168.30.14-p 3306-p

Tested Discovery:

All write requests are distributed to the main library, and all read requests are distributed to the slave library;

Read requests are sent to the main library when the library is not in line

MySQL Learning note ten: Using Mysql-proxy for MySQL read-write separation