Quickly backup and restore your MySQL database

It's very important that you have a backup plan of your MySQL database. Loosing even a single day's worth of data can be frustrating and critical to your business. If you have shell (command-line) access to your database server, here is a very easy and quick backup method: Use mysqldump.

Creating the backup

To create the dump, use this command from your shell/command-line:

mysqldump -uusername -ppassword –default-character-set=utf8 -N database > backup.sql

Where:

• username is your MySQL username.
• password is your MySQL password.
• database is the name of the database you want to backup.
• backup.sql is the full path to the mysql dump file that will contain the backup.

The above command is very efficient and takes very little time to execute. Notice that the character set specified is UTF8. Even if your database is encoded in another character set, I recommend using UTF8 as I found it to work fine with any database without having to make any guesses.

Restoring the database from your backup

Restoring a database from a backup file is pretty easy. First, empty the database to avoid error messages, then run this command:

mysql -u username -p password –default-character-set=utf8 database < backup.sql

Where:

• username is your MySQL username.
• password is your MySQL password.
• database is the name of the database you want to restore.
• backup.sql is the full path to the mysql dump file that you want to restore from.

You can also use the above command to copy your database from one server to another. This command is very efficient as well and executes in a very short time.

That's it! Now you have no excuses! Start making a regular backup of your database from today.