MySQL Cluster Point-In-Time Recovery (PITR)

If you’re using MySQL Cluster and want to restore data from a specific point-in-time after the last backup of a database, you can do so by replaying binary log entries from the last backup up until a specified point in time. The data restoration covered in this article only involves MySQL Cluster. It does not cover other storage engines.

Four steps are involved. Make sure that these steps are performed in single-user mode so as to reduce the chance that data is changed while restoring data.

1. Obtain a backup of the cluster, the binary logs and the mysql.ndb_binlog_index table
2. Restoring the cluster backup itself
3. Restoring the ndb_binlog_index table
4. Determining which binary log to start replaying and optionally to stop.

Please note that this procedure should work, however – there is a chance that you may hit bug where the schema changes do not trigger row in ndb_binlog_index. If you do, the workaround is to enable ndb_log_empty_epochs.

1. Obtain a backup of the cluster, the binary logs and the mysql.ndb_binlog_index table

The following backup data files are required:

• An online backup of the cluster (http://dev.mysql.com/doc/refman/en/mysql-cluster-backup.html). This backup must have already been made before the point in time you wish to recover up to, you cannot recover to a time period before the cluster backup was made.
• A logical backup of the mysql.ndb_binlog_index table using mysqldump (Example: mysqldump -u root -p mysql ndb_binlog_index >> /tmp/ndb_binlog_index.sql) — this can be done at any time before the cluster is restored.
• A copy of the binary logs from before the backup was created, until the point in time you wish to restore to.

2. Restoring the cluster backup

The first step of restoring Cluster from a backup is covered in detail in the MySQL manual (Restore a MySQL Cluster Backup). It’s important to use the –restore_epoch option for ndb_restore so that the epoch data is restored. This is to access easily the last epoch when the backup was finished.

As an example, restoring a backup with backup id 1 into a two data node cluster where the data nodes have the node ids 1 and 2, the steps are:

1. Restore the meta data like this:

ndb_restore --restore_epoch --backupid=1 --nodeid=1 -c mgmhost:1186 --restore_meta --disable-indexes

You only need to do this once.

2. Next restore the data for both nodes like this:

ndb_restore --restore_epoch --backupid=1 --nodeid=1 -c mgmhost:1186 --restore_data --disable-indexes
ndb_restore --restore_epoch --backupid=1 --nodeid=2 -c mgmhost:1186 --restore_data --disable-indexes

3. Rebuild the indexes:

ndb_restore --restore_epoch --backupid=1 --nodeid=1 -c mgmhost:1186 --rebuild-indexes

This should only be done for one node!

3. Restoring the ndb_binlog_index table

You must now also load the mysql.ndb_binlog_index table from the backup you created earlier, example:

mysql> source /tmp/ndb_binlog_index.sql

4. Determining which binary log to start replaying and optionally to stop.

The second step for point-in-time recovery of cluster is to replay the binary logs. You will need the last epoch which was backed up and restored in the first step. For this, connect to a MySQL server which is connected to MySQL Cluster and execute the following SQL statement:

SELECT @LATEST_EPOCH:=MAX(epoch)
  FROM mysql.ndb_apply_status;

Now retrieve the first binary log needed to restore from and the position to start:

SELECT Position, @FIRSTFILE:=File
  FROM mysql.ndb_binlog_index
 WHERE epoch > @LASTEPOCH
 ORDER BY epoch ASC
 LIMIT 1;
+----------+---------------------+
| Position | @FIRSTFILE:=File    |
+----------+---------------------+
|      601 | ./master_bin.000004 |
+----------+---------------------+

SELECT DISTINCT File FROM mysql.ndb_binlog_index
 WHERE epoch > @LASTEPOCH
       AND File <> @FIRSTFILE
ORDER BY File;
+---------------------+
| File                |
+---------------------+
| ./master_bin.000006 |
| ./master_bin.000008 |
+---------------------+

Now you will have the binary log files, that you can use the mysqlbinlog utility like this:

mysqlbinlog -D --start-position=601 ./master-log.000004 | mysql -uroot

Notice that the -D option tells MySQL not to log what you restore back to the binary logs. To perform a point-in-time recovery you to a specific time, for example 2016-02-16 10:30:

1. Locate the position where the restore should stop. As events in the binary log are logged with the time the transaction starts, you should not use the –stop-datetime option directly, but you can use it to make it easier to locate the position to stop at. Assuming no transactions take more than one minute you can use something like:

mysqlbinlog --start-datetime='2016-02-16 10:29:00' --stop-datetime='2016-02-16 10:31:00' ./master-log.000006 | less -S

This assumes you are using Linux with the less command installed. On other platforms something similar can be used.

2. Once you have determined the position – for example 123456, use the –stop-position option to stop replaying the binary log at that position:

mysqlbinlog -D --stop-position=123456 ./master-log.000006 | mysql -uroot

You should have at least two MySQL Server binary logging. If you only have one, and it stops, you might have gaps and you can’t fully restore data. You need to keep your binary logs, especially those after you’ve made a cluster backup. The mysql.ndb_binlog_index is a MyISAM table. If you are cleaning it, make sure you don’t delete entries of binary logs that you still need. Again, the above data restoration only involves MySQL Cluster, it does not cover other storage engines.