Categories

How to Back Up and Restore MySQL Databases with Mysqldump?

Posted by By Adam 2 years Ago
How to Back Up and Restore MySQL Databases with Mysqldump

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
--all-databases option.

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

The 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 For loop.

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
database_name-20210115.sql

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:-

For example:

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

Replace the mysql_root_password with your source MySQL root password. Also, replace the database_name_s with the source database name.

Replace the 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.cnf in 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

Replace database_user and 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 mkdir command to  
mkdir ~/db_backups
  • Open your user cron tab file:
crontab -e
  • Add the following details in the cron job that will create the backup of the database named as mydatabase  every day at 5am:
0 5 * * * /usr/bin/mysqldump -u database_user mydatabase > /home/username/db_backups/mydatabase-$(date +\%Y\%m\%d).sql

Replace 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.

Conclusion

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.