Read/write Separation mysql-proxy

MySQL Read-write separation

Scenario Description:

Database Master master server:192.168.203.138

Database Slave from server:192.168.203.133

Mysql-proxy Dispatch server:192.168.203.134

The following operations are performed on 192.168.203.134 , the Mysql-proxy Dispatch server .

3.1 MySQL installation and configuration

The exact installation process is the same as above.

3.2 checking the system for required packages

by Rpm-qa | grep name verifies that the following packages are all installed.

gcc* gcc-c++* autoconf* automake* zlib*libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*

libevent* glib*

Yum Install epel*-y

if the relevant software package is missing, it can be installed online via yum-y install or directly from the system installation CD and installed by RPM-IVH mode.

3.3 Compiling and installing LUA

Mysql-proxy 's read-write separation is primarily implemented through the Rw-splitting.lua script, so it is necessary to install Lua.

Lua can be obtained in the following ways

Download the source package from http://www.lua.org/download.html

Search related rpm packages from rpm.pbone.net

download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm

download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

The above link cannot be linked using the address below

Wgethttp://110.96.193.10:82/1q2w3e4r5t6y7u8i9o0p1z2x3c4v5b/down1.chinaunix.net/distfiles/lua-5.1.tar.gz

Here we recommend the use of the source package for installation

Cd/opt/install

wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

Tar zvfx lua-5.1.4.tar.gz

CD lua-5.1.4

VI Src/makefile

Add-fpic to the cflags=-o2-wall $ (mycflags) record and change to Cflags=-o2-wall-fpic $ (mycflags) To avoid errors during compilation.

Make Linux

A series of error prompt solutions appear

Make Linux

in the include from lua.h : - of the file,

from LUA.C : :

luaconf.h:275:31: Error: readline/readline.h : No file or directory

luaconf.h:276:30: Error: Readline/history.h : No file or directory

lua.c:in function ' Pushline ':

lua.c:182: WARNING: Implicitly declaring a function ' ReadLine '

lua.c:182: warning: Assigning integers to pointers, not type conversions

lua.c:in function ' Loadline ':

lua.c:210: WARNING: Implicitly declaring a function ' add_history '

make[2]: * * * [LUA.O] Error 1

MAKE[2]: leavingdirectory '/data0/software/lua-5.1.4/src '

make[1]: * * * [Linux] Error 2

MAKE[1]: leavingdirectory '/data0/software/lua-5.1.4/src '

Make : * * * [Linux] Error 2

Yum Install Libtermcap-devel ncurses-devellibevent-devel readline-devel

Make install

CP etc/lua.pc/usr/lib/pkgconfig/

Exportpkg_config_path= $PKG _config_path:/usr/lib/pkgconfig

3.4 Installation Configuration mysql-proxy

Mysql-proxy can be obtained from the following URLs:

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

The address above cannot get the address, use the address below

Wgethttp://mirrors.sohu.com/mysql/mysql-proxy/mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

It is recommended to use a compiled binary version, because the latest version when compiling with the source package Mysql-proxy has high requirements for Automake,glib, and libevent versions, which are the basic packages of the system and are not recommended for forced updates.

And these compiled binaries are all in a unified directory after decompression, so it is recommended to select the following versions:

level RHEL5 platform:

Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz

A -bit RHEL5 platform:

Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

Test Platform for RHEL5 , so select the package of the three-bit

Wgethttp://mysql.cdpa.nsysu.edu.tw/downloads/mysql-proxy/mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

Tar xzvf mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

MV Mysql-proxy-0.8.4-linux-rhel5-x86-64bit/opt/mysql-proxy

Create a mysql-proxy service management script

mkdir/opt/mysql-proxy/init.d/

Vim Mysql-proxy

#!/bin/sh

02 #

#mysql-proxy This script starts and stops the Mysql-proxy daemon

04 #

#chkconfig:-78 30

#processname: Mysql-proxy

#description: Mysql-proxy is a proxies daemon to MySQL

08

#Source function Library.

Ten./etc/rc.d/init.d/functions

11

#PROXY_PATH =/usr/local/bin

Proxy_path=/opt/mysql-proxy/bin

14

Prog= "Mysql-proxy"

16

#Source networking configuration.

/etc/sysconfig/network.

19

#Check that networking are up.

[${networking} = "No"] && exit 0

22

#Set default Mysql-proxy configuration.

#PROXY_OPTIONS = "--daemon"

Proxy_options= "--proxy-read-only-backend-addresses=192.168.203.133:3306--proxy-backend-addresses= 192.168.203.138:3306--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "

Proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid

27

#Source mysql-proxy configuration.

if[-f/etc/sysconfig/mysql-proxy]; Then

30. /etc/sysconfig/mysql-proxy

+ fi

32

Path= $PATH:/usr/bin:/usr/local/bin: $PROXY _path

34

#By default it s all good

Approx. retval=0

37

#See how we were called.

The case "$" in

+ start)

# Start Daemon.

Echo-n $ "Starting $prog:"

$NICELEVEL $PROXY _path/mysql-proxy$proxy_options--daemon--pid-file= $PROXY _pid--user=mysql--log-level=wa Rning--log-file=/opt/mysql-proxy/log/mysql-proxy.log

Retval=$?

Echo

if [$RETVAL = 0]; Then

Touch/var/lock/subsys/mysql-proxy

-Fi

49;;

Stop)

# Stop Daemons.

Echo-n $ "Stopping $prog:"

Killproc $prog

Retval=$?

Echo

if [$RETVAL = 0]; Then

Rm-f/var/lock/subsys/mysql-proxy

Rm-f $PROXY _pid

-Fi

60;;

Restart)

$ stop

3 Sleep

$ start

65;;

Condrestart)

[-e/var/lock/subsys/mysql-proxy]&& $ restart

68;;

Status)

The Status Mysql-proxy

Retval=$?

72;;

73 *)

The echo "Usage: $0{start|stop|restart|status|condrestart}"

Retval=1

76;;

Esac

78

Exit$retval

Detailed Script parameters:

==============================================

Proxy_path=/opt/mysql-proxy/bin// define mysql-proxy service binary file path

proxy_options= "--admin-username=root \// define Internal Management Server account

--admin-password=password \// Define internal Management Server password

--proxy-read-only-backend-addresses=192.168.10.131:3306\// define backend read-only from server address

--proxy-backend-addresses=192.168.10.130:3306\// define back-end primary server address

--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua\// define LUA Management script path

--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "\// define Lua read-write detach script path

proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid// defining mysql-proxy PID file path

$NICELEVEL $PROXY _path/mysql-proxy$proxy_options \

--daemon \// definition starts in daemon mode

--keepalive \// causes the process to automatically recover after an abnormal shutdown

--pid-file= $PROXY _pid \// define mysql-proxypid file path

--user=mysql \//Start the service as a mysql user

--log-level=warning \// define Log log level, from high to Low (Error|warning|info|message|debug)

--log-file=/opt/mysql-proxy/log/mysql-proxy.log// defining The log log file path

==============================================

CP mysql-proxy/opt/mysql-proxy/init.d/

chmod +x/opt/mysql-proxy/init.d/mysql-proxy

Mkdir/opt/mysql-proxy/run

Mkdir/opt/mysql-proxy/log

Mkdir/opt/mysql-proxy/scripts

Configuring and using the Rw-splitting.lua read-write detach script

The latest script we can get from the latest mysql-proxy source Package

Cd/opt/install

Wgethttp://mysql.cdpa.nsysu.edu.tw/downloads/mysql-proxy/mysql-proxy-0.8.4.tar.gz

Tar xzvf mysql-proxy-0.8.4.tar.gz

CD mysql-proxy-0.8.4

CP lib/rw-splitting.lua/opt/mysql-proxy/scripts

Modify read-write detach script Rw-splitting.lua

Modify the default connection, do a quick test, do not change the number of connections to reach 4 to enable read-write separation

Vim/opt/mysql-proxy/scripts/rw-splitting.lua

=============================

--Connection pool

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1,// default is 4

Max_idle_connections = 1,// default is 8

Is_debug = False

}

End

=============================

after the modification is complete, start mysql-proxy

/opt/mysql-proxy/init.d/mysql-proxy start

This article is from the "Learn Linux" blog, so be sure to keep this source http://10265013.blog.51cto.com/10255013/1762207

Read-write detach mysql-proxy