http://www.mysqlab.net/knowledge/kb/detail/topic/backup/id/8309

  • MySQL知识库 :: backup
  • MySQL Cluster point-in-time recovery

  • Discussion

    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.

    Solution

    Two steps are involved: restoring the cluster backup itself; determining which binary log to start replaying and optionally to stop. Make sure that these steps are performed in single-user mode so as to reduce the chance that data is changed while restoring data.

    The first step of restoring Cluster from a backup is covered in detail in the MySQL manual (Restore a Cluster Backup). It's important to use the -e 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 into a two data node cluster, first restore the meta data like this:

    ndb_restore -e -b 1 -n 3 -c mgmhost:1186 -m

    You only need to do this once. Next restore the data for both nodes like this:

    ndb_restore -e -b 1 -n 3 -c mgmhost:1186 -r
ndb_restore -e -b 1 -n 4 -c mgmhost:1186 -r

    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 @LASTEPOCH:=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 show point-in-time recovery you would restore the next binary log up until 10:30:

    mysqlbinlog -D --stop-datetime="2008-03-06 10:30:00" \
	./master-log.000006 | mysql -uroot

    There is still a bug (bug#33365) involving columns defined as auto incremental, but there is a workaround. Execute the following SQL statement after you restore the cluster backup:

    ndb_mgm> ALL DUMP 7099;

    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.