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.
[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 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.
[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.
