1. The principle of separation of reading and writing:

Read-write separation is to modify on the main server, data will be synchronized to the slave server, the slave server can only provide read data, can not write, backup at the same time to achieve the optimization of database performance, and improve server security.

Installation environment:

linux environment	centos7.3
192.168.2.201	Master master master database
192.168.2.202	slave Slave from database
192.168.2.203	mysql-proxy Middleware

2. The separation of reading and writing of MySQL is based on the master-slave replication of mysql, so we need to build the master-slave replication environment of MySQL first. The previous blog has written that there are no more details here. Master-slave replication of mysql

Next we operate on 192.162.2.203 machine:

Installation Dependency:

yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline-devel*

MySQL-Proxy reads and writes separately mainly through rw-splitting.lua script, so it is necessary to install lua.

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

tar xf lua-5.3.4.tar.gz

cd lua-5.3.4

vi src/Makefile

#Replace with the following configuration and delete the original
CFLAGS= -O2 -Wall -fPIC -Wextra -DLUA_COMPAT_5_2 $(SYSCFLAGS) $(MYCFLAGS)

make linux

make install

3. Download the myqsl-proxy middleware installation package, decompress and set the group permissions.

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

tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local

cd /usr/local/

mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

useradd mysql-proxy

chown -R mysql-proxy.mysql-proxy mysql-proxy*

cd /usr/local/mysql-proxy

#Create two directories for lua scripts and logs
mkdir lua 
mkdir logs 


#Copy Read-Write Separation Profile
cd /usr/local/mysql-proxy/lua
cp share/doc/mysql-proxy/rw-splitting.lua .
#Modify rw-splitting.lua configuration
proxy.global.config.rwsplit = {
min_idle_connections = 1, //The default is 4.
max_idle_connections = 1, //The default is 8.
is_debug = false
}


#Create admin.lua script without using the admin-sql.lua script that comes with it
vim admin.lua

#Add something

--[[ $%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"
}
end


function 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_RESULT
end

4. Create the configuration file needed for mysql-proxy startup

vi /etc/mysql-proxy.cnf   #create profile

[mysql-proxy]
user=root
admin-username=wyl
admin-password=1234
proxy-address=192.168.2.203:4040
proxy-read-only-backend-addresses=192.168.2.202:3306
proxy-backend-addresses=192.168.2.201:3306
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true
~              

Note that when copying the above configuration, there must be no space behind each line. Otherwise, the following errors will be reported. Especially when copying other people's blogs, there is a comment behind each line, that is, there will be spaces in this place.

 Key file contains key 'daemon' which has value that cannot be interpreted.

Start mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/etc/mysql-proxy.cnf --plugins=proxy --plugins=admin

View processes and ports after startup:

5. Create a database connection user for read-write separation
Log on to the main database server 192.168.2.201 and manage MySQL server through command line login

[root@sqldb01 opt]# mysql -uroot -p1234

mysql> GRANT ALL ON *.* TO 'wyl'@'192.168.%.%' IDENTIFIED BY '1234';

mysql>flush privileges;

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

6. After entering the mysql-proxy machine, it is found that mysql, version:5.0.99-agent-admin, has become a proxy.

When you enter the following instructions into the database command line, you can add, delete and modify the database check operation.

mysql -uwyl -p1234  -h 192.168.2.203 -P4040

Perform multiple queries and insert queries. If the state in the following figure appears to be up state, the read-write separation function is implemented.