0

I have two MySQL Clusters (NDB), Prod and Dev. Each cluster contains SQL nodes, Management nodes and Data nodes. Backups are done on one cluster using the article below:

https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-backup-using-management-client.html

How do I import the created backup file to separate MySQL Cluster? i.e. refresh dev cluster with prod data.

The backup seems to be tied to the cluster it was created on. Is there a way to do a 'restore' on a separate cluster with the backup file?

0

It is possible to restore backup on a different cluster. Did that many times myself. Just backup and restore procedures are not so simple in the case of NDB.

  1. Take backup of NDB tables structure with mysqldump.
  2. Make NDB backup.
  3. Restore mysqldump backup on the new cluster.
  4. Restore NDB data using ndb_restore (on both datanodes).
  5. Rebuild indexes (command has to run just on one datanode).

I was using the following steps to perform backup and restore. In the first step I generate dump script to make schemes structures backup.

echo "select concat('mysqldump --opt --no-data ', table_schema, ' ', table_name, ' > ', table_schema, '/', table_name, '.mysqldump') from tables where engine='ndbcluster' and table_schema not in ('mysql');" |
  mysql -NB information_schema > dumpscript.sh
ndb_mgm -e "start backup 222 wait completed"
[copy BACKUP/BACKUP-222 directories from the source to destination datanodes]
[restore mysqldump files]
ndb_restore --nodeid=10 --no-binlog --restore_data --disable-indexes --parallelism=1024 --promote-attributes --backupid=222 --exclude-databases=ndbinfo --backup_path=/mysql/cluster/BACKUP/BACKUP-222/
ssh othernode ndb_restore --nodeid=11 --no-binlog --restore_data --disable-indexes --parallelism=1024 --promote-attributes --backupid=222 --exclude-databases=ndbinfo --backup_path=/mysql/cluster/BACKUP/BACKUP-222/
ndb_restore --nodeid=10 --no-binlog --backupid=222 --exclude-databases=ndbinfo --backup_path=/mysql/cluster/BACKUP/BACKUP-222/ --rebuild-indexes --parallelism=512

If that looks too simple - keep in mind that you will have to drop and later recreate foreign keys if you have any. :)

  • How do you rebuild indexes? – McJagger Dec 11 '17 at 12:06
  • Check the last line from example - "--rebuild-indexes" option. – Mindaugas Riauba Dec 11 '17 at 13:17
0

Most certainly it is possible to restore a backup on a different cluster. This is a standard usage of backups and should be well covered by the documentation of ndb_restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.