4.6.2.5 Restoring a Backup to a Cluster with Fewer Data Nodes

Sometimes, you want to transfer data from your cluster to another one that has fewer data nodes—for example, when you want to scale down your cluster or prepare a smaller replica cluster for a replication setup. While the methods described in Section 4.6.2, “Basic MySQL NDB Cluster Backup and Restore Using MySQL Cluster Manager” will not work in that case, you can perform the transfer by just using the backup cluster command and the ndb_restore program.

The process starts with creating a backup for the original cluster using the backup cluster command. Next, create a new cluster with fewer data nodes using the create cluster command. Before the NDB table data can be transferred, the metadata for the NDB tables must first be restored to the new cluster. The backup cluster command also creates a logical backup for the metadata of the NDB tables (see Logical Backup for NDB Table Metadata, for details). Use the --all option with the list backups command to list all backups, including the logical backups for the NDB tables' metadata, which are marked by the comment “Schema”:

mcm> list backups --all mycluster;
+----------+--------+---------+----------------------+------+--------+
| BackupId | NodeId | Host    | Timestamp            | Part |Comment |
+----------+--------+---------+----------------------+------+--------+
| 1        | 1      | tonfisk | 2020-09-21 21:13:09Z | 1    |        |
| 1        | 2      | tonfisk | 2020-09-21 21:13:09Z | 1    |        |
| 1        | 3      | tonfisk | 2020-09-21 21:13:09Z | 1    |        |
| 1        | 4      | tonfisk | 2020-09-21 21:13:09Z | 1    |        |
| 1        | 50     | tonfisk | 2020-09-21 21:13:12Z |      | Schema |
| 2        | 1      | tonfisk | 2020-09-21 21:17:50Z | 1    |        |
| 2        | 2      | tonfisk | 2020-09-21 21:17:50Z | 1    |        |
| 2        | 3      | tonfisk | 2020-09-21 21:17:50Z | 1    |        |
| 2        | 4      | tonfisk | 2020-09-21 21:17:50Z | 1    |        |
| 2        | 50     | tonfisk | 2020-09-21 21:17:52Z |      | Schema |
+----------+--------+---------+----------------------+------+--------+
10 rows in set (0.01 sec)

Next, we have to find out the locations of the logical backup file and the backup files for each data node of the original cluster.

Locations of backup files.  The backup files for each node are to be found under the folder specified by the cluster parameter BackupDataDir for data nodes and the parameter backupdatadir for mysqld nodes. Because the get command is not case sensitive, you can use this single command to check the values of both parameters:

mcm> get BackupDataDir mycluster;
+---------------+----------------+----------+---------+----------+---------+---------+----------+
| Name          | Value          | Process1 | NodeId1 | Process2 | NodeId2 | Level   | Comment  |
+---------------+----------------+----------+---------+----------+---------+---------+----------+
| BackupDataDir | /opt/mcmbackup | ndbmtd   | 1       |          |         | Process |          |
| BackupDataDir | /opt/mcmbackup | ndbmtd   | 2       |          |         | Process |          |
| BackupDataDir | /opt/mcmbackup | ndbmtd   | 3       |          |         | Process |          |
| BackupDataDir | /opt/mcmbackup | ndbmtd   | 4       |          |         | Process |          |
| backupdatadir | /opt/mcmbackup | mysqld   | 50      |          |         | Process | MCM only |
+---------------+----------------+----------+---------+----------+---------+---------+----------+
5 rows in set (0.18 sec)

The backup files for each backup of a specific BackupID are found under BackupDataDir/BACKUP/BACKUP-ID/ for each data node, and under backupdatadir/BACKUP/BACKUP-ID/ for each mysqld node. The comment “MCM only” in the row returned for the parameter backupdatadir indicates that backupdatadir is used by MySQL Cluster Manager only, and the directory it specifies contains only backups for the NDB tables' metadata. Notice that If BackupDataDir is not specified, the get command will return no value for it, and it takes up the value of DataDir, so that the image is stored in the directory Datadir/BACKUP/BACKUP-backup_id. If backupdatadir has not been specified, the get command will again return no value for it, and the logical backup files for the mysqld node are to be found at the default locations of /path-to-mcm-data-repository/clusters/clustername/nodeid/BACKUP/BACKUP-Id.

The process of restoring the backed-up data from the original cluster to the new one consists of the following steps:

1. Stop the original cluster:

   mcm> stop cluster mycluster;
   +------------------------------+
   | Command result               |
   +------------------------------+
   | Cluster stopped successfully |
   +------------------------------+
   1 row in set (19.54 sec)
   
   mcm> show status mycluster;
   +-----------+---------+---------+
   | Cluster   | Status  | Comment |
   +-----------+---------+---------+
   | mycluster | stopped |         |
   +-----------+---------+---------+
   1 row in set (0.05 sec)

2. Start your new cluster. Make sure the new cluster is operational and it has at least one free ndbapi slot for the ndb_restore utility to connect to the cluster:

   mcm> start cluster newcluster2nodes;
   +------------------------------+
   | Command result               |
   +------------------------------+
   | Cluster started successfully |
   +------------------------------+
   1 row in set (33.68 sec)
   
   mcm> show status -r newcluster2nodes;
   +--------+----------+---------+---------+-----------+-----------+
   | NodeId | Process  | Host    | Status  | Nodegroup | Package   |
   +--------+----------+---------+---------+-----------+-----------+
   | 49     | ndb_mgmd | tonfisk | running |           | mypackage |
   | 1      | ndbmtd   | tonfisk | running | 0         | mypackage |
   | 2      | ndbmtd   | tonfisk | running | 0         | mypackage |
   | 50     | mysqld   | tonfisk | running |           | mypackage |
   | 51     | ndbapi   | *       | added   |           |           |
   +--------+----------+---------+---------+-----------+-----------+
   5 rows in set (0.09 sec)

3. Restore the logical backup of the metadata of the NDB tables onto the new cluster. See Reloading SQL-Format Backups for different ways to restore a logical backup. One way to do it is to open a mysql client, connect it to a mysqld node of the cluster, and then source the logical backup file with the mysql client:

   mysql> source path-to-logical-backup-file/BACKUP-BackupID.mysqld_nodeid.schema.sql

   See Locations of backup files above on how to find the path of the logical backup file. For our sample clusters, this is how the command looks like for restoring the NDB table metadata from the backup with the BackupID 2:

   mysql> source /opt/mcmbackup/BACKUP/BACKUP-2/BACKUP-2.50.schema.sql

4. Restore one by one the backup for each data node of the original cluster to the new cluster, using the ndb_restore program:

   $> ndb_restore -b BackupID -n nodeID -r --backup_path=backup-folder-for-data_node

   See Locations of backup files above on how to find the path of the data node backup files. For our sample clusters, to restore the data from the backup with the BackupID 2 for data node 1 to 4 of mycluster, execute the following commands:

   $> ndb_restore --backupid=2 --nodeid=1 --restore_data --backup_path=/opt/mcmbackup/BACKUP/BACKUP-2/ --disable-indexes

   $> ndb_restore --backupid=2 --nodeid=2 --restore_data --backup_path=/opt/mcmbackup/BACKUP/BACKUP-2/ --disable-indexes

   $> ndb_restore --backupid=2 --nodeid=3 --restore_data --backup_path=/opt/mcmbackup/BACKUP/BACKUP-2/ --disable-indexes

   $> ndb_restore --backupid=2 --nodeid=4 --restore_data --backup_path=/opt/mcmbackup/BACKUP/BACKUP-2/ --disable-indexes

   The option --disable-indexes is used so indexes are ignored during the restores. This is because if we also try to restore the indexes node by node, they might not be restored in the right order for the foreign keys and unique key constraints to work properly. Therefore, the --disable-indexes option is used in the above commands, after the execution of which we rebuild the indexes with the following ndb_restore command and the --rebuild-indexes option (you only need to run this on one of the data nodes):

   $> ndb_restore --backupid=2 --nodeid=1 --rebuild-indexes --backup_path=/opt/mcmbackup/BACKUP/BACKUP-2/

The data and indexes have now been fully restored to the new cluster.