MySQL read-write separation

The environment is based on the 2 machines previously configured for MySQL master-slave replication (IP is 10.10.10.13 primary server and 10.10.10.14 as slave server), enabling a 10.10.10.12 machine installation mysql-proxy for read-write separation practices.

Mysql-proxy is a MySQL proxy server, the user's request first sent to Mysql-proxy, and then mysql-proxy to the user's data packet analysis, from the next layer of MySQL database Select a database, the user's request package to MySQL processing.

One, mysql-proxy on the dispatch server

Check the system for required packages

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

1, compiling and installing LUA

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

--2016-07-28 07:20:50--http://www.lua.org/ftp/lua-5.1.5.tar.gz

Resolving www.lua.org ... 148.251.24.173, 2a01:4f8:201:620f::2001

Connecting to www.lua.org|148.251.24.173|:80 ... Connected.

HTTP request sent, awaiting response ... OK

length:221213 (216K) [Application/gzip]

Saving to:ua-5.1.5.tar.gz

100%[===========================================================================>] 221,213 30.7K/s in 7.0s

2016-07-28 07:21:01 (30.7 kb/s)-ua-5.1.5.tar.gzsaved [221213/221213]

2, downloaded after the discovery system actually installed LUA.

#rpm-qa |grep Lua

lua-5.1.4-4.1.e16.i686

#

3, then start the installation configuration Mysql-proxy

# wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz

--2016-07-28 07:50:05--http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz

Resolving mirrors.sohu.com ... 221.236.12.140

Connecting to mirrors.sohu.com|221.236.12.140|:80 ... Connected.

HTTP request sent, awaiting response ... OK

length:11825715 (11M) [Application/octet-stream]

Saving to:ysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz

100%[===========================================================================>] 11,825,715 562K/s in 21s

2016-07-28 07:50:26 (556 kb/s)-ysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gzsaved [11825715/11825715]

#tar ZXVF mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz-c/opt/

#mv/opt/mysql-proxy-0.8.4-linux-rhel5-x86-32bit/opt/mysql-proxy

#vi Mysql-proxy

The contents are as follows

#!/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=/opt/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 = "--daemon"

proxy_options= "--proxy-read-only-backend-addresses=10.10.10.14:3306--proxy-backend-addresses=10.10.10.13:3306- -proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "

Proxy_pid=/opt/mysql-proxy/run/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--log-level=warning-- log-

File=/opt/mysql-proxy/log/mysql-proxy.log

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

5, setting the execution environment

#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

6. Configure and use the Rw-splitting.lua read-write detach script

[[email protected] mysql-proxy]# Find/-name *split*

./share/doc/mysql-proxy/rw-splitting.lua

[email protected] mysql-proxy]# CP./share/doc/mysql-proxy/rw-splitting.lua/opt/mysql-proxy/scripts

[Email protected] mysql-proxy]# Vim/opt/mysql-proxy/scripts/rw-splitting.lua

Modify the read-write separation script Rw-splitting.lua, modify the default connection, do a quick test, do not change the number of connections to reach 4 o'clock to enable read and write separation

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1,

Max_idle_connections = 1,

                                                                                                       

                Is_debug = False                                                                       

}

End

7, after the modification is complete, start mysql-proxy

[[email protected] mysql-proxy]#/opt/mysql-proxy/init.d/mysql-proxy start

Starting Mysql-proxy:

[Email protected] mysql-proxy]# more Log/mysql-proxy.log

2016-07-28 08:31:37: (critical) plugin proxy 0.8.4 started

[[Email protected] mysql-proxy]# PS aux |grep Pro

MySQL 7879 0.0 0.2 3912 1256? S 08:31 0:00/opt/mysql-proxy/libexec/mysql-proxy--proxy-read-only-backend-addresses=10.10.10.14:3306-- proxy-backend-addresses=10.10.10.13:3306--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua--daemon-- Pid-file=/opt/mysql-proxy/run/mysql-proxy.pid--user=mysql--log-level=warning--log-file=/opt/mysql-proxy/log/ Mysql-proxy.log

Root 7886 0.0 0.1 4356 732 PTS/2 s+ 08:32 0:00 grep Pro

[Email protected] mysql-proxy]#

Second, prepare to test the read-write separation effect

1, create a database connection user on the primary server for read-write detach

Mysql> GRANT All on * * to ' myproxy ' @ ' percent ' identified by ' password ';

Query OK, 0 rows Affected (0.00 sec)

mysql> select * from Fuck;

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

| ID | name |

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

| 1 | ABCD |

| 9 | FST |

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

2 rows in Set (0.00 sec)

Mysql>

2, since we have configured the master-slave replication feature, this operation has been synchronized from the database server 10.10.10.14

3, in order to test the effect of read-write separation, temporarily turn off MySQL master-slave copy function

Log on from the database server 10.10.10.14, manage MySQL server via command line login

/opt/mysql/bin/mysql-uroot-p ' aaaaaa '

To close the slave synchronization process

mysql> stop Slave;

Query OK, 0 rows Affected (0.00 sec)

4, connect the master server Test on Mysql-proxy (10.10.10.12)

# mysql-uproxy1-p ' Password '-h10.10.10.13

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 9

Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use fuck;

Database changed

mysql> insert INTO fuck values (009, ' FST ');

Query OK, 1 row Affected (0.00 sec)

mysql> select * from Fuck;

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

| ID | name |

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

| 1 | ABCD |

| 9 | FST |

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

2 rows in Set (0.01 sec)

Mysql>

5, from the server at this time the situation

mysql> select * from Fuck;

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

| ID | name |

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

| 1 | ABCD |

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

1 rows in Set (0.00 sec)

Mysql>

No changes have occurred, no new records exist, MySQL read-write separation has been implemented, all the current write operations are all on the Master master server.

6, Client Connection test

First look at the Mysql-proxy service port

# NETSTAT-NLTP

Active Internet connections (only servers)

Proto recv-q send-q Local address Foreign address State Pid/program Name

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

Then connect on the client (the client needs to have MySQL clients, there is no installation of yum-y install MySQL, soon)

# mysql-uproxy1-ppassword-h 10.10.10.12--port 4040

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 10

Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

mysql> use fuck;

Database changed

mysql> select * from Fuck;

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

| ID | name |

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

| 1 | ABCD |

| 9 | FST |

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

2 rows in Set (0.00 sec)

What you see from the server

# tcpdump-i eth1 Port 3306

Tcpdump:verbose output suppressed, use-v OR-VV for full protocol decode

Listening on eth1, Link-type EN10MB (Ethernet), capture size 65535 bytes

00:20:46.163384 IP 10.10.10.12.45070 > 10.10.10.13.mysql:flags [P.], seq 46:69, ACK 265, win 658, options [nop,nop,ts Val 61366259 ECR 94221439], length 23

00:20:46.163622 IP 10.10.10.13.mysql > 10.10.10.12.45070:flags [P.], seq 265:397, ACK, win 453, options [nop,nop,ts Val 94222286 ECR 61366259], length 132

00:20:46.163668 IP 10.10.10.12.45070 > 10.10.10.13.mysql:flags [.], ACK 397, Win 691, options [Nop,nop,ts Val 61366260 ECR 94222286], length 0

00:20:46.707339 IP 10.10.10.12.45070 > 10.10.10.13.mysql:flags [P.], seq 69:92, ack 397, Win 691, options [nop,nop,ts Val 61366803 ECR 94222286], length 23

00:20:46.707639 IP 10.10.10.13.mysql > 10.10.10.12.45070:flags [P.], seq 397:529, ACK, win 453, options [nop,nop,ts Val 94222830 ECR 61366803], length 132

00:20:46.707724 IP 10.10.10.12.45070 > 10.10.10.13.mysql:flags [.], ACK 529, win 725, options [Nop,nop,ts Val 61366804 ECR 94222830], length 0

^c

Packets Captured

Packets Received by filter

0 packets dropped by kernel

MySQL read-write separation