MySQL : Restoring Data Since Last Backup

Even if you're running backups daily with a utility like mysqldump, there's still the problem of restoring data created or changed after the last backup. However, by using a binary log, you can may be able to recover interim data.

Setting Up Binary Logging
Binary logging will record all transactions executed and attempted
on the server. The mysqlbinlog utility can be used to extract the
SQL statements from the log to be executed again. To enable binary logging, add the following line to your server's options file (i.e., /etc/my.cnf or c:\my.ini, depending on your system) in the [mysqld] group and then restart the server daemon:
log-bin = /var/log/mysql/bin
The path where you backup data and keep log files should be on
a separate hard drive for added safety. Also, the directory should
be a secure one. You can limit binary logging to specific databases or you can omit certain databases (e.g., mysql, which contains user
passwords) from the log (see the documentation on binary logs).
To make interim restoration simpler, have mysqldump flush the logs when you run a backup.

Simple Interim Recovery
With daily backups and the binary logs running, it's simple to recover data. For example, suppose that your data is backed up each night using mysqldump. Suppose further that on January 10, 2006 at 10:00 a.m.
your data is lost and you want to recover it. To begin with, you may want to stop the MySQL server and then restart it in such a way that you will be the only user to have access to it. This can be done like so:
mysqld --port=3307 --socket=/tmp/mysql_restore.sock
You can use any non-privileged port that is unused by other services.
The --socket option will temporarily name a different socket file for Unix systems. On Windows systems, you would provide a named pipe (e.g., MySQL_restore) instead of a path and file name:
mysqld-nt --port=3307 --enable-named-pipe --socket=MySQL_restore
You can now proceed with restoring the dump file from the previous night's backup like so:
mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock < /var/backup/20060109.sql
The actual names of the paths and files will be different for your server.
Next you need to determine the name of the latest binary log file.
To do this, enter the following SQL statement:
SHOW MASTER LOGS;
You're now ready to restore the transactions since the dump file was created. If you are flushing the logs each day when making your nightly backups, you would restore the entire binary log file from the command-line like so:
mysqlbinlog /var/log/mysql/bin.123456 mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
In this example, the results of mysqlbinlog are piped to the mysql client for processing. When it's finished, restart the MySQL server without the temporary port and socket file options.
These couple of steps will restore the data as it stood at the time of the previous backup and then all SQL statements that were entered since the logs were flushed will be re-entered.

Position Recovery
The scenario in the previous section is based on the assumption that you want to restore all of the server's data. However, if the reason you need to perform the recovery is because some data was inadvertently deleted by an SQL statement, a simple full recovery using the binary logs will only repeat the mistake. For such a situation, a better solution would be to restore the data until a specific position and then again starting after the undesirable transactions.
The mysqlbinlog has options to recover data from and to a specific position in the log: --start-position and --stop-position.
To determine the position numbers, run mysqlbinlog for a range of times around when the unwanted transaction was executed using a start and stop time, and redirect the results to a text file for you to examine:
mysqlbinlog --start-date="2006-01-10 9:55:00" --stop-date="2006-01-10 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
Positions are labeled with log_pos followed by a number. Once you find in the text file the log position where you want to stop the recovery and the position in which you'd like to resume a recovery, make note of them. After restoring the backup file, with the position numbers noted, you would then enter something like the following from the command-line:
mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
The first line above will recover all of the transactions up until the stop position given. The next line will recover all transactions from the starting position given until the end of the binary log.

Conclusion
With binary logging, complete recovery up to any particular point can be achieved fairly easily and quickly with a standard installation of MySQL. The key is to perform backups and to flush the logs on a regular basis. With these safeguards in place, it's just a matter of being familiar with the procedures for recovering your data so that you may act quickly and accurately when called upon. To be better prepard for a crisis, you may want to practice restoring data as described here, but on a test server.