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

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 2

Due to https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_lock-tables :

Some options, such as --opt (which is enabled by default), automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.

Answer 3

As none of these approaches worked for me, I simply did a:

mysqldump [...] | grep -v "LOCK TABLE" | mysql [...]

It will exclude both LOCK TABLE <x> and UNLOCK TABLES commands.

Note: Hopefully your data doesn't contain that string in it!

Answer 4

--skip-add-locks helped for me

Answer 5

    mysqldump -uuid -ppwd --skip-opt --single-transaction --max_allowed_packet=1G -q db |   mysql -u root --password=xxx -h localhost db

Answer 6

To dump large tables, you should combine the --single-transaction option with --quick.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction