Integrating a MySQL Docker container with Docker Compose
October 1, 2017 (Last updated: July 26, 2018)

This is the fourth and final post in a series of blog posts about how to run an experiment with Amazon Mechanical Turk, using psiTurk.

This post will cover how to set up a MySQL container and how to hook it up to talk to the psiTUrk container, using Docker Compose.

Setting up the MySQL Docker container

Luckily, setting up the Docker container for MySQL container is very straightforward. You don’t have to do anything! There are a variety of official Docker images that are publicly available on DockerHub, and MySQL has such an official publicly available image.

All that you have to do is configure the MySQL usernames and passwords, which we will do in a file called docker-compose.yml. As the name of the file suggests, this is where we begin to start using Docker Compose.

Using Docker Compose

Ultimately, we want the docker-compose.yml file to have the following content:

version: '3'
services:
  db:
    image: mysql:5.7
    volumes:
     - ./data/db:/var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: psiturk
      MYSQL_DATABASE: participants
      MYSQL_USER: user
      MYSQL_PASSWORD: password

  psiturk-example:
    build: .
    links:
     - db
    ports:
     - 22362:22362
    volumes:
     - ./psiturk-example:/psiturk
    tty: true
    stdin_open: true
    restart: always

This is a file that uses the YAML markup to give Docker Compose some information to work with. Specifically, it tells Docker Compose to run two services, one called db, and one called psiturk-example (you can name these whatever you want).

db service

Let’s start with the db service. The information for the db service in the YAML file says to use the latest mysql image as the base image for the Docker container. Next, it says to mount the directory /data/db from your current directory to the volume /var/lib/mysql inside the Docker container. /var/lib/mysql is where MySQL usually stores its data, so this means that the data written to MySQL will be mirrored inside the folder ~/psiturk-example/data/db on your Linode server (assuming that you run Docker Compose from the directory ~/psiturk-example).

Next, Docker Compose is instructed to always restart the db service in case it shuts down for any reason. And, finally, some environment variables are declared for use inside of the MySQL container. You will not be able to run the container without declaring these environment variables. Note that you should probably change the values of these environment variables for security reasons; don’t just use the values that are used in this blog post! ☠

psiturk-example service

Next, let’s take a look at the psiturk-example service. First, rather than specifying an image, we tell Docker Compose to build the base image for the psiturk-example container from any Dockerfile that it finds in the current directry (i.e., .). Thus, we will want to put this file in the same directory as the Dockerfile that we created in the previous post. If you were following the instructions exactly from the last post, this means that you will want to create the docker-compose.yml file in the folder ~/psiturk-example on your Linode server.

The Docker Compose file next sets up a link to the db service so that the psiturk-example container can talk to the MySQL database. We will come back to this below, because we will need to modify the config.txt file in psiTurk so that it uses this database. But let’s first finish exploring the docker-compose.yml file.

Next, Docker Compose is instructed to publish the 22362 port of the container to the 22362 port of the host computer (i.e., your Linode server), just like we did in the previous post, except that here, we’re specifying this in a file, rather than on the command line.

Moreover, just as we published the port in the previous post, we also want to map the folder ~/psiturk-example/psiturk-example on the host computer to the /psiturk folder inside of the container. This is done in the next line of the docker-compose.yml file.

Likewise, we want to ensure that a pseudo-TTY is allocated inside the container and that the STDIN pipe is kept open, which are the next two lines in the docker-compose.yml file. And, finally, Docker Compose is also instructed to restart this container if it is accidentally stopped for any reason.

Creating the file

Now that we understand what the docker-compose.yml file does, let’s actually create it. On your Linode server, make sure you’re inside the project directory. You can get there by running the command cd ~/psiturk-example. From here, you can copy and paste the following command in order to create the docker-compose.yml file (this command uses heredocs, which you can read about if you’re not familiar with them):

cat << EOF > docker-compose.yml
version: '3'
services:
  db:
    image: mysql:latest
    volumes:
     - ./data/db:/var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: psiturk
      MYSQL_DATABASE: participants
      MYSQL_USER: user
      MYSQL_PASSWORD: password

  psiturk-example:
    build: .
    links:
     - db
    ports:
     - 22362:22362
    volumes:
     - ./psiturk-example:/psiturk
    tty: true
    stdin_open: true
    restart: always
EOF

Changing config.txt

We’re almost there! Before we can actually run the experiment, we need to change the config.txt file so that it knows how to access the MySQL database. In the file ~/psiturk-example/psiturk-example/config.txt there should be a line that says:

database_url = sqlite:///participants.db

We want to change this to:

database_url = mysql://user:password@db:3306/participants

Of course, you’ll want to replace user, password, and participants with whatever values you’ve given for the environment variables MYSQL_USER, MYSQL_PASSWORD, and MYSQL_DATABASE in the docker-compose.yml file. In other words, the value for the database_url should look like this, with the values in angle brackets replaced appropriately:

mysql://<MYSQL_USER>:<MYSQL_PASSWORD>@db:3306/<MYSQL_DATABASE>

The db:3306 part refers to the db service that Docker Compose runs, and 3306 is the default port for MySQL.

You can replace this value in config.txt on your Linode server by running the following command:¹

sed -i 's/database_url = sqlite:\/\/\/participants.db/database_url = mysql:\/\/user:password@db:3306\/participants/' ~/psiturk-example/psiturk-example/config.txt

Now that the psiTurk container is able to connect to the database, we can start both containers with Docker Compose. ✨

Running Docker Compose

Again, make sure that your docker-compose.yml file is in the same directory as your Dockerfile, since Docker Compose will try to build the psiturk-example service from the Dockerfile in the current directory. Specifically, if you’ve been following along, both of these files should be in the directory ~/psiturk-example.

So, make sure you’re in that directory by running cd ~/psiturk-example. Then, you can start all of the services that are specified in the docker-compose.yml file by running docker-compose up -d. The up command creates and starts containers, and the -d flag runs them in the background.

You can see which containers are being run by Docker Compose, with the docker-compose ps command, and you can stop all services being run with the docker-compose stop command. In general, you can run docker-compose --help to see more information about the available commands.

Starting the experiment

Once you’ve started your two services with docker-compose, you can attach to the psiTurk container in order to start the experiment. docker-compose will automatically name the containers for the services that have been started based on the directory name. It will most likely name the psiTurk container something like psiturkexample_psiturk-example_1, for example.

You can attach to this container by running the following command:

docker attach psiturkexample_psiturk-example_1

It might appear as if the command is hanging and not doing anything; however, this is not the case (see here for an explanation). Just hit ENTER a second time after running the docker attach command in order to get to the shell prompt of the psiTurk container.

Then, just like we did in the previous post, you can start the psiTurk shell by running the command psiturk inside of the container. You will then see something like the following:

And from here you can create and run your experiment! One final important thing to note is that when running an experiment, you’ll want to make sure that the container is still running and that psiturk is running inside of it. After you’ve deployed your experiment via the psiTurk shell from inside of the container, you can detach from the container and leave it running in the background by hitting CTRL+p and then CTRL+q.²

That’s it for this post! As always, please feel free to comment with any questions! And, if you do sign up for a Linode account, please consider signing up using my referral link. Thanks! 🐙

Notes