We could probably use a single database with multiple schemas for all our development work. That’s how we have been doing it for years. But sometimes that leads to us making compromises - like running a slightly different version of the database in dev vs production. And it leads to being somewhat conservative when trying out different database options. Now that we are using Docker for deploying our applications, it makes sense to use docker-compose to create all the services our apps use: relational databases, ElasticSearch, caches. The docker-compose file also manages mounted volumes (for preserving the data in our development databases) AND a private network for each set of containers.

Version 1

Creating a database and user

If we want to take full advantage of docker-compose’s automated workflow, we need to be able to recreate our databases - including loading dev data - automatically. The official MySQL database container image supports a really easy way to do this. If you set a handful of environment variables in your docker-compose.yml file, the first time you start the database container, it will create the database instance and set the root password to the value from MYSQL_ROOT_PASSWORD. If you include values for MYSQL_DATABASE, MYSQL_USER and MYSQL_PASSWORD, the first startup of the database will create that database and grant all privileges on that database to the specified user. Excellent! That gets us most of the way there. Now if we could only load some initial data….

Loading initial data

The MySQL image provides for this too. In the section “Initializing a fresh instance”:

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

So the database section of our standard docker-compose.yml looks like:

  mysql:
    image: mysql:8.0.23
    container_name: "db-vote"
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: vote
      MYSQL_USER: vote
      MYSQL_PASSWORD: password
    cap_add:
      - SYS_NICE  # removes error messages like: mbind: Operation not permitted
    volumes:
      - ./sql/docker/mysql-data:/var/lib/mysql
      - ./sql/docker/my.cnf:/etc/mysql/conf.d/dev.cnf
      - ./sql/docker:/docker-entrypoint-initdb.d

And our project’s sql/docker/ directory has:

  sql/
    docker/
      my.cnf
      initial_data.sql
      mysql-data/
        ....various files and directories for the actual database

And don’t forget to exclude that mysql-data directory from your image by including it in your .dockerignore file:

  .git
  .idea

  # Ignore the mysql data files that the dev mysql server creates.
  sql/docker/mysql-data

Version 2a - custom user creation

The stock setup above works great - until you have some super old code that you don’t want to have to upgrade to recent MySQL libraries. If you want to connect those projects to MySQL 8, you need to tell the database that this user will be using an older authentication plugin. So you need to issue the user create yourself. One option is to only do the user creation and privilege setting in your script, leaving the database creation and loading as above. Put the following in sql/docker/init.sql:

  create user 'vote' identified with mysql_native_password by 'password';
  grant all privileges on vote.* to 'vote';

And then remove MYSQL_USER and MYSQL_PASSWORD from the docker-compose.yml environment.

Version 2b - fully custom script

The version above works fine, but if you need additional configuration of the database - or just want to take full control of the user and database creation and data loading - you can use the following.

First, move the initial_data.sql file out of the project’s sql/docker/ directory:

  sql/
    initial_data.sql
    docker/
      my.cnf
      mysql-data/
        ....various files and directories for the actual database

Then remove MYSQL_DATABASE, MYSQL_USER, and MYSQL_PASSWORD variables from your docker-compose.yml and add another volume to mount the directory where you put initial_data.sql. I just moved the file up one level and then mounted /sql as /sql_data in the container. The docker-compose.yml should now look like this:

  mysql:
    image: mysql:8.0.23
    container_name: "db-vote"
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root_password
    cap_add:
      - SYS_NICE  # removes error messages like: mbind: Operation not permitted
    volumes:
      - ./sql/docker/mysql-data:/var/lib/mysql
      - ./sql/docker/my.cnf:/etc/mysql/conf.d/dev.cnf
      - ./sql/docker:/docker-entrypoint-initdb.d
      - ./sql:/sql_data

Then, in sql/docker/init.sql, create your user and database; then load your data from the mounted file:

  create database 'vote';
  create user 'vote' identified with mysql_native_password by 'password';
  grant all privileges on vote.* to 'vote';

  -- load initial data
  use vote
  source /sql_data/initial.sql