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

2021-11-20 00:00:00 mysql

我想将实时生产数据库复制到我的本地开发数据库中.有没有办法在不锁定生产数据库的情况下做到这一点?

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?

我目前正在使用:

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.

推荐答案

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

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

According to the man page, if you are dumping InnoDB tables you can use the --single-transaction option:

--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 DB:

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

相关文章