In the end we need something like LOCK TABLES FOR BACKUP like with Percona:
http://www.percona.com/doc/percona-server/5.6/management/backup_locks.html

Oracle has tables as part of a tablespace and the tablespace is able to be anywhere on disk the tables are within a tablespace and oracle you can place a tablespace in backup mode, all data within that tablespace is kept consistent. Oracle writes logs of activity to be changed in that tablespace until the lock is released. This might be a step too far but may help formulate a way forward to get this to work. Tablespaces probably need to be implemented within the engine but could still be kept in the mariadb variables.
This would allow the concept of then managing data with a lock that doesn't necessarily affect the total server instance only the tablespace/s you wish to back up in that window for consistency.
For example now I don't necessarily need to lock the mysql database at the same time as other databases and not have a consistent databases. Often we can have different applications without interaction and can be backed up separately without the data becoming inconsistent even when those backups are at different times. Lots of sites do this now with mysqldump for their server backups.

Hope backup locks come to MariaDB soon~

Perhaps we should already adopt 8.0 syntax, not sure about it https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

Notes:

• stage 1 assumes that InnoDB logs RENAME in the redo log
  Currenty it doesn't, but there's a plan to fix it.
• stage 2 assumes that "block writes" also blocks RENAME.
  it's a bit tricky, as thr_lock isn't taken for rename, and mdl is taken too early, before the table is opened
• everywhere "transactional" means "InnoDB"
  or "InnoDB-like engine with redo log that can lock redo purges, logs RENAME, and can be copied without locks by an outside process"
• RocksDB is "non-transactional" in this context
  copied in the stage 3 using see MDEV-13122
• InnoDB inplace ALTER, probably, not handled. Final frm rename happens when the alter is completely finished and committed internally. If frm rename is blocked, the backup will be inconsistent.

UI Notes. Better to avoid explicit stage numbers. Ideas, in the order from small to big

• LOCK FOR BACKUP NEXT STAGE (this completely avoids the question of specifying stage numbers out of order, but still allows an early unlock)
• LOCK FOR BACKUP, LOCK FOR BACKUP ... — every such statement returns in a Note what happens now, for example "3 stages left" or "now, copy innodb data files" etc. The final LOCK FOR BACKUP says "Note: backup finished all locks removed".
• It's confusing that LOCK FOR BACKUP will unlock, so don't call it LOCK, but BACKUP (or BACKUP START/BACKUP CONTINUE etc). This allows future extensions for non-xtrabackup backups, like filesystem snapshot (BACKUP ... ALGORITHM=snapshot) etc.
• Make BACKUP statement to return a useful result set, like, file names to copy or tables that were write-locked or created, so that mariabackup wouldn't need to scan the datadir over and over again. This extends nicely to a BACKUP statement that returns actual data, if we'd ever decide to implement that too.

Stage 1 is using confusing terminology:

Block purge of redo files

In InnoDB, redo log files can only be removed at startup, when creating new redo log files due to size mismatch, upgrade, or a change of the parameter innodb_encrypt_log. The InnoDB purge is associated with undo log records, which reside in data pages in the system tablespace or undo tablespaces. These data pages are redo-logged just like any other data pages.

For stage 1, InnoDB could issue a redo log checkpoint. This would require new handler API.

Stage 3 would block TRUNCATE TABLE among other things. I believe that this is unnecessary after the MDEV-13564 TRUNCATE, which is present in the latest 10.3, 10.4, and has also been successfully tested on top of 10.2, but not pushed yet.

As a prerequisite of MDEV-13564, we would have transactional rename operations inside InnoDB (MDEV-14717). With that, I believe that the only thing that needs to be blocked are operations on .frm files.

Note: For ALTER TABLE…ALGORITHM=INPLACE, the .frm file is replaced after the operation has been committed in the storage engine. The commit inside InnoDB cannot be blocked, because data dictionary transactions are special. So, the block would have to be somewhere before the call to handler::commit_inplace_alter_table(commit=true).

Note: If ALTER TABLE is blocked before the final rename operations, then the backup can end up containing tables whose name starts with #sql. Inside InnoDB, such tables would be dropped at startup by MDEV-14585 (available starting with 10.3; would be backported to 10.2 as part of the TRUNCATE fix). The #sql*.frm files would not be removed by InnoDB; those would have to be removed or omitted by the backup program.

Stage 4 would again be ignored by InnoDB. InnoDB can basically write redo and data files at any time, so the ib_logfile* would have to be copied until the very end, which marks the logical time of the backup.

I strongly believe that the TRUNCATE fix (MDEV-13564) should be pushed to 10.2, even though the result is that Percona Xtrabackup will no longer work with MariaDB Server 10.2. Without that, we would have an issue with orphan #sql tables if any table-rebuilding operations were blocked between stage 3 and the end of the backup.

From what I understand this framework aims to be engine independent. If so it should be reflected in description, so that we can think of other storage engines impact in advance.

Stage 1
-------
Should we implement additional handlerton method, like preapre_for_backup()? Then we need symmetric method to restore state on UNLOCK.

Stage 2
-------

FLUSH all changes for not active non transactional tables. Mark the tables as closed.

At low level it means: close unused non-transactional TABLE instances and mark their TABLE_SHARE's flushed? Anything else?

BLOCK all new write locks for the above non transactional tables.

Are we going to block all new write locks for all unused non-transactional tables, or only for just flushed tables?

Are we going to block write locks, but not DDL?

Is it going to be implemented via MDL or every non-transactional storage engine has to implement this functionality? Or some other way?

How do we pass list of used non-transactional tables to the client (that is tables that it should skip at this point)?

Stage 3
-------
There seem to be a deadlock at least with RENAME TABLE here:

backup thread: writes to non-transactional tables are blocked
user thread: RENAME TABLE t1 TO t2;
user thread: takes MDL_EXCLUSIVE for t1 and t2
user thread: attempts to write to non-transactional mysql.table_stats and mysql.index_stats, which are blocked
user thread: waits for backup thread
backup thread: attempts to block RENAME TABLE
backup thread: waits for user thread

Another possible deadlock:

1. user thread: locks some table
2. stage 2: block writes to all unused tables
3. stage 3: wait for statements using other tables to end
4. user thread: tries to write-lock into until-now-unused table

With MyISAM tables one can solve a deadlock with fallback-and-retry approach, as MyISAM tables should not be locked in the middle of the statement. But non-InnoDB transactional tables are locked till the end of the transaction, so fallback-and-retry doesn't work for then. And RocksDB is one of these "non-InnoDB transactional tables", currently it's backed up in one-shot action under FTWRL (see MDEV-13122)

Comment to Sergei's deadlock:

• user thread: locks some table
• stage 2: block writes to all unused tables
• stage 3: wait for statements using other tables to end
• user thread: tries to write-lock into until-now-unused table

This can be solved as we do with FTWRL where we abort any transaction that is waiting for a lock on non transnational table. As transactional tables will not be locked, user thread will either be able to continue (not blocked in thr_lock) or if locked in thr_lock, FTWRL will abort the thr_lock and stage 3 can continue.

serg monty : Why it is called "BACKUP STAGE X", if server only takes locks. The SQL command does not do backup.
We have a plan for such command, but this one is not that.

we do not always need BACKUP stage 4.
taking binlog current position is optional in mariabackup. Why should we block the commits, if we can rely on Innodb to ensure proper recovery if transactions did not finish.

Why do we need to start "service to log changed tables" at STAGE 1?

Why this service has to log changes for all storage engines, given that "mariabackup can detect DDL's for InnoDB that are stored in the redo log"?

Blocking purge of redo files (aria specific) doesn't also seem to be required at STAGE 1. However if it is instant and doesn't produce measurable overhead, it is probably fine.

Edge cases:
What if storage engine is unloaded using UNINSTALL PLUGIN between prepare_for_backup() and end_backup()?

What if storage engine is loaded using INSTALL PLUGIN between prepare_for_backup() and end_backup()?

Just for the record: previous review base before force push https://github.com/MariaDB/server/commits/6bb480cc58ab137d675497205e0d3566cbb39dd0

matthias, the above assert is fixed in the current code. I just added a test case to cover this case.

Will fix the privileges to only allow root to execute backup stages;
Statement timeouts works with backup stages; This is tested in backup_locks
SET STATEMENT max_statement_time=1 FOR backup stage block_ddl;
I have now also added the above test case to backup_locks.test

If you only allow "root" for the BACKUP STAGE statements, then users, who were able to run backup in 10.3, would not be able to do so in 10.4 . If this is by design, it should be documented.
10.3 only needed privileges necessary for FTWRL (RELOAD? LOCK TABLES?)

I will ensure it will use same privileges as FTWRL

Quick question on myisam: BLOCK all new write row locks for all non transactional tables
(except statistics and log tables), can this not be done by introducing a binlog copy in row based service, each non transactional would start reporting binlog copy into backup-binlog.000001 get locked one by one and report binlog copy until the end of the backup, restore would apply the binlog flahback copy ?

Discussions to implement usage of backup stages in mariabackup