Based on Mysql-proxy read/write separation

One, you can use the RPM package installation, need to install the Epel package library, or directly from the official website to download the RPM package;

Second, installation configuration mysql-proxy:

650) this.width=650; "title=" mysql-master-salve-proxy.jpg "alt=" wkiom1tl5byrpughaaipga9lpvw679.jpg "src="/HTTP/ S3.51cto.com/wyfs02/m00/59/cf/wkiom1tl5byrpughaaipga9lpvw679.jpg "/>

2.1 Download the required version, here the system platform for the rhel6.4 32-bit system, so take mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit.tar.gz as an example.

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

# cd/usr/local

# LN-SV Mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit Mysql-proxy

Add a delegate user

# Useradd Mysql-proxy

2.2 Provides the SYSV service script for Mysql-proxy, as shown in the following

#!/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

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

2.3 Provide the configuration file/etc/sysconfig/mysql-proxy for the service script, as shown in the following:

# 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 =192.168.1.21:3306--proxy-read-only-backend-addresses=192.168.1.211: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.

Configuration options for 2.4 mysql-proxy

Mysql-proxy's configuration options can be broadly categorized as help options, administrative options, proxy options, and application options, which are described below.

--help

--help-admin

--help-proxy

--help-all ———— above four options are used to obtain help information;

--proxy-address=host:port ———— the address and port of the agent service listener;

--admin-address=host:port ———— the address and port that the management module listens to;

--proxy-backend-addresses=host:port ———— the address and port of the backend MySQL server;

--proxy-read-only-backend-addresses=host:port ———— Backend Read Only the address and port of the MySQL server;

--proxy-lua-script=file_name ———— The Lua script to complete the MySQL proxy function;

--daemon ———— start mysql-proxy in daemon mode;

--keepalive ———— try to restart the mysql-proxy when it crashes;

--log-file=/path/to/log_file_name ———— log file name;

--log-level=level ———— log level;

--log-use-syslog ———— based on syslog logging;

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

--user=user_name ———— The user running the mysql-proxy process;

--defaults-file=/path/to/conf_file_name ———— The configuration file path used by default, and its configuration section uses [Mysql-proxy] identification;

--proxy-skip-profiling ———— Disable profile;

--pid-file=/path/to/pid_file_name ———— process file name;

3. 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), 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

4. Testing

4.1 Management function test

# mysql-uadmin-padmin-192.168.1.22--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 | 192.168.1.21:3306 | Up | RW |                 NULL | 0 |
| 2 | 192.168.1.211:3306 | Unknown | Ro |                 NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows in Set (0.00 sec)

4.2 Read-Write separation test

# mysql-uroot-h192.168.1.22--port=4040-p

This article is from the "Stones" blog, make sure to keep this source http://wangzan18.blog.51cto.com/8021085/1614918

Based on Mysql-proxy read/write separation