MySQL router implementation of MySQL read/write separation

1. Introduction

MySQL router is a lightweight MySQL middleware that is officially provided by MySQL to replace the previous version of SQL Proxy.

Since MySQL router is a database middleware, MySQL router must be able to analyze whether the SQL request from the previous client is a write request or a read request in order to determine whether the SQL request is sent to master or slave, and to which master, which slave. In this way, MySQLrouter realizes the read and write separation of MySQL and load balance MySQL requests.

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

The MySQL router is lightweight and can only be used for simple read/write separation via different ports, and the read request scheduling algorithm can only use the default RR (Round-robin), which is not available for a bit more and more complex capabilities. Therefore, when implementing MySQL router, it is necessary to configure the high availability of the backend MySQL itself. The highly available recommendations are implemented through Percona XtraDB cluster or mariadb Galera or the official MySQL group replication, and if there is no choice, it can be implemented by MHA.

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

In this paper, we will use MySQL router to separate the read/write separation of the back end without MySQL master and slave, as to why there is no MySQL high-availability read/write separation for the backend. In my opinion, MySQL router is just a toy, not only has less function, but also need to specify the different ports of read/write in the application code (see the explanation of the configuration file later), in the actual environment should no one use this.

2. Configure MySQL Router

The following is an experimental environment.

Role Name	Host IP	MySQL version	Data Status
MySQL Router	192.168.100.21	MySQL 5.7.22	No
Master	192.168.100.22	MySQL 5.7.22	New instances
Slave1	192.168.100.23	MySQL 5.7.22	New instances
Slave2	192.168.100.24	MySQL 5.7.22	New instances

Because the backend MySQL master-slave replication is not highly available, only one master node is responsible for the write operation.

All back-end MySQL nodes are new MySQL instances that have just been installed, so turn on master-slave replication directly. If it is a master-slave copy of the existing data, you need to ensure that they are synchronized, see: Restore slave to the coordinates specified by master.

2.1 Installing MySQL Router

Binary plate MySQL router:https://dev.mysql.com/downloads/router/
RPM Warehouse: http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

The MySQL Router 2.1.6 is used here in the binary plate.

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

That's it, it's so simple.

After unpacking the binary package, the following files are available in the Unzip directory.

[[email protected] mr]# lsbin  data  include  lib  run  share

The Bin directory has only one binary program Mysqlrouter, which is also the main program of MySQL router.

The Share directory has sample configuration files and example SYSV-style startup scripts, but unfortunately the script is based on the Debian platform and needs to be modified and installed on the Redhat series to be used. So later I wrote a SYSV script under CentOS.

[[email protected] 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.shchmod +x /etc/profile.d/mysqlrouter.shsource /etc/profile.d/mysqlrouter.sh

2.2 Start and test MySQL Router

Here is the configuration file for the lab environment described above, where there is only one master node 192.168.100.22:3306 , and if there are multiple write nodes (master), separate the nodes with commas. The configuration file is explained later in this article.

[DEFAULT]config_folder = /etc/mysqlrouterlogging_folder = /usr/local/mysqlrouter/logruntime_folder = /var/run/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 192.168.100.21:7001destinations = 192.168.100.23:3306,192.168.100.24:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 192.168.100.21:7002destinations = 192.168.100.22:3306mode = read-writeconnect_timeout = 2

Then create the directory used on the machine where MySQL router resides.

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

This allows you to start MySQL router to provide services (before booting, make sure that the backend MySQL is configured for master-slave replication).

[[email protected] mr]# mysqlrouter &[1] 16122

View the listening status. The two ports 7001 and 7002 monitored here are front-end connections to MySQL router, which are used to receive the SQL requests sent by the front end and to route the SQL requests to the backend MySQL master node according to the read and write rules.

[r[email protected] mr]# netstat -tnlpActive 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/mysqlroutertcp        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/mysqlroutertcp6       0      0 :::22                :::*             LISTEN  1152/sshd        tcp6       0      0 ::1:25               :::*             LISTEN  2151/master      

To view logs:

[[email protected] 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-only2018-07-07 10:14:29 INFO  [7f8a8ea54700] [routing:masters] started: listening on 192.168.100.21:7002; read-write

The final test can be done. Before testing, authorize the MySQL router node on the back-end master to allow the connection, and it will replicate to two slave nodes.

mysql> grant all on *.* to [email protected]'192.168.100.%' identified by '[email protected]!';

Connect the 7002 port on MySQL router, this port is responsible for writing the port. Since the master-slave is not configured, it is possible to write under a simple test.

[[email protected] mr]# mysql -uroot [email protected]! -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 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7002 -e 'create database mytest;'mysql: [Warning] Using a password on the command line interface can be insecure.[[email protected] mr]# mysql -uroot [email protected]! -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                |+--------------------+

After testing each slave node, can we realize the load balance of the read request of RR scheduling algorithm.

[[email protected] mr]# mysql -uroot [email protected]! -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 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -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 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -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 results of the test are all normal.

So it seems that MySQL router is very simple, it is really easy. Just provide a reasonable configuration file and everything is done. So, the following explains the MySQL router configuration file.

Configuration file interpretation for 3.MySQL router

The MySQL router configuration file is also very simple and requires few items to configure.

MySQL router by default looks for ". mysqlrouter.conf" under the "mysqlrouter.conf" and home directory under the installation directory. You can also specify the configuration file manually under the binary mysqlrouter command using "-C" or "--config".

MySQL router configuration files are fragment-like, commonly used on 3 fragments: [DEFAULT], [logger], [routing:name]. Fragment names are case-sensitive and support only one line of "#" or ";" Note, lines, end of line comments are not supported.

Take the configuration file for example above.

[DEFAULT]config_folder = /etc/mysqlrouterlogging_folder = /usr/local/mysqlrouter/logruntime_folder = /var/run/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 192.168.100.21:7001destinations = 192.168.100.23:3306,192.168.100.24:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 192.168.100.21:7002destinations = 192.168.100.22:3306mode = read-writeconnect_timeout = 2

Configuration of the 1.DEFAULT fragment.

[DEFAULT]A fragment typically configures the directory of the profile, the directory of logs, and the directory of the MySQL router runtime (such as a PID file).

For example:

[DEFAULT]config_folder=/etc/mysqlrouter   # 指定额外的配置文件目录，该目录下的conf文件都会被加载logging_folder=/usr/local/mysqlrouter/log  # 指定日志目录，日志文件名为mysqlrouter.logruntime_folder=/var/run/mysqlrouter        # 指定运行时目录，默认为/run/mysqlrouter

Configuration of the 2.logger fragment.

[logger]The fragment has only one option, setting the logging level for the log.

[logger]level=debug   # 有debug、info(默认)、warning、error、fatal，不区分大小写

Configuration of the 3.routing fragment.

[routing:NAME]Is the main part of MySQL router, set up different routing instances, where name can be arbitrarily named. such as [routing:slaves] , [routing:masters] .

In the routing configuration fragment, the options you can set include:

• (1). bind_address Andbind_port
  Bind_address and Bind_port are the addresses and ports of the MySQL router listener front-end SQL requests. Where the port is required to be mandatory for MySQL router , it is possible to bind without bind_port because it can be specified through the bind_address IP:PORT format.
  A routing rule can only set an address listening instruction, but "0.0.0.0" to listen to all the addresses on the host. If no listening address is provided, the default listener is 127.0.0.1.
  Additionally, the listener address cannot appear in the list specified by the destinations directive.
  Examples are as follows:
  

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

In general, it is not a good method to implement read/write separation over different ports, the biggest reason being that these connection ports need to be specified in the application code. However, MySQL router can only be used in this way to achieve read and write separation, so MySQL router to play as a toy is good .

• (2).destinations
  Defines the forwarding target for the routing rule, in the form HOST:PORT that the host can be either IP or host name, and multiple forwarding targets are separated by commas. The target list as defined is multiple slave.

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

• (3). Mode The
  MySQL Router provides two mode: read-only and Read-write . Both of these methods produce different forwarding schedules.
  • is set to Read-write, which is commonly used when setting destinations to master, to enable high availability of master.
    • scheduling: When MySQL router first receives a client request, it forwards the request to the first target in the destinations list, receives the client request for the second time, or forwards the first target only if the first target is not contacted ( If you close the MySQL service, downtime, etc.) will contact the second target, if all the targets are not connected, MySQL router will be interrupted. This scheduling method is called " first-available ".
      
    • When a target is contacted, MySQL router caches it, and the next time the request is received, it will continue to be forwarded to that target. Since it is the target of the cache, it means that it will expire after the MySQL router restarts.
      
    • So, with MySQL router for read-write separation, you can set up multiple master to put a good master in the first position of the destinations list, The other master is placed in the back position as the standby master.
      
  • is set to Read-only, and is commonly used when setting destinations to slave for MySQL read request load balancing.
    • Scheduling: When the MySQL route receives a client request, it polls backward from the first target in the destinations list (Round-robin), the first request is forwarded to the first target, and the second request is forwarded to the second target, The next request that is forwarded to the last target is then transmitted to the first target. If the first target is not available, it is checked backwards until the target is available and if all targets are unavailable, MySQL router is interrupted.
    • Those targets that are not available are temporarily quarantined, and MySQL router will constantly check their condition and rejoin the target list when re-usable.
      
• (4).connect_timeout
  MySQL Router Contact Destinations time-out, default is 1 seconds, and the value range is 1-65536. You should try to set a smaller value so that you don't wait too long.
  For the Read-write mode, you can set the time-out to a little longer, preventing the master master from being mistaken for a primary.
  For read-only mode, you can set the time-out to a little bit shorter, because this mode is destinations list polling, even if the impact is not too large.
  
• (5). Other options
  can also set some other instructions, such as the use of the Protocol, the maximum number of requests, but can not be set to use the default values, they are MySQL router combined with MySQL optimized some of the options, itself is more perfect.

Configuration file about this, after configuration, remember to first create the default fragment involved in the directory. You can then start MySQL router to provide a read/write separation service.

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, there is no stop option, so you can only use the KILL command to kill the process.

MySQL Router also provides a sample startup script that is in the location $basedir/share/doc/mysqlrouter/sample_mysqlrouter.init , but the script is based on the Debian platform and needs to be set up and installed on CentOS, so don't use it to write a rough point script.

Shell> vim/etc/init.d/mysqlrouter#!/bin/bash# chkconfig:-30# description:start/stop MySQL RouterDAEMON=/usr/lo cal/mysqlrouterproc= $DAEMON/bin/mysqlrouterdaemon_options= "-C ${daemon}/mysqlrouter.conf". /etc/init.d/functionsstart () {if [-e/var/lock/subsys/mysqlrouter]; then action ' MySQL Router is working '/bi        N/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 Ech O "Starting MySQL Router Failure" fi Fi} Stop () {if [-e/var/lock/subsys/mysqlrouter]; then kill        All $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 was running" else echo "MySQL Router is not running" fi}case "$" in start)     Start sleep 1;;    Stop) stop sleep 1;;    Restart) stop start sleep 1;;    status) status; *) echo "Usage: $ Start|stop|status|restart}" retval=1;;    Esacexit $retval

The Execute permission is then given.

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

MySQL router implementation of MySQL read/write separation