Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the principle of high availability of Router in MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Mysql route introduction

What is mysql route?

MySQL Router is a lightweight agent between application client and dbserver that detects, analyzes and forwards queries to back-end database instances and returns the results to client. Is a substitute for mysql-proxy. Its architecture and functions are as follows.

(1) Router realizes the separation of read and write, and the program is not directly connected to the database IP, but fixed to mysql router. MySQL Router is transparent to front-end applications. The application treats MySQL Router as a normal mysql instance and sends the query to MySQL Router, while MySQL Router returns the query result to the front-end application.

(2) if the database server fails, the business can run normally. The server is automatically logged off by MySQL Router. The program configuration does not require any modification.

(3) if the master database fails, the master-slave switch is determined by MySQL Router, and the business can be accessed normally. The program configuration does not need to be modified.

Principle of separation of reading and writing

After accepting the request of the front-end application, MySQL Router distinguishes between read and write according to different ports, sends all the queries connected to the read-write port to the master library, and sends the select query connected to the read-only port to multiple slave libraries in a polling manner, so as to achieve the purpose of separation of read and write. The result returned by the read and write is given to MySQL Router, which is returned by MySQL Router to the client application.

Use of Mysql router

The main uses of MySQL Router are read-write separation, automatic switching of master and master failures, load balancing, connection pooling and so on.

Pit for automatic switching of Mysql router master and master failover

There is no problem with the Mysql router master failover function after testing, but there is a large pit to pay attention to. The specific pit and solution are at the end of the article, because if you have not been in contact with mysql router before, according to my expression ability, you will be dizzy.

Introduction to mysql router Lab Architecture

The architecture of the lab environment is as follows:

Install and deploy mysql route

(1) download address: provide different platforms, binary package and rpm package. You can choose the appropriate installation method. I use binary package to install here.

(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 a configuration file directory and copy the template configuration file

[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 settings. Log directory needs 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 # configuration file storage directory config_folder = / etc/mysql-route # running directory runtime_folder = / var/run [logger] # log runtime Don't level = INFO # master node failover configuration [routing:basic_failover] # write node address bind_address=192.168.100.10 # write node port bind_port = 7001 # mode Read / write mode = read-write # Master Node address: by default, the first master database is the write master database, when the first master database down machine The second database is promoted to master library destinations = 192.168.100.11 destinations 3306192.168.100.12 IP 3306 # Slave node load balance configuration [routing:balancing] # bound IP address bind_port # listening port bind_port = 7002 # connection timeout connect_timeout = 3 # maximum number of connections max_connections = 1024 # backend server Address destinations = 192.168.100.12 keepalive 3306192.168.100.13: 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: mysql route officially does not provide a startup script, so you need to write it yourself.

[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 the service

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

Read-write separation test

1. Create a test account and test library

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

2. Verify read load balancer: from the result, we can see that each read accesses a different host.

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

Slave library downmachine test

The following is a screenshot of the test from the library downmachine. You can see that when both nodes are normal, the rotation training takes data from the library. If one of them fails from the library, mysql router will automatically log off the failed slave database. If the slave is restored from the library, mysql router will automatically launch the slave from the library.

Master master failover test

Verification: under normal circumstances, data are written to the main database

We manually stop the mysql service of the main library, simulate the failure, and write again, and we can see that the node that writes the data is the backup node of the main library.

Note:

1. The backup slave library cannot open read-only = on, otherwise an error will be reported if it is written in after switching.

2. What will MySQL Router do after the first main library DOWN?

By default, the first master database is the write master database. After the first master database down machine, the second database is promoted to the master database. Later, if the first master database is repaired, then by default the second master database is still connected to the master database for reading and writing, 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 has been repaired?

MySQL Router can be restarted

The pit of the master master failover

If the master and master failover of Mysql router runs in the case of one master and one slave, and the slave database is used as the backup of the master database, it is no problem to use mysql router master and master failover in this case, but in the case of one master and multiple slaves, the use of master and master failover will be in the following problems.

In the case of one master and many slaves, if the master library downmachine switches to the backup node, the address of the master library of other slave libraries is connected to the failed master library, which will result in a slave library slave_ Io thread connecting state and replication delay. My previous master library address is 192.168.10.11, and the backup master library address is 192.168.10.12, but after the master library failure, mysql route can route write requests to the new master library, but the slave library attempts to connect to the master library is still 192.168.10.11, this is normal, because the mysql router master master failover principle is like this, but the production environment can not play like this.

Solution:

1. When switching, you need to write your own script to trigger a shell script or event to deal with the reconnection problem.

2. Do not use the mysql router master failover feature, but use other ways to ensure the high availability of the mysql master library.

After reading the above, do you have any further understanding of the principle of high availability of Router in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.