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.

(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 service can run normally. Automatic logoff by MySQL Router is not available. The program configuration does not need any modification.

(3) if the master database fails, MySQL Router will decide to switch the master and slave automatically, 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 results returned from 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:

Install and deploy mysql route

(1) download address: provide different platforms, binary packages and rpm packages, and select the appropriate installation method. I use binary packages here to install.

(2) decompression and installation

[root@c7-node1 ~]# tar xf mysql-router-2.0.3-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
[root@c7-node1 ~]# cd /usr/local/
[root@c7-node1 local]# ln -s mysql-router-2.0.3-linux-glibc2.12-x86-64bit/ mysql-router
[root@c7-node1 local]# cd mysql-router
[root@c7-node1 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

[root@c7-node1 mysql-router]# mkdir /etc/mysql-route/
[root@c7-node1 mysql-router]# cp share/doc/mysqlrouter/sample_mysqlrouter.ini /etc/mysql-route/mysqlrouter.conf

2. Configuration file setting and log directory need to be created manually

[root@node1 ~]# 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 connection
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

[root@c7-node1 ~]# mkdir /data/log/mysql-route/
[root@c7-node1 ~]# chown mysql:mysql /data/log/mysql-route/

(4) startup: the official mysql route does not provide a startup script, which needs to be written by itself.

[root@c7-node1 ~]# 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

[root@c7-node1 ~]# systemctl enable mysqlrouter
[root@c7-node1 ~]# systemctl start mysqlrouter

Read write separation test

1. Create test account and 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 the read load balance: from the result, each read accesses a different host.

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

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.

Primary primary failover test

Verification: normally, data is written to the main database.

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.

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.

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

follow The road of technology for migrant workers Reply key words of wechat public account dialog box: 1024 you can get the latest technical dry goods: including system operation and maintenance, database, redis, MogoDB, e-book, java basic course, Java practical project, architect comprehensive course, architect practical project, big data, Docker container, ELK Stack, machine learning, BAT interview intensive video, etc.