Backup and Restore MySQL Database Using mysqldump

As the practical experience states, many people face difficulties with backing up and restoring the data of MySQL (and other) databases. However, backups are crucial for data safety and consistency.

The current article focuses on applying the mysqldump utility to backup and restore MySQL databases. This utility is the most common tool for generating backups in several formats or restoring MySQL databases.

CodingSight - Backup and Restore MySQL Database Using mysqldump

Note: On CodingSight you can also read about other MariaDB and MySQL backup strategies.

What is mysqldump in mySQL?

Mysqldump is a command-line utility that creates logical database backups. It generates SQL statements and saves them in a TXT file, a CSV file, or an XML file. These statements will create or/and populate the tables and restore the initial database structure with all relations.

Note that a logical backup is not a physical backup with all data present in the specific database. Instead, the set of generated statements can be executed whenever you need to restore the database with its schema and data.

If your job duties have anything to do with keeping the data safe, you will certainly use it. Mysqldump is simple to use and convenient, and it is suitable for both backing up and restoring the databases. Besides, it does not consume significant CPU resources using a single thread by default. Therefore, mysqldump is suitable for even heavily loaded servers.

mysqldump Syntax Example

First, let us see a simple example of the mysqldump. The syntax of the command is the following:

mysqldump -u [username] –p [password] [configoptions] [database name] [table name] > [backupfilename.sql]

In the syntax,

  • -u [username] is a username to connect to the MySQL server.
  • -p [password] is the user’s password.
  • [configoption] is the configuration option for customizing the backups.
  • [database name] is the name of the database you want to backup
  • [table “” not found /]
    is an optional parameter for the name of the table. You may specify the table name if you want to backup it specifically.
  • “<” OR “>” – the character indicates the direction. The “>” character serves to generate the backup, and the “<” character is for restoring the backup.
  • [backupfilename.sql] is the path and name of the backup file.

Now let us examine the application of the command by examples. For demonstration purposes, I created a demo database named EltechDB. It has a table named tblemployee with dummy records.

Backup MySQL databases with mysqldump

Example 1: Full Database Backup

To generate the full backup of the eltechDB database, we run the following query in the command prompt:

mysqldump -u root -p eltechDB > C:\MySQLBackup\EltechDB\Full_EltechDB.sql

Output

The output of the query to generate the full backup of the eltechDB database

The command saves the backup file to C:\MySQLBackup\EltechDB

The mysqldump command generates MySQL queries to create objects and data:

The mysqldump command generates MySQL queries to create objects and data

Example 2: Backup All Databases with All Tables

The mysqldump utility can dump several MySQL databases for backup. It is often useful when you deal with an application that uses several databases, or you just want to keep all the necessary databases in one file.

The simplest way is to backup all databases – it covers all tables contained in them. The syntax is:

mysqldump -u root -p --all-databases > C:\MySQLBackup\AllDB\Full_backup_allDB.sql

Or, if you want to backup several specific databases, use the below syntax where you’ll specify the database names:

mysqldump -u root -p --databases db1 db2 db3 > C:\MySQLBackup\AllDB\db1_db2_db3_backup.sql

Example 3: Convert and Export Database to CSV File 

To generate a full database backup as a Full_EltechDB CSV file, run the following query in the command prompt:

mysqldump -u root -p eltechDB > C:\MySQLBackup\EltechDB\Full_EltechDB.csv

The command saves the backup file to C:\MySQLBackup\EltechDB

Open the CSV file to view the content:

Open the CSV file to view the content

Example 4: Backup of One Single Table

Run the following query to generate the backup of the employees‘ table of the eltechDB database as the tblemployees.sql file.

mysqldump -u root -p eltechDB tblemployees > C:\MySQLBackup\EltechDB\tblemployees.sql

The command saves the backup file to C:\MySQLBackup\EltechDB

Open the tblemployees.sql file to view the content:

Open the tblemployees.sql file to view the content

Example 5: Backup Database Schema Only with No Data

If you want to generate the backup of the database schema, you can specify –no-data option. Run the following query to generate the backup of the schema of eltechDBin the eltechDB_schema.sql file:

mysqldump -u root -p --no-data eltechDB > C:\MySQLBackup\EltechDB\eltechDB_schema.sql

The command saves the backup file to C:\MySQLBackup\EltechDB. Open the eltechDB_schema.sql file:

The command saves the backup file to C:\MySQLBackup\EltechDB. Open the eltechDB_schema.sql file

Now let us examine how we restore the MySQL database.

How to Restore Database Backup

The mysqldump command does not generate the schema and data. Therefore, we must use the MySQL command to restore MySQL databases.

To demonstrate this, we drop and recreate the eltechDB database first:

mysql> drop database eltechDB;
Query OK, 3 rows affected (0.16 sec)
mysql> create database eltechDB;
Query OK, 1 row affected (0.01 sec)

To restore the eltechDB database using the full backup, run the following command in the command prompt:

mysql -u root -p eltechDB < C:\MySQLBackup\EltechDB\Full_EltechDB.sql

Once the database is restored, let us view the tables and data. Connect to MySQL and run the below query to view the tables:

mysql> use eltechDB;
Database changed
mysql> Show tables;

Output:

The output of the query to view the tables

Run the below query to view the data from the database:

mysql> Select * from tblemployees;

Output:

The output of the query to view the data from the database

Backup and Restore the Database Using dbForge Studio for MySQL

The Enterprise edition of dbForge Studio for MySQL is Devart’s flagship product for developing and managing MySQL databases. It increases productivity substantially due to the numerous automating options. Now, we are going to examine generating MySQL database backups with the help of this tool.

For test purposes, I downloaded and installed the free dbForge Studio for MySQL Enterprise Trial.

Open the tool and connect to the MySQL instance. Then right-click on the sakila database > Backup and Restore > Backup database.

Open dbForge Studio for MySQL and connect to the MySQL instance. Then right-click on the sakila database > Backup and Restore > Backup database.

Database Backup Wizard opens. On the General screen, specify the following details:

  1. The hostname from the Connection drop-down menu.
  2. The database name from the Database drop-down menu.
  3. Enter the backup path into the Path textbox.

You can also specify the retention period for the database backup files and enable backup compression.

Specify the retention period for the database backup files and enable backup compression

On the Backup content screen, select the objects you want to include into the backup from the list:

On the Backup content screen, select the objects you want to include into the backup from the list

On the Options screen, configure the backup performance:

On the Options screen, configure the backup performance

On the Errors handling screen, specify the necessary mechanism. After that, click the Backup button to launch the task of backup generating:

Once the backup is complete, you see the following:

Restore the Backup Using dbForge Studio for MySQL Enterprise

To restore the database, right-click on the connection (localhost) in the Database Explorer window > Backup and Restore > Restore Database:

In the Database Restore Wizard window, specify the connection name, database name, SQL backup file, and SQL file encoding. Click Restore.

The database has been restored without any errors:

Now you can view it in the Database Explorer window of the tool:

mysqldump vs dbForge Studio for MySQL Enterprise

What is better, the Studio for MySQL or the mysqldump command? The Studio is more productive, and it has other advantages too. Have a look at the below comparison table:

mysqldump commanddbForge Studio for MySQL Enterprise
A command-line tool requiring additional skills to manage backups.A GUI tool making the backup generation straightforward.
Naming conventions require additional skills in Windows and Linux scripting.Does not require additional skills in Windows and Linux scripting.
To automate backups and manage the backup file retention periods, we must write a separate script.The GUI tool can generate the script applicable for automating backups and backup files’ retention periods.
To configure the backup properly, we must refer to the options from additional sources.The Backup Wizard provides detailed information for configuring backups.
Does not support backup compression, and we cannot configure error handling options.Supports backup compression and allows the users to configure error handling options.

Summary

This way, we’ve explained the backing up and restoring MySQL databases via both the mysqldump command and dbForge Studio for MySQL Enterprise. We’ve explored the principles and examined the use of the advanced Studio for performing the tasks. Also, we’ve compared the productivity of both variants and their easiness of use.

Hope that you’ll find this article helpful when you configure backing up and restoring databases on MySQL.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

Leave a Reply

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