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

Published:21 June 2022 - 8 min. read

Nicholas Xuan Nguyen Image

Nicholas Xuan Nguyen

Read more tutorials by Nicholas Xuan Nguyen!

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 tutorial first if you haven’t installed MySQL yet.

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.

mysql -u root -p
Connect to MySQL as the root user.
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.

/* 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.

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
Querying the records from both databases and tables

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

Exiting MySQL
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.

mysqldump [OPTIONS] database_name > backup_file.sql

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

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

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.

mysqldump -u root -p -B menagerie1 menagerie2 > menagerie1_menagerie2_db_backup.sql
mysqldump command for multiple database backup
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.

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.

mysqldump -u root -p --all-databases > all_db_backup.sql
mysqldump command for all database backup
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.

ls -l *.sql

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

Listing all MySQL database backup files
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
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.

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.

mysqldump -u root -p menagerie1 pets > menagerie1_pets_tb_backup.sql
Creating a backup of a specific table
Creating a backup of a specific table

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

ls -l *.sql
Verifying the backup file creation
Verifying the backup file creation

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

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

mysql -u root -p

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

# 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
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!

source menagerie1_menagerie2_db_backup.sql

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

Restoring databases from backup
Restoring databases from backup

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

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

You’ve now successfully restored your databases from backup.

Querying data from the restored databases
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.

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

\. menagerie1_pets_tb_backup.sql

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

SHOW TABLES;
SELECT * FROM pets;

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

Listing the table and querying data
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 and save it on a network drive or, even better, to cloud storage like the Amazon S3.

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks

Looks like you're offline!