In this tutorial, we will learn How to Back Up and Restore MySQL Databases with Mysqldump.
Files created by the
mysqldump is a set of SQL statements that can be used to recreate the original database.
mysqldump can also create files in XML and CSV format.
We can also use this command to transfer your MySQL database to another MySQL server. If we don’t create a backup of our databases, if a software bug occurs or a hard drive failure could be disastrous. Take backup on regular basis to avoid any unexpected data loss or MySQL damage.
Mysqldump Command Syntax
Let’s review the syntax of the mysqldump command:
mysqldump [options] > file_name.sql
options– The mysqldump options
file.sql– The dump (backup) file
To use the mysqldump command the MySQL or MariaDB services must be running.
Backup a Single MySQL Database
For example, in the below command we are creating the backup of a database.
- If you are not a root user then you have the run the command with username. This command only works, if the user have permission to access the database.
mysqldump -u user -p database_name > database_name.sql
If you are a root user then, it is not required to mention the username.
mysqldump database_name > database_name.sql
You will be prompted to enter the root password. After successful authentication, the dump process will be started. Depending on the size of the database, the process can take some time.
Backup Multiple MySQL Databases
Sometimes we are required to take backup of multiple databases. We can use
--databaseoption with the list of databases that you want to backup and separate them by using space.
mysqldump -u root -p --databases database_1 database_2 > databases_full.sql
The above command will create a backup file containing both databases.
Backup All MySQL Databases
If we want to create the backup of all the MySQL or MariaDB databases, we use
mysqldump -u root -p --all-databases > all_databases.sql
It will create a single backup file for all the databases.
Backup all MySQL databases to separate files
mysqldump utility doesn’t provide an option to backup all the databases in separate files. We can easily perform this task with the help of
ALL_DBS=`mysql -e 'show databases' -s --skip-column-names` for DB in $ALL_DBS; do mysqldump $DB > "$DB.sql"; done
The command above will create a separate dump file for each database using the database name as the filename.
Create a Compressed MySQL Database Backup
If the size of the database is too large. We use
gzip utility to compress the databases.
mysqldump database_name | gzip > database_name.sql.gz
Create a Backup with Timestamp
Sometimes we have to create a backup of the website in the same location, we can add date and time to the backup file:
mysqldump database_name > database_name-$(date +%Y%m%d).sql
The above command will create the backup file in the following format
Restoring a MySQL dump
We can restore the backup of MySQL or MariaDB database by using the
mysql tool. To restore the database use the following command:
mysql database_name < backup_file.sql
In most cases, you need to create a database to restore the backup file. If the database already exists then first you need to delete it.
In the following commands, we will create the database and import the data of the backup file into the newly created database.
mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql
Restore the compressed MySQL Database
We can directly restore the compressed database without decompressing it.
In the below example, we will restore the compressed database directly. Replace the
compressed.sql.gz with your compressed file and replace
new_database with the database name in which you want to restore:
gunzip < compressed.sql.gz | mysql new_database
Restore a Single MySQL Database from a Full MySQL Dump
If we want to restore a single database from the backup file which contains multiple database backups. We use
--one-database argument to restore the single database as shown below:
mysql --one-database Linuxpanda_db1 < all_databases.sql
This command first searches for the
Linuxpandaa_db1 database in MySQL database server, If the match is found then it extracts the database from the backup file and restores the database.
Export and Import a MySQL Database in One Command
Instead of creating the backup file from one database and then importing the backup into another MySQL or MariaDB database:
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name
The Destination server must allow remote access. If you guys have no idea how to allow remote access, checkout this link:-
mysqldump -u 'root' -p'mysql_root_password' database_name_s | mysql -h 192.168.131.111 -u 'root' -p'destination_mysql_root_password' database_name_d
mysql_root_password with your source MySQL root password. Also, replace the
database_name_s with the source database name.
192.168.131.111 with the remote server IP address, also change the
destination_mysql_root_password with destination MySQL root password and replace the
database_name_d with the destination database.
The above command first creates the backup file then directly export and restore it on the remote server.
Automate Backups with Cron
One of the most popular uses of the cron command is to schedule automatic backup. We have to create a cron job that includes the command to run in cron at the specified time.
To create a cron job follow these steps:
- Create a file named
.my.cnfin your user home directory:
sudo vim ~/.my.cnf
- Copy and paste the following text into the .my.cnf file.
[client] user = database_user password = database_password
database_password with the database user and user’s password.
- Change the permission of the file, so that only the user can access the file.
chmod 600 ~/.my.cnf
- Create a directory where you want to store the backup. Use
- Open your user cron tab file:
- Add the following details in the cron job that will create the backup of the database named as
mydatabaseevery day at 5am:
0 5 * * * /usr/bin/mysqldump -u database_user mydatabase > /home/username/db_backups/mydatabase-$(date +\%Y\%m\%d).sql
database_user with your actual username. We are using
% percent signs, it is used to mention the date in the backup file name. It means whenever cron creates the back up it will save with the date on which it was created.
- To delete old backup we can also create another cron to delete the back older than 7 days:
find /path/to/backups -type f -name "*.sql" -mtime +7 -delete
You have to set up the cron according to your backup location and file name.
In this tutorial, we have learned how to backup and restore the MySQL or MariaDB databases. We also learn to compress the large database. It should be a good starting for anyone who wants to learn how to create and restore MySQL or MariaDB databases.
Read Also: How to Start, Stop, or Restart Apache
If anyone has any queries related to this Restore MySQL Databases with Mysqldump tutorial, Let me know in the comments.