As you know, MySQL InnoDB Cluster is composed of 3 elements:
- a group replication cluster of at least 3 servers
- the MySQL Shell used to manage the cluster
- the MySQL Router that send the traffic from the application server(s) to the cluster
When presenting the solution in conferences, one the main question is Where should I put the router ? and the answer is always the same: the best place to install the router is the application server !
The router is a very lightweight process that gets its configuration from the cluster’s metadata and doesn’t require a lot of resources or maintenance.
So the ideal setup is the following:
However for many (obscure?) reasons, sometimes people doesn’t want to have the MySQL Router on the same server as the application server and would like to have it on its own server instance (let’s hope they are not VM on the same physical machine 😉 ). Of course this setup is possible as illustrated below, but then, yes in that specific case, the router is a spof (Single Point Of Failure). Which means that if the router or the server where the router is installed crashes, none of your application servers are able to contact the database anymore:
If you really want to have such setup, you will have to setup HA for the MySQL Router. Such HA setup is very dependent of your infrastructure as you may have to play with virtual IPs and this is not managed the same way on Linux and Windows for example.
So if you want to install the MySQL Router on another place than the recommended application server, you will have to take care by yourself of the router’s HA.
Of course they are plenty solution to achieve such HA setup for the MySQL Router and I will try to illustrate some of them on this blog. The next article on the topic will be How to setup MySQL Router HA with Pacemaker, dont’ miss it 😉
Principle
The principle is simple, you will have multiple resources (the MySQL Router) running on multiple machines (at least more than one), and a VIP (virtual IP) resource that the application will use to connect to the router. If the “router resource” where the VIP resource runs, crashes, the VIP will be moved automatically to another server/node where the “router resource” is available:
If the router goes away, then the VIP is sent to another nodes:
Of course, if your application can use some round-robin (most of the MySQL Connectors allows that), you can have multiple VIP resources like this:
Conclusion
If you follow the recommendation, the MySQL Router is not a single-point-of-failure, but if you want to have it on its own server, then you will have to setup HA for it as this is very dependent of your infrastructure.
See:
[…] article will explain how to setup HA for MySQL Router as described in a previous article about where should the router […]
[…] having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after […]
[…] having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after […]
If a host name has a DNS name record with more than one ip address the mysql client will try to connect to any of the addresses configured.
The other more explicit configuration setup could be handled with: https://bugs.mysql.com/bug.php?id=72808 which for situations like this would be really useful.
So pacemaker and other techniques also work but I would love the client to be able to use DNS SRV records as this would simplify the HA configuration significantly for situations like the one you mention.
Hi,
On this architecture , round robin DNS technique is killing architecture itself.
Also, using DNS SRV is not convenient for round robin. Prioritizing IP’ s accumulates connections on the most prior IP’ s server?
MySQLrouter is aiming to decrease network latency via using local UNIX domain socket connection to the application instead of TCP/IP.
Ref: https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-faq.html
I have been researching with MySQL InnoDB and came to know about the cluster of a router. This came to be of great help for my work. I have learned a lot of thing from the blog. I am very much helpful to the author of the post for sharing such a great article with us.