**

mysql-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: 172.25.254.1
 Slave: 172.25.254.2
 Scheduling server MySQL proxy: 172.25.254.1

proxy installation and configuration

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

http://www.lua.org/download.html

//After downloading make make install

//Install proxy
http://mirrors.sohu.com/mysql/MySQL-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 proxy.global.config.rwsplit then
     proxy.global.config.rwsplit = {
              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
      }
end

proxy usage information

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

proxy-module
  -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: 127.0.0.1:3306)
  --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

#!/bin/sh
export LUA_PATH=/usr/local/share/mysql-proxy/?.lua

mode=$1
if [ -z "$mode" ] ; then
  mode="start"
fi

case $mode in
  'start')
    mysql-proxy --daemon \
--admin-address=:4401 \
--proxy-address=:3307 \
--proxy-backend-addresses=:3306 \
--proxy-read-only-backend-addresses=172.25.254.2:3306 \
--proxy-read-only-backend-addresses=172.25.254.3:3306 \
--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua
    ;;

  'stop')
    killall mysql-proxy
    ;;

  'restart')
    if $0 stop ; then
      $0 start
    else
      echo  "retart failed!!!"
      exit 1
    fi
    ;;
esac
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 127.0.0.1:3307
 --Proxy backend addresses =: 3306 specifies the port of mysql host
 --Proxy read only backend addresses = 192.168.1.1:3306 specifies the read-only mysql host port
 --Proxy read only backend addresses = 192.168.1.2:3306 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

**

test

**

[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
owners.

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 | 172.25.254.2:55477 | 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)

mysql>