MySQL Cluster includes a native backup tool that allows you to make online backups. This is the most common tool to use for backups because it allows normal continuous running of the cluster while you’re making the backup, as it doesn’t set any locks to make the backup. The native backup tool only backs up those database objects that are clustered (for example, table structures and data). This tool does not back up objects such as databases, stored procedures, and triggers, so you need to back them up by using some other method.

To control MySQL Cluster’s native backup tool, you use the management client, ndb_mgm. There are two commands related to dealing with the back process: START BACKUP and ABORT BACKUP. To initiate the backup process, you use the START BACKUP command. You can use a few options with this command to control when the command will return. The default is WAIT COMPLETED, which causes the command to wait until all the nodes have successfully reported that the backup was complete. If you have a lot of data in the cluster, this command may take a long time to complete, so it might be better to use one of the other options. WAIT STARTED causes the command to wait until all the nodes have successfully received the command to start making a backup and actually start the process. The final option is the NOWAIT option, which causes the command to return immediately, without waiting for the data nodes to confirm that they have started or finished. The following are some examples of these commands:

Regardless of the option you use to initiate a backup, the cluster log will contain information about the backup process as well. You can monitor this log if you have used the NOWAIT option to see when it completes. The cluster log file has output similar to the following:

Notice that each backup you make is assigned a number, called the backup ID. It is important to know this ID when you want to use a backup later on for recovery. If you don’t have the number, it isn’t too important because you can get it from the name of the backup. The backup ID numbers increment, so the largest number is the most recent backup. The incrementing number persists across cluster restarts, but an all --initial restart resets the number. You need to be careful if you reset it in this manner because it will overwrite the previous backups if they still exist in the same location when you try to make a new backup.

The backup itself is stored locally on the hard drive of each data node. For example, if you have four data nodes, four locations contain the data required to restore your backup. You normally have to copy all these files into a single location in order to store your backup for the long term. MySQL Cluster doesn’t have a built-in way to do this, but it should be fairly trivial to do it by using something such as scp or rsync. Chapter 4, “Security and Management,” provides some examples of scripts to do similar things.

The backup itself is stored in a directory called BACKUP. Normally, this is inside the DataDir defined in the cluster configuration file. However, it is possible to move it with an additional cluster configuration option called BackupDataDir. Each backup you make creates an additional directory within this BACKUP directory called BACKUP-#, where # is the backup ID. Inside the BACKUP-# directory are three files—BACKUP-#.N.ctl, BACKUP-#.N.log, and BACKUP-#-M.N.Data—where # is the backup ID, N is the node ID of the originating node, and M is the file number. The .ctl file is the metadata from the cluster. This includes information about what tables, indexes, triggers, and so on exist. The second set of files is the .Data files, which contain the data from your entire system. In theory, there can be multiple .Data files, depending on how much data is in your system. The final file is the .log file. This is the UNDO log used to make the backup consistent, and it allows the backup to not set any locks while occurring. It is automatically reapplied during system recovery. Your BACKUP directory should look similar to the following:

The other management client command involved in backups is the ABORT BACKUP # command. This command allows you to stop a backup that is in progress. It causes the data nodes to delete all the data that has to that point been written out as well. This command does not return an error if the backup isn’t in progress, even though it doesn’t do anything because there is nothing to abort. If the abort occurs, you see a message in the cluster log confirming that it was aborted:

In the cluster log you see this:

Another way you can make backups is by using a tool called mysqldump. If you have been using MySQL with other storage engines, this tool should be familiar to you. Using mysqldump to back up non-cluster objects, such as triggers and stored procedures, works exactly the same as with non-clustered databases. Quite commonly when doing this, you should use the --no-data flag with mysqldump to ensure that you aren’t backing up clustered data. If you want to use it to back up clustered data, you need to follow a few special restrictions related to using it with MySQL Cluster. The special restrictions exist because MySQL Cluster doesn’t have a repeatable read isolation level and doesn’t support distributed table locking (the two methods used by mysqldump for making consistent backups). Generally, you use mysqldump only if you needed to restore the data into a different system (that is, not into MySQL Cluster).

If you want to back up data with mysqldump in order for mysqldump to get a consistent view of the data, you need to enter single-user mode before making the backup (or otherwise ensure that no one else is going to be changing data while you’re making the backup). This allows mysqldump to get a consistent view of the data to backup. However, this will not be an online backup because users will be denied access to the cluster while this is continuing.

After you enter single-user mode, you can make a backup as you normally would. There are many different options you can use with mysqldump to change the format and other parameters. We recommend that you take a look at the output of mysqldump --help and at the webpage for mysqldump (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html), which includes many examples. You should make sure to use one of the backup methods that lock the tables first, such as the --lock-tables option or the --master-data option. With mysqldump, you can choose what pieces to back up as well, whereas the native hot backup tool always does all the data for all tables.

The following is an example of a mysqldump command to back up the world database data while in single-user mode:

The previous sections cover how to perform a backup operation. But a backup is useless if you cannot properly recover from it. The recovery process is relatively simple for both of the backup methods discussed. However, you should generally practice the process a few times before you attempt to do it on a production system.

There are a few reasons the MySQL Cluster native backup tool may fail to make a backup. The following sections discuss a few common problems and how to solve them.

If a problem occurs while making a backup, it should always be reported in the cluster log. It is a good practice to monitor the cluster log for any such issues. The cluster log should give a general idea as to why the backup failed. It might be because someone manually aborted the backup by using the ABORT BACKUP command or be due to an uncontrollable cause, such as the three mentioned in the following sections.

In the unlikely event that the backup causes a node to fail, you can find more information in the node log and also in the trace file for the node that failed.