[MDEV-25571] mariadb-dump support for backup lock Created: 2021-04-30  Updated: 2022-04-20

Status: Open
Project: MariaDB Server
Component/s: Backup
Affects Version/s: 10.4.18, 10.5.9, 10.6
Fix Version/s: None

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-28367 BACKUP LOCKS on table to be accessibl... Closed
PartOf
includes MDEV-25572 Improve compatibility of BACKUP STAGE... Open
Relates
relates to MDEV-28074 mysqldump --order-by-size Closed

 Description   

Some transactional backup can failed with Error 1412:Table definition has changed, thanks to the introduction of BACKUP STAGE MDEV-17309 this can be fix by introducing a new option
--lock-for-backup

that would for --single-transaction inject

BACKUP STAGE START;
BACKUP STAGE BLOCK_DDL;
START TRANSACTION WITH CONSISTENT SNAPSHOT;



 Comments   
Comment by Daniel Black [ 2022-03-16 ]

As mentioned in https://github.com/MariaDB/server/pull/2054, when doing a limited backup maybe BACKUP LOCK tbl is sufficient.

monty is there a reason BACKUP LOCK tbls requires RELOAD privs? Would a table level privilege be sufficient? A SELECT statement in a transaction is sufficient to hold a metadata lock so couldn't that apply to {{BACKUP LOCK}}s too?

Comment by Michael Widenius [ 2022-04-12 ]

About RELOAD. The documentation is:
"Execute FLUSH statements or equivalent mariadb-admin/mysqladmin commands."

In effect having RELOAD privilege is not something that should cause any issues to give to anyone.
It is more that anyone having RELOAD could potentially do a lot of not needed FLUSH commands, which could any people on busy server.
Same thing with BACKUP STAGE as one can stop DDL's if one is not using it carefully.

When it comes to BACKUP LOCK, having access to the table should probably be enough.
I will check with Sergei.

Comment by Michael Widenius [ 2022-04-13 ]

The conclusion would be to allow one to do a BACKUP LOCK if one has either the global RELOAD privilege or the LOCK privilege on the table.
This makes BACKUP LOCK similar to LOCK TABLE, but still backward compatible

Comment by VAROQUI Stephane [ 2022-04-13 ]

mydumper patch on his way: https://github.com/mydumper/mydumper/issues/8 with some documented case

Comment by Daniel Black [ 2022-04-13 ]

Agree that making an additional privilege in addition to RELOAD is the easiest compatible way.

LOCK_TABLES_ACL appears to be a database level privilege, which in addition to SELECT on the table give access to LOCK TABLE, so that pattern can be copied.

Which major version should this change target?

Generated at Thu Feb 08 09:38:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.