MySQL 如何锁定一个mysql数据库中的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14050627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to lock all tables in one mysql db?
提问by KeepZero
I write a backup shell script to execute mysqldump.
我写了一个备份shell脚本来执行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-transactionoption.
The --lock-tablesonly 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?
我的数据库的存储引擎是 MyISAM。所以我不能使用--single-transaction选项。该--lock-tables只锁定在正在进行的mysqldump一个表。我的 MySQL 实例中有很多数据库，我不想使用--lock-all-tables，它会锁定我服务器中运行的所有数据库。那么，如何同时锁定一个 mysql 数据库中的所有表，以便我可以转储它？
回答by Joshua Huber
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.
到目前为止，这不是最漂亮的解决方案，但这是有效的。我有同样的需求，这是我的解决方案，稍作修改以匹配您的变量名称。我假设您在 Linux 上运行 MySQL，因为这在很大程度上依赖于 shell BASH 语义。如果您使用的是 Windows，这可能不起作用。
# 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-" ] && rm "/tmp/mysql-db-lock-"
mkfifo "/tmp/mysql-db-lock-"
(
(
echo "SELECT CONCAT( 'LOCK TABLES '
, GROUP_CONCAT(CONCAT('\`',table_name,'\`'),' WRITE')
, ';'
) AS \"-- Statement to lock tables\"
FROM information_schema.tables
WHERE table_schema=''
ORDER BY table_name;
" | $MYSQLCMD
echo "\! cat '/tmp/mysql-db-lock-' >/dev/null"
echo 'UNLOCK TABLES;'
) | $MYSQLCMD -D""
rm "/tmp/mysql-db-lock-"
) &
}
function unlock_db {
>"/tmp/mysql-db-lock-"
}
# 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
回答by frustratedtech
You should look into this option.
你应该看看这个选项。
FLUSH TABLES WITH READ LOCK
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...
关闭所有打开的表并使用全局读锁锁定所有数据库的所有表。这是一种非常方便的备份方式...
回答by Nicomak
Here's how I did it. It should work in all cases since it uses FLUSH TABLES WITH READ LOCK.
这是我如何做到的。它应该适用于所有情况，因为它使用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 sleepcommand 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.
shellsleep命令只是为了确保在mysqldump启动之前执行运行mysql锁定命令的后台任务。您可以将其减少到 1 秒，它应该仍然可以。将其增加到 30 秒并尝试在这 30 秒内从另一个客户端的任何表中插入值，您会看到它被锁定。
There are 2 advantages in using this manual background locking, instead of using the mysqldumpoptions --single-transactionand --lock-tables:
使用此手动后台锁定有两个优点，而不是使用mysqldump选项--single-transaction和--lock-tables：
- This locks everything, if you have mixed MyISAM/InnoDB tables.
- You can do run other commands in addition to the
mysqldumpduring 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 withSHOW MASTER STATUS;at the exact state of the dump you created (before unlocking the database), to be able to create a replication slave.
- 如果您混合了 MyISAM/InnoDB 表，这将锁定所有内容。
- 除了
mysqldump在同一锁定期间，您还可以运行其他命令。例如，当在主节点上设置复制时，它很有用，因为您需要SHOW MASTER STATUS;在您创建的转储的确切状态（解锁数据库之前）获取二进制日志位置，以便能够创建复制从属。
