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!
High availability is usually done using what is called VIP (virtual IP address). So, this IP should be different from the "statically" assigned IP address to each cluster node. This VIP should be assigned "dynamically" by the high availability solution you are using. So, only one node will be serving the requests in this case and if that node fails, the high availability software will assign the VIP to another node in the cluster. This way, you will not suffer from a down time except the very small portion of time during failover from one node to the other.
If you are doing load balancing, you will need to have two load balancing nodes and at least two cluster nodes. The VIP will be assigned to one of the load balancing nodes. The application requests will be directed to the cluster nodes via one of the load balancing nodes.
An important point to worry about is the data sync among the cluster nodes especially in load balancing scenarios. For example, imagine that you are writing to one node and reading from another. Of course, this will not work unless a good sync/load balancing mechanism is applied.
EDIT:
The VIP is usually a private IP address assigned by the high availability software to the active node in the cluster. It is just a different IP address than the original IP of each node. It can be simply assigned from the same subnet. If the cluster nodes should be made accessible via NATing, the public IP address should be NATed to the VIP. This is important to be able to access the service regardless of which one is the active node.
You will need to deploy a load balancer to split the load on your sql nodes, resolve your DNS to the ip address of that load balancer