在不锁定表的情况下运行MySQLDump

Question

我想将生产环境的数据库复制到本地开发环境的数据库。有没有一种方法可以在不锁定生产环境数据库的情况下完成此操作？

我目前正在使用：

mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1

但它在运行时会锁定每个表。

- Greg

---

另一种延迟的解决方案：您还可以使用Percona XtraBackup在不影响事务处理的情况下转储生产数据库。它允许进行热备份，即不会影响当前活动。请参见此处： https://www.percona.com/software/mysql-database/percona-xtrabackup（我与Percona没有任何关联。） - delx

Answer 1

--lock-tables=false选项是否有效？

根据手册，如果您要转储InnoDB表，则可以使用--single-transaction选项：

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ
LOCAL to allow concurrent inserts in the case of MyISAM tables. For
transactional tables such as InnoDB and BDB, --single-transaction is
a much better option, because it does not need to lock the tables at
all.

对于 InnoDB 数据库：

mysqldump --single-transaction=TRUE -u username -p DB

- John Millikin

---

使用InnoDB数据库时，可以使用以下命令导出mysqldump数据：mysqldump --single-transaction=TRUE -u用户名 -p 数据库名。 - user1642018

如果您同时使用InnoDB和MyISAM引擎，会怎样呢？ - CMCDragonkai

这个默认开启了吗？ - CMCDragonkai

显然是（即锁定）吗？ - evandrix

我认为你不能向--lock-tables传递选项，因此不认为--lock-tables=false会起作用。 - Ian

Answer 2

虽然来得有些晚了，但对于任何正在搜索这个主题的人都是好消息。如果您不使用InnoDB，并且在转储时不担心锁定问题，只需使用以下选项：

--lock-tables=false

- Warren Krewenki

---

感谢您的回复，Warren。这非常有帮助，而且运行得很好。 - Gavin

使用 '--lock-table=false --quick' 可以最节省服务器资源。 - SyntaxGoonoo

你应该担心锁表的问题。如果在 mysqldump 运行时写入多个表（并且使用了外键），你的备份可能是不一致的。在还原数据后，如果恰好对不一致的数据运行 JOIN 查询，你才会知道这个问题。由于 JOIN 语句是由应用程序而非 MySQL（对于 MyISAM 表）使用的，因此发现不一致的数据可能需要一段时间。尽管还原操作可以正常工作，但 MySQL 不会警告你存在不一致的数据。所以：对于 MyISAM -> 总是锁定你的表；对于 InnoDB -> 使用 --single-transaction。 - Costa

@Costa，我认为仅锁定表对于MyISAM表来说甚至都不足够。如果mysqldump在应用程序执行的查询之间锁定表，则最终会产生相同的不一致性。答案更简单：对于MyISAM，请改用InnoDB。 - cdhowie

警告：当 @Gavin 输入 lock-table 时，他实际上是指 --lock-tables（复数形式）。 - Felipe Valdes

Answer 3

答案取决于您使用的存储引擎。理想情况是如果您使用InnoDB。在这种情况下，您可以使用--single-transaction标志，在转储开始时将为您提供数据库的一致快照。

- dvorak

Answer 4

--skip-add-locks 对我有帮助

- Azamat Tokhtaev

---

或者也可以使用--compact选项将跳过锁定与其他优化一起使用。 - ppostma1

这将从转储文件中移除LOCK TABLES和UNLOCK TABLES语句，但不影响导出期间的锁定。 - dabest1

不，这不是你想要的！请看dabest1的评论。这对于在执行mysqldump时防止表被锁定没有任何作用。这不是问题的答案。 - orrd

@dabest 和 @orrd 是正确的：--skip-add-locks 只会使备份恢复速度更快。这不是一个正确的答案。 - dr_

谢天谢地，这正是我需要的特定用例。使用mysql打包的mysqldump在转储数据时，AWS Aurora（我使用的是5.7.~something驱动程序）将在插入时锁定表，包括日志表。这将引发错误“您无法在日志表上使用锁定。”而重新插入。我只转储迁移所需的数据，因此我认为这将优雅地解决我的问题。 - Richard Tyler Miles

还要注意的是，如果参数中有“--opt”，它将覆盖此标志。 - Richard Tyler Miles

Answer 5

要转储大型数据表，应将--single-transaction选项与--quick选项结合使用。

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction

- dgitman

---

选项--quick现在是选项--opt的一部分。而--opt选项默认启用。 - Alexander Ushakov

在一个58Gb的数据库上，这对我仍然没有用。我看到很多LOCK TABLES，然后在恢复期间插入失败。 - Eamonn Kenny

Answer 6

针对InnoDB表，使用参数--single-transaction。该命令会在不阻止任何应用程序的情况下，在执行BEGIN时，将数据库处于一致状态的转储输出。

参考MySQL文档：http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction。

- Lex

---

也许还可以加上 --skip-lock-tables。 - Xavi Montero

Answer 7

相对于那个说他迟到了的人和最初的答案而言，我的情况（在Windows 7上通过WAMP使用MySQL）需要使用：

--skip-lock-tables

- dtbarne

---

这是我成功转储information_schema的方法，而不会出现“在使用LOCK TABLES时，用户'debian-sys-maint'@'localhost'对数据库'information_schema'的访问被拒绝”的错误。 - Rui F Ribeiro

@miken32，你是指建议使用--skip-add-locks的答案吗？那绝对不同于--skip-lock-tables。感谢你花时间在9年后错误地指出这一点。 :) - dtbarne

Answer 8

老实说，我会为此设置复制，因为如果您不锁定表格，您将从转储中获取不一致的数据。

如果转储需要更长时间，那么已经转储的表格可能已经发生了变化，以及某些即将被转储的表格。

因此，请锁定表格或使用复制。

- michal kralik

---

整个数据库几乎完全是只读的，所以我不太担心它会发生变化。 - Greg

这个评论是不正确的。MVCC 允许在 InnoDB 上没有锁的情况下读取一致的状态。 - Scott Hyndman

如果您还没有设置副本，请进行转储以进行设置。存在相同的问题。 - Matt Connolly

如果您还没有设置复制功能，那么在转储数据时，您需要锁定表以确保数据完整性。这是一个进退两难的局面。 - JordanC

Answer 9

    mysqldump -uuid -ppwd --skip-opt --single-transaction --max_allowed_packet=1G -q db |   mysql -u root --password=xxx -h localhost db

- naveen_sfx

---

点赞，这个对我有用，只需添加参数 --skip-opt --single-transaction --max_allowed_packet=1G。 - Steven Lizarazo

我不建议在这种情况下使用“--skip-opt”。它做的远远超出了原始问题所要求的范围。它关闭了快速模式，没有包括字符集等等。 - orrd

Answer 10

在使用MySQL Workbench时，在数据导出中，点击高级选项并取消"lock-tables"选项。

- Samuel Diogo