Jump to Navigation

MySQL: Roll forward (PITR)

After restoring a backup it is possible to roll forward your databases by applying the binlogs. This means that all committed transactions between two points in time are executed again. A roll forward  can happen on the original server the binlogs were generated or on some other server.

A roll forward is executed by translating the binlogs to plain SQL statements and executing them. This means that if you try to apply binlogs to a database some statements may fail, if the start situation was not entirely correct.

This can be used for PITR (Point In Time Recovery): restoring the database to the state it was in at a specific time.

Applying binlogs

Given a collection of binlogs, you can apply them by executing:

# On the command line
# The binlogs to apply
BINLOGS="mysqld-bin.000001 mysqld-bin.000002"
# Connection to the MySQL database
CONN="-u root -proot"
mysqlbinlog --force-read $BINLOGS | mysql --force $CONN

This will read all transactions from the given binlogs, translate them to MySQL statements and execute them on the database.

Depending on the scenario, it may be useful to add the following options to mysqlbinlog:

  • --disable-log-bin: Do not generate binlogs while applying the binlogs.
    As applying the binlogs consists of executing SQL statements, binlogs are normally written for those statements. Depending on your restore scenario this may or may not be useful.
  • --skip-gtids: Ignore GTIDs (for MySQL 5.6 and later when GTIDs are used)
    Transactions whose GTID have already been executed in the database will normally not be executed again. If your restore did not reset the list of executed GTIDs (for example, you read in a mysqldump dump and did not start a new set of binlogs), you will need this flag.

Inspecting the binlogs

Instead of piping the output of mysqlbinlog directly to MySQL, you can inspect the output (directly, by using something like less, or by redirecting it to a file). In this case, the option --verbose is very useful, especially if you are using row-based replication. The output can even be edited1 if you want to remove or change one or more offending transactions.

Including specific transactions

You can limit the transactions that mysqlbinlog generates MySQL statements for, by specifying time stamps, transaction numbers or GTIDs. Using time stamps can be ambiguous, as several transactions can have the same time stamp, so be careful.

Useful options are:

  • --start-datetime: time stamp of the first transaction to include.
  • --stop-datetime: time stamp of the first transaction not to include.
  • --start-position: position within the first binlog of the first transaction to include.
  • --stop-position: position within the last binlog of the first transaction not to include.
  • --include-gtids: Only include these GTID sets (for MySQL 5.6 and later when GTIDs are used)
  • --exclude-gtids: Exclude these GTID sets (for MySQL 5.6 and later when GTIDs are used)

Time stamps are represented by MySQL literals, an example would be 2015-12-25 11:25:562.

The start en stop positions can be found by inspecting the mysqldump output. Positions are the same as shown by the commands SHOW MASTER STATUS and SLOW SLAVE STATUS

  • 1. But be warned, it can be huge!
  • 2. Note that you may have to use quotes as there is a space in it

Technical_article | by Dr. Radut