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 whenSTART TRANSACTIONwas issued without blocking any applications,--skip-lock-tables: avoid locking table when dumping it.
