Restoring a MySQL Database

If you don't test your backup and prove that it works, you don't have a backup.

I've been backing up my Amphibian.com database for some time now, but haven't really had to restore it yet. That changed today. Thankfully, it worked.

This process started because I finally decided to get back to work on switching from MySQL to Postgres. I started this months ago but got sidetracked. To really make some progress, I wanted to set up a virtual machine that will run both the MySQL and Postgres versions of my database on Ubuntu 14. Then I can make my code changes and test against the VM until I know it's all working. I had both databases installed on a physical server in my home, but that limited me to only being able to work on it while I was at home. With a virtual server, I can run it on my laptop as well.

What does this have to do with restoring a MySQL database? I'm getting to that.

After installing VirtualBox on my laptop, installing Ubuntu 14 on a virtual machine, and installing MySQL on Ubuntu 14, it was time to test out one of my Amphibian backup files.

I make the backups using mysqldump on the "real" server (see one of my previous posts for more details):

# mysqldump amphibian > amphibian_data.dump

I copied the backup file off my server and onto my new virtual machine. Now I had a fresh install of MySQL and a .dump file of the Amphibian database. All I had to do was create the amphibian database on my new installation and have MySQL read the contents of the .dump file into it.

# mysql -uroot -p<password>

mysql> create database amphibian;
Query OK, 1 row affected (0.02 sec)

# mysql -uroot -p<password> amphibian < amphibian_data.dump

It ran for a few minutes, but when it finished I had a perfect copy of my Amphibian.com database. If I had to restore my actual server, I am now sure that it will work.

And of course, after spending all that time getting ready to work on the MySQL-to-Postgres transition, I didn't have any time left to actually work on it. But I'll be ready next time. Next time.

Amphibian.com comic for 31 August 2015