How To's

How to Backup and Restore MySQL Database using mysqldump

Written by William

This technical guide explains how to backup and restore the MySQL database from the command line using the mysqldump utility. It is an effective tool to backup a MySQL database. Using this utility, one can backup a local database and restore it on a remote database at the same time, with the help of a single command. The backup files created by the mysqldump are set of SQL statements that can be used to recreate the original database.

Besides, users can also use the mysqldump utility to transfer the MySQL database to another MySQL server. If you don’t back up your databases, then a software bug or a hard-drive failure can lead to a disastrous situation. To save time and energy, it is recommended that you take the precaution of regularly backing up your MySQL databases.

Mysqldump Command Syntax

Before jumping into how to use the mysqldump command, you should first know the basic syntax. You need to make sure that the MySQL server must be accessible and running.

The utility expressions take the following form:

mysqldump [options] > file.sql

  • options – The mysqldump options
  • file.sql – The dump (backup) file

How to Backup & Restore MySQL with mysqldump

The mysqldump utility can dump a database including the SQL statements which is required to rebuild the database. By default, the dump file contains the SQL commands to restore the tables and data.

To backup the MySQL database, the syntax is as follows:

sudo mysqldump -u [user] -p [database_name] > [filename].sql

  • Replace [user] with your username and password.
  • The [database_name] is the path and filename of the database.
  • The > command specifies the output.
  • [filename] is the path and filename you want to save the dump file as.

Backup a Single MySQL Database

The most common use case of the mysqldump is to back up a single database.

For example, to create a backup of the database named database_name using the user root, you have to run the following command:

mysqldump -u root -p database_name > database_name.sql

After that, you will be asked to enter the root password. After successful authentication, the dump process will start. The process can take longer depending on the database size. If you are logged in as the same user that is used to perform the export and that user does not require a password. You can omit the -u and -p options:

mysqldump database_name > database_name.sql

Backup Multiple MySQL Databases

To back up multiple MySQL databases with one command you need to use the –database option followed by the list of databases you want to backup. Each database name must be separated by space.

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

The above command will create a dump file that contains both databases.

Backup All MySQL Databases

Use the –all-databases option to back up all the MySQL databases:
mysqldump -u root -p –all-databases > all_databases.sql

Similarly, as with the previous example, the above command will create a single dump file that includes all the databases.

Backup all MySQL databases to separate files

The mysqldump does not provide an option to backup all databases to separate files but it can be easily done with a simple bash FOR loop:
for DB in $(mysql -e ‘show databases’ -s –skip-column-names); do
mysqldump $DB > “$DB.sql”;
done

The above-mentioned command will create a separate dump file for each database using the database name as the filename.

Create a Backup with Timestamp

If a user is willing to keep more than one backup at the same location, then you can add the current date to the backup filename:

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

The above command will create a file with the following format database_name-20180617.sql

Restoring a MySQL dump

Now, to restore a MySQL backup, you have to enter: mysql -u [user] -p [database_name] < [filename].sql

Make sure to include [databse_name] and [filename] in the path.

It’s likely that on the host machine, [database_name] can be in a root directory, so you may not need to add the path. Make sure that you specify the exact path for the dump file you’re restoring, including the server name.

It becomes quite easy to restore a MySQL dump using the MySQL tool. The command general syntax is as follows:

mysqld database_name < file.sql

In most of the cases, you are required to create a database to import into. In case the database already exists, you need to delete it first.

In the given example, the first command will create a database named database_name after that it will import the dump database_name.sql into it:

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

Restore a Single MySQL Database from a Full MySQL Dump

If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the –one-database option as shown below:

mysql –one-database database_name < all_databases.sql

Export and Import a MySQL Database in Single Command

Instead of creating a dump file from one database and then importing the backup into another MySQL database, you can use the below-mentioned single command:

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

Before running the above command, make sure the database already exists on the remote server.

Conclusion

This article is all about backing up your MySQL database from the command line. It should be a good starting for anyone who wants to learn how to create and restore MySQL databases using the mysqldump utility.

About the author

William

Leave a Comment