File Repair

How to Backup and Restore MySQL Databases using mysqldump Command?

Summary: Having proper backups of MySQL databases can help recover data and make the databases up and running if any problem occurs – like sudden system shutdown, hardware failures, database corruption, etc. This blog explains how to backup and restore MySQL databases using the mysqldump command. It also suggests using a MySQL repair tool if restoring the database backup fails.


Table of Contents

An Overview of mysqldump Command

Backing Up and Restoring MySQL Database Using mysqldump Command

1. How to Back Up a MySQL Database?
2. How to Restore a MySQL Dump?

What To Do If Database Backup Restore Fails?

FAQs

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.

FAQ

Q. How can I back up a single table using the mysqldump command?

A. You can back up a single table using the mysqldump command by executing the query below:

‘mysqldump db_name tbl_name > tbl_name.sql’

Replace db_name with the name of your database and tbl_name with the name of the table you want to back up.

Q. Where is the mysqldump tool located?

A. It is stored in the root/bin directory of the installation directory of MySQL Server. By default, the installation directory can be found at: C:\Program Files\MySQL\MySQL Server x.x. Here, replace ‘x.x’ with the MySQL Server version.

Q. On executing the mysqldump command using command line, I encountered an error 1064 (42000) with an error message that says, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near 'mysqldump' at line 1.” What am I doing wrong?

A. You may get the error 1064 when typing a semi-colon (;) after mysqldump command. This happens because mysqldump is an executable command and not an SQL syntax command. To resolve the issue, open a regular Windows command prompt window and then type and enter mysqldump.

76% of people found this article helpful