MySQL Proxy read/write separation (actual combat summary)

Planning:
Primary MySQL server: 192.168.1.21
From MySQL server: 192.168.1.22
MySQL read-write splitter: 192.168.1.23


1. Unpack the installation package on the read-write separation server, add the corresponding user, and edit the startup script;
# Tar XF mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz-c/usr/local/
# cd/usr/local/
# LN-SV Mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit Mysql-proxy
# CD Mysql-proxy
# Useradd Mysql-proxy
# Vim/etc/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"

# Source Networking configuration.
if [-f/etc/sysconfig/network]; Then
. /etc/sysconfig/network
Fi

# Check that networking are 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 $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: $ {Start|stop|restart|reload|status|condrestart|try-restart}"
Retval=1
;;
Esac

Exit $RETVAL
# chmod +x/etc/init.d/mysql-proxy
# Vim/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--plugins=proxy--plugins=admin--proxy-backend-addresses =192.168.1.21:3306--proxy-read-only-backend-addresses=192.168.1.22:3306--proxy-lua-script=/usr/local/ Mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "

2, edit the Admin.lua script file, save it to/usr/local/mysql-proxy/share/doc/mysql-proxy/;
# Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
--[[$%beginlicense%$
Copyright (c), the Oracle and/or its affiliates. All rights reserved.

This program was 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.

Distributed in the hope that it'll 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 has received a copy of the GNU general public License
Along with the program; If not, write to the free software
Foundation, Inc., Wuyi 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"
}
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
# service Mysql-proxy Start

3, testing;
3.1. Management function test:
# yum-y Install MySQL
# mysql-uadmin-padmin-h192.168.1.23--port=4041
Mysql> SELECT * from Backends;

3.2. Create a copy account on the primary server;
Mysql> GRANT All on * * to ' admin ' @ ' 192.168.1.23 ' identified by ' admin ';
mysql> FLUSH privileges;

3.3, on the Mysql-proxy use the main server account login;
# mysql-u Admin-p-H 192.168.1.23
You can log in to the main MySQL server at this time;
mysql> CREATE DATABASE mydb;
The creation of the database should only be sent to the primary MySQL server, you can use tcpdump to obtain the data flow, the creation of the database after the completion of Mysql-proxy and the primary MySQL server has established contact, so on Mysql-proxy use SELECT * FROM Backends can view the server status;

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

MySQL Proxy read/write separation (actual combat summary)