Menu
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.
585
By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.
The mysqldump tool is located in the root/bin directory of the MySQL installation directory.
4 mysqldump — A Database Backup Program. The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.
It took a total of 1 minute 27 seconds to take a dump of the entire database (same data as used for mysqldump) and also it shows its progress which will be really helpful to know how much of the backup has completed.
Does the --lock-tables=false 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. For innodb DB:
mysqldump --single-transaction=TRUE -u username -p DB If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
