MySQL fail-over clustering setup

MySQL provides cluserting but not fail over for end user. So here at JaguarPC Web Hosting labs we tested MySQL fail over cluserting. To setup MySQL clustering a minimum of 3 computers are required. However recommended number is 4 or more. And to setup a failover mysql cluster, we add one more machine making it 5. There are 3 parts in a MySQL Cluster:

Data: 2 or more nodes act as data. In MySQL 5.0, the data sits in memory, while in 5.1 data can go on disk as well (5.1 is release candidate). For our testing we use 2 data nodes. When one of the node dies, the other will serve the data.

Management: One machine is used for cluster management. Once the cluster is started and this machine goes down, the cluster can continue to work. So this is not critical for our fail over setup.

SQL:: 2 test machines were setup as SQL nodes in failover with a single “floating IP”. When one died, the “floating IP” moved to the second machine using arp spoofing. MySQL client would connect to SQL node using that floating IP, so for the client MySQL Server remains up and running when one of the SQL nodes die. This part of the setup is JaguarPC’s own making and is available through Dedicated Server Hosting offer.

The rough diagram for this whole setup is as follows:

MySQL client -> [ 2 SQL nodes fail-over ] -> [ 2 data nodes ]

RAM Requirement for Data Node: Since MySQL 5.0 uses RAM to hold the data in data nodes, you can use the following formula for obtaining a rough estimate of how much RAM is needed for each data node in the cluster:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

Say our database size is 10 GB, and we make 2 replicas and host it on 4 nodes, then we need about 6GB RAM in each node. If we store our 10GB with 2 replicas in 6 nodes, then we need 4GB RAM in each node. You get the idea.

More will be posted later about this topic.