Setting up MySQL Cluster in the cloud
Before embarking on a MySQL Cluster installation, it is important to remember that MySQL Cluster is 'just' a storage engine for your existing MySQL database servers. It stores data at the table level, not the whole database, it is therefore on the same functional level as MyISAM or INNODB. You still need a standard MySQL server to access the table data and store the database information. This has the fringe benefit that you can target specific tables to be saved to the cluster, rather than the whole database, if you have some tables that are either more important, or more heavily used.
In this article we are going to cover MySQL Cluster, it's installation on Debian (Lenny), Master-Master replication and how to tie all this together with HAProxy for a very high availability solution.
Once again, we will be using Rackspace Cloud servers to create our database cluster and to get this right, you are going to need to provision at least four Debian servers. Name them how you want but you are going to end up with two "nodes" and two "management" servers. If you decide to add the load balancing and failover of HAProxy, you're going to need an additional server for that, or even two if you want to add heartbeat later.
For the purposes of this article, we will use the following IP addresses in examples:
10.0.0.1 - Management 1
10.0.0.2 - Management 2
10.0.0.3 - Node 1
10.0.0.4 - Node 2
Base server configuration
When your servers are provisioned, run the following commands on all four servers:
apt-get upgrade -y
apt-get install mysql-server -y
These commands are updating the server with the latest patches and installing the Debian MySQL package, which includes all the MySQL cluster binaries as well as the traditional ones. Finally, stop the standard MySQL server, you'll need it on some servers, but not on others, either way you don't want the service running just yet.
Configuring the nodes
Setting up the data nodes for the MySQL cluster is one of the simplest parts of the whole process. First off, you need to stop the default MySQL and NDB Management services from starting on a reboot.
update-rc.d -f mysql remove
update-rc.d -f mysql-ndb-mgm remove
The standard Debian install for MySQL sets all three services (Node, Management and Server) to start up on a reboot, but detects whether the cluster parts have been configured before actually launching the services.
Now configure the two data nodes:
Remove all lines in this file (the default settings in there are for a "normal" MySQL install) and replace with the following:
Close and save the file in nano (CTRL+X, Y). Make these changes exactly the same on both data nodes.
The benefit of this simple set up and using cloud servers, means that to provision an additional node, no additional configuration of the node machine is required, just boot an image of one of your existing nodes and make a small change to the management server(s). The nodes pull all of their config from the management servers, so all you are doing here is telling them where to get their settings from.
You don't want to start your nodes yet, they should always be started after the management servers.
In the event of a complete failure of a management node (i.e permanently offline and unrecoverable), you can change the IP address in the above config file then do a "rolling restart", stopping and starting one node at a time.
Configuring management servers
The Debian install of MySQL creates a script that checks for the existence of an NDB config file, and only starts the service if it's present. So to start, create this file and populate it with our settings.
Now enter the following information:
Exit nano and save this file.
To break this file down a little is relatively easy, the first two sections are responsible for the node and management configuration across your cluster. Check the MySQL Cluster manual for more details on these and other options. MySQL Cluster nodes store all data in RAM, so make sure the DataMemory and IndexMemory allocations are sufficiently large enough for your circumstances.
You need one [ndb_mgmd] section for each management server, one [ndbd] section for each node and one [mysqld] section for each MySQL server that you are going to allow to connect to your cluster. Specifying the ID and hostname in each of these sections is optional, but be aware that without these it will allow any host to connect as a node or MySQL server.
The cluster part of your set up is now complete, so you can start up the management servers and then the nodes.
On both management servers type the following:
This will give you a display showing which nodes, management servers and MySQL servers are connected to your cluster. Check that both your management servers are showing up in the MGM section.
Now on your nodes type the following:
After a few seconds you should see both nodes change status on your management servers, if you don't get an additional console line appear just type "show" again until they appear. Exit the managment console by typing "quit".
Congratulations, you now have a cluster storage engine up and running. It's not a lot of use to you at the moment as there's no MySQL server set up to connect to it. If you have a MySQL server already running on your network you can connect this to the cluster and start moving tables to the new storage engine, simply add the following lines to your my.cnf in the mysqld section:
Then use the following SQL to change your table to use the cluster.
ALTER TABLE TableName SET ENGINE=NDBCLUSTER
In the event of the failure of a management server, you will need to change the connect string to the alternate management server.
Configuring your MySQL servers
If you don't already have a MySQL server set up, then you can configure it on the management servers and set up Master-Master replication so you can connect to either for reads and writes and don't lose any data in the event of a failure of one of them. This will still not provide us with automatic failover, we will build that out with HAProxy a little later. These MySQL servers don't have to live on the same box as the management, however the NDB Management service uses very little resources so would be a bit of a waste off on it's own.
Edit your MySQL config file, which is a good default starting point with the Debain install, by typing the following:
In the [mysqld] section, add the following:
Replace with the correct IP for the management server you are working on, do not use 127.0.0.1 or localhost. You will also need to put this ip next to the "bind-address" key to allow you to access the server from anywhere else.
What you will be doing here is enabling the ndbcluster table type and telling it to look at itself for cluster storage, this means it's not reliant on another box, so a failure of a management box is contained.
Now you can start your MySQL servers and they'll connect to your cluster:
Enter the NDB manager again (ndb_mgm), type "show" and you should now see your mysqld servers connected to your cluster. Congratulations again, you can now run tables on your cluster and connect from other hosts to your MySQL servers, but you are still not fully fault tolerant, and you have to add databases and tables on each MySQL server individually for them to be useable from that MySQL server.
Setting up MySQL Master-Master Replication
This is the same as for standard MySQL so follow our guide on MySQL Master-Master Replication. Combining these two can be overkill for a lot of MySQL installs, but if your load is high and you need supremely high availability, combining these two methods allows scalability, reliability and resilience. Furthermore, with the expense of cloud computing being so low, you can justify this within your budgets much easier.
Load Balancing and failover your cluster with HAProxy and heartbeat
As with the replication, load balancing and failover for your MySQL Cluster is the same as for standard MySQL servers.
If you've followed all the steps above; clustering, replication, load balancing and heartbeat, you will have a database infrastructure with blistering speeds and no single point of failure, with as little as four cloud servers. Yet you also have a system that can be easily scaled up to include many servers as load and budgets allow.
Don't forget, if you've done this from scratch on a publically accessible server, you are going to need to lock all servers down with iptables. See our guide on iptables and set ups for balancing, MySQL and others.
If you would like SBIT to consult on your own database scalability requirements, or if you have a database infrastructure that is mission critical to you, give us a call on +44 (0)1372 282828, email email@example.com or visit our main website itinsurrey.co.uk