MySQL™ Cluster Manager 1.3.6 User Manual
This section describes backing up and restoring a MySQL Cluster,
with examples of complete and partial restore operations. Note
that the backup cluster and restore
cluster commands work with
NDB tables only; tables using other
MySQL storage engines (such as
InnoDB or
MyISAM) are ignored.
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 | tonfisk | running | | mypackage |
| 1 | ndbd | tonfisk | running | 0 | mypackage |
| 2 | ndbd | tonfisk | running | 0 | mypackage |
| 50 | mysqld | tonfisk | running | | mypackage |
| 51 | mysqld | tonfisk | running | | mypackage |
| 52 | ndbapi | *tonfisk | added | | |
| 53 | ndbapi | *tonfisk | 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 | tonfisk | 2012-12-04 12:03:52 | |
| 1 | 2 | tonfisk | 2012-12-04 12:03:52 | |
| 2 | 1 | tonfisk | 2012-12-04 12:04:15 | |
| 2 | 2 | tonfisk | 2012-12-04 12:04:15 | |
| 3 | 1 | tonfisk | 2012-12-04 12:17:41 | |
| 3 | 2 | tonfisk | 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 | tonfisk | 2012-12-04 12:03:52 | |
| 1 | 2 | tonfisk | 2012-12-04 12:03:52 | |
| 2 | 1 | tonfisk | 2012-12-04 12:04:15 | |
| 2 | 2 | tonfisk | 2012-12-04 12:04:15 | |
| 3 | 1 | tonfisk | 2012-12-04 12:17:41 | |
| 3 | 2 | tonfisk | 2012-12-04 12:17:41 | |
| 4 | 1 | tonfisk | 2012-12-12 14:24:35 | |
| 4 | 2 | tonfisk | 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:
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.
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)
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 | tonfisk | 2012-12-04 12:03:52 | |
| 1 | 2 | tonfisk | 2012-12-04 12:03:52 | |
| 2 | 1 | tonfisk | 2012-12-04 12:04:15 | |
| 2 | 2 | tonfisk | 2012-12-04 12:04:15 | |
| 3 | 1 | tonfisk | 2012-12-04 12:17:41 | |
| 3 | 2 | tonfisk | 2012-12-04 12:17:41 | |
| 4 | 1 | tonfisk | 2012-12-12 14:24:35 | |
| 4 | 2 | tonfisk | 2012-12-12 14:24:35 | |
| 5 | 1 | tonfisk | 2012-12-12 14:31:31 | |
| 5 | 2 | tonfisk | 2012-12-12 14:31:31 | |
| 6 | 1 | tonfisk | 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 | tonfisk | stopped | | mypackage |
| 1 | ndbd | tonfisk | stopped | 0 | mypackage |
| 2 | ndbd | tonfisk | stopped | 0 | mypackage |
| 50 | mysqld | tonfisk | stopped | | mypackage |
| 51 | mysqld | tonfisk | stopped | | mypackage |
| 52 | ndbapi | *tonfisk | added | | |
| 53 | ndbapi | *tonfisk | 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 | tonfisk | 2012-12-04 12:03:52 | |
| 1 | 2 | tonfisk | 2012-12-04 12:03:52 | |
| 2 | 1 | tonfisk | 2012-12-04 12:04:15 | |
| 2 | 2 | tonfisk | 2012-12-04 12:04:15 | |
| 3 | 1 | tonfisk | 2012-12-04 12:17:41 | |
| 3 | 2 | tonfisk | 2012-12-04 12:17:41 | |
| 4 | 1 | tonfisk | 2012-12-12 14:24:35 | |
| 4 | 2 | tonfisk | 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 | tonfisk | running | | mypackage |
| 1 | ndbd | tonfisk | running | 0 | mypackage |
| 2 | ndbd | tonfisk | running | 0 | mypackage |
| 50 | mysqld | tonfisk | running | | mypackage |
| 51 | mysqld | tonfisk | running | | mypackage |
| 52 | ndbapi | *tonfisk | added | | |
| 53 | ndbapi | *tonfisk | added | | |
| 3 | ndbd | tonfisk | running | 1 | mypackage |
| 4 | ndbd | tonfisk | 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.