Configuring MySQL in a Docker Container

Posted in: MySQL, Open Source, Technical Track

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.

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

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 adding 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 is going to look for configuration is going to be /etc/mysql/my.cnf and if we check our recently deployed container, we’ll see that /etc/mysql isn’t used.

[root@centos7-1 ~]# 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.

[root@centos7-1 ~]# docker stop mysql1
mysql1
[root@centos7-1 ~]# docker rm mysql1
mysql1
[root@centos7-1 ~]# cat /mysqlcnf/mysql1/my.cnf
[mysqld]
server-id=123
[root@centos7-1 ~]# 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
[root@centos7-1 ~]# docker exec -it mysql1 cat /etc/mysql/my.cnf
[mysqld]
server-id=123
[root@centos7-1 ~]# 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   |
+---------------+-------+
[root@centos7-1 ~]#

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.

email

Interested in working with Peter? Schedule a tech call.

About the Author

Internal Principal Consultant
Peter Sylvester is one of the Internal Principal Consultants in the Open Source Database Consulting Group at Pythian. He has been with Pythian since January of 2015 and has been working with MySQL since 2008. Apart from work, Peter is an avid ice hockey player to stay in keeping with the stereotypical Canadian lifestyle, playing typically no less than twice a week!

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *