MySQL read-write separation--mysql-proxy

Preparation Prerequisites:

1, MySQL installation and configuration--omitted

Version: Server version:5.6.35

1.1 System version:

[Email protected] package]# lsb_release-a

LSB Version:: Core-4.1-amd64:core-4.1-noarch

Distributor ID:CentOS

Description:CentOS Linux release 7.2.1511 (Core)

Release:7.2.1511

Codename:Core

2. Scene description

Database Master master server: 192.168.1.83

Database slave from server: 192.168.1.66

Mysql-proxy Dispatch server: 192.168.1.67

The following operations are performed on 192.168.1.67, the Mysql-proxy dispatch server.

Configure master-slave replication and start slave on 192.168.1.66;

Install the required packages on the Mysql-proxy

Yum install-y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* Libeven t* glib* readline-devel*

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

Download:

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

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

3.1 Tar XF lua-5.3.4.tar.gz

3.2 CD lua-5.3.4

3.3 VI Src/makefile

Cflags=-o2-wall-fpic-wextra-dlua_compat_5_2 $ (syscflags) $ (mycflags)

3.4 Make Linux

3.5 Make Install

Download:

Https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

4. Tar XF mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

5, MV Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/usr/local/mysql-proxy

6, Cd/usr/local/mysql-proxy

7. mkdir Lua #创建脚本存放目录

mkdir logs #创建日志目录

8, CP Share/doc/mysql-proxy/rw-splitting.lua./lua #复制读写分离配置文件

9, CP Share/doc/mysql-proxy/admin-sql.lua./lua #复制管理脚本

10. Vi/etc/init.d/mysql-proxy #创建mysql-proxy Service Management script

[Email protected] ~]# Cat/etc/init.d/mysql-proxy

#!/bin/sh

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

#

# Chkconfig:-78 30

# Processname:mysql-proxy

# Description:mysql-proxy is a proxy daemon to MySQL

# Source function library.

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

#PROXY_PATH =/usr/local/bin

Proxy_path=/usr/local/mysql-proxy/bin

Prog= "Mysql-proxy"

# Source Networking configuration.

. /etc/sysconfig/network

# Check that networking are up.

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

# Set Default Mysql-proxy configuration.

proxy_options= "--log-level=info \

--log-file=/var/log/mysql-proxy.log \

--plugins=proxy-b 192.168.1.83:3306-r 192.168.1.66:3306 \

--proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua \

--plugins=admin--admin-username=admin \

--admin-password=admin \

--admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua "

Proxy_pid=/usr/local/mysql-proxy/mysql-proxy.pid

# Source Mysql-proxy configuration.

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

. /etc/sysconfig/mysql-proxy

Fi

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

# By default it's all good

Retval=0

# See how we were called.

Case "$" in

Start

# Start Daemon.

Echo-n $ "Starting $prog:"

$NICELEVEL $PROXY _path/mysql-proxy $PROXY _options--daemon--pid-file= $PROXY _pid--user=mysql

Retval=$?

Echo

if [$RETVAL = 0]; Then

Touch/var/lock/subsys/mysql-proxy

Fi

;;

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

;;

Restart

$ stop

Sleep 3

$ start

;;

Condrestart)

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

;;

Status

Status Mysql-proxy

Retval=$?

;;

*)

echo "Usage: $ {Start|stop|restart|status|condrestart}"

Retval=1

;;

Esac

Exit $RETVAL

11. Description of the script change:

Proxy_path=/usr/local/mysql-proxy/bin//define MYSQL-PROXY service binary file path

--proxy-read-only-backend-addresses=192.168.1.66:3306//definition from server readable

--proxy-backend-addresses=192.168.1.83:3306 //define master server writable--proxy-lua-script=/usr/local/mysql-proxy/lua/ Rw-splitting.lua "//define LUA read-write detach script path

Proxy_pid=/usr/local/mysql-proxy/mysql-proxy.pid//define Mysql-proxy PID file path

--daemon//definition starts in daemon mode

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

--pid-file= $PROXY _pid//define mysql-proxy PID file path

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

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

--log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log//define log log file path

12, chmod 755/etc/init.d/mysql-proxy

13, Vi/usr/local/mysql-proxy/lua/rw-splitting.lua //Modify the read/ write separation script//Modify the default connection for rapid testing, Do not modify to enable read-write separation until the number of connections is 4 o'clock

--Connection pool

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1,//defaults to 4

Max_idle_connections = 1,//defaults to 8

Is_debug = False

}

End

14. Start Mysql-proxy

/etc/init.d/mysql-proxy start

15. Startup success:

[Email protected] ~]# netstat-nutlp| grep MySQL

TCP 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 18520/mysql-proxy

TCP 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 18520/mysql-proxy

16. Test read-Write separation effect

Create a database connection user for read-write detach

Log in to the primary database server 192.168.1.83, manage the MySQL server via the command line

Mysql> GRANT All on * * to ' proxy1 ' @ ' 192.168.1.67 ' identified by ' password ';

Since we have configured the master-slave replication feature, this operation has been synchronized from the database server 172.16.1.66.

In order to clearly see the effect of read and write separation, you need to temporarily turn off the MySQL master-slave replication function, the line is required master-slave synchronization, here just to do experimental test, need to close

Log on from the database server 192.168.1.66

To close the slave synchronization process

mysql> stop Slave;

Connect to Mysql-proxy

[Email protected] ~]# mysql-uproxy1-p ' password '-p4040-h192.168.1.67

Insert 2 Piece of data

mysql> INSERT INTO T1 values ("third");

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO T1 values (121, ' for ');

Query OK, 1 row affected (0.01 sec)

Verify:

Mysql-proxy:

Mysql> select * from T1; Did not see the data

Master

Mysql> select * from T1; can see the data

Slave

Mysql> select * from T1; Did not see the data

Connection Management port:

[Email protected] ~]# mysql-uadmin-padmin-h192.168.1.67-p4041

Mysql> SELECT * from Backends;

+-------------+-------------------+-------+------+------+-------------------+

| Backend_ndx | Address | State | Type | UUID | connected_clients |

+-------------+-------------------+-------+------+------+-------------------+

| 1 | 192.168.1.83:3306 | Up | RW |                 NULL | 0 |

| 2 | 192.168.1.66:3306 | Up | Ro |                 NULL | 0 |

+-------------+-------------------+-------+------+------+-------------------+

2 rows in Set (0.00 sec)

Up: Indicates that read-write separation is in effect; UnKnown: not yet.

Conclusion: When the data is inserted in Mysql-proxy, it is written to master and the query data is viewed from slave, so the data is not queried. When the data is inserted on the slave, it can be seen on the mysql-proxy, stating that the read is from the slave and written on the master.

MySQL read-write detach--mysql-proxy