This question is not about how to install the servers nor its capabilities, but more of a design question.
So I have a cluster of MySQL servers.
I also have a DNS record "data.example.com".
The MySQL cluster has THREE MySQL/query nodes (the end-points of the cluster where applications connect to).
Behind that there are the rest of the servers of that cluster (data/management).
The aim is to treat this cluster as one solid unit (from the outside perspective).
Now, an outside app (say a web-server) wants to act (write/read) on the database. Here are the steps:
1) Resolve DNS "data.example.com".
2) Connect to the IP (it's an SQL node).
3) Do the work.
First issue that arises, the easier one, is:
How do I expose all the three SQL nodes through that single DNS?
- Round-robin on the DNS server level?
- Set that DNS record to point to three IPs of the SQL nodes?
Second issue that arises, let's say that the DNS resolved to 10.0.0.7, which is only one of the three SQL nodes: What if that node is down?
The whole cluster is still fine, but now the application that tries to connect to that node see the cluster as "down", because that node is indeed down, hence I'm losing "high availability".
So my question is simple:
What would you do to resolve the issue? Please describe in details, and, complexity doesn't frighten me :)
Note that I would ask here about load-balancing or stuff like that but I prefer to keep this question "open" and hear a wider range of solutions. Thanks!