Mysql-proxy Read/write separation

Mysql-proxy Introduction

MySQL Proxy is such an intermediary agent layer,The MySQL proxy is a connection pool that forwards the connection requests from the foreground application to the backend database, and through the use of Lua scripting, enables complex connection control and filtering for read-write separation and load balancing. For the application, the MySQL proxy is completely transparent, and the application only needs to be connected to the listening port of MySQL proxy.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/E1/wKioL1QdQROQYpUnAAAjTufccMQ404.png "title=" Capture 1. PNG "alt=" wkiol1qdqroqypunaaajtufccmq404.png "/> (please forgive me for a brief illustration)

Lab Environment:

Master 172.16.31.31

Slave 172.16.31.32

Proxy 172.16.31.30

1 Installation Configuration mysql-proxy:

# tar XF mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz-c/usr/local# cd/usr/local# ln-s    mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz Mysql-proxy # Useradd mysql-proxy//Add Proxy User # Yum install mysql-y Installing the MySQL Client

2 providing SYSV service scripts for Mysql-proxy

#!/bin/bash## mysql-proxy this script starts and stops the mysql-proxy  daemon## chkconfig: - 78 30# processname: mysql-proxy# description:  mysql-proxy is a proxy daemon for mysql# Source function  Library:  /etc/rc.d/init.d/functionsprog= "/usr/local/mysql-proxy/bin/mysql-proxy" # Source networking  configuration.if [ -f /etc/sysconfig/network ]; then    .  /etc/sysconfig/networkfi# check that networking is up. [ ${networking} =  "No"  ] && exit 0# Set default  Mysql-proxy configuration. Admin_user= "admin" admin_passwd= "admin" admin_lua_script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua "Proxy_options="--daemon "proxy_pid=/var/run/mysql-proxy.pidproxy_user=" Mysql-proxy "# Source  mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then    .  /etc/sysconfig/mysql-proxyfiretval=0start ()  {    echo -n $ "Starting   $prog:  "    daemon  $prog   $PROXY _options --pid-file= $PROXY _pid  --proxy-address= "$PROXY _address"  --user= $PROXY _user --admin-username= "$ADMIN _user"  -- admin-lua-script= "$ADMIN _lua_script"  --admin-password= "$ADMIN _password"     retval=$?     echo    if [  $RETVAL  -eq 0 ]; then         touch /var/lock/subsys/mysql-proxy     fi}stop ()  {    echo -n $ "stopping  $prog: "      killproc -p  $PROXY _pid -d 3  $prog     RETVAL=$?     echo    if [  $RETVAL  -eq 0 ]; then         rm -f /var/lock/subsys/mysql-proxy        rm -f   $PROXY _pid    fi}# see how we were called.case  "$"  in    start)         start         ;;     stop)         stop         ;;     restart)         stop         start        ;;     condrestart|try-restart)         if status  -p  $PROXY _pidfile  $prog  >&/dev/null; then            stop             start        fi        ;;     status)         status -p  $PROXY _pid   $prog         ;;     *)         echo  "usage: $0 { Start|stop|restart|reload|status|condrestart|try-restart} "         retval=1        ;; esacexit  $RETVAL

Then save the above content as/etc/rc.d/init.d/mysql-proxy, give execute permission, and then add to the list of services.

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

3 providing configuration files for service scripts/etc/sysconfig/mysql-proxy

# Options for Mysql-proxy admin_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 "

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 =172.16.31.31:3306--proxy-read-only-backend-addresses=172.16.31.32: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.

4 Copy the following to create the Admin.lua file and save it to the/usr/local/mysql-proxy/share/doc/mysql-proxy/directory

--[[ $%beginlicense%$ copyright  (c)  2007, 2012, oracle and/or its  affiliates. All rights reserved. This program is free software;  you can redistribute it and/or modify it under the terms  of the GNU General Public License as published by the  Free software foundation; version 2 of the license. this program  is distributed in the hope that it will be useful, but  WITHOUT ANY WARRANTY; without even the implied warranty of  Merchantability or fitness for a particular purpose. see the gnu  General Public License for more details. You should have  Received a coPy of the gnu general public license along with this program ;  if not, write to the free software foundation, inc., 51  franklin st, fifth floor, boston, ma 02110-1301  usa $% Endlicense%$ --]]function set_error (errmsg)  proxy.response = {type = proxy. mysqld_packet_err,errmsg = errmsg or  "Error"}endfunction read_query (PACKET) if  Packet:byte ()  ~= proxy.com_query thenset_error ("[admin] we only handle  text-based queries  (com_query) ") Return proxy. Proxy_send_resultendlocal query = packet:sub (2) local rows = { }local  Fields = { }if query:lower ()  ==  "Select * from backends"   thenfields = { { name =  "Backend_ndx",  &NBSp; type = proxy. mysql_type_long },{ name =  "Address",   type = proxy. mysql_type_string },{ name =  "state",   type = proxy. mysql_type_string },{ name =  "TYPE",   type = proxy. mysql_type_string },{ name =  "UUID",   type = proxy. mysql_type_string },{ name =  "Connected_clients",    type = proxy. mysql_type_long },}for i = 1,  #proxy. global.backends dolocal states =  {"Unknown", "Up", "Down"}local types = {"Unknown", "RW", "RO"}local b =  proxy.global.backends[i]rows[#rows  + 1] = {i,b.dst.name,           -- configured backend addressstates[b.state + 1],  -- the c-id is pushed down starting at 0types[b.type + 1],   -- the c-id is pushed  down starting at 0b.uuid,               -- the mysql server ' s uuid if it is  Managedb.connected_clients  -- currently connected clients}endelseif query:lower ()  ==  "Select * from help"  thenfields = { { name =   "Command",    type = proxy. mysql_type_string },{ name =  "description",    type = proxy. mysql_type_string },}rows[#rows  + 1] = {  "Select * from help",   "Shows this help"  }rows[#rows  + 1] = {  "Select * from  backends ", " Lists the backends and their state " }elseset_Error ("use  ' Select * from help '  to see the supported commands") Return proxy. Proxy_send_resultendproxy.response = {type = proxy. Mysqld_packet_ok,resultset = {fields = fields,rows = rows}}return proxy. Proxy_send_resultend

6 Start and test

# service Mysql-proxy start# netstat-tunlp |grep mysql-proxytcp 0 0 0.0.0.0:4041 0.0.0.0:*                   LISTEN 46219/mysql-proxy TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 46219/mysql-proxy

4041 port for monitoring Mysql-proxy read-write separation state, 3306 port for normal connection to the backend server read and write requests;

7 Management function test

# mysql-uadmin-padmin-h172.16.31.30--port=4041

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.0.99-agent-admin

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.

Mysql> SELECT * from Backends;

+-------------+-------------------+-------+------+------+-------------------+

| Backend_ndx | Address | State | Type | UUID | connected_clients |

+-------------+-------------------+-------+------+------+-------------------+

| 1 | 172.16.31.31:3306 | Up | RW |                 NULL | 0 |

| 2 | 172.16.31.32:3306 | Up | Ro |                 NULL | 0 |

+-------------+-------------------+-------+------+------+-------------------+

2 rows in Set (0.00 sec)

This article from the "Linux" blog, reproduced please contact the author!

Mysql-proxy Read/write separation