How to Backup and Restore a MySQL Database

MySQL is an relations database management system used for storing data in tabular format. If you are using MySQL server for an production use, then its necessary to create database backup for recovering from any crash. MySQL provides an utility mysqldump for taking backups. In this article you will learn, to taken databases backup in .sql format for archive format. Also we will explain various options for it.

This tutorial uses mysqldump command to backup mysql databases. The second is default mysql command for restoring databases from backup.

Backup MySQL Databases with mysqldump

You have many options for creating databases backups. read few options below. For this example we have using databse name 「mydb」.

1. Backup a single mysql database

   mysqldump -u root -p mydb > mydb.sql 
   

2. Backup a single mysql database with archive

   mysqldump -u root -p mydb | gzip > mydb.sql.gz 
   

3. Backup a single table from MySQL database

   mysqldump -u root -p mydb tbl_student > tbl_student.sql 
   

4. Backup multiple databases in mysql

   mysqldump -u root -p --databases mydb1 mydb2 mydb3 > mydb1-mydb2-mydb3.sql 
   

5. Backup all databases available in MySQL server.

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

6. Backup database structure only (no data)

   mysqldump -u root -p --no-data mydb > mydb.sql 
   

7. Backup data only from database (no table structure)

   mysqldump -u root -p --no-create-info mydb > mydb.sql 
   

8. Backup MySQL database in XML format

   mysqldump -u root -p --xml mydb > mydb.xml 
   

How to Restore MySQL Database from Backup

For restoring databases from backup is quite simple. We use mysql command for it. For example following command will restore all backup from mydb.sql to mydb database.

mysql -u root -p mydb < mydb.sql 

No need to specify database names during the restoration of all databases.

mysql -u root -p < all_databases.sql 

Conclusion

In this tutorial, you have learned to various options to backup mysql databases using mysqldump.