lock-ddl-per-table option improvements¶
Important
The lock-ddl-per-table option is deprecated in Percona Server for MySQL 8.0. Use –lock-ddl instead of this variable.
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
:
-
Parse and copy all redo logs after the checkpoint mark
-
Fork a dedicated thread to continue following new redo log entries
-
List the tablespaces required to copy
-
Iterate through the list. The following steps occur with each listed tablespace:
-
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.
-
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¶
The --lock-ddl-per-table
option 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 aCREATE 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, theSELECT
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.
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.