MySQL binlog Point-in-time Recovery (mysqlbinlog)

Just for my own quick reference

Official Document: https://mariadb.com/kb/en/mariadb/mysqlbinlog-options/

Some Important parameters:

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

To specify datetime range:
--start-datetime
--stop-datetime

To specify binlog position:

--start-position
--stop-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.