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
--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
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
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
%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:
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!
There are no comments yet. Start the conversation!