3.6.2.3 Partial restore—missing images

It is possible using MySQL Cluster Manager to perform a partial restore of a MySQL NDB 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 :

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 NDB 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)