; Date: Wed Mar 10 2021

Setting up MySQL on Docker is fairly simple, and the MySQL team has created a flexible Docker image that can be used in many ways. Databases, like MySQL, serve as the data persistence and retrieval layer of many applications we might deploy.

Having executed a few Docker containers we began to get a taste of what Docker can do. For example we were able to launch a containerized Ubuntu instance under Docker in a minute or so. Installing a full Ubuntu instance on, say, VirtualBox can take an hour. But let's do something which has a practical benefit, which is to install a database server.

In the past, database installation required building a dedicated server and hiring a database administrator to manage the system. Today there are several excellent open source database systems, as well as several more closed source commercial database systems, that we can choose from. But what we'll learn in this tutorial is the ease of using Docker to install a MySQL database, and getting started with it.

Installing MySQL under Docker requires either a docker run command or Docker Compose file describing how to initialize the container. This uses environment variables and MySQL command-line options to customize database configuration. Docker settings then describe mounting a data directory into the container, as well as memory and CPU allocation.

Successfully deploying a database involves two main considerations: Security, and Configuration. We have to protect the database from intruders, while insuring our application has access. We have to be sure the data is persisted outside the Docker container, while configuring the database to tune its performance. We'll only touch on these things in this tutorial, so keep them in mind.

It's often recommended that a Docker service serve a single purpose. For a database that means a given database server would contain the tables for a single application. This differs from traditional database deployment where there is The Database containing All Data from several applications, overseen by a grey-bearded Database Guru. With Docker, everybody is managing their own database, and following the one-application-per-database-server recommendation means you could end up with dozens of database server instances.

A Docker container for MySQL

There are thousands of MySQL-related containers published on Docker Hub, which gives us lots of choices for a MySQL container to use. Fortunately the standard MySQL container is very good, so let's use that. We should mention that the MariaDB database is compatible with MySQL, being derived from the same code, and it is a good alternative to using MySQL itself. You can consider using them interchangeably.

In this example we'll use the mysql/mysql-server image.

Download that image as so:

$ docker pull mysql/mysql-server:5.7
5.7: Pulling from mysql/mysql-server
501550cfd5c0: Pull complete 
eb8e4ab4d0ac: Pull complete 
3050337d0754: Pull complete 
970507e942eb: Pull complete 
Digest: sha256:14b0991b719af3b46113690c152f02d81e2cfb1276bb6cd5056b636585d9d9bc
Status: Downloaded newer image for mysql/mysql-server:5.7
docker.io/mysql/mysql-server:5.7

As of this writing, MySQL 5.7 is the current GA release, but we could also use MySQL version 8. The image name is mysql/mysql-server, which means it is the mysql-server container published by the mysql account. The portion after the : (colon) is the tag. Tags can be any string, and in this case the MySQL team tags their images with the release number. So, to use MySQL version 8, use mysql/mysql-server:8.0 to specify the container.

The four lines with a hex-coded string followed by Pull complete detail the layers comprising this image. Docker images are built with layers, where each layer adds new things to the image. For example one layer will be the base operating system, the next will add a few packages, another some configuration files, and so forth. The line saying Digest prints out the SHA256 identifier for this image.

We can immediately run the image as so:

$ docker run --name=mysql --env MYSQL_ROOT_PASSWORD=f00bar  mysql/mysql-server:5.7
[Entrypoint] MySQL Docker Image 5.7.33-1.1.19
[Entrypoint] Initializing database
[Entrypoint] Database initialized
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

[Entrypoint] ignoring /docker-entrypoint-initdb.d/*

[Entrypoint] Server shut down

[Entrypoint] MySQL init process done. Ready for start up.

[Entrypoint] Starting MySQL 5.7.33-1.1.19

This starts a MySQL server using the default configuration. MySQL is run in the foreground so you can view the server output.

We can see the running Docker image:

$ docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED              STATUS             PORTS                  NAMES
374ab77b0778   mysql/mysql-server:5.7   "/entrypoint.sh mysq…"   About a minute ago   Up About a minute  3306/tcp, 33060/tcp    mysql

What we've done is to take the image, mysql/mysql-server, and through the magic provided by the Docker system, instantiate a Container running the MySQL server. No grey bearded guru was required.

The first column shows a hex-coded string. This is the identifier for the container. Like for images, Docker generates a SHA256 identifier that is a lot longer than what's shown here. Docker also uses shortened identifiers derived from the SHA256 identifier, and that's what is shown here. Next is the image name, and some other information.

Let's talk about the strings shown in the PORTS column. Docker containers can publish a network port, using TCP or UDP or other protocols. In this case two ports are shown, including 3306 which is the default port for MySQL servers. Since we did not publish a port when launching MySQL, these ports are not visible to the public. If they were visible to the public the notation would be different.

Since the ports are not published, running the MySQL client on your laptop will fail:

$ mysql -u root -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

This means no software running on your laptop can directly access the MySQL server. Another Docker container could, though, access the server, and we'll touch on that later.

While we can't directly access the MySQL container, we can do this:

$ docker exec -it mysql mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

The docker exec command is used for running commands inside a container. The -it flag means to run the command interactively (-i) and attached to the terminal (-t). The next argument is the container name, and the last portion is the command to run. Therefore this runs mysql -u root -p inside the mysql container.

In other words, we can reach into the running container and run the MySQL command-line client within that container to access the database.

We can run other commands as well:

$ docker exec -it mysql bash
[root@374ab77b0778 /]# ps ax
  PID TTY      STAT   TIME COMMAND
    1 ?        Ssl    0:00 mysqld
  113 pts/0    Ss     0:00 bash
  126 pts/0    R+     0:00 ps ax

We can also reach into the container and get a regular BASH command-line prompt to do whatever we wish.

This shows us we can easily set up a MySQL database instance. But there are a couple issues to solve:

The access permissions and password is incompletely set up
The data will disappear as soon as the container is deleted -- because the database is stored inside the container
The default configuration has very limited buffer sizes and won't have good performance

Before taking care of some of this let's learn how to shut down the database container.

Cleaning up - and the ephemeral nature of Docker images

Now that our little experiment is done, lets clean up the laboratory:

$ docker stop mysql
mysql
$ docker rm mysql
mysql

In normal circumstances we can use docker stop container-name and docker start container-name to start/stop the container as needed. In this case we want to delete the container because we don't need it at all. If we had put some data into the database, that data would vaporize. When a container shuts down, the data in its local storage is deleted.

This is incompatible for many application services, such as databases. A database is a thing that might last for decades. For example I have a pair of websites ( visforvoltage.org and davidherron.com) which were built well over 10 years ago. The same MySQL databases have supported those sites for the whole time.

Docker images are meant to be created and destroyed freely. Therefore it's necessary to store the data and configuration outside the Docker container. That would let us delete and recreate the container, without deleting the data. We'll show how this is done over the next couple sections.

Configuring access to MySQL inside Docker

The password configured above might be too guessable, and a miscreant might be able to get into your server. Try this to generate a randomized password:

$ docker run --name=mysql --env MYSQL_RANDOM_ROOT_PASSWORD=true  mysql/mysql-server:5.7
Initializing database
Database initialized
MySQL init process in progress...
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
GENERATED ROOT PASSWORD: .IxXet13n0ssyhIvX@l+uplaDb

/entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

MySQL init process done. Ready for start up.

You can grab the generated password programmatically as so:

$ docker logs mysql 2>&1 | grep GENERATED
GENERATED ROOT PASSWORD: .IxXet13n0ssyhIvX@l+uplaDb

There are several environment variables which can be used to customize the MySQL instance that's started:

MYSQL_RANDOM_ROOT_PASSWORD: As shown earlier, this generates a randomized password that is printed to the console while initializing the container.
MYSQL_USER and MYSQL_PASSWORD: Together these set up a user name other than root to use with the server. You may prefer this rather than having the user-name be root.
MYSQL_DATABASE: Sets the database name to initialize in the server. The root user, and the user specified in MYSQL_USER, are given superuser access (GRANT ALL) to this database.
MYSQL_ROOT_PASSWORD Sets the root password as we saw earlier.
MYSQL_ROOT_HOST Sets a pattern for the allowed hosts to connect to the server. By default root@localhost is allowed to access the database. That's sufficient to connect to the database from inside the container. But, the most likely use-case is access to the database from other containers. For example --env MYSQL_ROOT_HOST=% allows access from any IP address. To allow access from the host on which the container is running, --env MYSQL_ROOT_HOST=172.17.0.1. To allow access from any Docker container, --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0.
MYSQL_LOG_CONSOLE: Sends the error output to the console so it can be viewed using the docker logs command.
MYSQL_ALLOW_EMPTY_PASSWORD: Allow the root password to be blank.

MySQL Container initialization is done only the first time

The MySQL Docker image only initializes and configures the database when it cannot find a database directory. The settings just shown are actually stored as values in tables in the MySQL database. The files comprising the database are stored in a directory named /var/lib/mysql. Clearly if the database directory already contains database files, the MySQL container should not initialize database files, and should instead just use the files which are there.

That was a long way of saying that the first time the Docker image is run, MySQL initializes its database and the user access rights onto the database. The environment variables mentioned earlier are consulted during that initialization and then are not used in subsequent times the database container is launched.

There's a concern that security configuration information -- the root password -- can "leak" and become known by miscreants if it's specified on the command line. Therefore it's preferable to use the MYSQL_RANDOM_ROOT_PASSWORD feature, and extract the password during the first time the database is run.

Mounting an external configuration file (customized `my.cnf`) into a Docker container

The default /etc/my.cnf in the image is limited. It specifies small buffer sizes that won't provide good performance under load. You'll almost certainly want to customize the configuration file.

One way to proceed is to create a custom Dockerfile. In a directory containing a Dockerfile, also have a my.cnf file with whatever settings you desire.

FROM mysql/mysql-server:5.7
COPY my.cnf /etc/my.cnf
EXPOSE 3306 33060
CMD ["mysqld"]

While we could see how to run this, there's a simpler way. This method doesn't involve creating a custom Docker image. We instead mount the configuration file into the container using the --volume option.

$ docker run --name mysql \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    mysql/mysql-server:5.7

This uses the standard image, but we've added this new option. The --volume option supports mounting data from outside the container into the container. It can be used either for mounting directory structures within the container, or individual files as shown here.

It's beyond the scope of this tutorial to show you how to customize the MySQL configuration file. Whole books and reams of software tools exist for that purpose. Among the links below are relavent sections in the MySQL manual.

Mounting an external data directory into a Docker container

The next thing to solve is storing the database outside the container. As it stands, the database files disappear as soon as we delete the container. Containers are ephemeral, while databases want to be immortal. Those are not compatible, and thus it's necessary to use the --volume option to store the data elsewhere.

MySQL stores its data in the directory /var/lib/mysql, by default. Using --volume we can cause a directory on the host computer to be visible within the container.

$ docker run --name mysql \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --env MYSQL_DATABASE=foodb \
    --volume `pwd`/mysql-data:/var/lib/mysql \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    mysql/mysql-server:5.7

This injects both the configuration file, and the data directory, into the container. Both actually live in the host file system, with a bit of software container magic subterfuge making both be visible inside the container.

We've also added a new environment variable, MYSQL_DATABASE. As we see from the writeup earlier, this creates a database in the data directory.

After running the above command this is the output:

Entrypoint] MySQL Docker Image 5.7.33-1.1.19
[Entrypoint] Initializing database
[Entrypoint] Database initialized
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
[Entrypoint] GENERATED ROOT PASSWORD: eSUG]EfZUDz4D%yrUnEcc3LvysO

[Entrypoint] ignoring /docker-entrypoint-initdb.d/*

[Entrypoint] Server shut down

[Entrypoint] MySQL init process done. Ready for start up.

[Entrypoint] Starting MySQL 5.7.33-1.1.19

Taking note of the GENERATED ROOT PASSWORD, run this:

$ docker exec -it mysql mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foodb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

mysql> use foodb
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

Sure enough, there's the database we specified. No tables exist because nothing has used the database.

Another thing to check is the data directory:

$ docker stop mysql
mysql
$ docker rm mysql
mysql
$ ls mysql-data/
auto.cnf           client-key.pem     ib_logfile1        private_key.pem    sys
ca-key.pem         foodb              ibdata1            public_key.pem
ca.pem             ib_buffer_pool     mysql              server-cert.pem
client-cert.pem    ib_logfile0        performance_schema server-key.pem

Not only have we stopped the mysql container, we deleted it. And, the mysql-data directory is there.

In passing notice that among the generated files are ones with the .pem extension. These are SSL certificates that can theoretically be used to authenticate connections to the server. For more on this see: Connect with SSL to MySQL in Docker container

At this point we can rerun the docker run command. This mimics stopping then recreating a container, perhaps to update it to a later release. You can then rerun docker exec -it mysql mysql -u root -p to login to the database server, and see the databases are still there.

Access by other containers - using a custom bridge network

We've learned a lot about Docker, how to launch a container, and how to mount data into a container. To access the database instance we executed a mysql command inside the container to run the MySQL CLI command. But that's obviously not what is required for an application deployment. Instead there must be a way to access the database from another container.

Docker provides a virtual network facility. You can easily create a private network that exists virtually within the host computer, and is only accessible by containers on that host. Docker can also create virtual networks that span multiple host computers, but that's outside our scope for now.

The point of creating a virtual private network between Docker containers is security. You want the containers to be able to communicate with each other, but you don't want outside access to the database.

Most advice about connecting Docker containers is to use the --link option. With that option the containers are attached together and can create IP networking connections between each other. However, that option is deprecated and may be removed in the future.

What's preferred now is to create a bridge network and attach the containers to that network.

$ docker network create --driver bridge dbnet
d89f8a8dd650981864e516d49c1a48fe2bf5e7978a11b6258558acd3789d017e

First we create the bridge network. These networks are cheap and easy to create and feel free to create as many as required for your service architecture.

We named this one dbnet because it's where the database server lives. Any container wishing to access the database should be attached to this network.

$ docker run --name mysql \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0 \
    --env MYSQL_DATABASE=foodb \
    --volume `pwd`/mysql-data:/var/lib/mysql \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    --network dbnet \
    mysql/mysql-server:5.7

Any other container you wish to have access to this network also requires the --network dbnet parameter.

Within the private network the container will have a DNS host name of whatever you specified in the --name parameter.

Docker sets up customized DNS servers within the infrastructure it creates. Service discovery is meant to be done using the DNS host name.

Once the MySQL container is launched you can inspect the network:

$ docker network inspect dbnet
[
    {
        "Name": "dbnet",
        "Id": "d89f8a8dd65098...",
        "Created": "2021-03-05T21:27:57.3197951Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "172.26.0.0/16",
                    "Gateway": "172.26.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "8c5178d0016feda...": {
                "Name": "mysql",
                "EndpointID": "f1238b61693cd...",
                "MacAddress": "02:42:ac:1a:00:02",
                "IPv4Address": "172.26.0.2/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

This says the network covers the 172.26.xxx.xxx address range. It is a bridge network, which is created by Docker to span the containers connected to the virtual network. Bridge networks do not span across Docker hosts, so they're useful solely for single host Docker installations.

Notice that when launching the MySQL container we specified MYSQL_ROOT_HOST with 172.0.0.0. This will support logging in to the database from another container using the root user. The MySQL container limits, by default, the root user to the local container, but this gives us the option of using that user ID from another container.

So, we have the database configured and ready to go, how do we access it from another container?

$ docker run  --rm -it --network dbnet imega/mysql-client \
                    mysql -h mysql -u root -p foodb
Unable to find image 'imega/mysql-client:latest' locally
latest: Pulling from imega/mysql-client
fd9a61a1277c: Pull complete 
Digest: sha256:e29aa7854db7510a6bf8b711fc631dd0d4b3eaf8e6d102031db3642453497a61
Status: Downloaded newer image for imega/mysql-client:latest
Enter password:
ERROR 1130 (HY000): Host '172.26.0.3' is not allowed to connect to this MySQL server

And.. we get a permission denied error. What's happened is we've run into an issue of when does database initialization occur. In MySQL, the user ID's and user permissions are stored in a table in the MySQL database instance. For the MYSQL_ROOT_HOST to be useful, it has to be used when initializing the database.

By now we've stopped and restarted the mysql database container several times. Did you notice in passing that the database was only initialized once? That's because the MySQL container initializes the database only when there is no database directory. Once there is a database directory, the MySQL container simply starts, and does not initialize the database. That means we could change the configuration all we want, and the MySQL container will ignore the configuration change.

$ docker stop mysql
mysql
$ docker rm mysql
mysql
$ rm -rf mysql-data/

This time let's stop and delete the container, and delete the data directory. Then, rerunning the docker run command above, the output now includes the statements Initializing database and Database initialized, as well as a new random root password. In other words, the database initialization was run, and the MYSQL_ROOT_HOST option will have been seen, allowing us to use the root user ID from another container.

We can now run this:

$ docker run  --rm -it --network dbnet imega/mysql-client \
                    mysql -h mysql -u root -p foodb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [foodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foodb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.006 sec)

MySQL [foodb]>

Look closely, and notice that this MySQL CLI experience is in a separate container, and we were able to access the database from that other container. In another command window, run docker ps and verify that there are two containers. Also run docker network inspect dbnet and see that two containers are now attached to the dbnet network.

This demonstrates accessing a MySQL instance from another Docker container.

We now have a MySQL server with a good user access controls, external data that can live for a long time, an external configuration, that is accessible from other containers on the given Docker bridge network.

Summary

We've covered a lot of material related to deploying MySQL instances with Docker, as well as a brief tour of Docker virtual networks. We've established the ground on which we can build a model for application stacks deployed with Docker.

Links

MySQL containers:

MySQL configuration:

Docker

MariaDB

hub.docker.com mariadb/

About the Author(s)

David Herron : David Herron is a writer and software engineer focusing on the wise use of technology. He is especially interested in clean energy technologies like solar power, wind power, and electric cars. David worked for nearly 30 years in Silicon Valley on software ranging from electronic mail systems, to video streaming, to the Java programming language, and has published several books on Node.js programming and electric vehicles.

Running MongoDB with Docker and Docker Compose Wordpress local development environment with Docker and Docker Compose on your laptop