3 mysqldump arguments everyone should know

mysqldump is a command-line utility maintained by MySQL which allows you to perform logical backups for one or multiple databases. The output is a .sql file that can be used to recreate the database with the same data on a different (or the same) server.

We will present 3 mysqldump arguments that everyone should use in order to create backups without unpleasant surprises.

1. Avoid tables locking using --single-transaction and --skip-lock-tables

By default, mysqldump will lock all tables. This means that new rows will not be inserted and existing rows will not be updated until the dump is done. This happens because altering the database during a backup may result in a corrupted mysqldump output. If your database is using the InnoDB engine, which is the default one since MySQL 5.7, you have a better alternative that will not lock your tables while the dump is performed.

# Run mysqldump without locking the tables
mysqldump --single-transaction --skip-lock-tables my_database > my_database.sql

More examples of how to perform mysqldump without locking the tables can be found on mysqldump.guru.

2. Include all databases using --all-databases]

If you ever wanted to create a single dump containing all your MySQL databases, then the flag --all-databases is what you are looking for.

# Include all databases in a single dump
mysqldump --all-databases > my_backups.sql

More examples of how to include multiple or all databases in a single dump can be found on mysqldump.guru.

3. Dump a remote database

mysqldump allows you to create a dump of a database that is stored on a remote server by using the flag -host, like in the example below:

mysqldump -host 14.212.331.87 -u your_user --port 3306 -p my_app > my_backup.sql

More examples about how to dump one or more databases from a remote machine can be found on mysqldump.guru.