MySQL binlog Point-in-time Recovery (mysqlbinlog)

Just for my own quick reference

Official Document:

Some Important parameters:

To generate human readable and grep-able SQL for binlog_format=row:
--base64-output=DECODE-ROWS --verbose

To specify datetime range:

To specify binlog position:


Create SQL within specified time-frame:

mysqlbinlog --start-datetime="2015-12-08 00:00:00" --stop-datetime="2015-12-08 07:00:00"  --base64-output=DECODE-ROWS --verbose /var/log/mysql/mysql-bin.000011  > /mnt/00-07-2.sql

Create SQL within specified binlog position:

mysqlbinlog --start-position=78258315 --stop-position=83446486 --base64-output=DECODE-ROWS --verbose /var/log/mysql/mysql-bin.000011 > /mnt/restore.sql

Restore generated SQL:

mysql -uroot -p < /mnt/restore.sql


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.