Using Docker to Read MySQL Backups

A few days ago, I relaunched this blog using the static site generator, Hugo. The transition was fairly smooth, but I had a bit of pain getting my content out of my old site.

I had somehow locked myself out of my old server on DigitalOcean (still not sure how, and the DO support guys were equally baffled; the theory is that I permabanned myself with fail2ban. Whoops.) Luckily, I still had access to the console from the web-based VNC tool they have, so I was able to get a backup of my blog’s database.

I spun up a new server and threw a splash page up while I got around to doing the static site thing. I had no desire to set up a temporary WordPress site if I was just going to be tearing it down a couple weeks later. That might have been a mistake.

You see, I had the blog content…but it was contained within a SQL file. A SQL file which had every version of every post I’d written in it. Needless to say, finding the content I needed was a pain in the neck.

I thought to myself with a sigh…better spin up a WordPress somewhere so you can import this SQL file and get your content. And then I signed again, because I’m lazy. It’s not a particularly hard process, but it is an annoying one.

Then I got an idea.

What if I just set up a MySQL database and used a DB browser to get at my content that way? That would work, right? Normally, setting up a MySQL database is another annoying process, but I knew a cheat code.

Enter Docker

If you know how to use Docker, you probably see where this is going. If you haven’t used Docker, don’t fret! I’ll take you through the process. First, a shopping list:

  1. Grab the Docker Toolbox - it’s for both Windows and OSX users. Linux friends, use this guide to install Docker.
  2. Get your favorite MySQL browser. I like MySQL Workbench but if you’re on OSX, Sequel Pro is pretty nice too.

While those things install, I’ll attempt to explain Docker. It’s a tool that compartmentalizes the different parts of your app into ‘containers’ - which are tiny little linux machines that install only the essentials to get a system running. This makes things lightweight, and highly portable. Once you create a container, you can start it on any server with Docker installed the the container will be identical every time.

It may sound a little wonky - I confess, it took me a few days to wrap my head around Docker, but after you get used to it, you start to see the power. It’s been especially useful working on a team - we really don’t have to worry too much about our environment.

I encourage you to read through Docker’s getting started guide, but we’re gonna use another tool that will make our lives a little easier. We’re trying to be lazy efficient here, after all.

Kitematic

Docker Toolbox comes with a handy little app called Kitematic that makes managing Docker containers a breeze. Go ahead and start it up, and you should see a message saying it’s “Starting [your] Docker VM.” It’s just booting up Virtual Box in the background so we can use Docker.

Search for MySQL, then hit the create button.

Searching for MySQL on Docker

It’ll download the image and run the container. After it’s set up, you’ll see a message like this:

Database is uninitialized and password option is not specified
  You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

As it says, we need to set one of the following options. Click on Settings near the top right, and enter something. Don’t forget to save!

Enter a password for your MySQL

Go back to the Home tab and take note of the information in the IP & Ports box near the right side of the interface:

IP and ports information

Fire up your SQL client (MySQL Workbench in my case). Enter the Docker IP for the host name, and whatever port you were assigned in for the port. The Username is still root, then enter whatever password you chose.

Connect to MySQL

Bam! You’re in! Simply create your Database, import your SQL, and get the data you need. I’m assuming you know how to do that…but if not, leave a note and I’ll be happy to explain.