MySQL Clusters Native Backup Tool

MySQL Cluster s Native Backup Tool

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:

shell> ndb_mgm e "START BACKUP NOWAIT"
ndb_mgm> START BACKUP WAIT COMPLETED
Waiting for completed, this may take several minutes
Node 2: Backup 2 started from node 1
Node 2: Backup 2 started from node 1 completed
 StartGCP: 114086 StopGCP: 114089
 #Records: 5321 #LogRecords: 0
 Data: 191220 bytes Log: 0 bytes

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:

2005-11-25 17:02:42 [MgmSrvr] INFO -- Node 2: Backup 1 started from node 1
2005-11-25 17:02:43 [MgmSrvr] INFO -- Node 2: Backup 1 started from node 1
 completed
 StartGCP: 114078 StopGCP: 114081
 #Records: 5321 #LogRecords: 0
 Data: 191220 bytes Log: 0 bytes
2005-11-25 17:02:53 [MgmSrvr] INFO -- Node 2: Backup 2 started from node 1
2005-11-25 17:02:53 [MgmSrvr] INFO -- Node 2: Backup 2 started from node 1
 completed
 StartGCP: 114086 StopGCP: 114089
 #Records: 5321 #LogRecords: 0
 Data: 191220 bytes Log: 0 bytes

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. Chapter4, "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 filesBACKUP-#.N.ctl, BACKUP-#.N.log, and BACKUP-#-M.N.Datawhere # 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:

shell:~/ndb/BACKUP/BACKUP-1$ ls -l
total 432
-rw-r----- 1 user group 99568 Nov 25 17:19 BACKUP-1-0.2.Data
-rw-r----- 1 user group 91956 Nov 25 17:19 BACKUP-1-0.3.Data
-rw-r----- 1 user group 6384 Nov 25 17:19 BACKUP-1.2.ctl
-rw-r----- 1 user group 44 Nov 25 17:19 BACKUP-1.2.log
-rw-r----- 1 user group 6384 Nov 25 17:19 BACKUP-1.3.ctl
-rw-r----- 1 user group 44 Nov 25 17:19 BACKUP-1.3.log

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:

ndb_mgm> ABORT BACKUP 2
Abort of backup 2 ordered

In the cluster log you see this:

2005-11-25 18:13:47 [MgmSrvr] INFO -- Node 3: Backup 5 started from node 1
2005-11-25 18:13:47 [MgmSrvr] ALERT -- Node 3: Backup 5 started from 1 has
been aborted. Error: 1321

 

Using mysqldump for Backups

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:

shell> mysqldump -u root -p --lock-tables world > backup-world.sql

 

Single-User Mode

Single-user mode allows you to lock down a cluster so that it is accessible only to a single MySQL server (or similar connection to the cluster). All other servers give an error when someone tries to access one of the tables stored in the cluster.

To enter single-user mode, you issue the command ENTER SINGLE USER MODE #, where # is the node ID of the node you want to allow to communicate with the cluster. Entering this mode can take a brief time to complete; you can see when you have entered single-user mode by using the ALL STATUS command or the SHOW command. When you are done with the mode, you can use the command EXIT SINGLE USER MODE to resume normal operation.

The following is an example of a session:

ndb_mgm> ENTER SINGLE USER MODE 4
Entering single user mode
Access will be granted for API node 4 only.
Use ALL STATUS to see when single user mode has been entered.
ndb_mgm> ALL STATUS
Node 2: single user mode (Version 5.0.13)
Node 3: single user mode (Version 5.0.13)

ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (Version: 5.0.13, single user mode, Nodegroup: 0)
id=3 @127.0.0.1 (Version: 5.0.13, single user mode, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (Version: 5.0.13)

[mysqld(API)] 2 node(s)
id=4 @127.0.0.1 (Version: 5.0.13)
id=5 (not connected, accepting connect from any host)

ndb_mgm> EXIT SINGLE USER MODE
Exiting single user mode in progress.
Use ALL STATUS to see when single user mode has been exited.
ndb_mgm> ALL STATUS
Node 2: started (Version 5.0.13)
Node 3: started (Version 5.0.13)

Single-user mode is useful for a few different things. First, it is somewhat like mysqldump, as mentioned previously. Second, it is useful when you're restoring a backup into MySQL Cluster. When you restore a backup (either from mysqldump or by using the hot backup method), you should normally enter single-user mode. In this way, restoring a backup is not a hot operation. Finally, there are some maintenance tasks for which you generally want to enter single-user mode as well. This includes tasks such as ALTER TABLE, which need to be synchronized across all the MySQL servers.

The following is a typical example of the usage of this mode in order to ensure you make a consistent backup of the world database:

shell> ndb_mgm -e "ENTER SINGLE USER MODE 4"; sleep 10;
 mysqldump -u root -p --lock-tables world > backup-world.sql;
 ndb_mgm -e "EXIT SINGLE USER MODE";