How to Backup and Restore MySQL Databases using mysqldump Command?

An Overview of mysqldump Command

Mysqldump is a command-line utility that generates backup (logical backup) files containing SQL statements to reproduce a database to its original state. It then dumps one or multiple databases to SQL Server or CSV/XML file format.

The syntax of mysqldump is as follows:

'mysqldump [options] > file_name'

Here options are different parameters you can use in the mysqldump command. And, file_name is the dump (i.e. backup) file that contains a database backup.

While mysqldump is easy to implement, it has certain downsides:

• It can take a considerably long time to perform the restore operation. The time can exceed if you need to restore large databases.
• The command only works if MySQL server is running and in an accessible state.

Backing Up and Restoring MySQL Database Using mysqldump Command

In this section, we will discuss how to use the mysqldump command to back up one or more databases and how to restore the database from a MySQL dump file.

How to Back Up a MySQL Database?

Let's discuss how you can back up a single, specific, or all the databases.

Backing Up a Single Database

Execute the following query to create a backup of a single db using the mysqldump command:

'mysqldump -u root -p db_name > dump.sql'

Here, we are creating a backup of the database named 'db_name' and dumping it to a SQL file format named 'dump.sql.' Also, we have specified username (-u) and password (-p) options to authenticate the database. You will need to enter the root password to continue with the dump process.

However, you might not require a password if you're logged in to the server – as the same user credentials are used to export the data. In that case, run the mysqldump query without -u and -p options.

'mysqldump db_name > dump.sql'

Backing Up Specific MySQL Databases

You can also back up a few specific MySQL databases with the mysqldump command by running the following query:

'mysqldump -u root –p --databases db1 db2 > dump.sql'

For backing up specific databases, we have specified databases and separated each database name with space. The above query is creating a dump file with two databases (i.e. db1 and db2 in our case).

Backing Up All MySQL Databases

To back up all your MySQL databases, use the --all-databases option as shown in the command below:

'mysqldump -u root -p --all-databases > dump.sql'

This command creates a single dump file with all the databases. Once you've created the dump file, let's now try to restore the database from that dump file.

How to Restore a MySQL Dump?

To restore a database using mysqldump, you'll first need to create an empty database and then follow these steps:

• Drop and recreate your database.

'mysql > drop db_name
mysql > create db_name'

• Restore the database using the below command:

'mysql -u root -p db_name < dump.sql'

Now, verify if the restored database contains all the objects.

'mysql > use db_name;
mysql > show tables;'

What To Do If Database Backup Restore Fails?

If you fail to restore a corrupt or inaccessible database using mysqldump, try using a MySQL repair tool to regain access to the database. Stellar Repair for MySQL is a powerful tool you can use to repair your database and restore it to its original form. 

Check out the video to understand the software working.

Was this article helpful?

YES2

NO1

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience