PITR Using Binary Logs

Point-In-Time Recovery refers to recovery of data changes made up to a given point in time. Typically, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made.

To restore data from the binary log, aside from adding the logs/ folder from the plugin file tree, you also must know the name and location of the current binary log files when the backup was made. This information is available in the “CHANGE MASTER” line on the top of the data.sql file.

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='sql-bin.000004', MASTER_LOG_POS=2083;

This information is also printed in the Bacula job report when restoring a dump directly into a new database using where=newdb parameter.

...
Found MASTER_LOG position sql-bin.000004:2083 for "database5276"
...

Once you have this information and all log files generated between the Full backup and the point in time when you want to restore, you need to use the mysqlbinlog program.

# mysqlbinlog -j 2083 sql-bin.000004 sql-bin.000005...

This command will generate an SQL script that you can load into your restored database to run the recover process. You may want to stop the recover process in a middle of a log file, for that, mysqlbinlog provides several options such as --stop-datetime to control this behavior. Refer to the mysqlbinlog documentation for all parameters:

http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html.

As the output of mysqlbinlog program is an SQL script, you can also edit the script to fit your needs. For example, if the database has a new name, you will need to edit the SQL script to change database references.

# mysqlbinlog -j 2083 mysql-bin.000004 ... | \
   sed 's/use `orgname`/use `newname`/'    | \
   mysql -u root newname

For more information on PITR with MySQL, refer to the MySQL documentation:

https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html