Backing up and restoring databases using mysqldump

Why backup?

As most people will know it is very important to make sure to keep regular backups as if your server, site or other service goes down you could lose it for good unless you have a working backup.

This means that you shouldn't just be creating backups but you need to test that they actually work and they can be restored when you need them.

Backing up using mysqldump

Backing up a database using mysqldump is quite simple. Whilst there are a number of different options that can be explored the command below will back up the chosen database to a file called dbbackup.sql.

mysqldump -u root -p [password] [database_name] --skip-lock-tables --single-transaction > dbbackup.sql

The dbbackup.sql file will contain all the necessary SQL commands to re-create the database elsewhere.

It is worth looking through the different options for those times when you may only need to backup specific tables in a database or you wish to backup all databases rather than just one.

Restoring your database

Restoring your database is a much simpler affair than backing it up is. However, you do need to remember to create the database first before importing all the content that you haev already backed up.

Once you have created the database you can run the following command to import your database:

mysql -u root -p [password] [database_name] < dbbackup.sql

Final thoughts

This only covers the very basics of backing up a database using mysqldump. There are many options that can be used to backup different sets of data in different ways. These should be considered if you use these instructions a lot.