Shulou(Shulou.com)06/03 Report--

The following mainly brings you practical examples of MySQL Proxy to achieve the separation of reading and writing. I hope these contents can bring you practical use, which is also the main purpose of this article when I edit MySQL Proxy to achieve the separation of reading and writing. All right, don't talk too much nonsense, let's just read the following.

Planning:

Main mysql server: 192.168.1.21

From mysql server: 192.168.1.22

Mysql read-write separator: 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 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 $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 "$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

# 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 and 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) 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"

}

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. Manage functional testing:

# yum-y install mysql

# mysql-uadmin-padmin-h292.168.1.23-- port=4041

Mysql > SELECT * FROM backends

3.2. Create a replication account on the main server

Mysql > GRANT ALL ON *. * TO 'admin'@'192.168.1.23' IDENTIFIED BY' admin'

Mysql > FLUSH PRIVILEGES

Log in using the account of the primary server on mysql-proxy

# mysql-u admin-p-h 192.168.1.23

Log in to the main mysql server at this point

Mysql > CREATE DATABASE mydb

At this time, the creation of the database should only be sent to the main mysql server, and the data flow can be obtained by using tcpdump. After the creation of the database, mysql-proxy has established contact with the main MySQL server, so you can use SELECT * FROM backends on mysql-proxy to view the server status.

For the above practical examples about the separation of reading and writing in MySQL Proxy, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.