MySQL - Avoid table locks during backup

Question

I am running nightly backups on my server using mysqldump. Unfortunately that pretty much kills my site for half an hour or so every night, since the tables are getting locked during the backup.

According to the processlist it even seems like queries on unrelated tables are getting blocked sometimes, e.g. "INSERT INTO A" is in state locked for several minutes while the only running query only touches table B.

Is there a better way to do backups like this?

Solution

You have two options:

A. Use --skip-lock-tables mysqldump option. Your tables won't be locked, but your backup might be not consistent (depends on your schema: if your database transactions touch several tables at once, and one table is already backed up and other is not. For example: two tables: customers and orders. If customers is backed up first, and then new customer/order pair is inserted, you might end up with order without customer in your backup).

This problem is primary reason why mysqldump locks all tables by default. If this issue does not apply to you, skipping the table locking in mysql is the most simple solution.

B. Use some other backup method. For example: Flush tables with read lock, create LVM snapshot, unlock tables, mount your LVM snapshot and back up your data. This gets quickly, much more complex than simple mysqldump. Google "mysql backups with LVM snapshots", there are many scripts and tutorials available.

If your MySQL data is not on a LVM volume, you can create a slave MySQL server and back it up, without touching the master, so locking occurs on the slave and master is always free.