Mysqldump remains one of the most popular backup solutions. If at this stage of the project development more advanced backup tools are not used, it is worth to use mysqldump with the correct parameters.

The most critical impact, which mysqldump has on production is table locking:

# table locking during dump execution

In this case, any requests for data changes will wait for a complete dump. And this means that your application will practically stop working for users. You can disable table locking using the following options:

The second option will automatically turn off the first for engines that support versioning (for example, InnoDB). But if you use different engines, you should use both options.

In addition, it is useful to enable the –insert-ignore option to avoid errors during next recovery (for example, if the keys have changed).

And of course, you should use streaming compression with gzip to save disk resources. The -v option is also useful when dumping multiple tables — it displays system information about the progress of the dump.

The final dump command to execute on production might look like this:

TL; DR

Mysqldump can be used on production, but you should disable table locking. If the database size exceeds several tens of GB, plan to use hot backups.