Mysql – Backup plan for MySQL NDB cluster databse not innoDB

backupMySQLndbcluster

I have a Database which will grow more than 250GB all data is in NDB engine(2 datanodes) and no other mysql engine used for data store.

  • What are the best approaches or plans for MySQL Cluster NDB database backup.

  • Is MySQL Enterprise Backup( part of MySQL Cluster CGE) or any other tool does backup for NDB?

  • Can a beginner like me do these backup of NDB easily or needs specialised skills?

Kind regards,

Best Answer

You should go through some more articles regarding cluster to make better understanding. I have put here the steps of taking cluster backup and restore with 4 data node scenario.

1.MySQL Cluster Native Backup Tool (ONLINE Backup) and Restore

1.1 

ndb_mgm> START BACKUP <Option>

<Option>:NOWAIT, WAIT STARTED, WAIT COMPLETED

NOWAIT - This returns a response to the display immediately and begins the backup without waiting for responses from the data nodes.

WAIT STARTED - This returns a response to the display only when a response is returned from all the data nodes saying the backup has begun.

WAIT COMPLETED - This returns a response to the display only when a response is returned from all the data nodes saying that the backups have been completed successfully.
In most cases, using the START BACKUP WAIT COMPLETED is the best option: 

 ndb_mgm> START BACKUP WAIT COMPLETED

Files that are created on Disk

1.1.1 .ctl - This group of files (labeled backup-..ctl) is the metadata from the cluster for each data node.

1.1.2 .log - This group of files (labeled backup-..log) contain the UNDO log files to keep the backup consistent and to ensure that no locks are needed during the backup process.

1.1.3 .Data - This group of files (labeled backup-- ..Data) contains the data for the entire system.

1.2 To interrupt the backup in progress.

If the abort was successful, a message will be displayed stating this and issuing an error (which is to be expected).If a backup id is entered, but there is no backup occurring that is associated with that id, there is a response but without an error being issued:

ndb_mgm> ABORT BACKUP <backup id> 
ndb_mgm> ABORT BACKUP 6

1.3 ndb_mgm> <node_id> REPORT BackupStatus

This command provides a status report on a cluster backup at anytime during the backup for node_id, or for all data nodes using ALL.

1.4 Restoring cluster Backup

1.4.1 Shutdown the cluster and then Restart Management Node

1.4.1.1 

 ndb_mgm>SHUTDOWN

1.4.1.2 

/usr/local/mysql/bin/ndb_mgmd

1.4.2 Start and empty the data nodes:-

Prior to restoring a backup, it is important to start and empty the data nodes.This is accomplished by starting each data node with the --initial option. 

./ndbd  --initial

1.4.3 Restore the Cluster Backup

1.4.3.1 ndb_mgm> ENTER SINGLE USER MODE [Node-Id of First Free Slot]

1.4.3.2 In an operating system console, enter the following command from the /var/lib/mysqlcluster/BACKUP/BACKUP-1/ directory:

ndb_restore -m -r -b 1 -n 2   /* -- For First DataNode*/
ndb_restore -r -b 1 -n 3      /*-- For Second DataNode */

1.4.3.3 

ndb_mgm> EXIT SINGLE USER MODE

2. MySQL Cluster Backup through MySQLDump Utility

2.1 

shell> mysqldump -u root -p --lock-tables Dd_Name > Db_Name-backup.sql

2.2 Restoring mysqldump Files 

shell> mysql -u root -p < Db_Name-backup.sql
Related Question