Skip navigation links
MySQL Backup Reference Manual :: 5 MySQL Backup and Replication :: 5.1 How MySQL Backup and Replication Interact
5.2 RESTORE and Replication of Non-Replicated Databases »
Section Navigation      [Toggle]

5.1. How MySQL Backup and Replication Interact

When you use BACKUP DATABASE or RESTORE on a server that is participating in replication, the backup or restore operation has the potential to affect replication activity in various ways. It may also affect client updates being performed on a master server. Some effects are relatively minor, such as temporary blocking of updates. Other effects are signficant, such as loss of master/slave synchrony. This section summarizes these effects.

MySQL Backup binary logging:

BACKUP DATABASE and RESTORE statements are not written to the binary log and therefore are not replicated to slaves. Nor is any backed-up or restored data logged or replicated. However, for RESTORE, an incident event is logged unless the SKIP_GAP_EVENT option is specified. This event signals to slaves that their SQL thread should stop executing events from the relay log because a change has occurred on the master that results in loss of master/slave synchrony.

For BACKUP DATABASE, the binary log coordinates comprising the backup validity point are written to the backup image and recorded in the backup_history log. To determine the validity point for a backup later, see Section 7.3, “Determining the Validity Point Associated with a Backup Image”.

If binary logging is not enabled, there is no validity point for BACKUP DATABASE, so it is not written to the backup image or backup_history. Also, for RESTORE, no incident event is written.

Interaction of BACKUP DATABASE with client and replication activity on a master:

  • Before the backup can begin, it must wait for any current DDL operations to terminate.

  • Clients can continue to read data, but are blocked from making changes to data locked by the backup operation until the lock is released.

  • Backup on a master does not affect replication to slaves, except in the sense that data changes blocked by the backup operation cannot be written to the binary log and sent to slaves until the block ends.

  • The backup does not change data, so master/slave synchrony is not lost.

Interaction of BACKUP DATABASE with client and replication activity on a slave:

  • Before the backup can begin, it must wait for any DDL operation currently being processed by the SQL thread to finish.

  • Clients can continue to read data. Because the server is a slave, it is assumed that no clients will be changing data. Therefore, client activity on the slave is unaffected.

  • The I/O thread can continue to read from the master. The SQL thread is blocked from making changes to data locked by the backup operation, but resumes when the lock is released. It is not necessary to stop and restart replication explicitly.

  • The backup does not change data, so master/slave synchrony is not lost.

Interaction of RESTORE with client and replication activity on a master:

  • Clients are blocked from reading or making changes to data locked by the restore operation until the lock is released.

  • The RESTORE operation writes an incident event to the binary log unless the SKIP_GAP_EVENT option is specified. This causes any slave that receives this event to stop executing any further events.

  • The change in master state is not replicated, so master/slave synchrony is lost. To restore synchrony, you must perform a restore on the slave using the same backup image so that it has the same state as the master and reset the coordinates at which the slave begins reading the master binary log to the point at which the restore was done on the master.

Interaction of RESTORE with client and replication activity on a slave:

  • Clients are blocked from reading data locked by the restore operation until the lock is released. Because the server is a slave, it is assumed that no clients will be changing data.

  • Restore on a slave can either lose or restore synchrony with the master:

    • Synchrony is lost if the restore does not correspond to a restore done on the master.

    • Synchrony is restored if the slave is restored from the same backup image used to restore the master. (The slave must also be reset to begin reading the master binary log from the point at which the restore was done on the master.)