Mysql-proxy realization of MARIADB read/write separation

About Mysql Proxy

MySQL Proxy is a simple program that is located between your client side and the MySQL server, which can monitor, analyze, or change their communication. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering or modification, and so on.

MySQL Proxy is such a middle-tier agent, simply said, MySQL proxy is a connection pool, responsible for the foreground application connection request forwarded to the background database, and through the use of Lua script, can achieve complex connection control and filtering, so as to achieve read-write separation and load balancing. For the application, the MySQL proxy is completely transparent, and the application only needs to be connected to the listening port of MySQL proxy. Of course, this proxy machine may become a single point of failure, but can be used more than one proxy machine redundancy, in the application server and connection pool configuration to configure multiple proxy connection parameters.

One of the more powerful functions of MySQL Proxy is to achieve "read and write Separation", the basic principle is to let the primary database processing transactional queries, let the server make a select query. Database replication is used to synchronize changes caused by transactional queries to the slave libraries of the cluster.

MySQL Proxy Installation method

First, the source installation

1. When the source is installed, the MySQL proxy depends on the relationship:

Libevent 1.x or higher

Lua 5.1x or higher

Glib2 2.6.0 or higher

Pkg-config.

Libtool 1.5 or higher

MySQL 5.0.x or higher developer files

2. Installation

# Tar XF mysql-proxy-0.8.2.tar.gz

# CD mysql-proxy-0.8.2

#./configure

# make

# Make Check

If the administrator has a password, the above steps need to be done in the following format:

#MYSQL_PASSWORD =root_pwd Make Check

# make Install

By default, Mysql-proxy is installed in/usr/local/sbin/mysql-proxy, and the LUA sample script is installed in the/usr/local/share directory

Second, the General binary format installation

1, download, the current system architecture is Centos6.5 64-bit system

Http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

2. Decompression

#tar XF mysql-proxy-0.8.4-linux-e16-x86-64bit.tar.gz

#mv Mysql-proxy-0.8.4-linux-e16-x86-64bit/usr/local/mysql-proxy

3. Add a proxy User

# Useradd Mysql-proxy

4. Provide SYSV service script for Mysql-proxy

5. Provide configuration file for Mysql-proxy script

MySQL proxy for read-write separation

Environment construction

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/27/9B/wKioL1NzYoHhEAEjAAEOHQfKrzE087.jpg "title=" 1 Schema diagram. png "alt=" wkiol1nzyohheaejaaeohqfkrze087.jpg "/>

As shown

MySQL Master master server	172.16.13.13	MariaDB-5.5.36
MySQL Slave from the server	172.16.13.14	MariaDB-5.5.36
MySQL Proxy Server	172.16.13.2	Mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

First, the implementation of MySQL master-slave replication architecture

For more information on MySQL master-slave replication, please refer to my blog:

http://jungege.blog.51cto.com/4102814/1396276

Second, MySQL Proxy server configuration

1, download the required version, here the system platform for the CentOS6.5 64 system

#wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz# Tar XF mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz# MV Mysql-proxy-0.8.4-linux-el6-x86-64bit/usr/local/mysql-proxy

2. Add a proxy User

# Useradd Mysql-proxy

3. Provide SYSV service script for Mysql-proxy

# Vim/etc/rc.d/init.d/mysql-proxy content is as follows #!/bin/bash## Mysql-proxy This script starts and stops the Mysql-proxy daemon## CHKC  Onfig:-30# 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 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.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= $PR Oxy_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 Star    t) 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;; Esacexit $RETVAL

#chmod +x/etc/rc.d/init.d/mysql-proxy  give execute permission #chkconfig--add mysql-proxy           Add to System Services list #chkconfig Mysql-proxy On boot          up

4. Provide configuration file for Mysql-proxy service script

#vim/etc/sysconfig/mysql-proxy content is as follows

# 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 "proxy_options="--daemon--log-level=info--log-use-syslog--plugins=proxy--plugins =admin--proxy-backend-addresses=172.16.13.13:3306--proxy-read-only-backend-addresses=172.16.13.14: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.

Configuration options for Mysql-proxy

The configuration options for Mysql-proxy can be broadly categorized as help options, administrative options, proxy options, and application options, together with a description of their

--help

--help-admin

--help-proxy

--help-all--above four options are used to obtain help information;

Address and Port of the--proxy-address=host:port--Agent service listener

--admin-address=host:port--the address and port the management module listens to

--proxy-backend-address=host:port--the address and port of the backend MySQL server

--proxy-read-only-backend-addresses=host:port--back-end read-only MySQL server address and port

--proxy-lua-script=file_name--the Lua script that completes the MySQL proxy function

--daemon--Boot in daemon mode mysql-proxy

--keepalive--attempts to restart when the Mysql-proxy crashes

--log-file=/path/to/log_file_name--log file name

--log-level=level--Log Level

--log-use-syslog--Syslog-based logging

--plugins=plugin,.. --Plug-ins loaded at mysql-proxy startup

--user=user_name--users running the mysql-proxy process

--defaults-file=/path/to/confi_file_name--the profile path used by default, and its configuration segment using the [Mysql-proxy] identity

--proxy-skip-profiling--Disabling profile

--pid-file=/path/to/pid_file_name--Process file name

5, copy the content to create a Admin.lua file, save it to/user/local/mysql-proxy/share/doc/mysql-proxy

#vim/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua content is as follows--[[$%beginlicense%$ Copyright (c) +, 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 Softwa Re Foundation; Version 2 of the License. This program was 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 and this 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"}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 = {"Unknow                N "," 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 Startin  G at 0 Types[b.type + 1],--the C-id are 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 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

6. Testing

6.1 Management function test

#service Mysql-proxy Start

#ss-ntl

Management port: 4041

Read/write split port: 3306

[Email protected] ~]# mysql-uadmin-padmin-h172.16.13.2--port=4041welcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 1Server version:5.0.99-agent-admincopyright (c), +, Oracle and/or its affiliate S. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

6.2 Read-Write separation test

Authorize a user on the MySQL master server mariadb [(none)]> grant all on * * to ' root ' @ ' 172.16.13.2 ' identified by ' 123 ';

6.2.1 Next we test on the MySQL Proxy server (172.16.13.2)

########### Multiple read and write operations, so the effect can be clearly as follows:

[Email protected] mysql-proxy]# mysql-uroot-h172.16.13.2-p123--port=3306-e "Select User from Mysql.user" [[Email Prot Ected] mysql-proxy]# mysql-uroot-h172.16.13.2-p123--port=3306-e "Select User from Mysql.user" [[email protected] MySQL -proxy]# mysql-uroot-h172.16.13.2-p123--port=3306-e "CREATE database TestDB" NOTE:--port=3306 does not need to take, The default listening read-write detach connection port is 3306. If Mysql-proxy is installed on a MySQL server, the port to which MySQL listens is changed to a different port, otherwise it will conflict (directly in the MY.CNF configuration file)

6.2. After more than 2 read and write operations, we go to the management port to view the read/write separation situation

[Email protected] ~]# mysql-uadmin-h172.16.13.2-padmin--port=4041mysql> select * from backends;+-------------+--- ----------------+-------+------+------+-------------------+| Backend_ndx | Address           | state | type | uuid | connected_clients |+-------------+-------------------+-------+------+------+------ -------------+|           1 | 172.16.13.13:3306 | Up    | RW   | NULL |                 0 | |           2 | 172.16.13.14:3306 | Up    | ro   | NULL |                 0 |+-------------+-------------------+-------+------+------+-------------------+2 rows in Set (0.00 sec)

Based on the mysql-proxy implementation of the MARIADB master-slave replication architecture read and write separation!

Summary: The entire example is not difficult to find, if the schema is the lamp architecture, then the PHP program code directly connected to the MySQL proxy address, MySQL proxy will read and write to the backend MySQL master-slave replication schema, MySQL master server is responsible for reading and writing, from the server is only read, so as to achieve a read-write separation architecture, if you worry about MySQL proxy single point of failure, you can establish redundancy, more than a few .

PS: The level is limited, if have improper please point out, MARIADB's MMM structure is coming soon, please look forward!!!

This article is from the "Westerly Lean Pig" blog, please be sure to keep this source http://jungege.blog.51cto.com/4102814/1411266