How to lock all tables in one mysql db?

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 tableslock_db $DB_NAME# Verify locks have been placedecho "SHOW OPEN TABLES WHERE in_use != 0" | $MYSQLCMD# Do whatever here that you needed the locked db formysqldump -u$BACKUP_USER -p$BACKUP_PASS $DB_NAME > $SQL_FILE# Release locksunlock_db $DB_NAME# Verify locks releasedecho "SHOW OPEN TABLES WHERE in_use != 0" | $MYSQLCMD

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

#!/bin/bashDB=exampleDUMP_FILE=export.sql# Lock the database and sleep in background taskmysql -uroot -proot $DB -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &sleep 3# Export the database while it is lockedmysqldump -uroot -proot --opt $DB > $DUMP_FILE# When finished, kill the previous background task to unlockkill $! 2>/dev/nullwait $! 2>/dev/nullecho "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.

You should look into this option.

FLUSH TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups...

http://dev.mysql.com/doc/refman/5.0/en/flush.html