How do I ‘containerize’ something, part 4: Docker and MySQL

Containers, From the field, Journey to the Cloud, Miscellaneous, Next-Gen Apps, TechInfo 5 comments

This is part 4 of a series of posts tracking my efforts at taking a tiered application (WordPress) and stuffing it into containers for repeatable, automate-able deployment.

Choices, choices, choices…

So, one of the things I discovered along the way is how many different choices there might be to getting data into a new MySQL container, and I investigated / tried a few of them.

WordPress plugins

The ‘easy button,’ of course, is simply to import all my previous posts to a brand new WordPress installation through the use of one or more WordPress plugins. The problem with this, however, is it requires manual intervention, and is not easy to automate. Certainly if my goal is to create a repeatable, reliable process for updating my web site, I want to try to avoid manual steps as much as possible.

Customizing an image

I tried spinning up a new MySQL image, and manually importing the data – that worked! And, it’s surprisingly easy – if you have a dump of your database file (which I do), one command from the CLI will do it for you. You can find all kinds of great instructions here, on the page for the official Docker MySQL image.

Once you have spun up a new container with MySQL, simply run the following command from the CLI:

docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/somedatabase.sql

You could also use Docker Desktop to execute commands directly to the MySQL server, but that would require a lot more effort, and could be more complex to automate.

Now, the nice thing about this approach is that such a command could be issued as part of a script, or baked into a Dockerfile or a Compose file – but it still requires some manual effort to create and maintain, and could be prone to error. Luckily Docker has already figured out an answer to this problem… initialization scripts.

Initialization Scripts

It seems there have been some special features built into Docker that enable the automatic customization of databases upon first startup – specifically, docker-entrypoint-initdb.d. There is some documentation for this in the Official Docker Image for postgres, interestingly, as follows:

“If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.”

https://registry.hub.docker.com/_/postgres/

Nonetheless, this capability also seems to work for MySQL, MariaDB, ArangoDB, Mongo as well some others.

Testing it out

Let’s test it out! I have put together a compose file that simply pulls a fresh MySQL image, sets some parameters to create a new database called ‘wordpress’ and sets passwords using secrets. Here is the text of the compose file:

version: "3.8"

services:

  db:
    image: mysql:8.3.0
    restart: always
    environment:
      MYSQL_DATABASE: wordpress
      MYSQL_USER: wordpress
      MYSQL_PASSWORD_FILE: /run/secrets/db_password
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/db_root_password
    volumes:
      - db:/var/lib/mysql
      - ./mysql-init-files:/docker-entrypoint-initdb.d
    secrets:
      - db_password
      - db_root_password

secrets:
   db_password:
     file: ./run/secrets/db_password.txt
   db_root_password:
     file: ./run/secrets/db_root_password.txt

volumes:
  db:

(If you are following along on your own system, be sure to create the appropriate subdirectories and files as referenced above. )

tree
.
├── compose.yml
├── mysql-init-files
│   └── wordpress.sql
└── run
    └── secrets
        ├── db_password.txt
        └── db_root_password.txt

4 directories, 4 files

All we should need to do is provide “docker compose up -d:”

docker compose up -d

[+] Running 1/3
 ⠹ Network test_default  Created                                                                                                                                                  0.2s
 ⠹ Volume "test_db"      Created                                                                                                                                                  0.2s
 ✔ Container test-db-1   Started                                                                                                                                                  0.2s

Let’s see what happened in Docker Desktop.

Here we see Docker has successfully pulled a fresh MySQL image from Docker Hub, and initialized the instance, creating the user “wordpress” as indicated in the Compose file, then running the database initialization script.

If we use Docker Desktop exec to log into the server and look at the databases, we can see that indeed the database has been imported:

And finally, getting some data from one of the tables:

select * from wp_comments;

Conclusion

There you have it – as long as I have a good backup / dump of my WordPress database, spinning up a new MySQL server (or MariaDB) and getting the database into the container is wicked easy!!! I have to admit, much easier than I thought it would be, though I am a bit frustrated with how long it took me to figure it out, and I needed the guidance of a colleague. I am also a bit frustrated that docker-entrypoint-initdb.d does not seem to be well documented, except in the official image for Postgres. Perhaps my frustration is simply an artifact of the learning process, and as I learn more I will understand further how to research properly such topics on behalf of my customers. I have definitely found this to be true in the past… on the other hand, if it is still in the realm of ‘tribal knowledge’ then perhaps I can help get by raising the issue with the Docker documentation team. Nonetheless, now that I know about it, you can be sure I will be leveraging this in the future!