top of page
CerebroSQL

MySQL: 

LOCK INSTANCE FOR BACKUP

Syntax:
LOCK INSTANCE FOR BACKUP

UNLOCK INSTANCE

LOCK INSTANCE FOR BACKUP acquires an instance-level backup lock that
permits DML during an online backup while preventing operations that
could result in an inconsistent snapshot.

Executing the LOCK INSTANCE FOR BACKUP statement requires the
BACKUP_ADMIN privilege. The BACKUP_ADMIN privilege is automatically
granted to users with the RELOAD privilege when performing an in-place
upgrade to MySQL 8.0 from an earlier version.

Multiple sessions can hold a backup lock simultaneously.

UNLOCK INSTANCE releases a backup lock held by the current session. A
backup lock held by a session is also released if the session is
terminated.

LOCK INSTANCE FOR BACKUP prevents files from being created, renamed, or
removed. REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE, and account
management statements are blocked. See
https://dev.mysql.com/doc/refman/8.0/en/account-management-statements.h
tml. Operations that modify InnoDB files that are not recorded in the
InnoDB redo log are also blocked.

LOCK INSTANCE FOR BACKUP permits DDL operations that only affect
user-created temporary tables. In effect, files that belong to
user-created temporary tables can be created, renamed, or removed while
a backup lock is held. Creation of binary log files is also permitted.

A backup lock acquired by LOCK INSTANCE FOR BACKUP is independent of
transactional locks and locks taken by FLUSH TABLES tbl_name [,
tbl_name] ... WITH READ LOCK, and the following sequences of statements
are permitted:

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

The lock_wait_timeout setting defines the amount of time that a LOCK
INSTANCE FOR BACKUP statement waits to acquire a lock before giving up.

URL: https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

Example

bottom of page