Skip navigation links
5.3.3 MySQL Backup Status Reporting and Monitoring »
Section Navigation      [Toggle]

5.3.2. How MySQL Backup Works

A backup operation creates a backup of one or more databases at a given point in time and saves it as a backup image, a file that contains the backup data (table contents) and metadata (definitions for databases, tables, and other objects, and server information).

The backup is intended to provide a consistent snapshot of the backed-up data as of the point at which the operation began, and it is intended to provide online operation as much as possible that allows other server activity to proceed without blocking.

A backup operation begins at time t1 and ends at time t2, producing a backup image that contains the backup state (database state) at time t, where t1 < t < t2. The time t is called the validity point of the backup image. It represents the time when all storage engines are synchronized for the backup. Restoring this image restores the state to be the same as it was at time t.

Consistency of the backup means that these constraints must be true:

  • Data from transactional tables is included only for committed transactions.

  • Data from nontransactional tables is included only for completed statements.

  • Referential integrity is maintained between all backed-up tables within a given backup image.

The referential-integrity constraint does not necessarily hold if two tables are related but only one of them is included in a backup. Restoring the backup then would restore only the backed-up table, which can produce tables for which referential integrity no longer holds.

For a backup to proceed properly, certain types of server activity must be blocked, so the backup system incorporates a commit blocker and a Backup Metadata Lock.

The commit blocker has these properties:

  • Changes for nontransactional tables must be blocked.

  • Changes for transactional tables are not blocked, but only changes that have been committed when the backup occurs appear in the backup. Changes that occur during the backup operation are not included in the backup image.

When a backup or restore operation is in progress, it is not allowable to modify the structure of database objects. Consequently, during the operation, the Backup Metadata Lock blocks statements that change database metadata from executing. A backup image stores metadata for the following types of objects:

  • Databases

  • Tablespaces

  • Privileges

  • Tables

  • Views

  • Stored programs (functions, procedures, events, triggers)

This requires that the following metadata changes be frozen during backup operation:

  • Databases being backed up should not disappear or be changed.

  • BACKUP DATABASE * ..., new databases should not appear.

  • The list of objects inside each database should not change.

  • Metadata for objects in the databases should not change.

  • The set of privileges for each database should not change.

  • Users for which privileges are stored should not disappear or change.

  • Tablespaces used by tables being backed up should not disappear or change.

To achieve these requirements, the Backup Metadata Lock blocks the following statements: 

DROP   DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TRIGGER/INDEX/
       USER/TABLESPACE
CREATE DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TRIGGER/INDEX
ALTER  DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TABLESPACE
RENAME TABLE/USER
GRANT/REVOKE
TRUNCATE/OPTIMIZE/REPAIR TABLE

Currently, all instances of statements that change metadata are blocked, even for database or table objects that are not included in the backup. Eventually, the goal is to block only metadata-changing statements for objects in the backup.

Blocking works in both directions. A backup or restore blocks DDL statements, but if a backup or restore operation is initiated while DDL statements are in progress, the operation waits until the statements have finished.

Implementation of BACKUP DATABASE and RESTORE uses an architecture with the following design:

  • The MySQL server communicates with the backup kernel.

  • The backup kernel is responsible for communicating with backup engines and for handling metadata (definitions for databases, tables, and other objects, as well as server information).

  • Each backup engine provides backup and restore drivers for the backup kernel to use.

  • An engine's backup and restore drivers perform actual transfer of data (table contents).

The backup system chooses from among the backup engines available to it:

  • There is a default backup engine to be used if a better one is not found. This engine provides default backup and restore drivers that use a blocking algorithm. For example, the backup driver locks all tables at the start of the backup and unlocks them after the last one is processed (which may occur before the operation is complete).

  • A consistent-snapshot engine implements the same kind of backup as that made by mysqldump --single-transaction.

    The backup driver for the snapshot engine works with only those storage engines that support consistent read via the handler interface, which currently includes only InnoDB and Falcon. The backup driver creates a logical backup because it reads rows one at a time and returns them to the backup kernel to be stored in the backup image.

A backup image must have contents that are consistent with the binary log coordinates taken from the time of the backup. Otherwise, point-in-time recovery using the backup image plus the binary log contents will not work correctly. BACKUP DATABASE synchronizes with binary logging to make sure that the backup image and binary log are consistent with each other. This way, if data loss occurs later, use of the backup image combined with the binary log makes point-in-time recovery possible:

  1. Restore the backup image

  2. Re-execute binary log contents beginning from the coordinates of the backup's validity point up to the desired point of recovery