MySQL 8.x Lightweight Backup Lock: LOCK INSTANCE FOR BACKUP Explained
MySQL 8.x adds a lightweight backup lock, LOCK INSTANCE FOR BACKUP, which permits DML during online backups while restricting only file creation, account changes, and certain table operations, requiring BACKUP_ADMIN privilege and offering a less disruptive alternative to FTWRL, used by MySQL Enterprise Backup and Percona Xtrabackup.
MySQL 8.x introduces a lightweight backup lock, called LOCK INSTANCE FOR BACKUP , which allows DML operations during an online backup while preventing snapshot inconsistencies.
The lock restricts only a few operations: creation, deletion or renaming of files; account management; REPAIR TABLE ; TRUNCATE TABLE ; and OPTIMIZE TABLE .
The lock is implemented with two statements, LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE , and requires the BACKUP_ADMIN privilege.
Unlike the traditional FLUSH TABLES WITH READ LOCK (FTWRL), which blocks all queries and can cause the entire system to hang, the new lock does not block reads and only blocks the listed operations, so other database activity can continue.
MySQL Enterprise Backup 8 and Percona Xtrabackup 8 both use this lock for their backup processes.
Percona also provides a lightweight backup lock LOCK TABLES FOR BACKUP , which does not flush tables and only waits for conflicting statements to finish, without blocking SELECTs or InnoDB updates.
For more details, refer to the MySQL documentation and Percona Server backup lock documentation.
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.