MySQL 8.0.11 supports lock instance for backup but MySQL Enterprise Backup (MEB) not support that still uses FTWRL (FLUSH TABLES WITH READ LOCK). Support for avoiding FLUSH TABLES WITH READ LOCK for InnoDB tables was added in MySQL Enterprise Backup 8.0.16.
Before 8.0.16, MySQL Enterprise Backup performs the backup in several steps:
- First the InnoDB data is copied.
- FLUSH TABLES WITH READ LOCK is executed.
- All other files are copied and other tasks requiring the lock are performed. See also below.
- The lock is released.
MySQL Server 8.0 introduced support for a specialized lock that is useful for backups:
“Backup lock. A new type of backup lock permits DML during an online backup while preventing operations that could result in an inconsistent snapshot. The new backup lock is supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax. The BACKUP_ADMIN privilege is required to use these statements.”
However, currently MySQL Enterprise Backup does not support this new lock.
Avoid locking in earlier versions
It is possible to avoid locking the tables. To enable this, you will have to specify the –no-locking option which disables locking while copying the non-InnoDB files. The option can also be specified in the configuration file:
When locking is disabled, the output when changing from the first to the second stage looks like:
... mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/' 111124 10:20:44 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/var/lib/mysql/' ...