How to Backup and Restore MySQL Database with mysqldump

Introduction

Before we begin talking about how to Backup and Restore a MySQL database, let's briefly understand - What is MySQL?

MySQL is a widely used open-source database management system. It allows users to easily store, manage, and retrieve vast amounts of data. Whether used for personal projects or enterprise-level applications, MySQL offers high performance, scalability, and reliability.

With its straightforward setup and user-friendly interface, MySQL makes it easy to create and maintain databases, providing efficient data organization and retrieval. It's a versatile tool that is compatible with various programming languages, making it a top choice for developers. By efficiently handling data queries and modifications, MySQL helps businesses drive growth and enhance user experiences.

In this tutorial, you will back up and restore a MySQL database with Mysqldump. We will also address a few FAQs on Backup and Restore MySQL Database with mysqldump.

Advantages of MySQL

1. Open-source: MySQL is freely available and can be customized as per user needs.
2. High performance: It offers excellent speed and handles large amounts of data efficiently.
3. Scalability: MySQL allows easy scalability, making it suitable for small projects and enterprise-level applications.
4. Reliability: It ensures data integrity, providing consistent and secure database management.
5. Flexibility: MySQL is compatible with multiple programming languages and platforms, making it versatile and widely adopted.

Mysqldump command Syntax

Firstly, let's understand the syntax of Mysqldump command. It takes the following form:

mysqldump [options] > file.sql

• options - Mysql dump options
• file.sql - The dump (backup) file

Make sure the MySQL server is accessible and running to use the mysqldump command.

Backup a Single MySQL Database

Mysqldump is widely used to take a backup of a single database.

1) Use the below command if you want to take a backup of the database named database_name using the root user and save it to the file named database_name.sql.

mysqldump -u root -p database_name > database_name.sql

2) Now enter the root password when prompted and after successful authentication, the dump process will start. Based on the size of the database, it might take some time.

3) If you are logged in as a user that doesn't require a password, you can skip the -u and -p options.

mysqldump database_name > database_name.sql

Backup Multiple MySQL Databases

1) If you want to take the backup of multiple files, then you need to use the --database option that should be followed by the list of databases you want to backup. Make sure to separate the database names with a space.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

The above command will create a dump containing both databases.

Backup All MySQL Databases

1) For taking the backup of all the MySQL databases, you need to use the --all-databases.

mysqldump -u root -p --all-databases > all_databases.sql

It will create a similar file that was created by the above command, containing all the databases.

Backup All MySQL Databases to a Separate File

1) mysqldump utility doesn't provide an option to get the backup of all the databases in a single file. For that, you can use a simple FOR loop:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

The above command will create a separate file for each database with the database name as the file name.

Compressed MySQL Database Backup

1) It's a good practice to compress the output if the database size is very large. For this, simply pipe the output to the gzip utility and then redirect it to the file as shown below:

mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp

1) Adding the current date to the backup file name can be helpful when you want to keep multiple backups in the same location. Use the following command:

mysqldump  database_name > database_name-$(date +%Y%m%d).sql

It will create a file with database_name-20211025.sql format.

Restoring a MySQL Dump

1) mysql tool can be used to restore MySQL dump. Follow the below syntax:

mysql  database_name < file.sql

2) Make sure to delete a database that is already existing and create a fresh database to import the backup.

In the following example, the first line will create the database and the second line will import the database into it:

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql

Restoring a Single MySQL Database from a Full MySQL Dump

1) Use the --one-database option if you want to restore a single database from a backup file that contains multiple databases:

mysql --one-database database_name < all_databases.sql

Exporting and Importing a MySQL Database in One Command

You can skip creating a dump file from one database and then import the backup into the MySQL database using the below one-liner command:

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

The above command will pipe the output to the MySQL client on the remote host, and then it will import it into a database named remote_database_name. Make sure that the database already exists in the remote server.

Automate Backups with Cron

Follow the below steps to automate the backups of a MySQL database using cron:

1) In your home directory, create a file named .my.cnf

sudo nano ~/.my.cnf

• Copy and paste the below lines in the newly created .my.cnf file:

[client]
user = dbuser
password = dbpasswd

2) Secure the credentials file, so that only your user has access to it:

chmod 600 ~/.my.cnf

3) Now, create a directory to store the backups:

mkdir ~/db_backups

4) After that, open your user crontab file:

crontab -e

5) Now, add the following cron job. It will create a backup of the database, named mydb every day at 4 am:

0 4 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql

Make sure to replace the username with your real user name. You need to escape the % sign as well because they have a special meaning in the crontab.

6) Set another cronjob that will delete the backups that are older than 30 days. Use the following command:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Adjust the command based on your backup locations and the file names.

FAQs to Backup and Restore MySQL Database with mysqldump

Conclusion

We hope this detailed guide helped you understand how to backup and restore the MySQL database.

If you have any queries or doubts, please leave them in the comment below. We'll be happy to address them.