---
title: "How To Back-Up & Restore Databases via the Mysqldump Command"
description: "Learn how to use the mysqldump command to back-up and restore databases in MySQL to save the day and make sure you don’t lose any data!"
canonical: "https://adamtheautomator.com/mysqldump-command/"
---

# How To Back-Up & Restore Databases via the Mysqldump Command

> Learn how to use the mysqldump command to back-up and restore databases in MySQL to save the day and make sure you don’t lose any data!

Source: https://adamtheautomator.com/mysqldump-command/

---

ATA Learning

Tap to hide

[

ATA Learning

](/)

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Search for:  

*   [](https://twitter.com/adbertram)
*   [](https://github.com/Adam-the-Automator)
*   [](https://www.linkedin.com/company/adam-the-automator-llc)
*   [](/feed/)

![How To Back-Up & Restore Databases via the Mysqldump Command](https://adamtheautomator.com/wp-content/uploads/2022/06/How-To-Back-Up-Restore-Databases-via-the-Mysqldump-Command.jpg)

# How To Back-Up & Restore Databases via the Mysqldump Command

[![](https://secure.gravatar.com/avatar/2788bb1a3f735603f81eca51d68daec56a9d97e805a10268fb2c20afcc76b81b?s=192&d=mm&r=g)Nicholas Xuan Nguyen](https://adamtheautomator.com/author/nicholas-xuan-nguyen/)21 June 20227 min. read

Categories: [IT Ops](/category/it-ops/)

Tags:[Databases](/tag/databases/)[MySQL](/tag/mysql/)

Table of Contents

*   [Prerequisites](#prerequisites)
*   [Creating the Test Database](#creating-the-test-database)
*   [Backing Up Entire Databases using the mysqldump Command](#backing-up-entire-databases-using-the-mysqldump-command)
*   [Specific Database Backup](#specific-database-backup)
*   [Specific Database Backup](#specific-database-backup-1)
*   [All Database Backup](#all-database-backup)
*   [Inspecting the Database Backup Files](#inspecting-the-database-backup-files)
*   [Backing Up Tables using the mysqldump Command](#backing-up-tables-using-the-mysqldump-command)
*   [Restoring from Backup](#restoring-from-backup)
*   [Restoring Entire Database](#restoring-entire-database)
*   [Restoring Database Table](#restoring-database-table)
*   [Conclusion](#conclusion)

Data is valuable to people and organizations. Imagine losing a company database with customer or payroll information. Backing up databases is vital as a safety net from possible data loss—and that’s not up for debate.

An excellent way to backup MySQL databases and tables is the `mysqldump` command. Don’t let the _dump_ in the name fool you; `mysqldump` is a versatile and powerful tool that comes with your MySQL installation.

Ready? Read on and become a MySQL database backup pro!

## Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, you must have a Linux computer with MySQL already installed. This tutorial uses an Ubuntu 20.04 computer with MySQL 8.0.29. Follow the [_**How to Install MySQL on Ubuntu**_](https://adamtheautomator.com/install-mysql-on-ubuntu/) tutorial first if you haven’t installed MySQL yet.

Related:[How to Install Ubuntu 20.04 \[Step-by-Step\]](https://adamtheautomator.com/install-ubuntu/)

This tutorial also assumes that you have already opened a terminal session to the Linux computer.

## Creating the Test Database

Before you can back up a database, you need to have at least one! In this section, you’ll create two sample databases with tables and data for testing.

> _This tutorial does not cover in detail the creation of MySQL databases, tables, and inserting data. The instructions or commands in this section are for demonstration purposes only._

1\. First, connect to the mysql command shell as root. Type the password when prompted, and press Enter.

```bash
mysql -u root -p
```

![Connect to MySQL as the root user. ](https://adamtheautomator.com/wp-content/uploads/2022/06/image-483.png)

Connect to MySQL as the root user.

2\. Next, copy the below code, paste them to the mysql prompt, and press Enter. Refer to the inline comments to understand what each statement does.

This code will create two databases named menagerie1 and menagerie2, create a pets table inside each database, and insert a sample record inside each table.

```sql
/* Create two databases called menagerie1 and menagerie2 */
CREATE DATABASE menagerie1;
CREATE DATABASE menagerie2;

/* Create a new table called pets on the menagerie1 database */
CREATE TABLE menagerie1.pets (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

/* Create a new table called pets on the menagerie2 database */
CREATE TABLE menagerie2.pets (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

/* Insert a new sample record into the pets table on menagerie1*/
INSERT INTO
    menagerie1.pets
VALUES
    (
        'Puffball',
        'Diane',
        'hamster',
        'f',
        '1999-03-30',
        Null
    );

/* Insert a new sample record into the pets table on menagerie2*/
INSERT INTO
    menagerie2.pets
VALUES
    (
        'Good Boy',
        'Karen',
        'dog',
        'm',
        '2000-02-18',
        NULL
    );
```

3\. Now, confirm that the sample data exists inside the tables in your databases by running the below statements.

```sql
SELECT * FROM menagerie1.pets;
SELECT * FROM menagerie2.pets;
```

You should see a result similar to the screenshot below. As you can see, the table in both databases has one record each.

![Querying the records from both databases and tables](https://adamtheautomator.com/wp-content/uploads/2022/06/image-484.png)

Querying the records from both databases and tables

4\. Lastly, exit the mysql shell by typing quit or \\q and press Enter.

![Exiting MySQL](https://adamtheautomator.com/wp-content/uploads/2022/06/image-485.png)

Exiting MySQL

## Backing Up Entire Databases using the mysqldump Command

You now have MySQL databases, and it’s time to see the `mysqldump` command in action!

`mysqldump` is a versatile command-line tool because it lets you back up one or more databases or specific tables only. Also, this tool can generate SQL files that you can use to recreate databases or tables.

There are two ways to back up entire MySQL databases; specific databases and all databases.

> _IMPORTANT! Each backup method you’ll learn in the succeeding sub-sections overwrites existing backup files with the same filename. Be cautious not to overwrite existing backups in production._

### Specific Database Backup

This method creates a backup of one database at a time, which is useful when you want to dump each database into separate backup files. The basic syntax for this method is shown below.

```bash
mysqldump [OPTIONS] database_name > backup_file.sql
```

For example, run the below commands to back up the `menagerie1` and `menagerie2` databases individually.

```bash
# OPTIONS
## * -u root = connect to the MySQL server as the root account.
## * -p = prompt for the account password.
mysqldump -u root -p menagerie1 > menagerie1_db_backup.sql
mysqldump -u root -p menagerie2 > menagerie2_db_backup.sql
```

![mysqldump command for single database backup](https://adamtheautomator.com/wp-content/uploads/2022/06/image-486.png)

mysqldump command for single database backup

### Specific **Database Backup**

The `mysqldump` command lets you specify one or more databases to back up. As you can see from the syntax below, you must specify the `--databases` or `-B` argument followed by the list of databases in the same line.

> _Note: This method backs up every specified database into one backup file._

```bash
mysqldump [OPTIONS] --databases database1 [database2] > backup_file.sql
# OR
mysqldump [OPTIONS] -B database1 [database2] > backup_file.sql
```

For example, run the below command to create a single backup for the `menagerie1` and `menagerie2` databases.

```bash
mysqldump -u root -p -B menagerie1 menagerie2 > menagerie1_menagerie2_db_backup.sql
```

![mysqldump command for multiple database backup](https://adamtheautomator.com/wp-content/uploads/2022/06/image-487.png)

mysqldump command for multiple database backup

### **All Database Backup**

When you need to backup all databases at once without listing each database name, you must specify the `--all-databases` or `-A` argument, as shown below.

```bash
mysqldump [OPTIONS] --all-databases > backup_file.sql
# OR
mysqldump [OPTIONS] -A > backup_file.sql
```

To back up all databases into a single backup file, run the below command in your terminal.

```bash
mysqldump -u root -p --all-databases > all_db_backup.sql
```

![mysqldump command for all database backup](https://adamtheautomator.com/wp-content/uploads/2022/06/image-488.png)

mysqldump command for all database backup

> _Note: This method will backup ALL existing MySQL databases, including the default ones, such as sys, mysql, information\_schema, and performance\_schema. Be mindful when using this backup method._

### Inspecting the Database Backup Files

If you followed all instructions, you now have two backup files with the `*.sql` extension in your current directory. To verify, run the below command in the terminal.

```bash
ls -l *.sql
```

As a result, you should see the four backup files on the list.

![Listing all MySQL database backup files](https://adamtheautomator.com/wp-content/uploads/2022/06/image-489.png)

Listing all MySQL database backup files

Now, open one of the backup files in your preferred text editor, such as _nano_ or _Visual Studio Code_. Choose the file with the smallest size, which, in this case, is the `menagerie1_menagerie2_db_backup.sql`.

Can you recognize the formatting in the below screenshot? Yes, those are SQL statements. The `mysqldump` command generates an SQL script that creates the databases, tables, and records.

![Viewing the database backup file content](https://adamtheautomator.com/wp-content/uploads/2022/06/image-490.png)

Viewing the database backup file content

## Backing Up Tables using the mysqldump Command

Apart from taking backups of entire databases, the `mysqldump` command allows you to backup specific tables. Taking specific table backups can be more manageable and uses less storage space.

Other benefits of table backups are portability and granularity of data restoration. A smaller backup file size is more portable, and you can restore table backups into a new or existing database.

The syntax for backing up tables is as follows. After specifying the database name, you must append the list of tables to back up. If you do not provide the table names, the `mysqldump` command will dump all tables in the database.

```bash
mysqldump [OPTIONS] database [table1 table2...] > table_backup.sql
```

Now that you know this syntax follow these steps to back up a database table.

1\. Run the below command to back up only the pets table in the menagerie1 database. This command creates a file named menagerie1\_pets\_tb\_backup.sql.

```bash
mysqldump -u root -p menagerie1 pets > menagerie1_pets_tb_backup.sql
```

![Creating a backup of a specific table](https://adamtheautomator.com/wp-content/uploads/2022/06/image-491.png)

Creating a backup of a specific table

2\. Verify that the backup file creation is successful by listing the \*.sql files.

```bash
ls -l *.sql
```

![Verifying the backup file creation](https://adamtheautomator.com/wp-content/uploads/2022/06/image-492.png)

Verifying the backup file creation

3\. Optionally, open the backup file in a text editor or display the contents on the screen.

```bash
# Open in the text editor
nano menagerie1_pets_tb_backup.sql
# OR display on the screen
cat nano menagerie1_pets_tb_backup.sql
```

![Viewing the database table backup file](https://adamtheautomator.com/wp-content/uploads/2022/06/image-493.png)

Viewing the database table backup file

## Restoring from Backup

Having backups of your database or tables gives you peace of mind. Knowing that if your database becomes corrupted or someone accidentally deleted it, you have a point in time of the data you can restore.

At this point, the `mysqldump` command has already done its job to back up the database and tables. Restoring those backups will now be through the `mysql` command shell.

### Restoring Entire Database

Earlier in this tutorial, you created a backup of the `menagerie1` and `menagerie2` databases to a file named `menagerie1_menagerie2_db_backup.sql`. In this section, you’ll simulate multiple database losses and restore them from your backup file.

1\. First, connect to the MySQL shell.

```bash
mysql -u root -p
```

2\. Next, drop the menagerie1 and menagerie2 databases to simulate losing multiple databases.

```sql
# Delete the databases
DROP DATABASE menagerie1;
DROP DATABASE menagerie2;

# List remaining databases
SHOW DATABASES;
```

As you can see below, the database deletion was successful. Both databases no longer exist on the MySQL server.

![Deleting databases](https://adamtheautomator.com/wp-content/uploads/2022/06/image-494.png)

Deleting databases

3\. Now, restore the databases from the menagerie1\_menagerie2\_db\_backup.sql file. To do so, execute the source or \\. command followed by the backup filename (full path or relative path), and watch the magic happen!

```sql
source menagerie1_menagerie2_db_backup.sql
```

You’ll see a similar output on the screen to the screenshot below.

![Restoring databases from backup](https://adamtheautomator.com/wp-content/uploads/2022/06/image-495.png)

Restoring databases from backup

4\. Finally, confirm that the databases and their data were successfully restored.

```sql
SELECT * FROM menagerie1.pets;
SELECT * FROM menagerie2.pets;
```

You’ve now successfully restored your databases from backup.

![Querying data from the restored databases](https://adamtheautomator.com/wp-content/uploads/2022/06/image-496.png)

Querying data from the restored databases

### Restoring Database Table

In this example, you’ll be restoring the `pets` table to the `menagerie1` database. You previously created a backup of this table to a file named `menagerie1_pets_tb_backup.sql`.

1\. First, to simulate the loss of the table, run the below commands to delete the pets table from menagerie1.

```sql
# Switch to the menagerie1 database
USE menagerie1;
# Delete the pets table from menagerie1
DROP TABLE pets;
# List the tables (if any) from menagerie1
SHOW TABLES;
```

The output below shows that no tables exist in the menagerie1 database after dropping the pets table.

![Deleting a table](https://adamtheautomator.com/wp-content/uploads/2022/06/image-497.png)

Deleting a table

2\. Now, execute the source or \\. command followed by the table backup file. In this example, the backup file is menagerie1\_pets\_tb\_backup.sql.

```sql
\. menagerie1_pets_tb_backup.sql
```

3\. Finally, check that the pet table restoration was successful by running the below commands in the mysql shell.

```sql
SHOW TABLES;
SELECT * FROM pets;
```

As you can see below, the restoration recreated the pets table and data.

![Listing the table and querying data](https://adamtheautomator.com/wp-content/uploads/2022/06/image-498.png)

Listing the table and querying data

## Conclusion

Backing up your MySQL databases is an essential task that an administrator should ensure to happen regularly. The `mysqldump` command makes creating backups of entire databases or individual tables effortless and uncomplicated.

This guide has only covered the basic options of using the `mysqldump` commands. You can further customize them to suit your needs. Perhaps you should set up a cron job to run the backups on schedule.

In real-life usage, your database is hundred if not thousands of times bigger, and you don’t want your disk filled with SQL files. In that case, you would want to [compress your dump file](https://www.tutorialspoint.com/best-way-to-compress-mysqldump) and save it on a network drive or, even better, to cloud storage like the Amazon S3.

Related:[Targeted Backups With Amazon S3 Glacier Storage Classes](https://adamtheautomator.com/amazon-s3-glacier/)

Share this article

[Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fmysqldump-command%2F&text=How%20To%20Back-Up%20%26%20Restore%20Databases%20via%20the%20Mysqldump%20Command)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fmysqldump-command%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fmysqldump-command%2F)

## Related Posts

![](https://adamtheautomator.com/wp-content/uploads/2024/01/mysql-indexes.jpg)

### [How To Use MySQL Indexes Through Examples](/mysql-indexes/)

Supercharge your database with MySQL indexes. Optimize queries, boost speed—master the art effortlessly, and elevate performance in this tutorial!

![](https://adamtheautomator.com/wp-content/uploads/2022/04/How-to-Install-MySQL-on-Ubuntu.jpg)

### [Install MySQL on Ubuntu: Get Your Database Server Up and Running](/install-mysql-on-ubuntu/)

Want a robust, secure database for your projects? Discover how to install MySQL on Ubuntu and implement essential security measures with this guide.

![](https://adamtheautomator.com/wp-content/uploads/2023/12/mysql-stored-procedures.jpg)

### [Advanced Database Techniques with MySQL Stored Procedures](/mysql-stored-procedures/)

Learn how to use MySQL Stored Procedures effectively to make database operations more efficient in this ATA Learning tutorial!

## Categories

*   [IT Ops](/category/it-ops/)
*   [Cloud](/category/cloud/)
*   [DevOps](/category/devops/)
*   [Home Ops](/category/home-ops/)
*   [Information Security](/category/infosec/)
*   [Software Development](/category/software-development/)

## Site

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)