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”;
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.
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.