Skip to content
Starting November 2023 Percona XtraBackup 2.4 has reached EOL status. If you have 5.7 databases, we encourage you to upgrade to 8.0 and then install Percona XtraBackup 8.0. Learn more

logo
Percona XtraBackup
lock-ddl-per-table Option Improvements
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup Works
      • Understand version numbers
      • Installing Percona XtraBackup 2.4
      • Installing Percona XtraBackup on Debian and Ubuntu
      • Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
      • Installing Percona XtraBackup from a Binary Tarball
      • Compiling and Installing from Source Code
      • Running Percona XtraBackup in a Docker container
      • Connection and Privileges Needed
      • Configuring xtrabackup
      • The Backup Cycle - Full Backups
      • Incremental Backup
      • Compressed Backup
      • Encrypted Backup
      • Percona XtraBackup User Manual
      • Throttling Backups
      • Lockless binary log information
      • Encrypted InnoDB Tablespace Backups
      • lock-ddl-per-table Option Improvements
        • --lock-ddl-per-table redesign
        • Other Improvements
      • How-tos and Recipes
        • Release notes index
        • Percona XtraBackup 2.4.29 (2023-12-18)
        • Percona XtraBackup 2.4.28 (2023-04-04)
        • Percona XtraBackup 2.4.27 (2022-12-06)
        • Percona XtraBackup 2.4.26
        • Percona XtraBackup 2.4.25
        • Percona XtraBackup 2.4.24
        • Percona XtraBackup 2.4.23
        • Percona XtraBackup 2.4.22
        • Percona XtraBackup 2.4.21
        • Percona XtraBackup 2.4.20
        • Percona XtraBackup 2.4.19
        • Percona XtraBackup 2.4.18
        • Percona XtraBackup 2.4.17
        • Percona XtraBackup 2.4.16
        • Percona XtraBackup 2.4.15
        • Percona XtraBackup 2.4.14
        • Percona XtraBackup 2.4.13
        • Percona XtraBackup 2.4.12
        • Percona XtraBackup 2.4.11
        • Percona XtraBackup 2.4.10
        • Percona XtraBackup 2.4.9
        • Percona XtraBackup 2.4.8
        • Percona XtraBackup 2.4.7-2
        • Percona XtraBackup 2.4.7
        • Percona XtraBackup 2.4.6
        • Percona XtraBackup 2.4.5
        • Percona XtraBackup 2.4.4
        • Percona XtraBackup 2.4.3
        • Percona XtraBackup 2.4.2
        • Percona XtraBackup 2.4.1
      • The xtrabackup Option Reference
      • The innobackupex Option Reference
      • The xbcloud Binary
      • Exponential Backoff
      • Using the xbcloud binary with Microsoft Azure Cloud Storage
      • The xbcrypt binary
      • The xbstream binary
      • Known issues and limitations
      • Frequently Asked Questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version Checking

    • --lock-ddl-per-table redesign
    • Other Improvements

    lock-ddl-per-table Option Improvements¶

    MySQL 5.7 has made improvements to bulk loading, one of these improvements is the ability to disable redo logging, which increased the speed of ADD INDEX operations.

    To block DDL statements on an instance, Percona Server implemented LOCK TABLES FOR BACKUP. Percona XtraBackup uses this lock for the duration of the backup. This lock does not affect DML statements.

    Percona XtraBackup has also implemented --lock-ddl-per-table, which blocks DDL statements by using metadata locks (MDL).

    The following procedures describe a simplified backup operation when using --lock-ddl-per-table:

    1. Parse and copy all redo logs after the checkpoint mark

    2. Fork a dedicated thread to continue following new redo log entries

    3. List the tablespaces required to copy

    4. Iterate through the list. The following steps occur with each listed tablespace:

      • Query INFORMATION_SCHEMA.INNODB_SYS_TABLES to find which tables belong to the tablespace ID and take a MDL on the underlying table or tables in case there is a shared tablespace.

      • Copy the tablespace .ibd files.

    The backup process may encounter a redo log event, generated by the bulk load operations, which notifies backup tools that data file changes have been omitted from the redo log. This event is an MLOG_INDEX_LOAD. If this event is found by the redo follow thread, the backup continues and assumes the backup is safe because the MDL protects tablespaces already copied and the MLOG_INDEX_LOAD event is for a tablespace that is not copied.

    These assumptions may not be correct and may lead to inconsistent backups.

    --lock-ddl-per-table redesign¶

    Implemented in Percona XtraBackup version 2.4.21, the --lock-ddl-per-table has been redesigned to minimize inconsistent backups. The following procedure reorders the steps:

    • Acquire the MDL lock at the beginning of the backup

    • Scan the redo logs. An MLOG_INDEX_LOAD event may be recorded if a CREATE INDEX statement has occurred before the backup starts. At this time, the backup process is safe and can parse and accept the event.

    • After the first scan has completed, the follow redo log thread is initiated This thread stops the backup process if an MLOG_INDEX_LOAD event is found.

    • Gather the tablespace list to copy

    • Copy the .ibd files.

    Other Improvements¶

    The following improvements have been added:

    • If the .ibd file belongs to a temporary table, the SELECT query is skipped.

    • For a FullText Index, an MDL is acquired on the base table.

    • A SELECT query that acquires an MDL retrieves no data.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.

    2022-07-19
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs