Running MySQL in Docker
In a recent article on Docker in this blog, we presented some basics for dealing with data in containers. This article will present another popular application for Docker: MySQL containers. Running MySQL instances in Docker allows isolating database infrastructure with ease.
Connecting to the Standard MySQL Container
The description of the MySQL docker image provides a lot of useful information how to launch and connect to a MySQL container. The first step is to create standard MySQL container from the latest available image.
sudo docker run \ --name=mysql-instance -e MYSQL_ROOT_PASSWORD=secret -p 3307:3306 -d mysql:latest
This creates a MySQL container where the root password is set to secret. As the host is already running its own MySQL instance (which has nothing to do with this docker example), the standard port 3306 is already taken. Thus we publish utilise the port 3307 on the host system and forward it to the 3306 standard port from the container.
Connect from the Host
We can then connect from the command line like this:
mysql -uroot -psecret -h 127.0.0.1 -P3307
We could also provide the hostname localhost for connecting to the container, but as the MySQL client per default assumes that a localhost connection is via a socket, this would not work. Thus when using the hostname localhost, we needed to specify the protocol TCP, wo that the client connects via the network interface.
mysql -uroot -psecret -h localhost --protocol TCP -P3307
Connect from other Containers
Connecting from a different container to the MySQL container is pretty straight forward. Docker allows to link two containers and then use the exposed ports between them. The following command creates a new ubuntu container and links to the MySQL container.
sudo docker run -it --name ubuntu-container --link mysql-instance:mysql-link ubuntu:16.10 bash
After this command, you are in the terminal of the Ubuntu container. We then need to install the MySQL client for testing:
# Fetch the package list root@7a44b3e7b088:/# apt-get update # Install the client root@7a44b3e7b088:/# apt-get install mysql-client # Show environment variables root@7a44b3e7b088:/# env
The last command gives you a list of environment variables, among which is the IP address and port of the MySQL container.
MYSQL_LINK_NAME=/ubuntu-container/mysql-link HOSTNAME=7a44b3e7b088 TERM=xterm MYSQL_LINK_ENV_MYSQL_VERSION=5.7.14-1debian8 MYSQL_LINK_PORT=tcp://172.17.0.2:3306 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin MYSQL_LINK_PORT_3306_TCP_ADDR=172.17.0.2 MYSQL_LINK_PORT_3306_TCP=tcp://172.17.0.2:3306 PWD=/ MYSQL_LINK_PORT_3306_TCP_PORT=3306 SHLVL=1 HOME=/root MYSQL_LINK_ENV_MYSQL_MAJOR=5.7 MYSQL_LINK_PORT_3306_TCP_PROTO=tcp MYSQL_LINK_ENV_GOSU_VERSION=1.7 MYSQL_LINK_ENV_MYSQL_ROOT_PASSWORD=secret _=/usr/bin/env
You can then connect either manually of by providing the variables
mysql -uroot -psecret -h 172.17.0.2 mysql -uroot -p$MYSQL_LINK_ENV_MYSQL_ROOT_PASSWORD -h $MYSQL_LINK_PORT_3306_TCP_ADDR -P $MYSQL_LINK_PORT_3306_TCP_PORT
If you only require a MySQL client inside a container, simply use the MySQL image from docker. Batteries included!