BACKUP STAGE commands are a set of commands to make it
possible to make an efficient external backup tool. 
 
The BACKUP STAGE command was introduced in MariaDB 10.4.1.
 
Syntax
------ 
BACKUP STAGE [START | FLUSH | BLOCK_DDL | BLOCK_COMMIT | END
]
 
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
support BACKUP STAGEs.
 
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 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 exceptions are
system statistic and log tables that are not blocked during
the backup until BLOCK_COMMIT.
Should work efficiently with backup tools that use disk
snapshots.
Should work as efficiently as possible for all table types
that store data on the local disks.
As little copying as possible under higher level
stages/locks. For example, .frm (dictionary) and .trn
(trigger) files should be copying 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 Under START
 
mariabackup can, under START:
Copy all transactional tables, aria_log_control, aria_log.#
and
other engines redo logs.
Call BACKUP STAGE FLUSH while copying the last set of files.
 
To copy InnoDB tables, mariabackup has to start to watch the
InnoDB backup redo log and copy all changes to the backup to
be able to run the redos later on in the final backup.
 
BACKUP STAGE FLUSH
 
Things Done by STAGE FLUSH
 
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.
 
mariabackup under STAGE_FLUSH
 
mariabackup can, under STAGE FLUSH:
Copy all non-transactional tables that are not in use. This
list of used tables can be found with 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, TRUNCATE TABLE, and RENAME
TABLE.
Blocks also start off a new ALTER TABLE and the final rename
phase of ALTER TABLE. Running ALTER TABLES are not blocked.
 
mariabackup under BLOCK_DDL
 
mariabackup can, under BLOCK_DDL:
Copy the non-transactional tables that were in use during
STAGE FLUSH
Copy new tables created before BLOCK DDL. The file names can
be read from ddl.log. The log also allows the backup to
execute renames of files for which RENAME TABLE was done
instead of copying them.
Add markers to backup stream of tables that were dropped
during the earlier BACKUP STAGEs.
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
commits 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 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.
 
mariabackup Under BLOCK_COMMIT
 
mariabackup can, under BLOCK_COMMIT:
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 be hard linked to the 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 After END
 
mariabackup can, 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 STAGEs 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 STAGEs in the future with even more precise locks
without causing problems for tools using an earlier version
of BACKUP STAGEs
While opening files for a table, mariabackup should use
BACKUP LOCK to ensure that all files for a table are from
the same generation, that is, 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 will only be available in MariaDB Enterprise
server 10.2, 10.3 and 10.4.
 


URL: https://mariadb.com/kb/en/backup-stage/