Separation of read and write based on mysql-proxy of Relational Database

Brief:

As a mysql test project, mysql-proxy can achieve read-write separation architecture. Companies with development capabilities improve bug applications in the production environment through secondary development. Here we use mysql-proxy to achieve read-write separation through a case study.

Prepare the environment:

1. System environment: Centos6.5

two。 Database version: 10.0.10-MariaDB-log MariaDB Server

3.Host:

Master host: master.samlee.com 172.16.100.7

Slave host: slave.samlee.com 172.16.100.8

Proxy host: proxy.samlee.com 172.16.100.9

The architecture diagram is as follows:

-

Configure the mysql-proxy server:

(1) install and deploy mysql-proxy

# wget # tar xf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz-C / usr/local/# cd / usr/local/# ln-sv mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit mysql-proxy

(2) create proxy users and modify program directory permissions

# useradd-s / sbin/nologin-r mysql-proxy# chown-R mysql-proxy.mysql-proxy / usr/local/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit/

(3) provide SysV service script for mysql-proxy, as follows:

# vim / EtcUnip rc.dUnip init.dUnip mysql speak proxylemaginit # mysql-proxy This script starts and stops the mysql-proxy daemon## chkconfig:-78 3 cycles processname: mysql-proxy# description: mysql-proxy is a proxy daemon for mysql# Source function library.. / etc/rc.d/init.d/functionsprog= "/ usr/local/mysql-proxy/bin/mysql-proxy" # Source networking configuration.if [- f / etc/sysconfig/network]; then. / etc/sysconfig/networkfi# Check that networking is up. [${NETWORKING} = "no"] & & exit examples 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.pidPROXY_USER= "mysql-proxy" # Source mysql-proxy configuration.if [- f / etc/sysconfig/mysql-proxy]; then. / etc/sysconfig/mysql-proxyfiRETVAL=0start () {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 "$1" 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: $0 {start | stop | restart | reload | status | condrestart | try-restart}" RETVAL=1;; esacexit $RETVAL-- grants execution permission to add to the service list self-launch # chmod + x / etc/rc.d/init.d/mysql-proxy# chkconfig-- add mysql-proxy

(4) provide the configuration file / etc/sysconfig/mysql-proxy for the service script as follows:

# vim / etc/sysconfig/mysql-proxy# Options for mysql-proxyADMIN_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-plugins=proxy-plugins=admin-proxy-backend-addresses=172.16.100.7:3306-proxy-read-only- Backend-addresses=172.16.100.8:3306-- proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "Last Line run option It can be modified as needed. -- proxy-backend-addresses=-- proxy--read-only-backend-addresses= can be specified multiple times, thus specifying multiple servers. Other common options are as follows: mysql-proxy configuration options can be roughly divided into help options, management options, agent options and application options, which are described below. -- help--help-admin-- help-proxy-- help-all-all of the above four options are used to obtain help information;-- proxy-address=host:port-address and port that the proxy service listens to;-admin-address=host:port-address and port that the management module listens to;-proxy-backend-addresses=host:port-the address and port of the back-end mysql server. -- proxy-read-only-backend-addresses=host:port-the address and port of the backend read-only mysql server;-proxy-lua-script=file_name-complete the Lua script for the mysql proxy function;-daemon-start mysql-proxy; in daemon mode-keepalive-attempt to restart the mysql-proxy if it crashes. -- log-file=/path/to/log_file_name-name of log file;-log-level=level-log level;-log-use-syslog-log based on syslog;-- 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-default configuration file path; its configuration segment is identified by [mysql-proxy];-proxy-skip-profiling-disables profile -- pid-file=/path/to/pid_file_name-process file name

(5) copy the following contents to create an admin.lua file and save it to the / usr/local/mysql-proxy/share/doc/mysql-proxy/ directory.

# vim / usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua-- [[$% BEGINLICENSE%$ Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved. This program is 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. This program is distributed in the hope that it will 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 have received a copy of the GNU General Public License along with this program If not, write to the Free Software Foundation, Inc., 51 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"} endfunction 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_RESULTend-- grants execute permission # chown mysql-proxy.mysql-proxy / usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua# chmod + x / usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua

(6) start the mysql-proxy proxy service

# service mysql-proxy start# ss-tnlLISTEN 0128 *: 4041 *: * LISTEN 0128 *: 3306 *: *

(7) configure Master and Slave host master-slave replication mode

Master configuration: # mkdir-pv / mydata/binlogs# chown-R mysql.mysql / mydata/binlogs/# vim / etc/mysql/my.cnfserver-id = 1log-bin=/mydata/binlogs/master-binSlave configuration: # mkdir-pv / mydata/relaylogs# chown-R mysql.mysql / mydata/relaylogs# vim / etc/mysql/my.cnfserver-id = 2relay-log=/mydata/relaylogs/relay-bin

(8) create a user with permissions on the Master host

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES

(9) connect to the Master server

MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass';MariaDB [(none)] > START SLAVE

(10) create a remote connection user

MariaDB [(none)] > GRANT ALL ON *. * TO 'admin'@'172.16.%.%' IDENTIFIED BY' admin';MariaDB [(none)] > FLUSH PRIVILEGES

(11) manage functional testing

-- Operation # mysql-uadmin-padmin-h272.16.100.9-- port=4041mysql > SELECT * FROM backends on the proxy server +-+ | backend_ndx | address | state | type | uuid | connected_clients | +-+- -+ | 1 | 172.16.100.7 NULL 3306 | unknown | rw | NULL | 0 | 2 | 172.16.100.8 NULL 3306 | unknown | ro | NULL | 0 | +-+-+

(12) connect to the server and read and write tests through the proxy server

Note: in the experimental environment, the 172.16.100.7 master server 172.16.100.8 is the slave server 172.16.100.9 is the proxy server-- connect to the server # mysql-h272.16.100.9-uadmin-padminmysql > CREATE DATABASE samleedb -- use the following command on the master and slave servers to monitor-- status of Master during write operations # tcpdump-I any-nn-XX-A-p port 3306 and src 172.16.100.9tcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes17:05:54.617412 IP 172.16.100.9.40783 > 172.16.100.7.3306: Flags [P.], seq 2523263157 link-type LINUX_SLL 2523263186 Ack 2431684210, win 457, options [nop,nop,TS val 12587304 ecr 11832810], length 29 0x0000: 00000001 0006000c 29d4 3452 00000800.) .4R.... 0x0010: 4508 0051 6cf2 4000 4006 ad7b ac10 6409 E.. Ql.cake. {. D. 0x0020: ac10 6407 9f4f 0cea 9665 f0b5 90f0 8e72.. d..O...e.r 0x0030: 8018 01c9 88ae 0000 0101 080a 00c0 1128. (0x0040: 00b4 8dea 1900 0343 5245 4154 4520. Create. 0x0050: 4441 5441 4241 5345 2073 616d 6c65 6564 DATABASE.samleed 0x0060: 62 BMY-manage on the proxy server: mysql > SELECT * FROM backends +-+ | backend_ndx | address | state | type | uuid | connected_clients | +-+- -+ | 1 | 172.16.100.7 NULL 3306 | up | rw | NULL | 0 | 2 | 172.16.100.8 NULL 3306 | unknown | ro | NULL | 0 | +-+-+

(13) use circular scripts to verify the direction of the request

Execute a read request # for i in {1... 10} to mysql-proxy; do mysql- h272.16.100.9-uadmin-padmin-e 'select * from hellodb.classes limit 1 read done monitor the master server and slave server using the following command. # tcpdump-I any-nn-XX-A-p port 3306 and src 172.16.100.9 can be found that when the number of read requests increases, both hosts can see that the reading message is intercepted. Execute a write request MariaDB [hellodb] > source / tmp/hellodb.sql to mysql-proxy. Only when the master end can see the intercepted message, after passing the above test, you can see that the status of both servers is upmysql > SELECT * FROM backends. +-+ | backend_ndx | address | state | type | uuid | connected_clients | +-+- -+ | 1 | 172.16.100.7 NULL 3306 | up | rw | NULL | 0 | 2 | 172.16.100.8 NULL 3306 | up | ro | NULL | 0 | +-+-+

The whole process of reading and writing separation is realized by mysql-proxy above.