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.