MySQL read-write separation (mysql-proxy)

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

Mysql-proxy is such a middle-tier agent, simply said, Mysql-proxy is a connection pool, is responsible for the front-end application of connection requests 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 Mysql-proxy's listening port. Of course, this proxy machine may become a single point of failure, but can use multiple proxy machine as redundancy, in the Application Server connection pool configuration to multiple proxy connection parameters can be configured.

One of the more powerful features of Mysql-proxy is the realization of "read and write Separation", the basic principle is to let the primary database process transactional queries, so that select queries are processed from the library. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster.

Mysql-proxy command

• --help-all ———— to get all the help information

• --proxy-address=host:port ———— the address and port that the agent service listens on

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

• --proxy-backend-addresses=host:port ———— the address and port of the backend MySQL server (primary server)

• --proxy-read-only-backend-addresses=host:port ———— Backend read-only MySQL server address and port (from server)

• --proxy-lua-script=file ———— The Lua script to complete the MySQL proxy function

• --daemon ———— start in daemon mode mysql-proxy

• --defaults-file=/path/to/conf_file_name ———— The default configuration file path used

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

• --log-level=level ———— Log Level

• --log-use-syslog ———— Syslog-based logging

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

The following is done on the basis of the previous blog (MySQL master-slave copy).

Http://www.cnblogs.com/Eivll0m/p/3776496.html

1. Description of the environment and system software version:

System version: CentOS 6.3_x86_64

MySQL version: mysql-5.6.17

Mysql-proxy version: mysql-proxy-0.8.4 (:http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz)

2. Deploying Mysql-proxy, the following actions are not specified, and are indicated on the Mysql-prxoy (172.16.10.60) server.

2.1 Check if LUA package is installed, Mysql-proxy need to use LUA script, the following means have been installed.

# rpm -q lua
lua-5.1.4-4.1.el6.x86_64
2.2 Create proxy user

# useradd -r mysql-proxy
2.3 Download the common compression package mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz (use direct decompression)

# wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz
# tar xf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz -C / usr / local
# cd / usr / local /
# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy
2.4 Set mysql-proxy related file permissions

chown -R root: mysql-proxy / usr / local / mysql-proxy / *
2.5 Update environment variables

# vi /etc/profile.d/mysql-proxy.sh
export PATH = $ PATH: / usr / local / mysql-proxy / bin
#. / etc / profile
2.6 Start mysql-proxy

# mysql-proxy --daemon --log-level = debug --log-file = / var / log / mysql-proxy.log --plugins = "proxy" --proxy-backend-addresses = "172.16.10.72: 3306 "--proxy-read-only-backend-addresses =" 172.16.10.61:3306 "
2.7 Check whether mysql-proxy starts normally

View through the command netstat -antplgrep mysql-prxoy or tail /var/log/mysql-proxy.log

2.8 Create a test account on the master server (172.16.10.72)

mysql> GRANT ALL ON *. * TO [email protected] ‘172.16.10.%’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected (0.22 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec) ‘
2.9 Test on the slave server (172.16.10.61) whether it can connect to mysql-proxy through the proxy port 4040 (the following indicates that the test is successful)

[email protected] _slave ~] # mysql -uproxy -p -h172.16.10.60 -P 4040
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 456
Server version: 5.6.17-log Source distribution

Copyright (c) 2000, 2014, 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>
2.10 Configure read and write separation, there is a script named rw-splitting.lua in the installation directory of mysql-proxy, through which you can achieve read and write separation, the path is / usr / local / mysql-proxy / share / doc / mysql- proxy / rw-splitting.lua.

# ls / usr / local / mysql-proxy / share / doc / mysql-proxy / | grep rw-splitting.lua
rw-splitting.lua
Kill the current mysql-proxy process, and then add the lua script to the startup options to restart mysql-proxy to achieve read and write separation

# mysql-proxy --daemon --log-level = debug --log-file = / var / log / mysql-proxy.log --plugins = "proxy" --proxy-backend-addresses = "172.16.10.72: 3306 "--proxy-read-only-backend-addresses =" 172.16.10.61:3306 "--proxy-lua-script =" / usr / local / mysql-proxy / share / doc / mysql-proxy / rw-splitting .lua "
View logs

# tail /var/log/mysql-proxy.log
2014-06-13 16:23:04: (message) added read / write backend: 172.16.10.72:3306
2014-06-13 16:23:04: (message) added read-only backend: 172.16.10.61:3306
2014-06-13 16:40:11: (message) Initiating shutdown, requested from signal handler
2014-06-13 16:40:11: (message) shutting down normally, exit code is: 0
2014-06-13 16:40:17: (debug) chassis-path.c.122: adjusting relative path (/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua) to base_dir (/usr/local/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit). New path: /usr/local/mysql-proxy-0.8.4-linux-glibc2.3-x86- 64bit / /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
2014-06-13 16:40:17: (critical) plugin proxy 0.8.4 started
2014-06-13 16:40:17: (debug) max open file-descriptors = 1024
2014-06-13 16:40:17: (message) proxy listening on port: 4040
2014-06-13 16:40:17: (message) added read / write backend: 172.16.10.72:3306
2014-06-13 16:40:17: (message) added read-only backend: 172.16.10.61:3306
2.11 Provide a management interface for mysql-proxy, which is convenient for viewing the status and access type of the back-end mysql server at any time in the future to realize the management function

 
# vi /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
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 0types [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
2.12 Restart mysql-proxy

Note that the following startup options are added this time because we have added additional plug-ins to add new features. The options are as follows.

--plugins = admin plugins loaded when mysql-proxy starts;

--admin-username = "admin" the user who runs the mysql-proxy process management;

--admin-password = "admin" password

--admin-lua-script = "/ usr / local / mysql-proxy / share / doc / mysql-proxy / admin.lua" the configuration file path used by the plugin;

# killall mysql-proxy
# mysql-proxy --daemon --log-level = debug --log-file = / var / log / mysql-proxy.log --plugins = "proxy" --proxy-backend-addresses = "172.16.10.72: 3306 "--proxy-read-only-backend-addresses =" 172.16.10.61:3306 "--proxy-lua-script =" / usr / local / mysql-proxy / share / doc / mysql-proxy / rw-splitting .lua "--plugins = admin --admin-username =" admin "--admin-password =" admin "--admin-lua-script =" / usr / local / mysql-proxy / share / doc / mysql- proxy / admin.lua "
Check if mysql-proxy is enabled

# netstat -antpl | grep mysql
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 9511 / mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 9511 / mysql-proxy # 4041 is the newly generated management port
2.13 Test on the slave server (172.16.10.61), log in using the management port 4041, and the password is admin. (Test success!)

Bye
[[email protected] _slave ~] # mysql -uadmin -p -h172.16.10.60 -P 4041
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2014, 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>
2.14 Test whether it can be connected

[email protected] _slave ~] # mysql -uadmin -p -h172.16.10.60 -P 4041
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2014, 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> select * from backends;
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
| backend_ndx | address | state | type | uuid | connected_clients |
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
| 1 | 172.16.10.72:3306 | unknown | rw | NULL | 0 |
| 2 | 172.16.10.61:3306 | unknown | ro | NULL | 0 |
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
2 rows in set (0.00 sec)

mysql>
Since no operation is performed at this time, the master-slave state is unknown.

2.15 Configure the startup script and repair the connection port (When connecting to mysql-proxy, the original port is 4040, which is modified to 3306 below)

# vi / etc / sysconfig / mysql-proxy
# Options for mysql-proxy
ADMIN_USER = "admin"
ADMIN_PASSWORD = "admin"
ADMIN_ADDRESS = ""
ADMIN_LUA_SCRIPT = "/ usr / local / mysql-proxy / share / doc / mysql-proxy / admin.lua"
PROXY_ADDRESS = "0.0.0.0:3306"
PROXY_USER = "mysql-proxy"
PROXY_OPTIONS = "-daemon --log-level = info --log-file =" / var / log / mysql-proxy.log "--plugins = proxy --plugins = admin --proxy-backend-addresses = 172.16 .10.72: 3306 --proxy-read-only-backend-addresses = 172.16.10.61: 3306 --proxy-lua-script = / usr / local / mysql-proxy / share / doc / mysql-proxy / rw-splitting. lua "
Add sysV script

# vi /etc/init.d/mysql-proxy
#! / bin / bash
##
# mysql-proxy This script starts and stops the mysql-proxy daemon
##
# chkconfig:-78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog = "/ usr / local / mysql-proxy / bin / mysql-proxy"
# Source networking configuration.
if [-f / etc / sysconfig / network]; then
    . / etc / sysconfig / network
fi
# Check that networking is 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.pid
PROXY_USER = "mysql-proxy"
# Source mysql-proxy configuration.
if [-f / etc / sysconfig / mysql-proxy]; then
    . / etc / sysconfig / mysql-proxy
fi
RETVAL = 0
start () {
    echo -n $ "Starting $ prog:"
    daemon $ prog $ PROXY_OPTIONS --pid-file = $ PROXY_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 "$ 1" in
    start)
        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: $ 0 {start | stop | restart | reload | status | condrestart | try-restart}"
        RETVAL = 1
        ;;
esac
exit $ RETVAL
# chmod + x /etc/rc.d/init.d/mysql-proxy
# chkconfig --add mysql-proxy
# chkconfig mysql-proxy on
2.16 Restart mysql-proxy and check the port (port 4040 has disappeared)

# service mysql-proxy restart
# netstat -antpl | grep mysql
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 12427 / mysql-proxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 12427 / mysql-proxy
2.17 Test read-write separation

Perform read and write operations

[[email protected] _slave ~] # mysql -uproxy -p -h172.16.10.60 -e "show databases;"
Enter password:
+ -------------------- +
| Database |
+ -------------------- +
| information_schema |
| eivll0m |
| mysql |
| performance_schema |
| test |
+ -------------------- +
[[email protected] _slave ~] # mysql -uproxy -p -h172.16.10.60 -e "create database mydb1;"
Enter password:
mysql> select * from backends;
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
| backend_ndx | address | state | type | uuid | connected_clients |
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
| 1 | 172.16.10.72:3306 | up | rw | NULL | 0 |
| 2 | 172.16.10.61:3306 | unknown | ro | NULL | 0 |
+ ------------- + ------------------- + --------- + ----- -+ ------ + ------------------- +
2 rows in set (0.00 sec)
Since the master supports reading and writing, it is only normal to find that the master state has changed to up. To view the slave state, you can execute multiple queries.

# mysql -uproxy -p -h172.16.10.60 -e "select user, host from mysql.user"
Enter password:
+ ------- + -------------- +
| user | host |
+ ------- + -------------- +
| root | 127.0.0.1 |
| proxy | 172.16.10.% |
| root | :: 1 |
| | localhost |
| root | localhost |
| | mysql \ _slave |
| root | mysql \ _slave |
+ ------- + -------------- +

mysql> select * from backends;
+ ------------- + ------------------- + ------- + ------ + ------ + ------------------- +
| backend_ndx | address | state | type | uuid | connected_clients |
+ ------------- + ------------------- + ------- + ------ + ------ + ------------------- +
| 1 | 172.16.10.72:3306 | up | rw | NULL | 0 |
| 2 | 172.16.10.61:3306 | up | ro | NULL | 0 |
+ ------------- + ------------------- + ------- + ------ + ------ + ------------------- +
2 rows in set (0.00 sec)
Now, the master-slave state has all changed to the up state.

 
Mysql read-write separation (mysql-proxy)