Mysql Proxy Lua master-slave build read/write Splitting Process

Mysql Proxy LuaMaster-slave read/writeSeparationThe whole process is the content to be introduced in this article, mainly to understandMysql ProxyRead/writeSeparationThe whole process is explained in detail. For details, let's look at the details.

DownloadMysql ProxyThe latest version. The latest version is MySQL Proxy 0.8.1 alpha.

Follow these steps to install Mysql Proxy:

Extract

 
 

  
  
Mysql Proxy-0.8.1-linux-glibc2.3-x86-32bit.gz  
  
  
tar zxvf Mysql Proxy-0.8.1-linux-glibc2.3-x86-32bit.gz  
  
  
cp -R Mysql Proxy-0.8.1-linux-glibc2.3-x86-32bit /usr/local/proxy-mysql 
 
 

Create or edit the/etc/Mysql Proxy. cnf file and add the following content (the specific parameters are modified according to the actual situation ):

 
 

  
  
vi /etc/Mysql Proxy.cnf  
  
  
[Mysql Proxy]  
  
  
admin-username = zhangdongyu 
  
  
admin-password = 123123 
  
  
daemon = true 
  
  
keepalive = true 
  
  
proxy-backend-addresses = 192.168.0.88:3306  
  
  
proxy-read-only-backend-addresses = 192.168.0.88:3307  
  
  
proxy-lua-script = /usr/local/proxy-mysql/share/doc/Mysql Proxy/rw-splitting.lua  
  
  
admin-lua-script = /usr/local/proxy-mysql/share/doc/Mysql Proxy/admin-sql.lua  
  
  
log-file = /usr/local/proxy-mysql/cn.log  
  
  
log-level = debug 
 
 

Main Parameter Annotations:

 
 

  
  
Proxy-backend-addresses mysql master database write) Address
  
  
Proxy-read-only-backend-addresses mysql read from database) Address
  
  
Proxy-lua-script read/write splitting script
  
  
Admin-lua-script admin script
  
  
Admin-username: the same user must be created on both the master and slave databases)
  
  
Admin-password Database password
  
  
Daemon process running
  
  
Keepalive maintains two processes for connection startup. Process 1 is used to monitor process 2. If process 2 dies and is automatically rebuilt, this parameter cannot be used in earlier versions)
 
 

Edit the profile/. bash_profile script

 
 

  
  
Vi/etc/profile (or. bash_profile)
  
  
LUA_PATH = "/usr/local/proxy-mysql/share/doc/Mysql Proxy /?. Lua"
  
  
Export LUA_PATH
  
  
Export PATH = $ PATH:/usr/local/proxy-mysql/bin
  
  
: Wq
  
  
Source/etc/profile
 
 

Create a Mysql Proxy. sh script for ease

 
 

  
  
#!/bin/bash  
  
  
mode=$1  
  
  
if [ -z "$mode" ] ; then  
  
  
mode="start" 
  
  
fi  
  
  
case $mode in  
  
  
start)  
  
  
Mysql Proxy --defaults-file=/etc/Mysql Proxy.cnf>/usr/local/proxy-mysql/cn.log   
  
  
;;  
  
  
stop)  
  
  
killall -9 Mysql Proxy  
  
  
;;  
  
  
restart)  
  
  
if $0 stop ; then  
  
  
$0 start  
  
  
else  
  
  
echo "Restart failed!"  
  
  
exit 1  
  
  
fi  
  
  
;;  
  
  
esac  
  
  
exit 0 
 
 

Start Mysql Proxy

Sh script

Start:./Mysql Proxy. sh or./Mysql Proxy. sh start

Startup log

Restart:./Mysql Proxy. sh restart

Stop:./Mysql Proxy. sh stop

Test:

To achieve the test effect, modify the following two lines of the admin-sql.lua script

 
 

  
  
Vi/usr/local/proxy-mysql/share/doc/Mysql Proxy/rw-splitting.lua
  
  
If not proxy. global. config. rwsplit then
  
  
Proxy. global. config. rwsplit = {
  
  
Min_idle_connections = 1, # modify the minimum connection to 1
  
  
Max_idle_connections = 2, # change the maximum connection to 2
  
  
Is_debug = true # debug is true
  
  
}
  
  
End
 
 

Restart Mysql Proxy

Enable two terminals to connect to the proxy respectively:

 
 

  
  
ONE ： ./mysql -h192.168.0.88 -uzhangdongyu -p123123 -P4040  
  
  
TWO ： ./mysql -h192.168.0.88 -uzhangdongyu -p123123 -P4040 
 
 

You can see the connection success information:

Operate ONE client:

Execute the select statement to observe the Database log File

 
 

  
  
select * from z_member_info; 
 
 

Observe the log file of the Master/Slave database and find that the query occurs in the master database?

 
 

  
  
Master Database: 192.168.0.88: 3306
  
  
Log File: tail-f/usr/local/mysql/localstate/mysqlexecute. log
 
 

This is because the Mysql Proxy agent detects the client connection. When the connection does not exceed the default value of min_idle_connections, read/write splitting is not performed, that is, the query operation will occur to the master database.

Operate the TWO client:

Execute the select statement to observe the Database log File

 
 

  
  
select * from z_member_info; 
 
 

Observe the log files of the master and slave databases, and find that the master database does not have any records, and the query logs are stored in the slave database.

 
 

  
  
SLAVE: 192.168.0.88: 3306
  
  
Log File: tail-f/usr/local/mysql3307/localstate/mysqlexecute. log
 
 

The insert operation is performed on two terminals, and the write operation is always recorded by the master database 3306.

So far, the Mysql Proxy installation test is complete.

Summary:Mysql Proxy LuaMaster-slave read/writeSeparationThe entire process has been introduced. I hope this article will help you!