Configuring MySQL in a Docker Container

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on May 23, 2018.

In recent weeks I’ve been focusing on Docker in order to get a much better understanding of the containerized world that is materializing in front of us. Containers aren’t just for stateless applications anymore and we’re seeing more cases where MySQL and other databases are being launched in a containerized fashion, so it’s important to know how to configure your MySQL container!

In docker hub, you will see an option for this by doing a volume mount from the Docker host to the container on /etc/mysql/conf.d. But the problem is that the container image you’re using may not have an !includedir referencing the conf.d directory, much like the latest version of mysql community, as you will see below.

[[email protected] ~]# docker run --memory-swappiness=1 --memory=2G -p 3306:3306 --name=mysql1 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:5.7.22
[[email protected] ~]# docker exec -it mysql1 cat /etc/my.cnf | grep -i include
[[email protected] ~]#

This means that if you use the prescribed method of placing a config file in /etc/mysql/conf.d in the container, it’s not going to be read and will have no impact on the configuration of the underlying MySQL instance.

You might think that the next step would be to attach to the container, modify the my.cnf file (after installing a text editor) and add the !includedir in your my.cnf file, but this goes against the Docker / containerization philosophy. You should be able to just launch a container with the appropriate arguments and be off to fight the universe’s data battles. So in this case, I would propose the following workaround:

Instead of using /etc/mysql/conf.d, we can look at the mysql option file reference and realize there is more than one place we can put a config file. In fact, it looks like the next place MySQL will look for configuration is /etc/mysql/my.cnf and if we check our recently deployed container, we’ll see that it doesn’t use /etc/mysql.

[[email protected] ~]# docker exec -it mysql1 ls /etc/mysql
ls: cannot access /etc/mysql: No such file or directory

We can mount a volume with a my.cnf file to this directory on the container and it should pick up whatever configuration we supply, as demonstrated below.

[[email protected] ~]# docker stop mysql1
mysql1
[[email protected] ~]# docker rm mysql1
mysql1
[[email protected] ~]# cat /mysqlcnf/mysql1/my.cnf
[mysqld]
server-id=123
[[email protected] ~]# docker run --memory-swappiness=1 --memory=2G -p 3306:3306 -v /mysqlcnf/mysql1:/etc/mysql --name=mysql1 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:5.7.22
d5d980ee01d5b4707f3a7ef5dd30df1d780cdfa35b14ad22ff436fb02560be1b
[[email protected] ~]# docker exec -it mysql1 cat /etc/mysql/my.cnf
[mysqld]
server-id=123
[[email protected] ~]# docker exec -it mysql1 mysql -u root -ppassword -e "show global variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 123   |
+---------------+-------+
[[email protected] ~]#

Another option for doing this is overriding the my.cnf file in /etc/ with our own version. You can do this with a mount as noted in the MySQL reference for Persisting Data and Configuration Changes, but in that case you will be overwriting other items that might be included in the my.cnf as part of the Docker build. This may or may not be your intention depending on how you want to deploy your containers.

Conclusion

Be aware of the container image you’re using and what configuration options are available to you. Some forks will include a !includedir reference to /etc/mysql/conf.d, some won’t. You may want to overwrite the entire my.cnf file by volume mounting to a copy of the my.cnf on the Docker host. Or you may just want to supplement the configuration with a second configuration file in /etc/mysql. The important things are to test, to make sure your configuration is properly read by the MySQL container, and to establish confidence in the configuration method used before deploying in your environment.