6.1 Point-in-Time Recovery from a Hot Backup

InnoDB only stores the binlog position information to its tablespace at a transaction commit. To make InnoDB aware of the current binlog position, you must run at least one transaction while binlogging is enabled. When you run ibbackup --apply-log on your backup, ibbackup versions >= 1.03 print the latest MySQL binlog position the backup knows of. Also, mysqld prints it when you start it on the backup after the --apply-log:

$ mysqld --defaults-file=/home/pekka/.backup-my.cnf
040122 15:41:57  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
…
InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005
…
mysqld: ready for connections.

The MySQL version must be >=  5.1.

The printed position is the MySQL binlog byte position from the moment when MySQL Enterprise Backup finished the copying of your data files. Then you can apply the binlog file(s) starting from that position to the restored database:

$ mysqlbinlog --start-position=27183537 /sqldata/binlog.000005 | mysql

To recover the database to a specific point in time, direct the output of mysqlbinlog to an output file, instead of piping it directly to mysql. This output file contains timestamps for all SQL statements in the binlog. In an editor, remove all statements after the specified point in time. Process the modified file with mysql, like this:

$ mysql < modified_output_file