Movatterモバイル変換

LOCK INSTANCE FOR BACKUPUNLOCK INSTANCE

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

Executing theLOCK INSTANCE FOR BACKUP statement requires theBACKUP_ADMIN privilege. TheBACKUP_ADMIN privilege is automatically granted to users with theRELOAD 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 TABLETRUNCATE TABLE,OPTIMIZE TABLE, and account management statements are blocked. SeeSection 15.7.1, “Account Management Statements”. Operations that modifyInnoDB files that are not recorded in theInnoDB 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.

PURGE BINARY LOGS should not be issued while aLOCK INSTANCE FOR BACKUP statement is in effect for the instance, because it contravenes the rules of the backup lock by removing files from the server. From MySQL 8.0.28, this is disallowed.

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

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

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

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