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

The main content of this article is to explain "what is mysql router". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what mysql router is.

What is MySQL Router?

MySQL Router is an open source lightweight middleware between the application layer and the DB layer. It can forward the request analysis of the front-end application to the back-end DB server for processing, so as to achieve the load balancing of DB. It can be said that it is a substitute for the previous MySQL Proxy, and we can find its source code in Github. There are several similar tools such as Atlas, Meituan Dianping's DBProxy, MyCat and so on.

MySQL Router architecture

Using this architecture, the front-end application does not need to connect directly to DB Server, but only to MySQL Router, which can easily expand the database cluster. Next, let's take a look at some of its other main functions.

Main functions of MySQL Router

1. Automatic failover

Using MySQL Router to implement read-write separation, the front-end application layer does not need to connect directly to the underlying DB, but uniformly connects to the MySQL Router. MySQL Router is transparent to the front-end application layer, and we don't need to do any processing in the code layer. The application layer will use a normal MySQL instance of MySQL Router, and the underlying DB Master will automatically be replaced by a Backup node when it is hung up, which is very convenient.

2. DB load balancing

MySQL middleware distributes requests to the underlying database according to certain rules in a way similar to connection pool, so as to achieve read-write separation and high availability of DB.

MySQL Router is used to replace MySQL Proxy, and it is recommended that MySQL Router be deployed on the same machine as the application. The application accesses the MySQL Proxy just as it accesses MySQL, and the MySQL Proxy forwards the data to the back-end MySQL. Supports a variety of operating systems.

The working mode of MySQL Router

Read-write

For multi-master servers, personal feeling can also be used for active and standby servers, and multi-masters can avoid brain fissure problems, but in fact, only one multi-master is in Active status. And the master-slave and multi-master replication of MySQL are troublesome to restore and need to be operated manually. In contrast, MySQL Group Replication provides a simpler solution for automatic maintenance of clusters.

All requests are sent to the first server, and the subsequent second server is used only after the first server is down.

Downtime server recovery detection is not supported

Cannot be used in the following two situations

Slave downtime, then recovery, master downtime

The master goes down first, then resumes, and then goes down again

[routing:example_strategy]

Bind_port = 7001

Destinations = master1.example.com,master2.example.com,master3.example.com

Mode = read-writ

Read-only

Used to route slave server clusters.

Each request is polled to access all servers. If one server goes down, the next server is tried. If all servers are down, the route will exit. Re-available servers will be re-added to the available list in the original order

[routing:ro_route]

Bind_port = 7002

Destinations = slave1.example.com,slave2.example.com,slave3.example.com

Mode = read-only

Start up

Mysqlrouter-config=/path/to/file/my_router.ini

The maxStatements=0 of the database connection pool needs to be set to 0, otherwise when the host goes down, the connection pool will not be able to find the corresponding Statement on the slave and report an error.

Performance.

It is much higher than MySQL Proxy, and the performance loss is very small compared to directly connected MySQL.

At this point, I believe you have a deeper understanding of "what mysql router is", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.