BACKUP LOCK blocks a table from DDL statements. This is mainly intended to be used by tools like mariadb-backup that need to ensure there are no DDL statements on a table while the table files are opened. For example, for an Aria table that stores data in 3 files with extensions .frm, .MAI and .MAD. Normal read/write operations can continue as normal.

Syntax

To lock a table:

To unlock a table:

Usage in a Backup Tool

This ensures that all files are from the same generation, that is created at the same time by the MariaDB server. This works, because the open files will point to the original table files which will not be affected if there is any ALTER TABLE while copying the files.

Privileges

Notes

• The idea is that the BACKUP LOCK should be held for as short a time as possible by the backup tool. The time to take an uncontested lock is very short! One can easily do 50,000 locks/unlocks per second on low end hardware.

• One should use different connections for commands and BACKUP LOCK.

Implementation

• Internally, BACKUP LOCK is implemented by taking an MDLSHARED_HIGH_PRIO MDL lock on the table object, which protects the table from any DDL operations.

See Also

• - BACKUP LOCK: DDL locking of tables during backup

_{This page is licensed: CC BY-SA / Gnu FDL}

The BACKUP STAGE statements are a set of statements to make it possible to make an efficient external backup tool. These commands could even be used by tools that perform backups by taking a snapshot of a file system, SAN, or some other kind of storage device.

Generic Backup Process with Storage Snapshots

A tool that backs up MariaDB by taking a snapshot of a file system, SAN, or some other kind of storage device could use each BACKUP STAGE command in the following way:

• First, execute the following:

• Then, take the snapshot.

• Then, execute the following:

The above ensures that all non-transactional tables are properly flushed to disk before the snapshot is done. Using BACKUP STAGE commands is also more efficient than using the command as the above set of commands will not block or be blocked by write operations to transactional tables.

Note that when the backup is completed, one should delete all files with the "#sql" prefix, as these are files used by concurrent running ALTER TABLE. Note that InnoDB will on server restart automatically delete any tables with the "#sql" prefix.

_{This page is licensed: CC BY-SA / Gnu FDL}

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool.

Syntax

In the following text, a transactional table means InnoDB, or specifically an InnoDB-like engine with redo log that can lock redo purges and can be copied without locks by an outside process.

Goals with BACKUP STAGE Commands

• To be able to do a majority of the backup with the minimum possible server locks. Especially for transactional tables (InnoDB, MyRocks etc) there is only need for a very short block of new commits while copying statistics and log tables.

• DDL are only needed to be blocked for a very short duration of the backup while is copying the tables affected by DDL during the initial part of the backup.

• Most non transactional tables (those that are not in use) will be copied during BACKUP STAGE START. The exceptions are system statistic and log tables that are not blocked during the backup until BLOCK_COMMIT.

BACKUP STAGE Commands

BACKUP STAGE START

The START stage is designed for the following tasks:

• Blocks purge of redo files for storage engines that needs this (Aria)

• Start logging of DDL commands into 'datadir'/ddl.log. This may take a short time as the command has to wait until there are no active DDL commands.

BACKUP STAGE FLUSH

The FLUSH stage is designed for the following tasks:

• FLUSH all changes for inactive non-transactional tables, except for statistics and log tables.

• Close all tables that are not in use, to ensure they are marked as closed for the backup.

• BLOCK all new write locks for all non transactional tables (except statistics and log tables). The command will not wait for tables that are in use by read-only transactions.

DDLs don't have to be blocked at this stage as they can't cause the table to be in an inconsistent state. This is true also for non-transactional tables.

BACKUP STAGE BLOCK_DDL

The BLOCK_DDL stage is designed for the following tasks:

• Wait for all statements using write locked non-transactional tables to end.

• Blocks , , , and .

• Blocks also start off a new and the final rename phase of . Running ALTER TABLES are not blocked.

BACKUP STAGE BLOCK_COMMIT

The BLOCK_COMMIT stage is designed for the following tasks:

• Lock the binary log and commit/rollback to ensure that no changes are committed to any tables. If there are active commits or data to be copied to the binary log this will be allowed to finish. Active transactions will not affect BLOCK_COMMIT.

• This doesn't lock temporary tables that are not used by replication. However these will be blocked when it's time to write to the binary log.

• Lock system log tables and statistics tables, flush them and mark them closed.

When the BLOCK_COMMIT's stages return, this is the 'backup time'. Everything committed will be in the backup and everything not committed will roll back.

Transactional engines will continue to do changes to the redo log during the BLOCK COMMIT stage, but this is not important as all of these will roll back later as the changes will not be committed.

BACKUP STAGE END

The END stage is designed for the following tasks:

• End DDL logging

• Free resources

Using BACKUP STAGE Commands with Backup Tools

Using BACKUP STAGE Commands with mariadb-backup

See for some examples on how uses these commands.

Using BACKUP STAGE Commands with Storage Snapshots

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. These commands could even be used by tools that perform backups by taking a snapshot of a file system, SAN, or some other kind of storage device. See for some examples on how to use each BACKUP STAGE command in an efficient way.

Privileges

BACKUP STAGE requires the privilege.

Notes

• Only one connection can run BACKUP STAGE START. If a second connection tries, it will wait until the first one has executed BACKUP STAGE END.

• If the user skips a BACKUP STAGE, then all intermediate backup stages will automatically be run. This will allow us to add new stages within the BACKUP STAGE hierarchy in the future with even more precise locks without causing problems for tools using an earlier version of the BACKUP STAGE implementation.

See Also

• Locking a table from DDL.

• . Implement BACKUP STAGE for safe external backups.

_{This page is licensed: CC BY-SA / Gnu FDL}