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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. How to List and Set SELinux Context for MySQL Server
  2. “expect” script to provide password to mysql_config_editor
  3. Beginners Guide to Storage Engines in MySQL
  4. Counting Rows Of A Table In MySQL Server
  5. MySQL : How To Find the Slowest Queries
  6. Beginners Guide to MySQL User Management
  7. How to Restore a Specific Database or Table (MySQL)
  8. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  9. How to Install MySQL Cluster on a single server for testing purposes
  10. Understanding the Different Configuration files used for MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright