Docker Compose and App Deployment with MySQL

In this post we show how to use the mysql-server Docker image for local development. We first introduce a simple example app that starts up and tries to connect to a given db until successful. We then show how to start containers for multiple MySQL versions and use our example app to connect to them. This results in a fully self-contained way of testing multiple MySQL versions locally.

In order to run the example we require a working Docker environment and docker-compose. The full example is available here (and works out of the box for Linux):

Sources for the docker-compose example on Github

Example Application

Application deployment in cloud environments can be challenging due to dynamic changes in availability of resources. Compared to traditional settings resources are much less static and web apps (following the software-as-a-service paradigm) depend on both each other and database backends. Components in both categories can change, i.e. new resources might appear, old resources might disappear, and/or become available under new names.

A good summary of best practices for developing modern web apps is given in The Twelve-Factor App. Not all of the 12 factors are applicable to a simple example app, however, they directly fit the overall docker model. In this context we focus on the most relevant four factors:

  • Dependencies are contained in the app itself resulting in a small image
  • Configuration can easily be injected via Docker’s environment variable handling
  • Our app exposes a port to be accessed and accesses other resources via their exposed ports
  • Stdout logging will be handled by docker logs

When thinking of multiple containers depending on each other and the likely lack of central coordination, a certain flexibility when it comes to service availability is important. In practice this means that a simple wait-and-retry logic should be part of applications to make sure they work well in cloud environments.

Following the principles outlined above our example app does the following:

  • Read parameters from environment variables
  • Try connecting to db (with increasing backoff time)
  • Add a simple health check and report it is running

This is all we need to try connecting to a database.

Details

The example application is a simple go app and consists of only one file main.go. The image is available on docker hub under neumayer/dbwebapp. For the purpose of this post it is enough to simply pull the container from there. If you want to build it yourself, the full sources for the example application are available on Github as Dbwebapp.

MySQL Containers

MySQL server images are available under mysql/mysql-server on Docker Hub. For now our goal is to start multiple versions of mysql-server images and have one example app connecting to each.

The docker-compose File

Docker-compose makes it easy to start multiple Docker containers locally and provides networking out of the box. It requires a docker-compose.yml file and is usually invoked via docker-compose up.

The docker-compose file basically sets up three instances of both mysql-server and the example app. In the following we show sample code for mysql-server 8:0.

The mysql-server images are configured via an sql file setting up a db, user and password to be used from the application. This is done by mounting an .sql file as a volume (which is then executed in the container). Our instances will not allow root access. When started, the port 3308 on localhost will be mapped to the internal port 3306. In the following we show how to start a container for mysql-server:8.0 in the Docker compose file:

  mysql-server-80:
    image: mysql/mysql-server:8.0
    volumes:
        - ./docker-entrypoint-initdb.d/:/docker-entrypoint-initdb.d/
    ports:
      - "3308:3306"

The example app is configured via the dbwebapp.env file which contains the user credentials for the db. In addition we define a dependency on the mysql-server service (which only means that the mysql-server image will be started first, it provides no further guarantees). We inject an additional environment variable DBHOST to point to the mysql-server container (this uses the DNS that is provided by docker/docker-compose). As such our app is fully configured via environment variables as outlined earlier. Once started, we map port 8080 to the internal port 8080.

  dbwebapp-80:
    env_file:
      - dbwebapp.env
    environment:
      - DBHOST=mysql-server-80
    image: neumayer/dbwebapp
    ports:
      - "8080:8080"
    depends_on:
      - mysql-server-80

An overview of the full setup is given here:

The full example can now be spun up via docker-compose up. Docker-compose first starts all six containers in no particular order. The app images will start, wait to get access to the mysql-server images and report success when they do:

dbwebapp-80_1      | 2017/10/18 10:32:49 Pinging db mysql-server-80.
dbwebapp-80_1      | 2017/10/18 10:32:49 Connected to db.
dbwebapp-80_1      | 2017/10/18 10:32:49 Starting dbwebapp server.

Run the Full Example

The project is organised as follows:

|-- dbwebapp.env
|-- mysql-server
|   `-- docker-entrypoint-initdb.d
|       `-- db.sql
|-- docker-compose.yml
`-- README.md

Further, dbwebapp.env contains configuration for the example app. Similarly, the docker-entrypoint-initdb.d folder contains the startup SQL script used for the mysql images. The docker-compose.yml file puts it all together. It is fully self-contained, a simple docker-compose up will run the containers.

Outlook

We showed how to run multiple MySQL containers locally and how to access them from applications. Although we presented only a simple example, our images are flexible enough to not only work locally but also in a cloud environment.

Further we want to be clear that our examples are not feasible in a production setting without adjustments. We have no focus on the security of the MySQL instances themselves, the distribution of secrets to our application, or general network-level security. Most of these security questions should be addressed by the design of your cloud environment or production setting.

References and Further Reading

The Twelve-Factor App

Sources for the docker-compose example on github

Dbwebapp on docker hub

Dbwebapp on github

MySQL server on docker hub

docker-compose-dot

About Robert Neumayer

Software engineer with a passion for programming and automation. I have hands-on experience in all steps of the development process, particularly focusing on streamlining deployment processes and supporting agile teams. I am working on release engineering, devops integration, and deployment of MySQL products across the MySQL engineering organisation.

2 thoughts on “Docker Compose and App Deployment with MySQL

  1. Good day. If there any way run mysql container by root user ? In my case all containers run by root inside and mapped (namespaced) by local user on real host.

    1. Hi.

      The mysqld process inside the containers drops privileges to run under the user specified in /etc/my.cnf (the default value is user=mysql). It is possible to specify a custom my.cnf to adjust all config values. This file needs to be mounted to /etc/my.cnf via a docker volume. We advise against running mysqld as root user without evaluating the security implications.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload the CAPTCHA.