O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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" ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required