Extracting based on time and position

You can extract the partial data from the binary log by specifying the position. Suppose you want to do point-in-time recovery. Assume a DROP DATABASE command was executed at 2017-08-19 12:18:00 and the latest available backup was 2017-08-19 12:00:00, which you already restored. Now, you need to restore data from 12:00:01 till 2017-08-19 12:17:00. Remember, if you extract the full log, it will also contain the DROP DATABASE command and it will wipe your data again.

You can extract the data by specifying the time window through --start-datetime and --stop-datatime options.

shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-datetime="2017-08-19 00:00:01" --stop-datetime="2017-08-19 12:17:00" ...

Get MySQL 8 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.