How To Backup and Restore MySQL Databases with Mysqldump


Introduction

mysqldump is a utility that performs backups for MySQL database. These backup files comprise of SQL statements that can be run in a certain operating system to recreate the original database. The mysqldump utility can also backup a local database and at the same time restore it on a remote database using a single command. It also features an easy to use interface.

If you fail to back up the databases, you could experience a hard-drive/system failure or software bug that can be terrible. It is important to perform regular back up of your MySQL database.

In this tutorial, we will show you a step-by-step process on how to backup and restore MySQL databases using the mysqldump utility.

Command Syntax for Mysquldump

Before we start, we are going to show you how to run various commands in mysqldump. All mysqldump utility functions take the form:

mysqldump [options] > file.sql

How To Backup MySQL Database

1. Backup A Single Database

We shall start by creating a backup for a database calleddatabase_name with the userrootthen save it in the directory database_name.sql.  To accomplish this we’ll execute the command below:

$ mysqldump -u root -p database_name > database_name.sql

You will be asked to provide the root password. Type it then click Enter to initiate the dump. The time it will take to complete this process depends on the size of your database.

You can omit the value -u and -p if you are logged in the system as the same user you have used to execute the export in which case you may not be prompted to enter a password.

$ mysqldump database_name > database_name.sql

2. Backup Multiple MySQL Databases

If you want to create a backup for multiple databases, you can use the--databaseoption including the databases list you wish to backup. Make sure each database name is separated by a space.

$ mysqldump -u root -p --database database_name_a database_name_b > databases_a_b.sql

This command will create a backup or one dump file with databases listed.

3. Backup All MySQL Databases

To create a backup for all databases, you need to use the option --all databases.

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

This command will also create a backup with all databases.

4. Backup All Databases To Different Files

By default, mysqldump doesn’t have a backup option for all databases to different files. However, this can be achieved with a bash FOR loop as shown below:

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

This command will create a separate backup file for all MySQL databases that utilize the name of the database as the filename.

Creating A Compressed Database Dump File/Backup

If your database is very big, it’s wise to create compressed output. To do so, feed the output to gzip utility, then redirect the output to a specific file using the command below:

$ mysqldump database_name > | gzip > database_name.sql.gz

Creating A Dump File using Timestamp

If you wish to create multiple backup files in the same location, add the date to the filename of your backup as shown below:

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

This command will create a dump file with the format database_name-20180817.sql

On the other hand, if you are utilizing cronjob systematize your databases, you can execute the command below to erase any backups that have existed for more than 30 days.

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

Make sure you edit the command based on your backup file names and location.

How To Restore MySQL Dump

To restore a MySQL backup file, use the MySQL tool. First, create a new database where you will import the files into. If there is an existing database, start by deleting it.

In our example, we will create a new database called database_name then import the backup file database_name.sql into it. To achieve this, run the commands below:

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

Restoring A Single Database For MySQL

In case you have your MySQL databases backed up with -all databases option and you want to restore a single MySQL database file from the dump file, you will need to use the option--one database as shown in the command below:

$ mysql --one-database database_name < all_databases.sql

Exporting And Importing A Database An A Single Command

To import and export a dump file into one MySQL database, run the command below:

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

This command will channel the details to MySQL client hosted on a remote machine and import it into another database calledremote_database_name. But before you run this command, ensure the database is present on the remote server.

Conclusion

That’s it. We have covered the basics to get you started. We hope this guide has helped you understand how to backup and restore MySQL databases using the mysqldump utility.

Check out these top 3 Best web hosting services

Was this article helpful?