Mysql-proxy read/write splitting

Mysql-proxy read/write splitting

:
Mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
Http://downloads.mysql.com/archives/proxy/
Add User:
[Root @ martin tools] # groupadd-r-g 303 mysql-proxy
[Root @ martin tools] # user add-g mysql-u 303-r-s/sbin/nologin mysql-proxy

[Root @ martin tools] # tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz-C/usr/local/
[Root @ martin tools] # cd/usr/local/
[Root @ martin local] # ln-svmysql-proxy-0.8.3-linux-glibc2.3-x86-64bit/mysql-proxy

Configure the service script:

[Root @ martin mysql-proxy] # vim/etc/rc. d/init. d/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/functions
Prog = "/usr/local/mysql-proxy/bin/mysql-proxy"
Conf = "/etc/mysql-proxy.cnf"
PROXY_PID = "/var/run/mysql-proxy.pid"

# 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.pid
PROXY_USER = "mysql-proxy"
# Source mysql-proxy configuration.
If [-f/etc/sysconfig/mysql-proxy]; then
./Etc/sysconfig/mysql-proxy
Fi
RETVAL = 0
Start (){

Echo-n $ "Starting $ prog :"
Daemon $ prog -- defaults-file = $ conf &>/dev/null
RETVAL =$?
If [$ RETVAL-eq 0]; then
Touch/var/lock/subsys/mysql-proxy
Success
Echo-e "\ r"
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 "$1" 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
;;
Esac
Exit $ RETVAL

[Root @ martin mysql-proxy] # chmod + x/etc/rc. d/init. d/mysql-proxy
[Root @ martin mysql-proxy] # chkconfig -- add mysql-proxy

Provide the configuration file/etc/mysql-proxy.cnf for the service script

[Root @ martin ~] # Vim/etc/mysql-proxy.cnf
[Mysql-proxy]
Daemon = true # Start mysql-proxy in daemon mode
Pid-file =/var/run/mysql-proxy.pid
Log-level = info # log level
Plugins = admin, proxy # Plug-ins loaded when mysql-proxy is started;
User = mysql-proxy # user who runs the mysql-proxy process;
#
# Proxy Configuration
Proxy-address = 0.0.0.0: 3307 # proxy service listening address and port;
Proxy-backend-addresses = 192.168.1.222: 3306 # the address and port of the backend mysql Server are generally the master server;
Proxy-read-only-backend-addresses = 192.168.1.223: 3306 # slave server
Proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua # complete the Lua script for mysql proxy functionality
# Proxy-skip-profiling = true # disable profile
#
# Admin Configuration
Admin-address = 0.0.0.0: 4042 # management module listening address and port
Admin-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/admin. lua
Admin-username = admin # Management Module User
Admin-password = admin # management module password

Modify permissions:
1 [root @ martin mysql-proxy] # chmod 660/etc/mysql-proxy.cnf

Copy the following content to create the admin. lua file and save it to the/usr/local/mysql-proxy/share/doc/mysql-proxy/directory.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103 [root @ martin mysql-proxy] # vim/usr/local/mysql-proxy/share/doc/mysql-proxy/admin. lua


-- [[$ % BEGINLICENSE % $
Copyright (c) 2007,201 2, 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
Published by the Free Software Foundation; version 2 of
License.

This program is distributed in the hope that it will be useful,
But without any warranty; without even the implied warranty
MERCHANTABILITY or fitness for a particle PURPOSE. See
GNU General Public License for more details.

You shoshould have your ed a copy of the GNU General Public License
Along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, th Floor, Boston, MA
02110-1301 USA

$ % ENDLICENSE % $ --]

Function set_error (errmsg)
Proxy. response = {
Type = proxy. MYSQLD_PACKET_ERR,
Errmsg = errmsg or "error"
}
End

Function read_query (packet)
If packet: byte ()~ = Proxy. COM_QUERY then
Set_error ("[admin] we only handle text-based queries (COM_QUERY )")
Return proxy. PROXY_SEND_RESULT
End

Local query = packet: sub (2)

Local rows = {}
Local fields = {}

If query: lower () = "select * from backends" then
Fields = {
{Name = "backend_ndx ",
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 do
Local states = {
"Unknown ",
"Up ",
"Down"
}
Local types = {
"Unknown ",
"Rw ",
"Ro"
}
Local B = proxy. global. backends [I]

Rows [# rows + 1] = {
I,
B. dst. name, -- configured backend address
States [B. state + 1], -- the C-id is pushed down starting at 0
Types [B. type + 1], -- the C-id is pushed down starting at 0
B. uuid, -- the MySQL Server's UUID if it is managed
B. connected_clients -- currently connected clients
}
End
Elseif query: lower () = "select * from help" then
Fields = {
{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 "}
Else
Set_error ("use 'select * FROM help' to see the supported commands ")
Return proxy. PROXY_SEND_RESULT
End

Proxy. response = {
Type = proxy. MYSQLD_PACKET_ OK,
Resultset = {
Fields = fields,
Rows = rows
}
}
Return proxy. PROXY_SEND_RESULT
End

STARTUP script:
[Root @ martin mysql-proxy] #/etc/init. d/mysql-proxy restart
Stopping/usr/local/mysql-proxy/bin/mysql-proxy: [OK]
Starting/usr/local/mysql-proxy/bin/mysql-proxy: [OK]
[Root @ martin mysql-proxy] # ss-lntup | grep mysql-proxy
Tcp LISTEN 0 128 *: 3307 *: * users :( ("mysql-proxy", 111652,10 ))
Tcp LISTEN 0 128 *: 4042 *: * users :( ("mysql-proxy", 111652,9 ))

The master server creates a proxy account:
Mysql> grant all on *. * to 'admin' @ '192. 192.% 'identified by '123 ';
Mysql> flush privileges;

View proxy information:
[Root @ martin ~] # Mysql-uadmin-padmin-h192.168.1.222 -- port = 4042

Mysql> SELECT * FROM backends;
+ ------------- + ------------------ + --------- + ------ + ------------------- +

| Backend_ndx | address | state | type | uuid | connected_clients |

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

| 1 | 192.168.1.222: 3306 | up | rw | NULL | 0 |

| 2 | 192.168.1.223: 3306 | unknown | ro | NULL | 0 |

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

Logon Proxy:
[Root @ martin mysql-proxy] # mysql-uadmin-p222222-h192.168.1.222 -- port = 3307

Over

This article permanently updates the link address: