Skip to content
Percona Software for MySQL Documentation

Reduced backup lock time

Important

The --lock-ddl=REDUCED option is a tech preview. Before using this option in production, we recommend that you test restoring production from physical backups in your environment, and also use the alternative backup method for redundancy.

Percona Server for MySQL implemented LOCK TABLES FOR BACKUP to block Data Definition Language (DDL) statements on a server. Percona XtraBackup utilizes this lock during the backup process to ensure that any DDL event does not corrupt the backup. The lock does not affect the Data Manipulation Language (DML) statements.

How the backup lock works

With the --lock-ddl=ON option the backup lock is enabled by default from the start of the backup process.

  • Set a backup lock: Set a backup lock on the server to prevent any DDL operations, ensuring no new tables are created, renamed, or dropped during the backup.

  • Copy the redo logs: Transfer all redo logs from the checkpoint up to the current Log Sequence Number (LSN) and start tracking new entries.

  • Identify and copy all InnoDB tablespace, .ibd, files. This step takes most of the time and is performed under the lock.

  • Copy non-InnoDB files.

  • Gather sync point: Collect synchronization points from all engines (InnoDB LSN, binlog, Global Transaction Identifier (GTID), etc.) by querying the log status.

  • Stop the redo follow thread: Cease the redo fllow thread once it has copied up to the sync point.

  • Release the backup lock on the server.

If the backup lock is disabled with the --lock-ddl=OFF option, a backup continues while concurrent DDL events are executed. Such backups are invalid and fail at --prepare step.

The --lock-ddl=REDUCED option overview

Percona XtraBackup 8.4.0-2 adds the --lock-ddl=REDUCED option to reduce the time the instance remains under the backup lock. The backup lock is now taken after copying the .ibd files and before copying the non-InnoDB files.

Copying the InnoDB files with the --lock-ddl=REDUCED option is split into two stages.

Operations performed without the lock

  1. Copying all redo logs from checkpoint up to the current LSN and start following new entries.
  2. Start the redo log thread.
  3. Tracking file operations from the redo log.
  4. Copying of all .ibd without taking any lock.
  5. Acquiring Low-traffic Flush Barrier (LTFB)/Low-traffic Insert Barrier (LIFB). This step ensures no new DDL operations, such as creating or altering tables, will occur.
  6. Quering the log_status to discover the LSN. This step ensures that all subsequent operations are tracked and that the backup process captures a consistent state of the database.
  7. Copying all InnoDB files.
  8. Ensuring the redo log has catch up to LSN from step 5.

Operations performed under the lock

  1. Checking the file operations that were tracked and recopying the tablespaces.
  2. Creating additional meta files to perform the required actions (deletions or renames) on the already copied files. This approach ensures that the backup remains consistent and accurate without disrupting the streaming process. This step is required for taking streaming backups.
  3. Gathering a sync point from all engines (InnoDB LSN, binlog, GTID, etc.) by querying the log_status.
  4. Stoping the redo follow thread once it copies at least up to sync point at step 11.
  5. Releasing LTFB/LIFB.
  6. Processing the new files during --prepare phase before crash recovery starts.

The --lock-ddl=REDUCED option key features are as follows:

  • Reduced locking: Instead of holding a full LOCK TABLES FOR BACKUP for the entire duration of the backup, it only locks the tables for a shorter period after copying the InnoDB files.

  • DDL statements: It allows some DDL statements to proceed, reducing the downtime and impact on the database operations.

  • Consistency: Ensures that the backup remains consistent by carefully timing the locks to avoid conflicts with ongoing DDL operations.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert

Last update: 2024-10-30