The Geek Diary

MySQL Backup stuck at “Starting to lock all the tables”

by

The Problem

When starting a backup, the message “Starting to lock all the tables” appears and then backup stalls, it will not continue even after many hours. MySQL Enterprise Backup connection on the server can be observed with SHOW PROCESSLIST to be in the following state:

State: Waiting to get readlock
Info: FLUSH TABLES WITH READ LOCK

The Solution

Locking the server is required for the backup to get a consistent snapshot of any non-transactional data such as MyISAM tables, table definitions as well as the binary and slave log positions.
There are two main causes of this situation

  • caused MySQL to erroneously never grant a lock under some high load server situations. This was fixed in MySQL 5.5.8 and above but will not be backported to earlier versions including MySQL 5.0 or 5.1 as it changes server behavior in a backward-incompatible way.
  • A long-running query or transaction exists on the server which has not finished and thus prevents the backup from taking a lock.

You should review the PROCESSLIST and check for any long-running connections or statements that may be interfering. Sometimes a connection may not be running a query, but still holding a lock because a transaction was started and not completed. You can check the InnoDB transaction status to check for this (SHOW ENGINE INNODB STATUS).

If you are on MySQL 5.0 or 5.1 and are unable to upgrade, the only workaround is to disable locking in MySQL Enterprise Backup. You can achieve this by passing the option –no-locking option to mysqlbackup (MySQL Enterprise Backup 3.7.1 or later is required)

Using the –no-locking option has the following side effects:

  • The server is unable to observe a reliable binary log position. You will not be able to perform point-in-time recovery with this backup by rolling forward past the backup with binary logs.
  • This is the case irrespective of whether position based or GTID based replication is used. In MySQL 8.0, MySQL Server has special backup and log locks (the LOCK INSTANCE FOR BACKUP statement and the performance_schema.log_status table respectively) which will allow lock free backups of InnoDB tables.
  • You will not be able to backup any MyISAM tables (if any) in a consistent manner. Only InnoDB tables will be consistent with each other.

You May Also Like