Shulou(Shulou.com)06/01 Report--

Separation of read and write of MySQL by MySQL Router

Https://www.cnblogs.com/f-ck-need-u/p/9276639.html

Https://www.cnblogs.com/f-ck-need-u/p/9276639.html

1. Brief introduction

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

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

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

MySQL Router is very lightweight and can only achieve simple read / write separation through different ports, and the scheduling algorithm for read requests can only use the default rr (round-robin), and there are no more or more complex capabilities. Therefore, when implementing MySQL Router, you need to configure the high availability of the back-end MySQL. High availability advice is implemented through Percona XtraDB Cluster or MariaDB Galera or MySQL's official group replication, or through MHA if there is really no choice.

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

This article will use MySQL Router to achieve the read-write separation of backend master-slave high availability cases without MySQL, respectively, as to why the backend read-write separation case with high availability of MySQL is not implemented. In my opinion, MySQL Router is just a toy with few functions and needs to specify different ports to read / write in the application code (see the later explanation of the configuration file), which should not be used in the real world.

two。 Configure MySQL Router

The following is the experimental environment.

Role name Host IPMySQL version data status MySQL Router192.168.100.21MySQL 5.7.22 No master192.168.100.22MySQL 5.7.22 New instance slave1192.168.100.23MySQL 5.7.22 New instance slave2192.168.100.24MySQL 5.7.22 New instance

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

All backend MySQL nodes are new MySQL instances that have just been installed, so you can directly enable master-slave replication. If it is a master-slave copy of existing data, you need to make sure that they have been synchronized. For more information, please see: restore slave to the coordinates specified by master.

2.1 install MySQL Router

Download binary MySQL Router address: https://dev.mysql.com/downloads/router/

Rpm warehouse: http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

The binary version of MySQL Router 2.1.6 is used here.

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

It's over. It's as simple as that.

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

12 [root@s1 mr] # lsbin 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 this script is based on the debian platform and needs to be modified and installed on the redhat series to use. So I wrote a SysV script under centos later.

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

123echo "PATH=$PATH:/usr/local/mysqlrouter/bin" > / etc/profile.d/mysqlrouter.shchmod + x / etc/profile.d/mysqlrouter.shsource / etc/profile.d/mysqlrouter.sh2.2 launch and test MySQL Router

The following is the configuration file for the above lab environment, where there is only one master node 192.168.100.22 master 3306, and if there are multiple write nodes (master), separate the nodes with commas. The configuration file will be explained later.

19 [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:3306192.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 above on the machine where MySQL Router is located.

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

This allows you to start MySQL Router to provide services (before starting, make sure that the back-end MySQL is configured for master-slave replication).

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

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

11 [root@s1 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 only servers 6032 0.0.0.0 LISTEN 1231/proxysql tcp 0 0 0.0.0.015 6033 0.0.0.015 * LISTEN 1231/proxysql tcp 0 0 0.0.0.0 LISTEN 1152/sshd tcp 22 0.0.0.0 LISTEN 2151/master tcp 00 192.168.100.21 LISTEN 2151/master tcp 7001 0.0.0.0 LISTEN 2151/master tcp 00 127.0.0.1 168.100.21 LISTEN 1152/sshd tcp6 7002 0.0.0.0 LISTEN 16122/mysqlroutertcp6 00: 22:: * LISTEN 1152/sshd tcp6 00:: 1:25:: * LISTEN 2151/master

View the log:

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

Finally, the test can be carried out. Before testing, authorize the MySQL Router node on the back-end Master to allow the connection, which will be copied to the two slave nodes.

Mysql > grant all on *. * to root@'192.168.100.%' identified by 'Prissword1words'

Connect to port 7002 of MySQL Router, which is the port responsible for writing. Since there is no configuration of master-slave high availability, simply test whether it can be written.

22 [root@s1 mr] # mysql-uroot-pP@ssword1!-h292.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!-h292.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!-h292.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 whether each slave node can achieve load balancing of read requests of rr scheduling algorithm.

27 [root@s1 mr] # mysql-uroot-pP@ssword1!-h292.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!-h292.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!-h292.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 all right.

From this point of view, MySQL Router is very simple, indeed very simple. Just provide a reasonable configuration file and everything is done. So, let's explain the configuration file for MySQL Router.

Configuration file interpretation for 3.MySQL Router

The configuration file for MySQL Router is also simple, and there are not many items to configure.

By default, mysqlrouter looks for "mysqlrouter.conf" in the installation directory and ".mysqlrouter.conf" in the home directory. You can also specify the configuration file manually using "- c" or "--config" under the binary program mysqlrouter command.

The configuration file of MySQL router is fragmented, and there are only three commonly used fragments: [DEFAULT], [logger], and [routing:NAME]. Fragment names are case-sensitive and only support single-line "#" or ";" comments, not mid-line and end-of-line comments.

Take the configuration file in the example above as an example.

19 [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:3306192.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 1.DEFAULT fragments.

[DEFAULT] fragments typically configure the directory of the configuration file, the directory of the log, and the directory of the MySQL router runtime (such as the pid file).

For example:

1234 [DEFAULT] config_folder=/etc/mysqlrouter # specifies an additional configuration file directory, where all conf files are loaded into logging_folder=/usr/local/mysqlrouter/log # specified log directory, and the log file name is mysqlrouter.logruntime_folder=/var/run/mysqlrouter # specified runtime directory, default is / run/mysqlrouter

Configuration of 2.logger fragments.

The [logger] section has only one option to set the logging level of the log.

12 [logger] level=debug # has debug, info (default), warning, error, fatal, and is not case sensitive

Configuration of 3.routing fragments.

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

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

(1)。 Bind_address and bind_port

Bind_address and bind_port are the addresses and ports that mysql router listens for front-end SQL requests. Where the port is mandatory for MySQL Router requirements, but bind_port binding can be avoided because it can be specified through the IP:PORT format of bind_address.

Only one address can be set in a routing rule to listen for instructions, but you can use "0.0.0.0" to listen for all addresses on the host. If no listening address is provided, the default listening is 127.0.0.1.

In addition, the listening address cannot appear in the list specified by the destinations instruction.

Examples are as follows:

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

Generally speaking, it is not a good way to achieve 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 can just play with it as a toy.

(2)。 Destinations

Define the forwarding destination of the routing rule in the format of HOST:PORT,HOST or IP or hostname, and multiple forwarding destinations are separated by commas. For example, the target list defined is multiple slave. 123456 [routing:slaves] bind_address = 192.168.100.21:7001destinations = 192.168.100.23 192.168.100.21:7001destinations 3306192.168.100.24: 3306 [routing: masters] bind_address = 192.168.100.21:7002destinations = 192.168.100.22 192.168.100.21:7001destinations 3306 (3). Mode

MySQL router provides two kinds of mode: read-only and read-write. These two methods will result in different forwarding and scheduling methods. Set to read-write, which is often used to achieve high availability of master when setting destinations to master. Scheduling method: when MySQL router receives a client request for the first time, it will forward the request to the first destination in the destinations list, and the second time it will be forwarded to the first destination. Only when the first target cannot be contacted (such as MySQL service disabled, downtime, etc.) will the second target be contacted. If all the targets cannot be reached, the MySQL Router will be interrupted. This scheduling method is called "first-available".

When a target is contacted, MySQL Router caches it and continues to forward it to that target the next time a request is received. Since it is the target of caching, it means that it will expire after the MySQL Router is restarted.

So when you implement read-write separation through MySQL Router, you can set up multiple master so that the master with good performance is placed at the first place in the destinations list, and the other master is placed at the back as a backup master.

Set to read-only, which is often used to achieve load balancing of MySQL read requests when destinations is set to slave. Scheduling method: when MySQL route receives a client request, it will round-robin 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 it is forwarded to the last destination is forwarded to the first destination. If the first target is not available, it is checked backward in turn until the target is available, and if none of the targets are available, the MySQL Router is interrupted. Targets that are not available will be temporarily quarantined, 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 contacting destinations is 1 second by default, and the range of values is 1-65536. You should try to set a small value so as not to wait too long.

For read-write mode, you can set the timeout a little longer to prevent contacting the standby master for the primary master if it is mistakenly thought that the primary master is not available.

For read-only mode, you can set the timeout to be a little shorter, because in this mode, the destinations list is polled, and even if misjudged, the impact will not be too great.

(5)。 Other options

Can also set some other instructions, such as the protocol used, the maximum number of requests, etc., but can not be set to use default values, they are MySQL Router combined with MySQL optimized some of the options, itself has been more perfect.

That's about the content of the configuration file, and after you've configured it, remember to create the directory involved in the default fragment first. After that, you can start mysql router to provide read / write separation services.

4. Provide SysV scripts for MySQL Router

MySQL Router provides only one main program (mysqlrouter in the bin directory), and the program can only be started with no 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, but the script is based on the Debian platform and needs to set up and install something on CentOS, so instead of it, you can write your own rough script.

74shell > vim / ETC _ init. D _ binqlrouterBash chkconfig:-78 3 minutes Description: Start / Stop MySQL RouterDAEMON=/usr/local/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" / 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;; esacexit $retval

And then give the execute permission.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.