[HowTo] Back Up and Restore MySQL Databases with Mysqldump
[HowTo] Back Up and Restore MySQL Databases with Mysqldump
This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.
The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database.
If you don’t backup your databases, a software bug or a hard-drive failure could be disastrous. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of regularly backing up your MySQL databases.
You can also use the mysqldump utility to transfer your MySQL database to another MySQL server.
Mysqldump Command Syntax
Before going into how to use the mysqldump command, let’s start by reviewing the basic syntax.
The mysqldump utility expressions take the following form:
To create a backup of a database named database_name using the user root and save it to a file named database_name.sql you would run the following command:
You will be prompted to enter the root password. Enter it, press Enter and the dump process will start. Depending on the database size, the process can take some time.
If you are logged in as the same user that you are using to perform the export and that user does not require a password, you can omit the -u and -p options:
Backup Multiple MySQL Databases
To backup multiple MySQL database 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.
The command above will create a dump file containing both databases.
Backup All MySQL Databases
To back up all the MySQL databases you would use the --all-databases option:
Same as with the previous example the command above will create a single dump file containing all databases.
Backup all MySQL databases to separate files
The mysqldump utility doesn’t provide an option to backup all databases to separate files but we easily achieve that with a simple bash FOR loop:
The command above will create a separate dump file of all databases using the database name as the filename.
Create a Compressed MySQL Database Backup
If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown bellow:
Create a Backup with Timestamp
If you want to keep more than one backup in a same location, then you would like to add the date to the backup filename:
The command above will create a file with the following format database_name-20180617.sql
If you are using cronjob to automate your databases backups then you can also use the following command to delete any backups older than 30 days:
Of course you need to adjust the command according to your backup location and file names. To learn more about the find command check our How to Find Files in Linux Using the Command Line guide.
Restoring a MySQL dump
You can restore a MySQL dump using the mysql tool. In most cases you’ll need to create a database to import into. If the database already exist first you need to delete it.
In the following example the first command will create a database named database_name and then it will import the dump database_name.sql into it:
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 bellow:
Export and Import a MySQL Database in One Command
Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner:
The command above will pipe the output to a mysql client on remote host and it will import it into a database named remote_database_name. Before running the command, make sure the database already exists on the remote server.
The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database.
If you don’t backup your databases, a software bug or a hard-drive failure could be disastrous. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of regularly backing up your MySQL databases.
You can also use the mysqldump utility to transfer your MySQL database to another MySQL server.
Mysqldump Command Syntax
Before going into how to use the mysqldump command, let’s start by reviewing the basic syntax.
The mysqldump utility expressions take the following form:
Code: Select all
mysqldump [options] > file.sql
- options - The mysqldump options
- file.sql - The dump (backup) file
To create a backup of a database named database_name using the user root and save it to a file named database_name.sql you would run the following command:
Code: Select all
mysqldump -u root -p database_name > database_name.sql
If you are logged in as the same user that you are using to perform the export and that user does not require a password, you can omit the -u and -p options:
Code: Select all
mysqldump database_name > database_name.sql
To backup multiple MySQL database 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.
Code: Select all
mysqldump -u root -p --database database_name_a database_name_b > databases_a_b.sql
Backup All MySQL Databases
To back up all the MySQL databases you would use the --all-databases option:
Code: Select all
mysqldump -u root -p --all-databases > all_databases.sql
Backup all MySQL databases to separate files
The mysqldump utility doesn’t provide an option to backup all databases to separate files but we easily achieve that with a simple bash FOR loop:
Code: Select all
for DB in $(mysql -e 'show databases' -s --skip-column-names); do
mysqldump $DB > "$DB.sql";
done
Create a Compressed MySQL Database Backup
If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown bellow:
Code: Select all
mysqldump database_name > | gzip > database_name.sql.gz
If you want to keep more than one backup in a same location, then you would like to add the date to the backup filename:
Code: Select all
mysqldump database_name > database_name-$(date +%Y%m%d).sql
If you are using cronjob to automate your databases backups then you can also use the following command to delete any backups older than 30 days:
Code: Select all
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
Restoring a MySQL dump
You can restore a MySQL dump using the mysql tool. In most cases you’ll need to create a database to import into. If the database already exist first you need to delete it.
In the following example the first command will create a database named database_name and then it will import the dump database_name.sql into it:
Code: Select all
mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql
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 bellow:
Code: Select all
mysql --one-database database_name < all_databases.sql
Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner:
Code: Select all
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name