Use mysqldump to Backup and Restore a MySQL Database

If you’re interested in the process to backup and restore a MySQL database, then mysqldump is the tool to use.

In this guide, you’ll learn:

• what mysqldump is
• how to use it to backup your database in different ways
• how to restore your database from a backup file

Let’s get into the guide.

What is mysqldump?

The mysqldump utility is a command-line tool that comes with the MySQL database and lets you backup and restore your database. It produces SQL files that can be used to restore the database.

You can use it to backup one or more databases, or backup tables within a database. It can also be used to restore these backup files into another database.

The database backup process in MySQL Workbench uses mysqldump behind the scenes. The official documentation is here.

Command Summary

Here’s a summary of the commands to run to perform different tasks using mysqldump.

You can read further down in the guide for more information and steps.

MySQLDump Command Syntax

Here’s the syntax for the mysqldump command:

This includes:

• mysqldump: the command to run
• options: a list of options for running the command, such as username and database, which we’ll explain further below
• > to indicate that an output is sent somewhere
• file.sql is used to store the output of the mysqldump file. This is the file that can be used to restore your database.

In order to backup a database, we’ll need:

• Access to a MySQL database server (this can be on your own computer or elsewhere)
• A database that has been created

In these examples, we’ll be using a MySQL database server installed on the local computer, which has a few databases on it already:

Let’s see some examples of using this mysqldump tool.

Backup a Single MySQL Database

We’ll use the mysqldump tool to backup a single database.

Let’s say we want to backup the database called “desc_test” on the MySQL database server. Our command would look like this:

This includes a few things:

• -u root means the username is root.
• -proot means the password is root (which was the default password used when I set up MySQL database on MAMP). Note that there is no space between the -p and the password. If you enter a space, mysqldump will prompt for a password and think that the “root” is the database name,
• gravity_books is the database on the server
• db_gravity_books.sql is the file that the database is backed up to.

Where do we run this command?

First, open a command line window using Command Line (Windows) or Terminal (Mac).

Here’s what the terminal looks like on Mac:

Enter this command (or the command you want to use with your database details)

The command should run. You won’t see a progress bar or confirmation. You’ll just be taken to the command prompt again.

If you used this command above, which included the password on the command line, you’ll see this message:

A better way to do this is to enter the password at a prompt. To do this, use the parameter of -p without the password.

You’ll then be asked for a password.

Enter the password and press enter. The process will then run.

Bash Command Not Found

When you run mysqldump, you may get this error:

If so, you can add the mysqldump to your PATH environment variable by using this command:

You can see your PATH variable by running this command:

Here’s what mine shows:

Next, add the folder that has the flyway files to the PATH variable by running the export command and appending this to the current PATH variable:

You should be able to run the mysqldump command now.

Viewing Your Exported File

You can open the SQL file that you specified in the mysqldump command in a text editor or IDE.

You’ll see a bunch of comments at the top of the file, then some SQL generated to create your tables:

-- MySQL dump 10.13  Distrib 5.7.30, for osx10.12 (x86_64)
--
-- Host: localhost    Database: gravity_books
-- ------------------------------------------------------
-- Server version 5.7.30
 
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `address`
--
 
DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (
  `address_id` int(11) NOT NULL,
  `street_number` varchar(10) DEFAULT NULL,
  `street_name` varchar(200) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  KEY `fk_addr_ctry` (`country_id`),
  CONSTRAINT `fk_addr_ctry` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

We’ll look at how to use this file to import data later in this guide.

How to Backup Multiple Databases

You can also use mysqldump to backup multiple databases.

To do this, you use the –databases option of the mysqldump command and specify the databases, each separated by a space.

In this example, we’re backing up the databases called “gravity_books”, “ecom”, and “booktracker”.

The db_multiple_dbs.sql file will have the statements to create and populate these databases. It includes a Create Database command for each of them and a Use command before running the Create Table commands.

How to Backup All Databases

You can backup all databases on the server using mysqldump.

To do this, add the –all-databases option to the command.

For example:

This command will take longer than single databases because there is more to export (depending on the amount of data and number of databases).

You may get this error when you run this command:

This happens because a table was removed but the underlying files were not updated. There are some steps on this page to resolve it.

How to Backup a Specific Table

You can use mysqldump to backup a specific table in a database by specifying the table name after the database name.

This command will backup the author table in the gravity_books database.

The SQL file generated will contain the Create Table and Insert statements for this table.

How to Backup Multiple Tables

To backup multiple tables using mysqldump, enter the table names separated by a space after the database name.

For example, this command will backup both the author and book tables in the gravity_books database.

The SQL file generated contains the commands for both tables.

How to Create a Compressed Backup

When you create a backup file, sometimes the file can be quite large.

Fortunately you can compress the backup file using gzip.

To do this, add “| gzip” to your command.

For example, here’s the command to backup a single database:

This results in a file that is 3.2 MB.

To compress this backup:

This results in a file that is 1.1 MB.

Quite a significant saving!

How to Create a Backup with a Timestamp

If you’re backing up a database regularly, you may want to store multiple files in the same folder.

You can give them different filenames in each command, but there is an easier way.

You can dynamically insert the date into your command using some variables.

To backup a single database, for example, with the current date in the filename, add this string to your filename:

So the command would be:

If I run this on 7 Oct 2021, the filename I’ll see is “db_gravity_​​20211007.sql”.

You can add the hour, minute, and second if you prefer as well.

This will create a filename of “db_gravity_​​20211007_053608.sql”

How to Backup the Database Structure Only (No Data)

If you want to backup just the database structure, and no data, you can do that with MySQL. This may be useful if you want to replicate the structure on another database, or reset it back to an empty database.

You simply add the –no-data parameter to the command.

For example, to export only the gravity_books structure, this is the command.

The file will be much smaller as there is far less SQL to include.

How to Restore a Backup

You can restore a backup made that has been made with mysqldump by using the mysql command.

Yes, we don’t use mysqldump for restoring.

To restore one of the files, run the mysql command which looks like this:

For example, to import the gravity_books database from a recently exported file, I would run this command:

If you run this command and the gravity_books database does not exist, and there is no Create Database command in your SQL file, you’ll get this error:

So, you’ll need to create the database first. You can do this with a simple Create Database command in MySQL Workbench or the command line:

CREATE DATABASE gravity_books;

You could try to include a create database command in your SQL file. Add this to your SQL file:

CREATE DATABASE gravity_books;
USE gravity_books;

However, you’ll get the same error. It seems the database check is done by the mysql command before the SQL script is run.

How to Restore a Single Database From a File

If you’ve used mysqldump to backup multiple (or all) databases into a single file, you can restore a single database from this file instead of all databases.

To do this, add the –one-database command to your mysql command.

This example will restore just the gravity_books database from a dump file that contains all databases.

How to Export and Import in One Command

You can use the mysqldump and mysql commands together to import and export in one command.

This can be useful if you want to copy a database to your own computer or another server.

For example, we can copy the gravity_books database to a database called gravtiy_books_copy.

First, create the database gravity_books_copy in the location where you want the database to be (e.g. the same server, a different server).

CREATE DATABASE gravity_books_copy;

Next, we can use this command, which will “pipe” the output of mysqldump into the mysql command.

Replace the parameters with what you want to use.

This will backup the gravity_books database, and send that output to the mysql command. The mysql command will look for the host of localhost, and import the data into the gravity_books_copy database.

You may get an error when running this, as the command may ask for two passwords:

To get around this, you can specify your password for mysqldump inside a configuration file.

The mysqlpassword.cnf file will contain just this information:

When you run the command, this is what you’ll see.

You can then check your server to see that the new database of gravity_books_copy is populated.

Conclusion

The mysqldump utility is a useful tool for generating backups of a MySQL database. It can be used to backup a single table, multiple tables, and multiple databases. There is a range of options that can be specified to adjust how the command is run.