Backing up your MySQL database without disturbing your production

In most cases, to back up a MySQL database, the mysqldump command is used (it’s the official utility provided by the database). But did you know that the command is risky and can disturb your production?

I recently experimented it when trying to back up a large database. Because by default, the tool will lock tables, making them inaccessible. If you don’t have a lot of data, there won’t impact. But otherwise, it can cause a service interruption for the duration of the operation.

To avoid this, mysqldump offers a number of options that can help you avoid these issues. The two most important I use systematically are:

• --single-transaction: allow dumping the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications,
• --skip-lock-tables: avoid locking table when dumping it.