How to Use MySQLDump Utility to Backup MySQL or MariaDB Database

MySQL databases come with a backup utility called MySQLdump. MySQLdump allows you to quickly backup a MySQL database from the command line. However, the MySQLdump tool is only useful if the database you wish to backup is up and running and accessible.

This tutorial will show you how to use the mysqldump tool to backup your databases from the terminal.

NOTE: To follow this tutorial, you will need to have a fully working MySQL installation and a root account or one with sudo privileges.

Backing Up Database

To backup a database using mysqldump tool, use the general syntax below:

mysqldump [OPTIONS] [databaseName] > [name].sql

For example, if you want to back up a single database, you can use the command below:

mysqldump -u root -p reviews --quick --lock-tables=false > reviews_backup.sql

Enter password: *****

In the above command, we used the mysqldump tool to backup the database (reviews) into a file reviews_backup.sql

Once the above command executes, the mysqldump utility will prompt you for a password and then proceed to back up the database if the password is correct. The backup file created will be in the directory from which the utility runs.

We use the options such as:

–quick – Tells mysqldump to enforce dumping of tables row by row.
–lock-tables=false – Prevents locking of the tables during the backup process.

You can check other options by calling the command mysqldump –help

Backing Up Entire DBMS

The Mysqldump utility also allows you to backup all the databases in the DBMS using the single command below:

mysqldump -u root -p --all-databases --quick --lock-tables=false > master_backup.sql

Using the above command, you will have a copy of all the databases in the system in a single file that you can use to restore.

Backing up a Table

If you wish to back up a single table within a specific database, you can use the command below to accomplish this:

mysqldump -u root -p --quick --lock-tables=false reviews log > db_reviews_log_tb.sql

In the above command, we create a backup of the log table from the reviews database into a file called db_reviews_log_tb.sql.

Restore Backup

Once you have created backups of your databases and tables, you can restore them by specifying the database and filename. For example:

mysql -u root -p reviews < reviews_backup.sql

The above command restores the reviews_backup to the reviews database.

NOTE: A database restore removes and overwrites current data with the data specified in the backup file. To restore the entire DBMS backup. Use the command below:

mysql -u root -p < master_backup.sql

Conclusion

In this quick guide, we discussed how to use the mysqldump tool to backup databases. The ideas discussed in this tutorial can help you make a quick copy of your databases.