linux implements mysql read-write separation based on proxy



A powerful function of MySQL Proxy is to realize "read-write separation". The basic principle is to let the main database handle write transactions and the slave database handle SELECT queries.

Amoeba for MySQL is an excellent middleware software, which can also achieve read-write separation, load balancing and other functions, and its stability is higher than MySQL Proxy. If you are interested, you can test it.

Environment Description:

Operating system: CentOS 6.5
 Master server:
 Scheduling server MySQL proxy:

proxy installation and configuration

lua is required for proxy installation
Download it on the official website

//After downloading make make install

//Install proxy
//After downloading 
tar zxvf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit /usr/local/mysql_proxy
cd /usr/local/mysql_proxy/
mkdir scripts
cp share/doc/mysql-proxy/rw-splitting.lua scripts/
vim scripts/rw-splitting.lua

if not then = {
              min_idle_connections = 1,           #By default, 4 is changed to 1, which means that the minimum number of links can only be read-write separated when it exceeds 1
             max_idle_connections = 1,           #Default 8
         is_debug = false

proxy usage information

[root@server1 mysql-proxy]# bin/mysql-proxy --help-proxy
  mysql-proxy [OPTION...] - MySQL Proxy

  -P, --proxy-address=<host:port>                         listening address:port of the proxy-server (default: :4040)
  -r, --proxy-read-only-backend-addresses=<host:port>     address:port of the remote slave-server (default: not set)
  -b, --proxy-backend-addresses=<host:port>               address:port of the remote backend-servers (default:
  --proxy-skip-profiling                                  disables profiling of queries (default: enabled)
  --proxy-fix-bug-25371                                   fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
  -s, --proxy-lua-script=<file>                           filename of the lua script (default: not set)
  --no-proxy                                              don't start the proxy-module (default: enabled)
  --proxy-pool-no-change-user                             don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
  --proxy-connect-timeout                                 connect timeout in seconds (default: 2.0 seconds)
  --proxy-read-timeout                                    read timeout in seconds (default: 8 hours)
  --proxy-write-timeout                                   write timeout in seconds (default: 8 hours)

proxy management script

export LUA_PATH=/usr/local/share/mysql-proxy/?.lua

if [ -z "$mode" ] ; then

case $mode in
    mysql-proxy --daemon \
--admin-address=:4401 \
--proxy-address=:3307 \
--proxy-backend-addresses=:3306 \
--proxy-read-only-backend-addresses= \
--proxy-read-only-backend-addresses= \

    killall mysql-proxy

    if $0 stop ; then
      $0 start
      echo  "retart failed!!!"
      exit 1
exit 0
--Daemon is started in the mode of daemon
 --Admin address =: 4401 specifies the management port of mysql proxy. Here, it represents the 4401 port of the machine
 --Proxy address =: 3307 specifies the listening port of mysql proxy, which can also be represented by
 --Proxy backend addresses =: 3306 specifies the port of mysql host
 --Proxy read only backend addresses = specifies the read-only mysql host port
 --Proxy read only backend addresses = specify another read-only mysql host port
 --Proxy Lua script = / usr / local / share / MySQL proxy / RW splitting.lua specifies Lua script. Here, RW splitting script is used for read-write separation




[root@server1 mysql-proxy]#  mysql -h127.0.0.1 -uroot -pHh~802119323
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.17-log MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

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

mysql> show processlist;
| Id | User | Host               | db   | Command     | Time | State                                                         | Info             |
|  8 | bobo | | NULL | Binlog Dump | 8532 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 14 | root | localhost:59434    | NULL | Query       |    0 | starting                                                      | show processlist |
2 rows in set (0.00 sec)


Keywords: MySQL Oracle Database Linux

Added by GM on Wed, 01 Jan 2020 15:46:20 +0200