3.6.2 Basic MySQL Cluster Backup and Restore Using MySQL Cluster Manager

This section describes backing up and restoring a MySQL Cluster, with examples of complete and partial restore operations. For purposes of example, we use a MySQL Cluster named mycluster whose processes and status can be seen here:

mcm> show status -r mycluster;
+--------+----------+------------+---------+-----------+-----------+
| NodeId | Process  | Host       | Status  | Nodegroup | Package   |
+--------+----------+------------+---------+-----------+-----------+
| 49     | ndb_mgmd | localhost  | running |           | mypackage |
| 1      | ndbd     | localhost  | running | 0         | mypackage |
| 2      | ndbd     | localhost  | running | 0         | mypackage |
| 50     | mysqld   | localhost  | running |           | mypackage |
| 51     | mysqld   | localhost  | running |           | mypackage |
| 52     | ndbapi   | *localhost | added   |           |           |
| 53     | ndbapi   | *localhost | added   |           |           |
+--------+----------+------------+---------+-----------+-----------+
7 rows in set (0.08 sec)

You can see whether there are any existing backups of mycluster using the list backups command, as shown here:

mcm> list backups mycluster;
+----------+--------+-----------+---------------------+---------+
| BackupId | NodeId | Host      | Timestamp           | Comment |
+----------+--------+-----------+---------------------+---------+
| 1        | 1      | localhost | 2012-12-04 12:03:52 |         |
| 1        | 2      | localhost | 2012-12-04 12:03:52 |         |
| 2        | 1      | localhost | 2012-12-04 12:04:15 |         |
| 2        | 2      | localhost | 2012-12-04 12:04:15 |         |
| 3        | 1      | localhost | 2012-12-04 12:17:41 |         |
| 3        | 2      | localhost | 2012-12-04 12:17:41 |         |
+----------+--------+-----------+---------------------+---------+
6 rows in set (0.12 sec)

Simple backup.  To create a backup, use the backup cluster command with the name of the cluster as an argument, similar to what is shown here:

mcm> backup cluster mycluster;
+-------------------------------+
| Command result                |
+-------------------------------+
| Backup completed successfully |
+-------------------------------+
1 row in set (3.31 sec)

backup cluster requires only the name of the cluster to be backed up as an argument; for information about additional options supported by this command, see Section 4.7.2, “The backup cluster Command”. To verify that a new backup of mycluster was created with a unique ID, check the output of list backups, as shown here (where the rows corresponding to the new backup files are indicated with emphasized text):

mcm> list backups mycluster;
+----------+--------+-----------+---------------------+---------+
| BackupId | NodeId | Host      | Timestamp           | Comment |
+----------+--------+-----------+---------------------+---------+
| 1        | 1      | localhost | 2012-12-04 12:03:52 |         |
| 1        | 2      | localhost | 2012-12-04 12:03:52 |         |
| 2        | 1      | localhost | 2012-12-04 12:04:15 |         |
| 2        | 2      | localhost | 2012-12-04 12:04:15 |         |
| 3        | 1      | localhost | 2012-12-04 12:17:41 |         |
| 3        | 2      | localhost | 2012-12-04 12:17:41 |         |
| 4        | 1      | localhost | 2012-12-12 14:24:35 |         |
| 4        | 2      | localhost | 2012-12-12 14:24:35 |         |
+----------+--------+-----------+---------------------+---------+
8 rows in set (0.04 sec)

If you attempt to create a backup of a MySQL Cluster in which each node group does not have at least one data node running, backup cluster fails with the error Backup cannot be performed as processes are stopped in cluster cluster_name.

Simple complete restore.  To perform a complete restore of a MySQL Cluster from a backup with a given ID, follow the steps listed here:

  1. Identify the backup to be used.

    In this example, we use the backup having the ID 4, that was created for mycluster previously in this section.

  2. Wipe the MySQL Cluster data.

    The simplest way to do this is to stop and then perform an initial start of the cluster as shown here, using mycluster:

    mcm> stop cluster mycluster;
    +------------------------------+
    | Command result               |
    +------------------------------+
    | Cluster stopped successfully |
    +------------------------------+
    1 row in set (15.24 sec)
    
    mcm> start cluster --initial mycluster;
    +------------------------------+
    | Command result               |
    +------------------------------+
    | Cluster started successfully |
    +------------------------------+
    1 row in set (34.47 sec)
    
  3. Restore the backup.

    This is done using the restore cluster command, which requires the backup ID and the name of the cluster as arguments. Thus, you can restore backup 4 to mycluster as shown here:

    mcm> restore cluster --backupid=4 mycluster;
    +--------------------------------+
    | Command result                 |
    +--------------------------------+
    | Restore completed successfully |
    +--------------------------------+
    1 row in set (16.78 sec)
    

Partial restore—missing images.  It is possible using MySQL Cluster Manager to perform a partial restore of a MySQL Cluster—that is, to restore from a backup in which backup images from one or more data nodes are not available. This is required if we wish to restore mycluster to backup number 6, since an image for this backup is available only for node 1, as can be seen in the output of list backups in the mcm client (emphasized text):

mcm> list backups mycluster;
+----------+--------+-----------+---------------------+---------+
| BackupId | NodeId | Host      | Timestamp           | Comment |
+----------+--------+-----------+---------------------+---------+
| 1        | 1      | localhost | 2012-12-04 12:03:52 |         |
| 1        | 2      | localhost | 2012-12-04 12:03:52 |         |
| 2        | 1      | localhost | 2012-12-04 12:04:15 |         |
| 2        | 2      | localhost | 2012-12-04 12:04:15 |         |
| 3        | 1      | localhost | 2012-12-04 12:17:41 |         |
| 3        | 2      | localhost | 2012-12-04 12:17:41 |         |
| 4        | 1      | localhost | 2012-12-12 14:24:35 |         |
| 4        | 2      | localhost | 2012-12-12 14:24:35 |         |
| 5        | 1      | localhost | 2012-12-12 14:31:31 |         |
| 5        | 2      | localhost | 2012-12-12 14:31:31 |         |
| 6        | 1      | localhost | 2012-12-12 14:32:09 |         |
+----------+--------+-----------+---------------------+---------+
11 rows in set (0.08 sec)

To perform a restore of only those nodes for which we have images (in this case, node 1 only), we can use the --skip-nodeid option when executing a restore cluster command. This option causes one or more nodes to be skipped when performing the restore. Assuming that mycluster has been cleared of data (as described earlier in this section), we can perform a restore that skips node 2 as shown here:

mcm> restore cluster --backupid=6 --skip-nodeid=2 mycluster;
+--------------------------------+
| Command result                 |
+--------------------------------+
| Restore completed successfully |
+--------------------------------+
1 row in set (17.06 sec)

Because we excluded node 2 from the restore process, no data has been distributed to it. To cause MySQL Cluster data to be distributed to any such excluded or skipped nodes following a partial restore, it is necessary to redistribute the data manually by executing an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement in the mysql client for each NDB table in the cluster. To obtain a list of NDB tables from the mysql client, you can use multiple SHOW TABLES statements or a query such as this one:

SELECT CONCAT('' TABLE_SCHEMA, '.', TABLE_NAME) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE ENGINE='ndbcluster';  

You can generate the necessary SQL statements using a more elaborate version of the query just shown, such the one employed here:

mysql> SELECT 
    ->     CONCAT('ALTER ONLINE TABLE `', TABLE_SCHEMA, 
    ->            '`.`', TABLE_NAME, '` REORGANIZE PARTITION;') 
    ->     AS Statement
    -> FROM INFORMATION_SCHEMA.TABLES 
    -> WHERE ENGINE='ndbcluster';  
+--------------------------------------------------------------------------+
| Statement                                                                |
+--------------------------------------------------------------------------+
| ALTER ONLINE TABLE `mysql`.`ndb_apply_status` REORGANIZE PARTITION;      |
| ALTER ONLINE TABLE `mysql`.`ndb_index_stat_head` REORGANIZE PARTITION;   |
| ALTER ONLINE TABLE `mysql`.`ndb_index_stat_sample` REORGANIZE PARTITION; |
| ALTER ONLINE TABLE `db1`.`n1` REORGANIZE PARTITION;                      |
| ALTER ONLINE TABLE `db1`.`n2` REORGANIZE PARTITION;                      |
| ALTER ONLINE TABLE `db1`.`n3` REORGANIZE PARTITION;                      |
| ALTER ONLINE TABLE `test`.`n1` REORGANIZE PARTITION;                     |
| ALTER ONLINE TABLE `test`.`n2` REORGANIZE PARTITION;                     |
| ALTER ONLINE TABLE `test`.`n3` REORGANIZE PARTITION;                     |
| ALTER ONLINE TABLE `test`.`n4` REORGANIZE PARTITION;                     |
+--------------------------------------------------------------------------+
10 rows in set (0.09 sec)

Partial restore—data nodes added.  A partial restore can also be performed when new data nodes have been added to a MySQL Cluster following a backup. In this case, you can exclude the new nodes using --skip-nodeid when executing the restore cluster command. Consider the MySQL Cluster named mycluster as shown in the output of the following show status command:

mcm> show status -r mycluster;
+--------+----------+------------+---------+-----------+-----------+
| NodeId | Process  | Host       | Status  | Nodegroup | Package   |
+--------+----------+------------+---------+-----------+-----------+
| 49     | ndb_mgmd | localhost  | stopped |           | mypackage |
| 1      | ndbd     | localhost  | stopped | 0         | mypackage |
| 2      | ndbd     | localhost  | stopped | 0         | mypackage |
| 50     | mysqld   | localhost  | stopped |           | mypackage |
| 51     | mysqld   | localhost  | stopped |           | mypackage |
| 52     | ndbapi   | *localhost | added   |           |           |
| 53     | ndbapi   | *localhost | added   |           |           |
+--------+----------+------------+---------+-----------+-----------+
7 rows in set (0.03 sec)

The output of list backups shows us the available backup images for this cluster:

mcm> list backups mycluster;
+----------+--------+-----------+---------------------+---------+
| BackupId | NodeId | Host      | Timestamp           | Comment |
+----------+--------+-----------+---------------------+---------+
| 1        | 1      | localhost | 2012-12-04 12:03:52 |         |
| 1        | 2      | localhost | 2012-12-04 12:03:52 |         |
| 2        | 1      | localhost | 2012-12-04 12:04:15 |         |
| 2        | 2      | localhost | 2012-12-04 12:04:15 |         |
| 3        | 1      | localhost | 2012-12-04 12:17:41 |         |
| 3        | 2      | localhost | 2012-12-04 12:17:41 |         |
| 4        | 1      | localhost | 2012-12-12 14:24:35 |         |
| 4        | 2      | localhost | 2012-12-12 14:24:35 |         |
+----------+--------+-----------+---------------------+---------+
8 rows in set (0.06 sec)

Now suppose that, at a later point in time, 2 data nodes have been added to mycluster using an add process command. The show status output for mycluster now looks like this:

mcm> show status -r mycluster;
+--------+----------+------------+---------+-----------+-----------+
| NodeId | Process  | Host       | Status  | Nodegroup | Package   |
+--------+----------+------------+---------+-----------+-----------+
| 49     | ndb_mgmd | localhost  | running |           | mypackage |
| 1      | ndbd     | localhost  | running | 0         | mypackage |
| 2      | ndbd     | localhost  | running | 0         | mypackage |
| 50     | mysqld   | localhost  | running |           | mypackage |
| 51     | mysqld   | localhost  | running |           | mypackage |
| 52     | ndbapi   | *localhost | added   |           |           |
| 53     | ndbapi   | *localhost | added   |           |           |
| 3      | ndbd     | localhost  | running | 1         | mypackage |
| 4      | ndbd     | localhost  | running | 1         | mypackage |
+--------+----------+------------+---------+-----------+-----------+
9 rows in set (0.01 sec)

Since nodes 3 and 4 were not included in the backup, we need to exclude them when performing the restore. You can cause restore cluster to skip multiple data nodes by specifying a comma-separated list of node IDs with the --skip-nodeid option. Assume that we have just cleared mycluster of MySQL Cluster data using the mcm client commands stop cluster and start cluster --initial as described previously in this section; then we can restore mycluster (now having 4 data nodes numbered 1, 2, 3, and 4) from backup number 4 (made when mycluster had only 2 data nodes numbered 1 and 2) as shown here:

mcm> restore cluster --backupid=4 --skip-nodeid=3,4 mycluster;
+--------------------------------+
| Command result                 |
+--------------------------------+
| Restore completed successfully |
+--------------------------------+
1 row in set (17.61 sec)

No data is distributed to the skipped (new) nodes; you must force nodes 3 and 4 to be included in a redistribution of the data using ALTER ONLINE TABLE ... REORGANIZE PARTITION as described previously in this section.