Dumping/Backing up Mysql Databases (Innodb) without locking tables.

Jan 27 2012

When not using hosted backup solutions or gems, I am make use good old “mysqldump” a lot to take a dump of my databases and move the sql around. I think its good practice generally to avoid locking tables for read and write atleast in a production like environment. I am yet to go deeper into this issue, but as a practice I have started using  the options almost all the time.

So in case of Transactional Tables ( Innodb ) , you can use the 

–single-transaction

option. What this option does is that it opens a new transaction which in turn avoids locking. The database is available to read and write in that time, though you may see a considerable loss in performance. 

Another useful option is 

–quick

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Checkout more details about mysqldump here

I have also heard good things about Percona XtraBackup for Mysql. I am yet to explore that option. 

Posted via email from Put to flight

No responses yet

Leave a Reply