How to lock all tables in one mysql db?

Question

I write a backup shell script to execute mysqldump.

mysqldump -u$BACKUP_USER -p$BACKUP_PASS --flush-logs --lock-tables $DB_NAME > $SQL_FILE

My db's storage ENGINE is MyISAM. So I can't use --single-transaction option. The --lock-tables only lock one table in mysqldump progress. There are many databases in my MySQL instance, I don't want to use --lock-all-tables, it will lock all databases run in my server. So, How to lock all tables in ONE mysql database at sametime so I can dump it?

Answer 1

Here's how I did it. It should work in all cases since it uses FLUSH TABLES WITH READ LOCK.

#!/bin/bash

DB=example
DUMP_FILE=export.sql

# Lock the database and sleep in background task
mysql -uroot -proot $DB -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &
sleep 3

# Export the database while it is locked
mysqldump -uroot -proot --opt $DB > $DUMP_FILE

# When finished, kill the previous background task to unlock
kill $! 2>/dev/null
wait $! 2>/dev/null

echo "Finished export, and unlocked !"

The shell sleep command is just to make sure that the background task running the mysql locking command is executed before the mysqldump starts. You could reduce it to 1 second and it should still be fine. Increase it to 30 seconds and try inserting a values in any table from another client during those 30 seconds you'll see it's locked.

There are 2 advantages in using this manual background locking, instead of using the mysqldump options --single-transaction and --lock-tables:

1. This locks everything, if you have mixed MyISAM/InnoDB tables.
2. You can do run other commands in addition to the mysqldump during the same locking period. It's useful, for instance, when setting up replication on a master node, because you need to get the binary log position with SHOW MASTER STATUS; at the exact state of the dump you created (before unlocking the database), to be able to create a replication slave.

Answer 2

Not the prettiest solution by far, but this works. I had the same need and here's my solution, slightly modified to match your variable names. I'm assuming you're running MySQL on Linux, as this relies pretty heavily on shell BASH semantics. If you're on Windows, this probably won't work.

# Mysql script to lock all tables in one DB
# (such as to get a consistent export dump of one database)

MYSQLCMD="mysql -u$BACKUP_USER -p$BACKUP_PASS -A"

function lock_db {
  [ -e "/tmp/mysql-db-lock-${1}" ] && rm "/tmp/mysql-db-lock-${1}"
  mkfifo "/tmp/mysql-db-lock-${1}"
  (
    (
      echo "SELECT CONCAT( 'LOCK TABLES '
             , GROUP_CONCAT(CONCAT('\`',table_name,'\`'),' WRITE')
             , ';'
             ) AS \"-- Statement to lock tables\"
      FROM information_schema.tables
      WHERE table_schema='${1}'
      ORDER BY table_name;
      " | $MYSQLCMD
      echo "\! cat '/tmp/mysql-db-lock-${1}' >/dev/null"
      echo 'UNLOCK TABLES;'
    ) | $MYSQLCMD -D"${1}"
    rm "/tmp/mysql-db-lock-${1}"
  ) &
}

function unlock_db {
  >"/tmp/mysql-db-lock-${1}"
}

# Lock one database, all tables
lock_db $DB_NAME

# Verify locks have been placed
echo "SHOW OPEN TABLES WHERE in_use != 0" | $MYSQLCMD

# Do whatever here that you needed the locked db for
mysqldump -u$BACKUP_USER -p$BACKUP_PASS $DB_NAME > $SQL_FILE

# Release locks
unlock_db $DB_NAME

# Verify locks released
echo "SHOW OPEN TABLES WHERE in_use != 0" | $MYSQLCMD