如何在一个MySQL数据库中锁定所有表？

Question

如何在一个MySQL数据库中锁定所有表？

9

我编写了一个备份脚本来执行mysqldump。

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

我的数据库存储引擎是MyISAM。因此我无法使用--single-transaction选项。 --lock-tables选项只能锁定mysqldump进程中的一个表。在我的MySQL实例中有许多数据库，我不想使用--lock-all-tables，它会锁定服务器上运行的所有数据库。那么，如何同时锁定一个mysql数据库中的所有表以便可以导出它？

- KeepZero

1

“--lock-tables”在转储表之前应该锁定所有要转储的表。你觉得它没有这样做吗？ - cdhowie

我在mysql 5.1中进行测试，当我在mysqldump中使用--lock-tables时，我仍然可以向$DB_NAME数据库的表中插入数据。 - KeepZero

3个回答

5

以下是我如何操作的步骤。因为使用了 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 !"

Shell中的sleep命令只是为了确保在mysqldump开始之前执行了运行mysql锁定命令的后台任务。您可以将其减少到1秒，应该还是可以的。将其增加到30秒，并尝试在这30秒内从另一个客户端向任何表中插入值，您会发现它被锁定了。

使用此手动后台锁定有两个优点，而不是使用mysqldump选项--single-transaction和--lock-tables：

如果您有混合MyISAM / InnoDB表，则会锁定所有内容。
您可以在相同的锁定期间运行其他命令，除了mysqldump。例如，在主节点上设置复制时很有用，因为您需要使用SHOW MASTER STATUS;在创建转储的确切状态（在解锁数据库之前）获取二进制日志位置，以便能够创建复制从库。

- Nicomak

2

这也会在备份单个数据库时跨所有数据库发出全局锁定。 - Stefan Lasiewski

4

你应该考虑这个选项。

FLUSH TABLES WITH READ LOCK

关闭所有打开的表并为所有数据库锁定所有表格，使用全局读锁。这是获取备份的非常方便的方式...

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

- frustratedtech

FLUSH TABLES WITH READ LOCK - BenMorel

这会发出一个全局读取锁，而不仅仅是单个数据库中的表。 - Stefan Lasiewski

3

问题是针对单个数据库而非所有数据库。 - Webomatik

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Joshua Huber · Accepted Answer

虽然这并不是最美观的解决方案，但它却有效。我也有同样的需求，以下是我的解决方案，稍作修改以匹配您的变量名称。我假设您正在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-${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