FB

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.

Task Command
Backup a single database
mysqldump -u [username] -p [database_name] > [output_file.sql]
Backup multiple databases
mysqldump -u [username] -p –databases [database_names] > [output_file.sql]
Backup all databases
mysqldump -u [username] -p –all-databases > [output_file.sql]
Backup a specific table
mysqldump -u [username] -p [database_name] [table_name] > [output_file.sql]
Backup multiple tables
mysqldump -u [username] -p [database_name] [table_name] [table_name] > [output_file.sql]
Compress the backup file
mysqldump -u [username] -p [database_name] | gzip > [output_file.sql.gz]
Add current date to filename
mysqldump -u [username] -p [database_name] > db_​​$(date +%Y%m%d).sql
Add current date and time to filename
mysqldump -u [username] -p [database_name] > db_​​$(date +%Y%m%d_%H%M%S).sql
Backup structure only, no data
mysqldump -u [username] -p [database_name] –no-data > [output_file.sql]
Restore a backup
mysql -u [username] -p [database_name] < [input_file.sql]
Restore a single database from all databases
mysql -u [username] -p –one-database [database_name] < [input_file.sql]
Export and import in one command
mysqldump –defaults-extra-file=mysqlpassword.cnf -u [username] [database_name] | mysql -h [target_server] -u [username] -p [target_database_name]

 

MySQLDump Command Syntax

Here’s the syntax for the mysqldump command:

mysqldump [options] > file.sql

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:

mysql database list

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:

mysqldump -u root -proot gravity_books > db_gravity_books.sql

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).

macos terminal

Here’s what the terminal looks like on Mac:

MacBook-Pro:~ BB$

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

MacBook-Pro:~ BB$ mysqldump -u root -proot gravity_books > db_gravity_books.sql

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:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

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.

MacBook-Pro:~ BB$ mysqldump -u root -p gravity_books > db_gravity_books.sql

You’ll then be asked for a password.

MacBook-Pro:~ BB$ mysqldump -u root -p gravity_books > db_gravity_books.sql
Enter 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:

-bash: mysqldump: command not found

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:

echo $PATH

Here’s what mine shows:

MacBook-Pro: BB$ echo $PATH
/Library/Frameworks/Python.framework/Versions/3.9/bin:/Applications/MAMP/bin/php/php7.4.9/bin:/Applications/MAMP/bin/php/php7.2.8/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin

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:

export PATH=$PATH:/Applications/MAMP/Library/bin

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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@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.

mysqldump -u root -p –databases gravity_books ecom booktracker > db_multiple_dbs.sql

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:

mysqldump -u root -p –all-databases > db_all_dbs.sql

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:

mysqldump: Got error: 1146: Table ‘mysql.innodb_index_stats’ doesn’t exist when using LOCK TABLES

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.

mysqldump -u root -p gravity_books author > db_gravity_author.sql

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.

mysqldump -u root -p gravity_books author book > db_gravity_author_book.sql

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:

mysqldump -u root -p gravity_books > db_gravity_books.sql

This results in a file that is 3.2 MB.

To compress this backup:

mysqldump -u root -p gravity_books | gzip > db_gravity_books.sql.gz

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:

$(date +%Y%m%d)

So the command would be:

mysqldump -u root -p gravity_books > db_gravity_​​$(date +%Y%m%d).sql

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.

mysqldump -u root -p gravity_books > db_gravity_​​$(date +%Y%m%d_%H%M%S).sql

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.

mysqldump -u root -p gravity_books –no-data > db_gravity_structure.sql

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:

mysql -u [username] -p [database_name] < [filename.sql]

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

mysql -u root -p gravity_books < db_gravity_​​20211007.sql

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:

ERROR 1049 (42000): Unknown database ‘gravity_books’

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.

mysql -u root -p –one-database gravity_books < db_all_dbs.sql

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.

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

Replace the parameters with what you want to use.

mysqldump -u root -p gravity_books | mysql -h localhost -u root -p gravity_books_copy

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:

MacBook-Pro:~ BB$ mysqldump -u root -p gravity_books | mysql -h localhost -u root -p gravity_books_copy
Enter password: Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
root
mysqldump: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: YES) when trying to connect

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

mysqldump –defaults-extra-file=mysqlpassword.cnf -u root gravity_books | mysql -h localhost -u root -p gravity_books_copy

The mysqlpassword.cnf file will contain just this information:

[mysqldump]
# The following password will be sent to mysqldump
password=root

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

MacBook-Pro:~ BB$ mysqldump –defaults-extra-file=mysqlpassword.cnf -u root gravity_books | mysql -h localhost -u root -p gravity_books_copy
Enter password:
MacBook-Pro:~ BB$

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents