MySQL Router implements MySQL read-write separation

1. Introduction

MySQL Router is a lightweight MySQL middleware officially provided by MySQL to replace the previous old version of SQL proxy.

Since MySQL Router is a database middleware, it must be able to analyze whether the SQL request from the previous client is a write request or a read request, so as to decide whether the SQL request is sent to the master or slave, and to which master or slave. In this way, MySQL Router realizes the read-write separation of MySQL and load balances MySQL requests.

Therefore, the premise of MySQL Router is that the backend implements MySQL master-slave replication.

MySQL Router is very lightweight and can only implement simple read/write separation through different ports. The scheduling algorithm for read requests can only use the default rr (round-robin). It does not have more advanced or complex capabilities. Therefore, when implementing MySQL Router, you need to configure the high availability of the backend MySQL yourself. It is recommended to implement high availability through Percona XtraDB Cluster, MariaDB Galera, or MySQL's official group replication. If there is no other choice, it can also be achieved through MHA.

So, a simple MySQL Router deployment diagram is as follows.

This article will use MySQL Router to implement read-write separation in the case where there is no MySQL master-slave high availability on the backend, as for why the read-write separation is not implemented with MySQL high availability on the backend. In my opinion, MySQL Router is just a toy. It not only has few functions, but also requires specifying different ports for reading/writing in the application code (see the explanation of the configuration file later). No one will use it in a real environment.

2. Configure MySQL Router

The following is the experimental environment.

Because the backend MySQL master-slave replication does not achieve high availability, there is only one master node responsible for write operations.

All backend MySQL nodes are newly installed MySQL instances, so you can directly enable master-slave replication. If it is a master-slave replication of existing data, you need to ensure that they are synchronized first. The method is as follows: Restore the slave to the coordinates specified by the master.

2.1 Install MySQL Router

Download the binary version of MySQL Router at: https://dev.mysql.com/downloads/router/
rpm repository: http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

The binary version of MySQL Router 2.1.6 is used here.

tar xf mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit.tar.gz
mv mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit /usr/local/mysqlrouter

That's it, it's that simple.

After decompressing the binary package, there are the following files in the decompressed directory.

[root@s1 mr]# ls
bin data include lib run share

There is only one binary program mysqlrouter in the bin directory, which is also the main program of MySQL Router.

There are sample configuration files and sample SysV-style startup scripts in the share directory, but unfortunately the script is based on the Debian platform and needs to be modified and installed before it can be used on the Red Hat series. So later I wrote a SysV script under centos.

[root@s1 mr]# ls share/doc/mysqlrouter/
License.txt README.txt sample_mysqlrouter.conf sample_mysqlrouter.init

Finally, add the main program to the PATH environment variable.

echo "PATH=$PATH:/usr/local/mysqlrouter/bin" >/etc/profile.d/mysqlrouter.sh
chmod +x /etc/profile.d/mysqlrouter.sh
source /etc/profile.d/mysqlrouter.sh

2.2 Start and test MySQL Router

The following is the configuration file for the above experimental environment. There is only one master node 192.168.100.22:3306 . If there are multiple write nodes (master), use commas to separate the nodes. The configuration file will be explained later.

[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

Then create the directories used above on the machine where MySQL Router is located.

shell> mkdir /etc/mysqlrouter /usr/local/mysqlrouter/log /var/run/mysqlrouter

This will start MySQL Router to provide services (before starting, make sure that the backend MySQL has been configured with master-slave replication).

[root@s1 mr]# mysqlrouter &
[1] 16122

Check the monitoring status. The two ports 7001 and 7002 monitored here are used by the front-end to connect to MySQL Router. They are used to receive SQL requests sent by the front-end and route SQL requests to the back-end MySQL master and slave nodes according to the read and write rules.

[root@s1 mr]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1231/proxysql    
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1231/proxysql    
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1152/sshd        
tcp 0 0 192.168.100.21:7001 0.0.0.0:* LISTEN 16122/mysqlrouter
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2151/master      
tcp 0 0 192.168.100.21:7002 0.0.0.0:* LISTEN 16122/mysqlrouter
tcp6 0 0 :::22 :::* LISTEN 1152/sshd        
tcp6 0 0 ::1:25 :::* LISTEN 2151/master

View the logs:

[root@s1 mr]# cat /usr/local/mysqlrouter/log/mysqlrouter.log 
2018-07-07 10:14:29 INFO [7f8a8e253700] [routing:slaves] started: listening on 192.168.100.21:7001; read-only

2018-07-07 10:14:29 INFO [7f8a8ea54700] [routing:masters] started: listening on 192.168.100.21:7002; read-write

Finally, test it. Before testing, authorize the MySQL Router node on the backend Master to allow the connection, which will be replicated to the two slave nodes.

mysql> grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';

Connect to port 7002 of MySQL Router, which is the writing port. Since the master-slave high availability is not configured, a simple test to see if it can be written is sufficient.

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 110 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'create database mytest;'
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| mytest |
| performance_schema |
|sys|
+--------------------+

Then test each slave node to see if it can achieve load balancing of read requests of the rr scheduling algorithm.

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e 'select @@server_id;' 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 120 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 130 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| mytest |
| performance_schema |
|sys|
+--------------------+

Obviously, the test results are normal.

It seems that MySQL Router is very simple. It is indeed very simple. Just provide a reasonable configuration file and you are all set. So, let’s explain the configuration file of MySQL Router.

3. MySQL Router configuration file explanation

The configuration file of MySQL Router is also very simple, and there are not many items that need to be configured.

By default, mysql router will look for "mysqlrouter.conf" in the installation directory and ".mysqlrouter.conf" in the home directory. You can also use "-c" or "--config" in the binary program mysqlrouter command to manually specify the configuration file.

The configuration file of MySQL router is in fragment format, and there are three commonly used fragments: [DEFAULT], [logger], and [routing:NAME]. The fragment name is case-sensitive and only supports single-line "#" or ";" comments. It does not support mid-line or end-of-line comments.

Take the configuration file in the above example.

[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

1.Configuration of the DEFAULT fragment.

The [DEFAULT] section usually configures the directory for configuration files, the directory for logs, and the directory where the MySQL router is running (such as the pid file).

For example:

[DEFAULT]
config_folder=/etc/mysqlrouter #Specify an additional configuration file directory. All conf files in this directory will be loaded. logging_folder=/usr/local/mysqlrouter/log #Specify the log directory. The log file name is mysqlrouter.log
runtime_folder=/var/run/mysqlrouter #Specify the runtime directory, the default is /run/mysqlrouter

2.Configuration of logger fragment.

The [logger] section has only one option, which is to set the logging level.

[logger]
level=debug # debug, info (default), warning, error, fatal, case insensitive

3.Configuration of routing fragment.

[routing:NAME] is the main part of MySQL router, which sets different routing instances. NAME can be named arbitrarily. Such as [routing:slaves] , [routing:masters] .

In the routing configuration section, the options that can be set include:

(1). bind_address and bind_port

bind_address and bind_port are the address and port where mysql router listens for front-end SQL requests. The port is mandatory for MySQL Router, but it does not need to be bound with bind_port because it can be specified in IP:PORT format using bind_address.
Only one address monitoring instruction can be set in a routing rule, but "0.0.0.0" can be used to monitor all addresses on the host. If no listen address is provided, the default listen address is 127.0.0.1.

Additionally, the listening address cannot appear in the list specified by the destinations directive.

Here is an example:

[routing:slaves]
bind_port = 7001
[routing:slaves]
bind_address = 192.168.100.21
bind_port = 7001
[routing:slaves]
bind_address = 192.168.100.21:7001

Generally speaking, it is not a good idea to implement read/write separation through different ports. The biggest reason is that these connection ports need to be specified in the application code. However, MySQL Router can only achieve read-write separation in this way, so MySQL Router should only be used as a toy.

(2). destinations

Define the forwarding target of the routing rule in the format of HOST:PORT . HOST can be either an IP or a host name. Multiple forwarding targets are separated by commas. If the target list is defined as multiple slaves.

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306,192.168.100.100:3306

(3). mode

MySQL router provides two modes: read-only and read-write. These two methods will produce different forwarding scheduling methods.

Set to read-write, which is often used to set destinations to master to achieve high availability of the master.

• Scheduling method: When MySQL Router receives a client request for the first time, it forwards the request to the first destination in the destinations list. When it receives a client request for the second time, it still forwards the request to the first destination. The second destination will only be contacted if the first destination cannot be contacted (such as when the MySQL service is shut down or down). If all destinations cannot be contacted, MySQL Router will be interrupted. This scheduling method is called "first-available".
• When a target is contacted, MySQL Router will cache it and forward it to the target the next time it receives a request. Since it is a cache target, it means that it will become invalid after MySQL Router is restarted.
• Therefore, when implementing read-write separation through MySQL Router, you can set up multiple masters, put the master with good performance at the first position in the destinations list, and put other masters at the back as backup masters.

Set to read-only, often used to set destinations to slave to achieve MySQL read request load balancing.

• Scheduling method: When MySQL route receives a client request, it will poll backward (round-robin) starting from the first destination in the destinations list. The first request is forwarded to the first destination, the second request is forwarded to the second destination, and the next request after forwarding to the last destination is forwarded to the first destination. If the first target is unavailable, the router will check the next target in turn until the target is available. If all targets are unavailable, MySQL Router will be interrupted.
• Those unavailable targets will be temporarily isolated, and MySQL Router will constantly check their status and re-add them to the target list when they are available again.

(4). connect_timeout

• The timeout for MySQL Router to contact destinations. The default value is 1 second. The value range is 1-65536. You should try to set the value as small as possible to avoid waiting too long.
• For read-write mode, you can set the timeout period to be slightly longer to prevent the backup master from being contacted because the primary master is believed to be unavailable.
• For read-only mode, the timeout period can be set a little shorter, because in this mode the destinations list is polled, and even if a misjudgment occurs, the impact will not be too great.

(5) Other options

You can also set some other instructions, such as the protocol used, the maximum number of requests, etc., but you don't need to set the default values. They are all options that MySQL Router has optimized in combination with MySQL, and they are already perfect in themselves.

The configuration file contains roughly these contents. After configuration, remember to create the directories involved in the default segment first. After that, you can start mysql router to provide read/write separation services.

4. Provide SysV scripts for MySQL Router

MySQL Router only provides a main program (mysqlrouter in the bin directory), and the program can only be started without a stop option, so you can only use the kill command to kill the process.

MySQL Router also provides a sample startup script, which is located at $basedir/share/doc/mysqlrouter/sample_mysqlrouter.init . However, this script is based on the Debian platform and requires some settings and installation on CentOS, so do not use it and just write a rough script yourself.

shell> vim /etc/init.d/mysqlrouter
#!/bin/bash

# chkconfig: -78 30
# Description: Start / Stop MySQL Router

DAEMON=/usr/local/mysqlrouter
proc=$DAEMON/bin/mysqlrouter
DAEMON_OPTIONS="-c ${DAEMON}/mysqlrouter.conf"

. /etc/init.d/functions

start() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        action "MySQL Router is working" /bin/false
    else
        $proc $DAEMON_OPTIONS & &>/dev/null
        retval=$?
        echo
    if [ $retval -eq 0 ]; then
             touch /var/lock/subsys/mysqlrouter
        action "Starting MySQL Router" /bin/true
        else
        echo "Starting MySQL Router Failure"
        fi
    fi
}
    
stop() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        killall $proc
        retval=$?
        echo
        if [ $retval -eq 0 ]; then
            rm -f /var/lock/subsys/mysqlrouter
            action "Stoping MySQL Router" /bin/true
        fi
    else
        action "MySQL Router is not working" /bin/false
    fi
}

status() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        echo "MySQL Router is running"
    else
        echo "MySQL Router is not running"
    fi
}

case "$1" in
    start)
        start
        sleep 1
        ;;
     stop)
        stop
        sleep 1
        ;;
    restart)
        stop
        start
        sleep 1
        ;;
    status)
        status
        ;;
    *)
        echo "Usage: $0 {start|stop|status|restart}"
        retval=1
        ;;
esac

exit $retval

Then grant execute permissions.

shell> chmod +x /etc/init.d/mysqlrouter

This is the end of this article about how to implement MySQL read-write separation with MySQL Router. For more information about MySQL Router read-write separation, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!