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 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:
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
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)]&amp;gt; create database mysql_route; MariaDB [(none)]&amp;gt; GRANT all ON mysql_route.* TO 'route'@'%' IDENTIFIED BY 'route'; MariaDB [(none)]&amp;gt; FLUSH PRIVILEGES;
2. Verify read load balancing: see from the result that each read accesses a different host
3. Verify write: you can see that every 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.
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 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.