Building a High-Availability MySQL Cluster

by Kris Buytaert
02/16/2006

Today's enterprise depends on the availability of mail and web services. Failure is never far away, whether it be a hardware failure or a human error. We have to try to make an infrastructure as highly available as possible.

When building highly available clusters, people often choose one extra physical machine per service, creating an A-B, fail-over schema. With static websites, there is no problem making the application highly available; you can just store the data in two places. However, the moment you add a database to your environment, things start to become more difficult. The easy way out is to move the database to a different machine and move that server into a SEP field.

That's not how we do it. In the old days when sites became too heavily loaded, we used MySQL replication to create multiple read-only copies of the database, which LVS load balanced. This, however, meant that we had to modify the application itself so that it could only write to the master node.

Later, many people tried to create a replication environment that implemented high availability. All of them struggled with the problem that they couldn't exactly define where a node failed, so it was possible to lose records. Also, recovering from a failover seemed to be a difficult task.

In late 2004 and early 2005, MySQL announced MySQL Cluster--the NDB storage engine from MySQL that plugs into the MySQL environment. With this cluster environment, I built a highly redundant environment. This article discusses how to do it yourself.

Hold on before you decide to implement MySQL cluster, as it certainly doesn't suit all jobs yet. The MySQL NDB engine currently runs its database completely in memory. This means that you have to be able to fit your database in memory. If you have a 1GB dataset and you want to have your data spread over two nodes, you need 1GB of memory per node. If you have four nodes, you can deal with 512MB per node. The first thing that becomes clear is that you have to look at your dataset. Giant data-warehousing databases won't fit into the MySQL NDB engine yet. Today, you might need to look at commercial alternatives such as Continuent, the former Emic Networks.

Secondly, this article is about building a truly available MySQL cluster, which means it focuses on high availability, not on high throughput. If your database is suffering from too-high loads, you still might want to look back at replication in combination with LVS.

The MySQL NDB storage engine consists of different parts:

ndb_mgmd is the NDB management daemon. This daemon manages the cluster. It should start first in order to monitor the state of the other parts. The management daemon can arbitrate who becomes master and which nodes have to be disconnected from the cluster. It is also capable of (re)starting different nodes and starting backups. The other nodes will ask the management node for their configuration details, but from then on, they don't really need the management node anymore. You can easily stop and start the management node while not disturbing the cluster, as long as no other fault happens during this restart. The management node listens on port tcp/1186 (tcp/2200 in older versions).
ndb_mgm is the management client. It sends commands to ndb_mgmd.
ndbd is the actual network database engine. You need at least the number of nodes equal to the amount of replicas you want. To spread data over multiple nodes, increase the number of nodes.
mysqld is the standard SQL node that connects to the ndbd for NDB engine type storage. It still can use MyISAM or InnoDB tables.

Standard MySQL clients connect to the SQL node and won't notice the difference between a MyISAM or InnoDB query, so there is no need to change the API.

In order to achieve high availability, you need at least three nodes: one management node, and two different replica nodes for ndbd.

Pages: 1, 2, 3