Skip to content
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
      • Install Percona XtraBackup 8.0
      • Use an APT repo to install Percona XtraBackup
      • Use a YUM repo to install Percona XtraBackup
      • Use DEB or RPM downloaded packages to install Percona XtraBackup
      • Install Percona XtraBackup from a Binary Tarball
      • Compile and install Percona XtraBackup from source code
      • What’s in the packages
      • Uninstall Percona XtraBackup
      • Run Percona XtraBackup in a Docker container
      • Implementation details
      • Connection and privileges needed
      • Configure xtrabackup
      • Server version and backup version comparison
      • xtrabackup exit codes
      • The backup cycle - full backups
      • Incremental backup
      • Compressed backup
      • Partial backups
      • Percona XtraBackup user manual
      • Throttling backups
      • Encrypted InnoDB tablespace backups
      • Encrypt backups
      • LRU dump backup
      • Point-in-time recovery
      • Smart memory estimation
      • Work with binary logs
      • Improved log statements
      • Work with SELinux
      • Work with AppArmor
      • Enable the server to communicate via TCP/IP
      • Install and configure an SSH server
      • Analyze table statistics
      • FLUSH TABLES WITH READ LOCK option
      • lock-ddl-per-table option improvements
        • --lock-ddl-per-table redesign
        • Other improvements
      • Take an incremental backup using page tracking
      • The xbcloud binary
      • Use the xbcloud binary with Swift
      • Use xbcloud Binary with Amazon S3
      • Use the xbcloud binary with MinIO
      • Use the xbcloud with Google Cloud Storage
      • Exponential backoff
      • Use the xbcloud binary with Microsoft Azure Cloud Storage
      • How-tos and recipes
      • Release notes index
      • Percona XtraBackup 8.0.30-23 (2022-11-14)
      • Percona XtraBackup 8.0.29-22 (2022-07-19)
      • Percona XtraBackup 8.0.28-21 (2022-05-25)
      • Percona XtraBackup 8.0.28-20
      • Percona XtraBackup 8.0.27-19
      • Percona XtraBackup 8.0.26-18.0
      • Percona XtraBackup 8.0.25-17.0
      • Percona XtraBackup 8.0.23-16.0
      • Percona XtraBackup 8.0.22-15.0
      • Percona XtraBackup 8.0.14
      • Percona XtraBackup 8.0.13
      • Percona XtraBackup 8.0.12
      • Percona XtraBackup 8.0.11
      • Percona XtraBackup 8.0.10
      • Percona XtraBackup 8.0.9
      • Percona XtraBackup 8.0.8
      • Percona XtraBackup 8.0.7
      • Percona XtraBackup 8.0.6
      • Percona XtraBackup 8.0.5
      • Percona XtraBackup 8.0.4
      • Percona XtraBackup 8.0-3-rc1
      • Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
      • The xtrabackup option reference
      • The xbcrypt binary
      • The xbstream binary
      • 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¶

    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:

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

    6. 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 8.0.22-15.0, the --lock-ddl-per-table has been redesigned to minimize inconsistent backups.

    The following procedure reorders the steps:

    • The MDL lock acquired 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 does not retrieve any data.

    Warning

    The lock-ddl-per-table variable is deprecated in Percona Server for MySQL 8.0. Use --lock-ddl instead of this variable.

    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.


    Last update: 2022-12-06
    Back to top
    Previous FLUSH TABLES WITH READ LOCK option
    Next Take an incremental backup using page tracking
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs