The last section, Section 1.5.1, “Point-in-Time Recovery Using Binary Log”, explains the general idea of using the binary log to perform a point-in-time-recovery. The section explains the operation in details with an example.
As an example, suppose that around 20:06:00 on March 11, 2020, an SQL statement was executed that deleted a table. You can perform a point-in-time recovery to restore the server up to its state right before the table deletion. These are some sample steps to achieve that:
Restore the last full backup created before the point-in-time of interest (call it
tp
, which is 20:06:00 on March 11, 2020 in our example). When finished, note the binary log position up to which you have restored the server for later use, and restart the server.NoteWhile the last binary log position recovered is also displayed by InnoDB after the restore and server restart, that is not a reliable means for obtaining the ending log position of your restore, as there could be DDL events and non-InnoDB changes that have taken place after the time reflected by the displayed position. Your backup and restore tool should provide you with the last binary log position for your recovery: for example, if you are using mysqlbinlog for the task, check the stop position of the binary log replay; if you are using MySQL Enterprise Backup, the last binary log position has been saved in your backup. See Point-in-Time Recovery.
Find the precise binary log event position corresponding to the point in time up to which you want to restore your database. In our example, given that we know the rough time where the table deletion took place (
tp
), we can find the log position by checking the log contents around that time using the mysqlbinlog utility. Use the--start-datetime
and--stop-datetime
options to specify a short time period aroundtp
, and then look for the event in the output. For example:$> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \ --stop-datetime="2020-03-11 20:08:00" --verbose \ /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE" /*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 232 #200311 20:06:20 server id 1 end_log_pos 355 CRC32 0x2fc1e5ea Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1583971580/*!*/; SET @@session.pseudo_thread_id=16/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; DROP TABLE `pets`.`cats` /* generated by server */ /*!*/; # at 355 #200311 20:07:48 server id 1 end_log_pos 434 CRC32 0x123d65df Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1583971668462467 immediate_commit_timestamp=1583971668462467 transaction_length=473 # original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) # immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) /*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/; /*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 434 #200311 20:07:48 server id 1 end_log_pos 828 CRC32 0x57fac9ac Query thread_id=16 exec_time=0 error_code=0 Xid = 217 use `pets`/*!*/; SET TIMESTAMP=1583971668/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE dogs
From the output of mysqlbinlog, the
DROP TABLE `pets`.`cats`
statement can be found in the segment of the binary log between the line# at 232
and# at 355
, which means the statement takes place after the log position 232, and the log is at position 355 after theDROP TABLE
statement.NoteOnly use the
--start-datetime
and--stop-datetime
options to help you find the actual event positions of interest. Using the two options to specify the range of binary log segment to apply is not recommended: there is a higher risk of missing binary log events when using the options. Use--start-position
and--stop-position
instead.Apply the events in binary log file to the server, starting with the log position your found in step 1 (assume it is 155) and ending at the position you have found in step 2 that is before your point-in-time of interest (which is 232):
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \ | mysql -u root -p
The command recovers all the transactions from the starting position until just before the stop position. Because the output of mysqlbinlog includes
SET TIMESTAMP
statements before each SQL statement recorded, the recovered data and related MySQL logs reflect the original times at which the transactions were executed.Your database has now been restored to the point-in-time of interest,
tp
, right before the tablepets.cats
was dropped.Beyond the point-in-time recovery that has been finished, if you also want to reexecute all the statements after your point-in-time of interest, use mysqlbinlog again to apply all the events after
tp
to the server. We noted in step 2 that after the statement we wanted to skip, the log is at position 355; we can use it for the--start-position
option, so that any statements after the position are included:$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \ | mysql -u root -p
Your database has been restored the latest statement recorded in the binary log file, but with the selected event skipped.