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
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
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.