Backing up MySQL Databases: mysqldump

This article assumes familiarity with Linux shell operations.

Simple Database Backup

Usually, when I back up a MySQL database, I just want to create a backup copy of the entire database to one file that can be easily restored or transported to a different server. The simplest way to accomplish this is with the mysqldump command.

Here’s a simple command to backup an entire database:

shell> mysqldump db_name > backup-file.sql

You can restore the backup to MySQL like this:

shell> mysql db_name < backup-file.sql

Passwords

You probably have your database secured with usernames and passwords. The simple syntax above will yield an error if you don’t submit your credentials. On a shared server, I like to pass my username in the command line, but not the password. This just ensures my password doesn’t get recorded on any logs. (Check with your system administrator if this is a concern.)

Command to backup:

shell> mysqldump -u username -p db_name > backup-file.sql

Restore:

shell> mysql -u username -p db_name < backup-file.sql

Other Options

The mysqldump command has many options to do more specific tasks, such as backup only certain tables, backup all databases, as well as enable lots of detailed functionality. I won’t bother to detail them here, because for my purposes I don’t use that stuff 95% of the time. However, all this and more is very well-documented on MySQL’s website:

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Test Your Backups!

As a final note…always test your backup system before you need to use it! If you haven’t tested your ability to restore from your backups, then you have no idea whether your data is safe. Just test it!