Official tool MySQL router high availability principle and Practice

Time:2020-5-2

Before that, I also introduced the articles related to MySQL high availability:

Summary of MySQL synchronous replication and high availability schemes

Introduction to MySQL route

What is MySQL route

MySQL router is a lightweight agent between application client and DBServer. It can detect, analyze and forward queries to back-end database instances, and return the results to the client. It is an alternative to MySQL proxy. The frame composition and functions are as follows.

Official tool MySQL router high availability principle and Practice

(1) The router implements read-write separation. The program is not directly connected to the database IP, but is fixedly connected to MySQL router. MySQL router is transparent to front-end applications. The application program regards MySQL router as a common MySQL instance, sends the query to MySQL router, and MySQL router will return the query results to the front-end application program.

(2) If the slave database server fails, the business can run normally. Automatic logoff by MySQL router is not available. The program configuration does not need any modification.

(3) When the master database fails, the MySQL router determines the master-slave automatic switch, and the service can be accessed normally. The program configuration does not need to be modified.

Principle of separation of reading and writing

After receiving the front-end application request, MySQL router distinguishes read and write according to different ports, sends all queries connected to the read-write port to the main database, and sends select queries connected to the read-only port to multiple slave databases in polling mode, so as to achieve the purpose of separation of read and write. The result of reading and writing will be delivered to MySQL router, which will return to the client’s application.

MySQL router purpose

MySQL router is mainly used for read-write separation, automatic failover, load balancing, connection pool, etc.

The pit for automatic failover of MySQL router

There is no problem with the main and main failover functions of MySQL router after testing, but there is a large pit to pay attention to. The specific pit and solutions are at the end of the article, because if you haven’t contacted MySQL router before and estimated my expression ability, you will be dizzy.

The introduction of MySQL router experiment architecture

The experimental environment architecture is as follows:

Official tool MySQL router high availability principle and Practice

Install and deploy MySQL route

(1) Download address: provide different platforms, binary packages and RPM packages, and choose the appropriate installation method. I use binary packages here to install

(2) Unzip installation

[[email protected]node1 ~]# tar xf mysql-router-2.0.3-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
[[email protected] ~]# cd /usr/local/
[[email protected] local]# ln -s mysql-router-2.0.3-linux-glibc2.12-x86-64bit/ mysql-router
[[email protected] local]# cd mysql-router
[[email protected] mysql-router]# ll
total 0
drwxr-xr-x 2 7161 wheel 24 Feb 23  2016 bin
drwxr-xr-x 4 7161 wheel 36 Feb 23  2016 include
drwxr-xr-x 3 7161 wheel 150 Feb 23  2016 lib
drwxrwxr-x 2 7161 wheel 6 Feb 23  2016 run
drwxr-xr-x 3 7161 wheel 16 Feb 23  2016 share

(3) Configuration
1. Create profile directory and copy template profile

[[email protected] mysql-router]# mkdir /etc/mysql-route/
[[email protected] mysql-router]# cp share/doc/mysqlrouter/sample_mysqlrouter.ini /etc/mysql-route/mysqlrouter.conf

2. Configuration file settings, log directory needs to be created manually

[[email protected] ~]# cat /etc/mysql-route/mysqlrouter.conf
[DEFAULT]
#Log storage directory
logging_folder = /data/log/mysql-route
#Plug in storage directory
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
#Profile storage directory
config_folder = /etc/mysql-route
#Running directory
runtime_folder = /var/run
 
[logger]
#Log run level
level = INFO
 
#Primary node failover configuration
[routing:basic_failover]
#Write node address
bind_address=192.168.100.10
#Write node port
bind_port = 7001
#Patterns, reading and writing
mode = read-write
#Master node address: by default, the first master database is the write master database. When the first master database is down, the second database is promoted to the master database
destinations = 192.168.100.11:3306,192.168.100.12:3306
 
#Load balancing configuration of slave node
[routing:balancing]
#Bound IP address
bind_address=192.168.100.10
#Listening port
bind_port = 7002
#Connection timeout
connect_timeout = 3
#Maximum connections
max_connections = 1024
#Back end server address
destinations = 192.168.100.12:3306,192.168.100.13:3306
#Mode: read or write
mode = read-only
 
[keepalive]
interval = 60

Log directory creation and authorization

[[email protected] ~]# mkdir /data/log/mysql-route/
[[email protected] ~]# chown mysql:mysql /data/log/mysql-route/

(4) Startup: MySQL route official does not provide startup script, which needs to be written by itself

[[email protected] ~]# cat /usr/lib/systemd/system/mysqlrouter.service
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
 
[Service]
Type=simple
User=mysql
Group=mysql
 
ExecStart=/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf
 
PrivateTmp=true
 
[Install]
WantedBy=multi-user.target

5. Start service

[[email protected] ~]# systemctl enable mysqlrouter
[[email protected] ~]# systemctl start mysqlrouter

Read write separation test

1. Create test account, test library

MariaDB [(none)]> create database mysql_route;
MariaDB [(none)]> GRANT all ON mysql_route.* TO 'route'@'%' IDENTIFIED BY 'route';
MariaDB [(none)]> FLUSH PRIVILEGES;

2. Verify read load balancing: see from the result that each read accesses a different host
Official tool MySQL router high availability principle and Practice

3. Verify write: you can see that every write is written to the primary node

Official tool MySQL router high availability principle and Practice

Down machine test from library

The following is a screenshot of the down machine test from the database. It can be seen that when both nodes are normal, the rotation training takes data from two databases. If one of the databases fails, MySQL router will automatically logoff the failed database. If it recovers from the database, MySQL router will automatically online the failed database.
Official tool MySQL router high availability principle and Practice

Primary primary failover test

Verification: normally, data is written to the main database
Official tool MySQL router high availability principle and Practice

We manually stop the MySQL service of the main database, simulate the failure, and write again. We can see that the node that writes data is the backup node of the main database.
Official tool MySQL router high availability principle and Practice

be careful:
1. Read only = on cannot be opened in the backup slave database, otherwise write error will be reported after switching
2. After the first main database is down, how to deal with MySQL router?
By default, the first master database is the write master database. When the first master database is down, the second database is promoted to the master database. Later, if the first master database is repaired, the second master database is still connected to read and write by default, and will not automatically switch back to the first master database.
3. What if you want to switch back to the first master database after the first master database is repaired?
You can restart MySQL router

Pit for primary master failover
If the master master failover of MySQL router is running in the case of one master and one slave, the slave database is used as the backup of the master database. In this case, it is no problem to use the master master failover of MySQL router, but in the case of one master and many slaves, the master failover will be in the following problems.
Official tool MySQL router high availability principle and Practice

In the case of one master and many slaves, if the master database goes down and switches to the backup node, the address of the other slave databases is still the failed master database, which will cause a slave IO thread connection state and a replication delay. My previous primary database address is 192.168.10.11, and the backup primary database address is 192.168.10.12. But after the primary database fails, MySQL route can route the write request to the new primary database, but the primary database that the slave database attempts to connect to is 192.168.10.11. This is normal, because the principle of MySQL route primary primary and primary failover is like this, but the production environment can’t play this way.

terms of settlement:
1. When switching, you need to write your own script to trigger a shell script or event to deal with the problem of reconnection.
2. Do not use the MySQL router primary failover function, but use other methods to ensure the high availability of the MySQL primary database.

Copyright notice: Author: Ximen feibing, a post-90s it man, has been working in Beijing, loves sports, adventure and travel. It is submitted by the author, and the copyright belongs to the author. Unless we can’t confirm, we will mark the author and the source. If there is any infringement, please let us know. We will delete it immediately and apologize. Thank you.

followThe road of technology for migrant workersWeChat official account dialog box answers key words: 1024, you can get a new finishing dry cargo: including system operation and maintenance, database, redis, MogoDB, e-book, Java basic course, Java actual combat project, architect comprehensive course, architect’s actual combat project, big data, Docker container, ELK Stack, machine learning, BAT interview, fine lecture video, etc.