MySQL 跳过锁表和 mysqldump
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7415698/
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
skip-lock-tables and mysqldump
提问by Jafo
Daily we run mysql dumps on about 50 individual databases, package them up and then store them offsite. Some of these databases are rather large and contain myisam tables (which CANNOT be changed so suggesting it is pointless).. I have been reading up on using the skip-lock-tables option when doing a dump but have not read what the downside would be. All I see are basically different iterations of "it could have adverse effects if data is inserted to a table while it is dumping."
每天我们在大约 50 个单独的数据库上运行 mysql 转储，将它们打包，然后将它们存储在异地。其中一些数据库相当大，包含 myisam 表（不能更改，因此表明它毫无意义）。我一直在阅读在执行转储时使用跳过锁定表选项的内容，但还没有读过它的缺点是什么是。我所看到的基本上是“如果在转储时将数据插入到表中可能会产生不利影响”的不同迭代。
What are these adverse effects? Does it just mean we will miss those queries upon a restore or will it mean the dump file will be broken and useless? I honestly could care less if we lose NEW data posted after the dump has started as I am just looking for a snapshot in time.
这些不良反应是什么？这是否只是意味着我们会在恢复时错过这些查询，还是意味着转储文件将被破坏和无用？老实说，如果我们丢失在转储开始后发布的新数据，我可能不太关心，因为我只是在寻找及时的快照。
Can I rely on these database dumps to contain all the data that was saved before issuing the dump.
我可以依靠这些数据库转储来包含在发出转储之前保存的所有数据吗？
回答by georgepsarakis
--skip-lock-tablesparameter instructs the mysqldump utility not to issue a LOCK TABLEScommand before obtaining the dump which will acquire a READ lock on every table. All tables in the database should be locked, for improved consistency in case of a backup procedure. Even with skip-lock-tables, while a table is dumped, will not receive any INSERTs or UPDATEs whatsoever, as it will be locked due the SELECT required to obtain all records from the table. It looks like this
--skip-lock-tables参数指示 mysqldump 实用程序在获取转储之前不要发出LOCK TABLES命令，这将在每个表上获取 READ 锁。应锁定数据库中的所有表，以提高备份过程中的一致性。即使使用skip-lock-tables，当表被转储时，也不会收到任何插入或更新，因为它会被锁定，因为需要从表中获取所有记录。看起来像这样
SELECT SQL_NO_CACHE * FROM my_large_table
and you can see it in the process list with the SHOW PROCESSLISTcommand. If you are using the MyISAM engine which is non-transactional, locking the tables will not guarantee referential integrity and data consistency in any case, I personally use the --skip-lock-tablesparameter almost always. In InnoDB use the --single-transactionparameter for the expected effect. Hope this helps.
您可以使用SHOW PROCESSLIST命令在进程列表中看到它。如果您使用的是非事务性的 MyISAM 引擎，锁定表在任何情况下都不能保证引用完整性和数据一致性，我个人几乎总是使用--skip-lock-tables参数。在 InnoDB 中，使用--single-transaction参数来达到预期效果。希望这可以帮助。
