Backup & Restore MySQL Databases with mysqldump

In this article, we'll discover how to back up a MySQL database using the mysqldump utility in the command line. Then we'll quickly walk through restoring that database from the output file.

There are many options for backing up a database using this tool. We won't cover all of them in this article, but there are some very useful options that are good to know when creating quick backups.

Backing Up Your Entire MySQL Database

The most common backup approach I take is backing up an entire MySQL database into a single file. This approach backs up everything, including the database, table structure, and table data:

mysqldump -u [username] --password=[password] [database-name] > [filename].sql

The command is simple. Here, we define the database username and password using the -u and --password options so we can connect to our database without a credentials prompt. Then we define the name of our database and an output filename.

Backing Up Multiple Databases at Once

If you have multiple databases you'd like to back up into a single file, you can do so by adding the --databases option and a space-delimited list of database names:

mysqldump -u [username] --password=[password] --databases [database-name-1] [database-name-2] > [filename].sql

If you want to back up all of your databases without naming them individually, you can do so with the --all-databases option:

mysqldump -u [username] --password=[password] --all-databases > [filename].sql

Backing Up A Single Table Only

The command for backing up a single table and its data is almost identical to backing up an entire database. The only difference is you need to add the table name after the database name to the command:

mysqldump -u [username] --password=[password] [database-name] [table-name] > [filename].sql

Backing Up the Database Structure Only

If you want to back up your database and table structure only, omitting all table data from the database, this command will do the trick for you when adding the --no-data option:

mysqldump -u [username] --password=[password] --no-data [database-name] > [filename].sql

Date and Time Backups

There may be a scenario where you want to back up your MySQL databases on a schedule instead of doing it manually each time. This can be accomplished by creating scheduled tasks with Crontab in Linux.

If you need to create daily backups and need a file naming structure that differentiates each backup file, you can use this command:

mysqldump -u [username] --password=[password] [database-name] > mydb-$(date +\%Y\-\%m\-\%d).sql

This creates a database backup to a file with the name containing the current date, including year %Y, month %m, and day %d. In this case, the filename would be mydb-2022-01-08.sql.

If you need to create multiple backups at different times of each day, you can also include the current hour, minute, and second in your filename:

mysqldump -u [username] --password=[password] [database-name] > mydb-$(date +\%Y\-\%m\-\%d_\%H-\%M-\%S).sql

Restoring Your MySQL Database

When restoring your MySQL database, all you need to do is open the generated .sql backup file, and copy and paste the contents into your command prompt or MySQL Workbench if you're using that.

Before doing so, make sure you have a MySQL connection open and that you're using the correct database:

use [database-name];

Conclusion

That's all there is to it! These are the most common options I use when backing up databases and they've proven to be helpful time and time again.

If there are any other helpful backup options you know of, let us know in the comments below!