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

ALTER comic DROP MySQL

I've decided to migrate my comic away from MySQL. I have my reasons. Lots of people have reasons.

Some people complain that MySQL isn't truly open source anymore. It's true that it does have closed-source modules now. And Oracle is not exactly forthcoming with test cases or security patches. But these things don't bother me that much, since I'm just looking for a free, easy to use database.

Some people say that MySQL is not standards-compliant. Well, this is not my main reason but certainly a factor. I've been doing SQL for a looong time, and MySQL's is weird. I've always just dealt with it. But it would be nice to not have to think about is this the MySQL way or the normal way when I'm working on something.

Some people say that the performance doesn't scale as well as other RDBMS software. I don't know. I only ever use it for basic, low-performance stuff.

Some people say that MySQL has become stagnant. Ah, this is certainly a factor for me. It seems as though Oracle doesn't have a lot of interest in making MySQL better. And why would they? It completes with their classic RDBMS, which makes them big money. Honestly, up until this point I've just been happy that they haven't killed it off altogether. Some people have forked MySQL, like MariaDB, to move the product forward.

PostgreSQL. I'm going back.

But it's the new features in PostgreSQL that really have me wanting to change. Specifically the native JSON support. Since I store and work with my comic data in JSON, it makes sense to use a database that treats JSON as a first-class data type. I know that many of the popular NoSQL databases do this, but I don't have other use cases that drive me towards a NoSQL solution.

I once used Postgres for all my projects. It was my go-to RDBMS product back around 2001. But then I hopped on the MySQL train with everyone else and rode it to Disillusionment Town. Now I'm heading back.

I'm currently working with the node-postgres client pg to convert my comic. Thanks to my prior design decisions, it is fairly easy to swap one product for another. I use a module I called data.js which encapsulates all the database details. I've swapped it out for a new pg-data.js which implements all the same functions and I'm working through the issues. The Node pg module uses a fairly different design pattern than the mysql module does, particularly for pooled connections. That's really my biggest hurdle.

And now for the comic! They haven't been very deep lately. I've been putting too much time into the GitHub Game Off for the past 2 and half weeks. But I am working on a very interesting set for later in the month.

The Game Off ends Monday! Remember to play my game: http://caseyleonard.com/ggo15!

Amphibian.com comic for 8 April 2015

Finally Backing Up

I did something today that I should have done a long time ago - set up automatic backups of my amphibian.com database.

As you may or may not know (depending on how many of my blog posts you've read before), all of the data for my web comics is stored in a MySQL database. The words coming out of the frogs mouths, their positions in each cell, and the SVG images of the frogs themselves - all stored in simple tables in MySQL. I needed to be doing backups.

I set it up with cron and mysqldump. I wrote a simple bash script that will call mysqldump to create the complete backup of my amphibian database. It looks something like this:

#!/bin/bash
fn=/path/to/backups/amphibian-$(date +%d-%b-%Y).dump
mysqldump amphibian > "$fn"

To make sure each backup file gets a unique name, I create the fn variable which includes a section made out of the formatted date, in DD-Mon-YYYY format. To the Linux date command, that is %d-%b-%Y. Then I simply call mysqldump giving it a single parameter: the database name. I redirect the output to a file of the name I made above...and that's it.

Wait, why didn't it prompt for a user name and password to dump that database? Good question! That was my initial problem - I didn't want to put the password right in the script file. I found that if I make a file in my home area named .my.cnf and have it contain a section like this,

[mysqldump]
user = username
password = pw12345

...I can use mysqldump with a default user and without being prompted for a password. As long as the .my.cnf file has 0600 (read-write only owner) file permissions, it is reasonably secure.

I set my backup script to be called once per week by cron and I'm feeling much better now. I made an additional script that automatically copies the backup files offsite to a remote server for added safety. Being prepared for catastrophic data loss will really let me sleep better tonight.

Amphibian.com comic for 19 December 2014