Skip navigation links

Additional languages
6.3.3 BACKUP DATABASE and RESTORE Status Reporting and Monitoring »
Section Navigation      [Toggle]

6.3.2. How BACKUP DATABASE and RESTORE Work

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 non-transactional 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 DDL blocker.

The commit blocker has these properties:

  • Changes for non-transactional 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 databases or tables. Consequently, the DDL blocker prevents these statements from executing during the operation:

  • ALTER DATABASE

  • ALTER TABLE

  • CREATE DATABASE

  • CREATE INDEX

  • CREATE TABLE

  • DROP DATABASE

  • DROP INDEX

  • DROP TABLE

  • OPTIMIZE TABLE

  • RENAME TABLE

  • REPAIR TABLE

  • TRUNCATE TABLE

Currently, all instances of the the DDL statements in the list are blocked, even for database or table objects that are not included in the backup. Eventually, the goal is to block only DDL 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 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