Installation Configuration Mysql-proxy

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

# Tar XF mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz-c/usr/local
# cd/usr/local
# LN-SV Mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit Mysql-proxy

Add a delegate user
# Useradd Mysql-proxy

4.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

Mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins= "proxy"-- Proxy-backend-addresses= "172.16.0.106:3306"--proxy-read-only-backend-addresses= "172.16.100.107:3306"

Tail/var/log/mysql-proxy.log
Netstat-tlnp
4040

Main
Grant all on * * to [e-mail protected] '% ' identified by ' nihao201314 '
Flush Privileges

Mysql-uroot-p-H 172.16.100.201--port=4040 201 Proxy
Show global variables like '%dns% '
Show global variables like '%hostname% '
Show global variables like '%name% '
Skip_name_resolve off
SET @ @global. skip_name_resolve=on


Killall Mysql-proxy

Mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins= "proxy"-- Proxy-backend-addresses= "172.16.0.106:3306"--proxy-read-only-backend-addresses= "172.16.100.107:3306"-- Proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "






4.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-file="/var/log/mysql-proxy.log "--plugins=proxy--plugins=admin-- proxy-backend-addresses=172.16.100.6:3306--proxy-read-only-backend-addresses=172.16.100.7: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.

Service Mysql-proxy
mysql-uadmin-padmin-h172.16.100.107--port=4040-e '

Configuration options for 4.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;




5. 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

Mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins= "proxy"-- Proxy-backend-addresses= "172.16.0.106:3306"--proxy-read-only-backend-addresses= "172.16.100.107:3306"-- Proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "--plugins=admin--admin-username = "Admin"--admin-password= "admin"--admin-lua-script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"



/usr/local/mysql-proxy/bin/mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins= " Proxy "--proxy-backend-addresses=" 172.23.208.39:3306 "--proxy-read-only-backend-addresses=" 172.23.208.40:3306 "-- Proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua--plugins=admin--admin-username= "Admin"--admin-password= "admin"--admin-lua-script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"


Tail/var/log/mysql-proxxy.log
4040 4041 Management Interface


6. Testing

6.1 Management function test

# mysql-uadmin-padmin-h172.16.100.107--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.100.6:3306 | Up | RW |                 NULL | 0 |
| 2 | 172.16.100.7:3306 | Up | Ro |                 NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in Set (0.00 sec)

6.2 Read-Write separation test

# mysql-uroot-pmagedu.com-h172.16.100.107--port=4040


Mysql-proxy--daemon--defaults-file= "/ETC/MYSQL-PROXY.CNF"

Installation Configuration Mysql-proxy