How to Backup (Dump) and Restore MySQL Database

This article shows the proper way to dump and restore the MySQL database using mysqldump and mysql commands.

If you are using MySQL or MariaDB databases, you need to understand how to take backup and restore data. In addition, you need to keep regular backups of the database, which will help you restore your data if any system failure corrupts your application or the data.

MySQL and MariaDB include command-line tools that you can use to quickly and easily dump and restore databases.

For example, the mysqldump tool is used to export the contents of a database to a text file. At the same time, the mysql client app can be used to import data from a text file into a MySQL/MariaDB database.

How to Backup (Dump) MySQL Database

The mysqldump command line utility exports databases to SQL text files. In other words, it is used for taking the MySQL database dump.

The syntax for the mysqldump command is given below:

mysqldump -u [username] -p [database_name] > [filename.sql]

Where:

• username is your MySQL/MariaDB username.
• -p prompt for password for this user.
• database_name is the name of the database you want to backup.
• filename.sql is the full path to the SQL dump file containing the backup.

It is also essential to be mentioned that database procedures, functions, views, and events are dumped at the time of backup.

How to Backup a Single MySQL Database

Let’s assume we have already created a database called my_wordpress. Of course, in the command below, replace this with the name of the database you want to export.

Then, according to the syntax, mysqldump will create the backup file at the current location.

mysqldump -u root -p my_wordpress > my_wordpress_backup.sql

After the command is run, enter your MySQL root password.

This creates a MySQL dump file named my_wordpress_backup.sql that contains all of SQL statements to create tables and restore data into an existing database. If errors occur during the export process, mysqldump will print them to the screen.

The command will produce no visual output, but you can inspect the contents of my_wordpress_backup.sql using the head command to check if it is a legitimate MySQL dump file.

head -n 5 my_wordpress_backup.sql

-- MariaDB dump 10.19  Distrib 10.5.9-MariaDB, for Linux (x86_64)
-- Host: localhost    Database: my_wordpress
 
-- Server version    10.3.27-MariaDB-0+deb10u1

How to Backup Multiple MySQL Databases

To backup multiple MySQL databases with one command you need to use the --databases option followed by the list of databases you want to backup. In addition, you can put the result file in a location of your choice.

Run the following command to create a MySQL dump file my_backup.sql in /home/backups directory of two databases named my_wordpress and ftpusers.

mysqldump -u root -p --databases my_wordpress ftpusers > /home/backups/my_backup.sql 

How to Backup All MySQL Database at Once

Use the --all-databases option to back up all the MySQL databases:

mysqldump -u root -p --all-databases > all_db_backup.sql

How to Create a Compressed MySQL Database Backup

If the database size is huge, it is a good idea to compress the output. To do that, pipe the output to the gzip command, and redirect it to a file as shown below:

mysqldump -u root -p my_wordpress | gzip > my_wordpress_backup.sql.gz

Restore MySQL Database from a MySQL Dump File

To restore a backup created with mysqldump, you must use the mysql command.

mysql -u [username] -p [database_name] < [filename.sql]

Where

• username is your MySQL/MariaDB username.
• -p prompt for password for this user.
• database_name is the name of the database which you want to restore.
• filename.sql is the full path to the SQL dump file that contains the backup.

Restore a Single MySQL Database

You can restore all tables from the MySQL dump file of any particular database.

The restore is performed with the below command. We are again specifying a user to be prompted for a password and passing the my_wordpress_backup.sql file into the database my_wordpress.

mysql -u root -p my_wordpress < my_wordpress_backup.sql

Once this completes, the database should be available in the running instance of MySQL. The MySQL dump file you imported from will still exist, so you can either store that securely at this point or delete it if it is no longer required.

Restore All MySQL Databases

You can restore all databases of any MySQL server to another MySQL server from the MySQL dump file.

mysql -u root -p < all_db_backup.sql

Conclusion

Now you can keep a regular backup of your database files and restore the data when required by following the above steps.

This tutorial also applies when you wish to migrate to a new server. Back up the database on your old server, and upload the MySQL dump file to the new server.

Next, create a destination database on the new server, then execute the uploaded MySQL dump file in the new database. In addition to, you should also recreate users and permissions from the old MySQL server to the new one.

For more detailed information, here’s the link to the official documentation. If you have any questions or feedback, feel free to leave a comment.