How to Backup a Live MySQL DB Without Locking Tables Using mysqldump

Backup of MySQL is not very difficult – just run mysqldump and get out in a file – but it is not really designed either for production environments either.

At first, running mysqldump only took a few seconds, so it did not matter that everything was locked, but over time our backups were so big that the whole site was going to fall for half an hour if we made a backup. Even with a lot of cache that always meant that someone was getting a page of error every day. The problem is that mysqldump locks tables in the database before they are exported so that new things are not inserted while you are exporting.

The solution is to use the –single-transaction argument, which will give you a consistent backup without any lock. What actually happens is that mysqldump will start a new SQL transaction, dump all the pending writes, and then finish the backup as part of a transaction that does not block other updates

Note: The only caveat is that your database tables should use InnoDB rather than MyISAM. As this has been the default for a while, you should probably be fine.

So now that we have understood this, just add the argument to your normal backup routine, like this:

mysqldump -uUser -pPass -hHost – single transaction database> backup.bak

And now your backups will be much more reliable.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.