A case study of ​ MySQL Router

This article mainly introduces the case study of MySQL Router, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

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.

Working mode

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 = 7001destinations = master1.example.com,master2.example.com,master3.example.commode = 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 = 7002destinations = slave1.example.com,slave2.example.com,slave3.example.commode = read-only

Start

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.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the case study of MySQL Router. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!