October 22, 2006
MySQL Clustering backing up your Data
MySQL engineers where quite on top of things when they thought about backup and restore of data onto a cluster. When you start a backup the command is issued from the management node. The command can be done in one of two ways.
First, your can issue the start backup command from within the ndb_mgm program by simply issuing the command:
start backup
This usually this takes time based on the size of the database. What the management node does is talk to the storage nodes and asks which one has the master at that time. Once that is determined it slices the database into the number of storage nodes. For example 4 storage nodes 4 slices are created and storage is on each of the nodes. If one of the nodes is not responding at the time of the backup the nodes that would normally be carrying a duplicate of each other will handle 2 of the slices. This is determined by the config.ini file.
Numberofreplica=2
If you use the default location that mysql recommends you will see that a new directory is made each time that the start backup command is issued. I used /var/lib/mysql-cluster in my config.ini file so when issuing this command you can change to this directory and see that it creates the following:
/var/lib/mysql-cluster/BACKUP/BACKUP-##/
##-replacing with the incremented number of the backup. Each time that you initially start the cluster this number is reset to 1. For example if this is the 12th time you have backed up the cluster you will find three files at this location on each of the storage nodes. Remembering that each storage node will save a slice of the entire database each time that you create a backup. The files are located at
/var/lib/mysql-cluster/BACKUP/BACKUP-12/
The three files that you will find are named in a similar fashion. One for data, one for pointers and the third for a counter so when you restore the data it knows which slice of the database you are reloading.
The second way to start the backup is from the command line. This command can be placed in the /etc/cron.daily/ directory on a redhat machine to get the database to automatically back itself up each night. Usually files in this directory start around 4am each day.
ndb_mgm -e “start backup”
This allows you to backup the database directly from the command prompt. One the master has completed telling all of the storage nodes to store there slice of the database on your hard-drives it will return to the manager node an all complete signal and return the stats of backing up your database.
