Jump to Navigation

MySQL: Restores, binlogs and GTIDs

Part of a backup strategy is deciding how to handle the binlogs. When using GTIDs this is even more important. This page assumes you have enabled the binlogs in the server you backup.

Binlog handling by MySQL

When MySQL starts, it looks for existing binlogs.

Whether it finds existing binlogs or not, it will start a new binlog file. It will get number 000001 if no binlogs were found, or the next highest number if there are already binlogs.

If GTIDs are in use, each binlog will contain the GTIDs which were applied using earlier binlogs. Using this information, the list of executed GTIDs is initialized (@@global.gtid_executed) as well as the list of purged GTIDs (@@global.gtid_purged, executed GTIDs that are no longer found in a binlog file).

The lists of executed and purged GTIDs are empty if no binlogs were found.

Resetting executed GTIDs

It is impossible to assign a value to @@global.gtid_executed. The only way to change is (besides of executing transactions) is with a RESET MASTER. This will not only reset the variable, but also delete any binlog files from disk.

When @@global.gtid_executed is empty it is possible to assign a new value to @@global.gtid_purged. This value will be copied automatically to @@global.gtid_executed.

Restore strategies

There are several ways of handling the binlogs in a backup and after a restore.

Starting new binlogs

One option of handling binlogs is to start new binlogs before a restore. The easiest way to do that is to stop MySQL, change in the /etc/my.sql the binlog name prefix, and to start MySQL again. Another way of doing that is issuing a RESET MASTER statement, but that will also delete all existing binlogs from disk. You may still need them for a roll forward.

If you are not using GTIDs, any slaves of the server will have to reset their synchronization position (using CHANGE MASTER TO) after the restore.

If you are using GTIDs, either the restore should set @@global.gtid_purged or you should do so yourself.

This is a good option if you are using GTIDs.

Continuing with the existing binlogs

Another option is to continue with the existing binlogs, if you still have them. That means that you do not restore the old binlogs.

If you are not using GTIDs, this should just work. The only thing you should be careful of is creating a new slave of this server; it must not try to apply binlogs before the restore.

If you are using GTIDs, you will have transactions registered that are not really executed. This is a bit troublesome, especially in a replication structure. If, after a roll forward (which must be executed with --skip-gtids), all executed GTIDs correspond to executed transactions, all will be well. If not, you may get unexpected problems (slaves requesting transactions that are not available or should not be run, and transactions that are not requested from a master even though they are not run).

This is usually the best option if you are not using GTIDs and you still have all binlogs.

Restoring all or a few of the binlogs

Yet another approach is restoring the binlogs to the situation at the time of the backup. Care should be taken that the binlogs correspond exactly. That may or may not be possible, depending on the backup method. Usually, only the binlog with the highest number is really needed.

If you are not using GTIDs, any slaves of the server will have to reset their synchronization position (using CHANGE MASTER TO) after the restore.

If you are using GTIDs, this should just work.

Note that you will have to keep a separate copy of the binlogs if you want to do a roll forward.

This is a good option if you are using GTIDs and your backup method allows this.

Roll forward options

The best way to handle a roll forward is by applying the binlogs, starting at the moment the backup was made and up to either the latest or the last one you trust (for example, when recovering from an accidental bad statement).

If the current server is a slave to a master that still has all its binlogs starting at the backup time, and if no DML or DDL statements were ever directly executed on the slave, you can use that master for the roll forward. If you use GTIDs, you must take care that you start with the correct list of executed GTIDs; if you do not use GTIDs, you must initialize the position on the master correctly.

Location of binlogs

It is often a good idea to put the binlogs somewhere else than the datadir of MySQL (usually /var/lib/mysql). The binlogs should usually not be a part of your MySQL backup but are backed up separately; perhaps by copying them every minute or so to a remote system.


Technical_article | by Dr. Radut