BACKUP STAGE

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

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

In the text we refer to mariabackup as the backup tool to use. However the description should work for any tools that supports BACKUP STAGE's.

Syntax

BACKUP STAGE [START | FLUSH | BLOCK_DDL | BLOCK_COMMIT | END ]

Goals with BACKUP STAGE commands

• To be able to do a majority of the backup with the minium possible server locks. Especially for transactional tables (InnoDB, MyRocks etc) there is only need of a very short block of new commits while copying statistics and log tables.
• DDL are only needed to blocked for a very short duration of the backup while mariabackup 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 exception are system statistic and log tables that are not blocked during the backup until BLOCK_COMMIT.
• Should work efficiently with backup tools that uses disk snapshots.
• Should work as efficiently as possible for all table types that stores data on the local disks.
• As little coping as possible under higher level stages/locks. For example, .frm (dictionary) and .trn (trigger) files should be coping while copying the table data.

BACKUP STAGE commands

BACKUP STAGE START

Things done by STAGE START

• 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 all now active DDL commands.

mariabackup can do under START

• Tool can now copy all transactional tables, aria_log_control, aria_log.# and other engines redo logs.
• Tool can call BACKUP STAGE FLUSH while copying the last set of files.

To copy InnoDB tables mariabackup has to start watch the InnoDB backup redo log and copy all changes to the backup to be able to run the redo's later on the final backup.

BACKUP STAGE FLUSH

Things done by STAGE FLUSH

• FLUSH all changes for not active 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.

DDL's doesn't have to be blocked at this stage as they can't cause the table to be in a non consistent state. This is true also for non-transactional tables.

mariabackup can do under STAGE_FLUSH

• Copy all non transactional tables that are not in use. This list of used tables can be found SHOW OPEN TABLES
• Copy all new changes to the aria_log.# tables

At this point data for all old tables should have been copied (except for some system tables)

BACKUP STAGE BLOCK_DDL

Things done by BLOCK_DDL

• Wait for all statements using write locked non-transactional tables to end.
• Blocks CREATE TABLE, DROP TABLE and RENAME TABLE.
• Blocks also start of a new ALTER TABLE and the final rename phase of ALTER TABLE. Running ALTER TABLES are not blocked.

mariabackup can do under BLOCK_DDL

• Copy the non-transactional tables that where in use during STAGE FLUSH
• Copy new tables created before BLOCK DDL. The file names can be read from ddl.log. The log also allow the backup to execute renames of files for which RENAME TABL was done instead of copying them.
• Add markers to backup stream of tables that where dropped during the earlier BACKUP STAGE's.
• Copy changes to system log tables (this is easy as these are append only)
• Copy changes to aria_log.# tables (this is easy as these are append only)

BACKUP STAGE BLOCK_COMMIT

Things done by BLOCK_COMMIT

• Lock the binary log and commit/rollback to ensure that no changes are committed to any tables. If there are active commit's or data to be copied to the binary log this will be allowed to finish.
• This doesn't lock temporary tables that are not used by replication. However these will be blocked when it's time to write to binary log.
• Lock system log tables and statistics tables, flush them and mark them closed.

When stage BLOCK_COMMIT's returns, 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 stage BLOCK COMMIT, but this is not important as all of these will roll back later as the changes will not be committed.

mariabackup can do under BLOCK_COMMIT

• Can now copy the last changes to the redo files for InnoDB and Aria (aria_log.#), and the part of the binary log that was not copied before.
• MyRocks files can also hard linked to backup directory
• End of system log tables (slow_log and general_log) and all statistics tables (table_stats, column_stats and index_stats) should also be copied.

BACKUP STAGE END

Things done by END

• End DDL logging
• Free resources

mariabackup can do after END

• Copy MyRocks tables

Using BACKUP STAGE with disk snapshots

A tool that is using disk snapshots for copying MariaDB files should do

BACKUP STAGE START
BACKUP STAGE BLOCK_COMMIT

disk snapshot

BACKUP STAGE END

The above ensures that all non transactional tables are properly flushed to disk before the snapshot is done. Using BACKUP STAGE's is also more efficient than using FLUSH TABLES WITH READ LOCK 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.

Privileges

BACKUP STAGE requires the RELOAD privilege.

Other things

• 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, all intermediate backup stages will automatically be run. This will allow us to add new BACKUP STAGE's in the future with even more precise locks without causing problems for tools using an earlier version of BACKUP STAGE's
• While opening files for a table, mariabackup should use BACKUP LOCK to ensure that all files for a table is from the same generation (was created at the same time).
• One can use the max_statement_time or lock_wait_timeout variables to ensure that a BACKUP STAGE command doesn't block the server too long.
• DDL logging is for the moment only in MariaDB enterprise server 10.2 and 10.3. The DDL logging code will be applied to 10.4 at some future time.

See also

• BACKUP LOCK Locking a table from DDL's.
• MDEV-5336. Implement BACKUP STAGE for safe external backups.