Creating a (persistent) MySQL Docker App

Dockerize it!

Encapsulating and abstracting code is good practice that any programmer should recognize from the days of learning object oriented programming. This concept extends to higher levels of software development when we consider containerizing software stacks. This is typically done with virtual machines and, more recently, with docker containers. I'll save the whole spiel on why this is so great for now, but maybe you'd like to read about it here.

Anyway, I decided to put some SNP data into a MySQL container so that multiple scripts from various computers could quickly access it. Here is how I did it:

Getting Docker (and other tools)

I'd already installed docker to play around with, and the process took some getting used to.

Fortunately, its now possible to get everything you need in one nice toolbox, including a GUI for managing docker containers (Kitematic). You can download it here.

Once you install it'll ask you to use a quick command launcher or the Kitematic GUI. I chose to use the GUI since it looks nice and also allows you to run commands on a CLI if you'd like. I created a docker account too, but this is optional.

The Kitematic interface:

Getting a MySQL container

OK, cool. Kitematic opens and I see a search box. Typing in MySQL yields an official image.

  • Docker uses the word 'image' to refer to static preconfigured machine. A 'container' is a instance of an image. Just like age is a instance of an integer when you type int age in many programming languages.

I click on the ellipses to get more info, and I see a link that takes me to a page describing the image.

Seems legit. I click the Create button back in kitematic.

Configuring the box

OK, pressing start and trying to connect to the MySQL database didn't work out-of-the-box as I'd hoped. Figuring out what to do next was a bit confusing and took some googling. Basically I needed to set the root MySQL password as an environment variable (typcially passed with -e with the docker CLI), and specify some persistent storage for the container so database information is saved between container runs. Here's how you can accomplish this:

  • Create the MySQL image. This essentially downloads it.
  • In the Settings tab, click General and add the following environment variable as a key: MYSQL_ROOT_PASSWORD.
  • For its value, use any password you'd like to access the database with (the user will be root-- this is OK since MySQL is the only thing encapsulated within this container!).
  • Under Volumes click Change, and set this to a location where the container can write SQL data on you host machine. Inside the guest container the volume is mapped to the default MySQL directory: /var/lib/mysql.
  • Now press Start and give the machine a second or two to fully prepare itself.
  • In the Ports tab, you'll see the typical MySQL port is used for MySQL inside the docker container (3306). This tab will also list the host IP and Port. You can use these to connect to the container like you would any other MySQL database.
  • Stopping and restarting the machine will demonstrate that the data does, in fact, stick around!

Thats pretty much it. Now time to populate a database!

"Not too shabby" --Nima