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 tomycluster
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.