跳过锁定表和mysqldump [英] skip-lock-tables and mysqldump

问题描述

每天，我们在大约50个单独的数据库上运行mysql转储，将它们打包，然后将它们存储在异地.这些数据库中的一些相当大，并且包含myisam表(无法更改，因此表明它毫无意义.).我一直在阅读转储时使用skip-lock-tables选项的内容，但没有读懂其不利之处.是.我所看到的基本上是不同的迭代:如果在转储时将数据插入到表中，则会产生不利影响."

这些不利影响是什么?这是否只是意味着我们将在还原时错过这些查询，还是意味着转储文件将被破坏且无用?老实说，如果我丢失了转储开始后发布的新数据，我会在乎，因为我只是在及时寻找快照.

我可以依靠这些数据库转储来包含在发出转储之前保存的所有数据.

解决方案

-skip-lock-tables 参数指示mysqldump实用程序不要发出 LOCK TABLES 命令在获取转储之前，该转储将获取每个表的READ锁.数据库中的所有表都应被锁定，以在备份过程中提高一致性.即使使用了skip-lock-tables，在转储表时，它也不会收到任何INSERT或UPDATE，因为由于需要SELECT才能从表中获取所有记录，因此它将被锁定.看起来像这样

SELECT SQL_NO_CACHE * FROM my_large_table

，您可以使用 SHOW PROCESSLIST 命令在进程列表中看到它. 如果您使用的是非事务处理的MyISAM引擎，则在任何情况下锁定表都不能保证引用完整性和数据一致性，我个人几乎总是使用-skip-lock-tables 参数.在InnoDB中，将-single-transaction 参数用于预期效果. 希望这会有所帮助.

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."

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.

解决方案

--skip-lock-tables parameter instructs the mysqldump utility not to issue a LOCK TABLES command 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

SELECT SQL_NO_CACHE * FROM my_large_table

and you can see it in the process list with the SHOW PROCESSLIST command. 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-tables parameter almost always. In InnoDB use the --single-transaction parameter for the expected effect. Hope this helps.

这篇关于跳过锁定表和mysqldump的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！