• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

By admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  2. Understanding MySQL Query Cache
  3. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  4. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  5. MySQL Error “Too many connections” and how to resolve it
  6. Examples of mysqldump partial backups
  7. How to Generate Unique IDs For MySQL Cluster Backups
  8. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  9. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  10. Understanding the REVOKE statement in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary