MySQL 在不锁定表的情况下运行 MySQLDump
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/104612/
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
Run MySQLDump without Locking Tables
提问by Greg
I want to copy a live production database into my local development database. Is there a way to do this without locking the production database?
我想将实时生产数据库复制到我的本地开发数据库中。有没有办法在不锁定生产数据库的情况下做到这一点？
I'm currently using:
我目前正在使用：
mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1
But it's locking each table as it runs.
但是它在运行时锁定每个表。
回答by John Millikin
Does the --lock-tables=falseoption work?
该--lock-tables=false选项有效吗？
According to the man page, if you are dumping InnoDB tables you can use the --single-transactionoption:
根据手册页，如果您要转储 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.
For innodb DB:
对于innodb 数据库：
mysqldump --single-transaction=TRUE -u username -p DB
回答by Warren Krewenki
This is ages too late, but good for anyone that is searching the topic. If you're not innoDB, and you're not worried about locking while you dump simply use the option:
这为时已晚，但对任何正在搜索该主题的人都有好处。如果您不是 innoDB，并且在转储时不担心锁定，只需使用以下选项：
--lock-tables=false
回答by dvorak
The answer varies depending on what storage engine you're using. The ideal scenario is if you're using InnoDB. In that case you can use the --single-transactionflag, which will give you a coherent snapshot of the database at the time that the dump begins.
答案因您使用的存储引擎而异。理想的情况是如果您使用 InnoDB。在这种情况下，您可以使用该--single-transaction标志，它会在转储开始时为您提供数据库的连贯快照。
回答by Azamat Tokhtaev
--skip-add-lockshelped for me
--skip-add-locks对我有帮助
回答by dgitman
To dump large tables, you should combine the --single-transaction option with --quick.
要转储大表，您应该将 --single-transaction 选项与 --quick 结合使用。
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
回答by Lex
For InnoDB tables use flag --single-transaction
对于 InnoDB 表使用标志 --single-transaction
it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications
它在发出 BEGIN 时转储数据库的一致状态，而不会阻止任何应用程序
MySQL DOCS
MySQL 文档
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
回答by michal kralik
Honestly, I would setup replication for this, as if you don't lock tables you will get inconsistent data out of the dump.
老实说，我会为此设置复制，就好像您不锁定表一样，您将从转储中获得不一致的数据。
If the dump takes longer time, tables which were already dumped might have changed along with some table which is only about to be dumped.
如果转储需要更长的时间，则已经转储的表可能会随着一些即将被转储的表一起更改。
So either lock the tables or use replication.
所以要么锁定表，要么使用复制。
回答by dtbarne
This is about as late compared to the guy who said he was late as he was to the original answer, but in my case (MySQL via WAMP on Windows 7), I had to use:
与说他迟到的人相比，这与他对原始答案的迟到一样晚，但在我的情况下（MySQL 通过 Windows 7 上的 WAMP），我不得不使用：
--skip-lock-tables
回答by naveen_sfx
mysqldump -uuid -ppwd --skip-opt --single-transaction --max_allowed_packet=1G -q db | mysql -u root --password=xxx -h localhost db
回答by Samuel Diogo
When using MySQL Workbench, at Data Export, click in Advanced Options and uncheck the "lock-tables" options.
使用 MySQL Workbench 时，在数据导出中，单击高级选项并取消选中“锁定表”选项。
