Mysqldump Backup and Restore for Beginner

MySQL is a great database system that is very stable, reliable and popular among website owners. Even it’s a stable database, it doesn’t mean that it is crash free. So, we still need to do some prevention tasks such as backup. There are many ways of backup MySQL database. We can use third party MySQL backup software such as iPerius Backup, Uranium Backup and many more. But, actually MySQL already equipped with built in backup and restore feature, called mysqldump. This tutorial is going to show you some example how to use mysqldump backup and restore feature. 

mysqldump backup and restore

How to Backup MySQL database using mysqldump

First, we will learn how to backup database using mysqldump. We can select a single database on the backup but also we can backup the entire databases in a single backup file. mysqldump is a great, powerful and effective tool to backup MySQL database. It will create .sql file that includes DROP table, Create table and INSERT INTO sql statement of the source database. So, it will be easier to restore the entire database in the future. 

In order to take backup using mysqldump, you will need MySQL username and password that has privileges to perform the backup task. For example, I use my root user and password: 12345678.

A. Backup a single database using mysqldump

The following command will backup database “test” and save the backup as “test_backup.sql”

The general syntax for backup single database using mysqldump is:

You can also provide a complete path for the backup destination for example:

B. Backup the entire databases

The following command will backup all databases in MySQL server

How to restore MySQL Database using mysqldump

To restore the database, you will need to create the database first. Otherwise you won’t be able to restore the database from the dump file. 

Create the database. See my example below to create test database on the MySQL server.

Now restore the database with this command

If the destination database does not exist yet, you will get the following error

So, we’ve learned how to perform mysqldump backup and restore via command line. There are many options you can play with. Please visit the following page for more information and examples:

Thanks for reading this mysqldump backup and restore tutorial. Please consider share this article if you think it’s useful. 

1 Trackback / Pingback

  1. MySQLBackupFTP Fair Review from End User Perspective

Leave a Reply

Your email address will not be published.