How to Backup and Restore Mysql Database

If you are a database administrator and responsible for managing MySQL server then you must have a knowledge of how to backup and restore the MySQL database. Regularly backing up MySQL database is a good practice for any database administrator as this will help you to prevent data loss if your server goes down. There are several ways to perform backup operations of MySQL databases.

In this tutorial, we will explain how to backup and restore the MySQL database in Linux.

Prerequisites

• A server running Ubuntu 18.04 with MySQL installed.
• An existing Database.
• You must know your database user and password.

Backup a Single MySQL Database

You can backup and restore the MySQL database using the mysqldump utility. This utility will help you to backup a local or remote MySQL database into a single file.

You can backup a single MySQL database using the following syntax:

mysqldump -v -u [Username] –p[Password] [Database_name] > [Dump_file.sql]

A brief explanation of each parameter is shown below:

Username : Specify the MySQL username.
Password : Specify the password of the MySQL user.
Database_name : A name of the database that you want to backup.
Dump_file.sql : A name of the dump file that you want to generate.

For example, to take a backup of a single database named testdb and generates a dump file named testdb_backup.sql, run the following command:

mysqldump -v -u root –proot-password testdb > testdb_backup.sql

You should see the following output:

The above command will backup the testdb database into a file called testdb_backup.sql.

Backup Multiple Databases

In some cases, you may need to backup more than one database. In this case, you can perform this operation using the –-databases option.

For example, to take a backup of a database named test1db and test2db, and generates a dump file named testdb.sql, run the following command:

mysqldump -v --user=root --password=root-password --databases test1db test2db > testdb.sql

You should see the following output:

Backup All Databases

You can also backup all MySQL databases in your system using the –all-databases option.

For example, backup all databases in your system and generates a single dump file named alldb.sql, run the following command:

mysqldump -u root –proot-password --all-databases > alldb.sql

It is also possible to backup all databases in your system and generates a separate dump file for each database. You can achieve this with the following command:

for DATABASE in $(mysql --user=root --password=root-password -e 'show databases' -s --skip-column-names); do
mysqldump --user=root --password=root-password $DATABASE > "$DATABASE.sql";
done

Backup MySQL Database with Compression

If your database is very large, you will need to compress the dump file to save the disk space. You can achieve this with gzip utility.

mysqldump -v -u root -proot-password compressdb | gzip > compressdb_backup.sql

You should see the following output:

Backup Table Of MySQL Database

You can also backup a specific table or multiple tables of MySQL database using the following syntax:

mysqldump -u root -proot-password [Database_name] [Table_name] > Dump_file.sql

For example, to take a backup of a single table named table1 from the database testdb, run the following command:

mysqldump -v -u root -proot-password testdb table1 > testdb_table1.sql

You should see the following output:

To take a backup of multiple tables named table1 and table2 from the database testdb, run the following command:

mysqldump -v -u root -proot-password testdb table1 table2 > testdb_table.sql

You should see the following output:

Backup Remote MySQL Database

It is also possible to take a backup of the database from the remote MySQL server. In order to backup the remote MySQL database, you target MySQL must be configured to allow remote connection.

For example, to take a backup of a database named remotedb from the remote MySQL server 192.168.0.100, run the following command:

mysqldump -v -h 192.168.0.100 -u root -proot-password remotedb > remotedb_backup.sql

You should see the following output:

Restore MySQL Database

In order to restore a database on the target computer, you will need to create an empty database on the target computer.

For example, to restore a database named testdb, log into the target computer and create an empty database with the following command:

mysql -v -u root -proot-password -e "create database testdb";

Output:

Next, restore the database from the dump file testdb_backup.sql with the following command:

mysql -v -u root -proot-password testdb < testdb_backup.sql

Once the command is successfully executed, you do not receive any feedback, and return to the command prompt.

Conclusion

In the above guide, you learned several methods to backup and restore MySQL database with practical examples. I hope this will helps you to simplify your database administration task and save a lot of time.