Backing up and restoring mySQL db

I am in phpMyAdmin and looking to automate the backing up of my db. Looks like the way to accomplish this is via a cron script, more on this later. First, I wanted to test a couple simple hypotheses about backing up and restoring a single db. Lets face it, if you cannot restore a backup file, what is the point. Its like practicing for disaster recovery.

Steps to Creating the backup file

1. Click on the database
2. and then on the Export tab.
3. On the Export screen, choose Quick, with format of SQL, both defaults
4. Click the Go button.
5. A file download dialog appeared, click the Save button,
6. Choose where to save the file.
7. Rename the saving script if you like, default name is dbname.sql.

The download was real quick, as my db is very small, for now. I browsed to the folder to view the file, just to verify that it stored where i choose. The file is only 3 MB.

Restoring the script (actually importing).

From phpMyAdmin,

• Click on the Export tab for my db
• I clicked on Import
• Browsed to my backup (export) file and selected it.
• Leaving the default options alone, scrolled down and clicked the Go button and was presented with an error indicating a Duplicate record could not be inserted for a col that is a primary key for the initial table in my db. I did not expect to see this, but the error is obvious, the import was trying to restore to an existing table with the same values. I must be looking at something a little sideways.

My workaround was to

1. simply delete the db, since it has *corrupt data (in my disaster scenario)
2. 
   
3. Clicked on
   
4. and the re-create the db with the same name.

• Click on Databases,
  
• Type in new name

1. Click Create
   
2. Select the newly created db
3. Click on the Import tab
4. Browse to the file
5. Click Go and walla, the import worked like a charm.

There is probably a better way than for me to physically delete the corrupt db, re-create and then import into a empty db, but that will come at a later time.

An interesting side note, the importing of the restore script ran 384 queries, the script is just a bunch of create and insert queries for each of the 228 moodle 2.2 tables.

Moral of the story?

Test you disaster recovery plans when there is not an actual disaster at hand.