There are two common options for backing up a MySQL cluster database: mysqldump or a native cluster backup. The utility mysqldump is more general since it is a storage engine independent. It has an advantage in that you can usually restore to a different table type with only minor effort. Also with it, backups can be limited to only certain databases or even tables. Since the dump file is written as SQL INSERT statements, it may take some time to restore, though.
The cluster backup creates a full backup of all data in a MySQL cluster. It can only be restored into another MySQL cluster instance and it is not possible to restrict the backup or restore process to single MySQL databases or tables. The MySQL cluster backup is faster, though, on backups and especially on restores. Plus, it doesn’t require any data locking to be consistent.
How to backup a MySQL Cluster
For a completely functional backup of MySQL Cluster that allows for a complete restore, partial restore and/or point-in-time recovery process, the following steps should be taken:
1. Perform a native cluster backup. Issue a START BACKUP statement in the MySQL cluster management client, ndb_mgm. You can automate the process by passing the command at the command line using the following:
shell> ndb_mgm -e "START BACKUP"
A MySQL cluster backup can be restored into a newly set up a MySQL cluster using the ndb_restore utility.
2. Obtain a copy of the mysql.ndb_binlog_index table.
3. Copy the binary logs from each of the SQL nodes using your favorite method. This will allow for a point-in-time recovery to be performed if it is required.
4. Use mysqldump utility to get a backup of the user accounts and privileges.
5. A schema dump using the mysqldump utility is very useful. This allows for greater options of recovery if something is wrong with the native backup, but also allows for other useful techniques such as versioning the schema to manage changes over time.
shell> mysqldump --no-data --triggers --routines --events mydb > schema_mydb.sql