Tag Archives: docker

Docker MySQL with multiple databases

As discussed before I am containerizing this server for ease of development and portability. One problem is initializing the database, which is an RDS with several databases inside for different applications. Having the container initialize with the correct permissions on all databases was a headache that I at first solved by customizing the docker image, but I finally have a stable way of doing it with the upstream image.

Docker is awesome, I think we can all agree, and the RDB images like mysql and mariadb are essential for a lot of applications. Those databases have good docker images ready to go with helpful initialization support like environment variables to create the desired database, user, and setting root and user passwords. The containers also support initializing db state using a .sql or .sql.gz file in a specific directory, which is very useful for when you want to work on real data and not fixtures/fresh and empty databases. Using docker-compose, you could initialize a db container like this:

services:
  database:
    image: mysql:5.7
    volumes:
      - "./blog.sql.gz:/docker-entrypoint-initdb.d/initdb.sql.gz"
      - "./database/data:/var/lib/mysql"
    container_name: "database"

    environment:
      MYSQL_USER: "dbuser"
      MYSQL_PASSWORD: "somepassword"
      MYSQL_DATABASE: "blog"
      MYSQL_RANDOM_ROOT_PASSWORD: 1

The variables are only hardcoded for the purposes of example and you should be using secrets instead. An extra neat thing is that we’re using environment variables to tell the docker image to create the blog database, but the sqldump also has that database, and this all works as you would expect: The database is created, the dump is applied, and the user is granted access on it. There is one huge limitation though; Using the environment variables you can only create a single database in this way that the user will be granted all privileges on. My server has several apps that have separate databases, and I would like to be able to keep adding more! How can I do that?

It turns out the initialization files are loaded in alphabetical order! If only I could create an SQL file that grants access on the databases I need…

CREATE DATABASE IF NOT EXISTS archive;

SET @grantto = (select User from mysql.user where User!="root" and Host!="localhost");
SET @grantStmtText = CONCAT("GRANT ALL ON archive.* to ", @grantto);
PREPARE grantStmt FROM @grantStmtText;
EXECUTE grantStmt;

Now this is dark magic, and is likely to break in the future in strange ways. The first line speaks for itself. The second line assigns the username defined in the MYSQL_USER environment variable to the mysql user defined variable @grantto. I’m taking advantage of a known initial state for the database, as there doesn’t seem to be a way to read actual environment variables from within mysql. The only users allowed access are some internal mysql users and our user created from the environment variable. Next I just construct the grant statement as a string. The last two lines are turning that string into a mysql expression and executing it, et voila, our user has access to the archive database!

Now we just take advantage of the alphabetic nature of the init files to add this short sql file to our docker-compose.yml like so:

services:
  database:
    image: mysql:5.7
    volumes:
      - "./blog_and_archive.sql.gz:/docker-entrypoint-initdb.d/initdb.sql.gz"
      - "./grant-all.sql:/docker-entrypoint-initdb.d/zz-grant-all.sql"
      - "./database/data:/var/lib/mysql"
    container_name: "database"

    environment:
      MYSQL_USER: "dbuser"
      MYSQL_PASSWORD: "somepassword"
      MYSQL_DATABASE: "blog"
      MYSQL_RANDOM_ROOT_PASSWORD: 1

If we clear out the database data and restart the container it will start with our user having access to both “blog” and “archive”! We could tweak it even further in order to figure out which databases were created and granting on all of them, but I have a manageable amount of databases and a job, so I’m not doing it.

containerized blog

Another chapter in the ever growing book that is the story of my blog, as is good and right for any developer.

This is now coming at you from docker-compose. The blog, I mean. It used to be on a normal digital ocean droplet running on bare metal (well, low tier instance so probably a vmware instance but you know what I mean). Even worse, to my great shame it was just a normal wordpress instance. Now, it’s still running on that same vmware instance and it’s still wordpress, but it’s using roots/bedrock.

Dark Mode

bedrock (this link opens in a new window) by roots (this link opens in a new window)

WordPress boilerplate with modern development tools, easier configuration, and an improved folder structure

roots/bedrock lets you manage wordpress as a composer dependency, including themes and plugins. Essentially that means the whole blog is now a git repo with a single composer.json and composer.lock file. Of course there’s a bit more to it with .env files and persistent stuff, but essentially that’s it. This is very cool on its own, but just moving one wordpress site to using composer isn’t cool enough, so I did the same for the archive. The archive was using some plugins that don’t even exist anymore, but I manged to find and patch their successors well enough to keep it afloat, so now that’s also managed with composer. That means I can easily upgrade and patch both blogs on my machine, test them here, and if everything work quickly run the same upgrade in a predictable manner in production. Cool.

But this server doesn’t just host wordpress, it’s also running my nrk_subs app, my cv app, and new as of today, my lolz aggregator. What I really want is to run everything in nice little docker containers so I can duplicate everything locally and develop it further there in the same way I would do at work, so that’s what I did. I first built the containers I needed for the blogs and then started incorporating the other projects which were already mostly containerized. So currently, this is the docker-compose.yml that manages everything here.

version: "3.8"

services:
  database:
    build:
      context: "./database/docker"
    volumes:
      - "./storage/blog_and_archive.sql.gz:/docker-entrypoint-initdb.d/initdb.sql.gz"
      - "./database/data:/var/lib/mysql"
    container_name: "database"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "--silent"]
    command: "--default-authentication-plugin=mysql_native_password"
    env_file: .env
    environment:
      MYSQL_DATABASE: $MYSQL_BLOG_DATABASE
      MYSQL_RANDOM_ROOT_PASSWORD: 1

  blog:
    image: brbcoffee/blog-base
    env_file: .env
    depends_on:
      - database
    environment:
      DB_HOST: database:3306
      DB_USER: $MYSQL_USER
      DB_PASSWORD: $MYSQL_PASSWORD
      DB_NAME: $MYSQL_BLOG_DATABASE
      WP_HOME: $WP_HOME_BLOG
      WP_SITEURL: $WP_SITEURL_BLOG
      XDEBUG_CONFIG: remote_host=172.17.0.1
    volumes:
      - "./blog/:/var/www/blog"
      - "./storage/media/blog:/var/www/blog/web/app/uploads"

  archive:
    image: brbcoffee/blog-base
    env_file: .env
    depends_on:
      - database
    environment:
      DB_HOST: database:3306
      DB_USER: $MYSQL_USER
      DB_PASSWORD: $MYSQL_PASSWORD
      DB_NAME: $MYSQL_ARCHIVE_DATABASE
      WP_HOME: $WP_HOME_ARCHIVE
      WP_SITEURL: $WP_SITEURL_ARCHIVE
      XDEBUG_CONFIG: remote_host=172.17.0.1
    volumes:
      - "./archive/:/var/www/archive"
      - "./storage/media/archive:/var/www/archive/web/app/uploads"

  proxy:
    image: brbcoffee/proxy
    env_file: .env
    ports:
      - "80:80"
      - "443:443"
    depends_on:
      - blog
      - archive
      - cv
      - subs
    volumes_from:
      - blog
      - archive
      - lolz

  mailhog:
    image: mailhog/mailhog
#    ports:
#      - "1025:1025"
#      - "8025:8025"

  cv:
    image: brbcoffee/cv
    volumes:
      - "./storage/resume/CV.xml:/app/data/CV.xml"

  subs:
    image: "brbcoffee/subs"

  lolz:
    image: php:7.3-fpm
    environment:
      - APP_ENV=prod
    volumes:
      - "./lolz:/var/www/lolz"

  lolz-cron:
    image: brbcoffee/lolz-cron
    environment:
      - APP_ENV=prod
    volumes:
      - "./lolz:/app

As you can see a lot is managed in the .env file, and a lot of code is mounted in. The code mounting’s not necessary for everything, and I’ll be tweaking it going forward, but for now I mostly wanted to get it live so I had an MVP to work from. There are also a lot of brbcoffee/* images here, those are built in a Makefile specific to the project. I factored it out of the docker-compose.yml file in order to separate concerns a bit once the docker-compose.yml file started getting too hairy. The goal is to get rid of the droplet entirely and run the whole setup in kubernetes or something like that.

One hiccup was ssl. The rest has actually been working for weeks, but I couldn’t figure out a clean way to do ssl. In the end I decided I’m ok with not having the certificates automatically renew in version one and just fetched a wildcard with certbot and built it into the proxy container for now.

So there it is, all the stuff on brbcoffee now runs in docker containers under docker-compose. The blogs and the proxy are in the main repo, while the other services have their own repositories which are installed as git submodules. I can toggle a single .env variable and add a build arg and have node serve in dev mode, have the blog containers run xdebug, and have the python containers run a debugpy listener for fullstack local dev. Pretty cool stuff.