Run MySQLDump without Locking Tables

Question

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.

Answer 1

Does the --no-lock-tables option work?

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.

Answer 2

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.

Answer 3

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-transaction flag, which will give you a coherent snapshot of the database at the time that the dump begins.

Answer 4

I'm all innoDB. --single-transaction works perfectly, thanks so much!

Answer 5

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:

--lock-tables=false

Answer 6

The different between "--single-transaction" and "--lock-tables" when using innodb tables?If you know,mail to me:[email protected],thanks.I come from China.