MySQL and Docker

Note

The outline for this section is inspired by MySQL’s Basic Steps for MySQL Server Deployment with Docker

Note

Another useful respource is this Github repo from MySQL which provides the source dockerfiles for each of the MySQL images.

Spinning Up a Container

Pulling Latest Version

An optional (but recommended) first step to setting up a MySQL container is to pull the latest (or desired) MySQL image:

We could replace latest with 8.0 or 7.0 or any version of MySQL for quich there is an image in the repository (use docker search MySQL as a starting point). After running this command we will see the pulled image in the list of images when we run docker images

Hello World

We can now run a docker container from the downloaded image.

$ docker run -ti -d --name mysql1 mysql/mysql-server

We now have a docker container running with MySQL server as its main process. The built-in dockerfile from MySQL has verified this for us. So one might wonder, what is the MySQL host/port/password?

Lets take a look at the log generated by the container with the command docker logs mysql1. Here’s the output:

[Entrypoint] MySQL Docker Image 5.7.21-1.1.3
[Entrypoint] No password option specified for new database.
[Entrypoint]   A random onetime password will be generated.
[Entrypoint] Initializing database
[Entrypoint] Database initialized
[Entrypoint] GENERATED ROOT PASSWORD: bbaAA3rwEd%IBTyDujacYsUm0SUR
[Entrypoint] ignoring /docker-entrypoint-initdb.d/*
[Entrypoint] Server shut down
[Entrypoint] Setting root user as expired. Password will need to be changed before database can be used.
[Entrypoint] MySQL init process done. Ready for start up.
[Entrypoint] Starting MySQL 5.7.21-1.1.3

So we can see that the server was successfully created and started, and since we did not specify a password option for the database, docker has generated one for us, namely bbaAA3rwEd%IBTyDujacYsUm0SUR. So if we now enter our MySQL shell using the command:

$ docker exec -ti mysql1 mysql -u root -p

We’ll be prompted to enter this password. We now have access to the MySQL shell.

Changing Root Password

Obviously we may not want to use the root generated password for our MySQL server, so we instead change it. MySQL will force us to do this anyway.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'

To view the data directory library of this MySQL container, we can detach from it then reattach with the bash command

$ docker exec -ti mysql1 bash
$ ls /var/lib/mysql

We can also start, restart, and stop the MySQL server using the simple commands:

$ docker mysql1 start
$ docker mysql1 restart
$ docker mysql1 stop

And obviously we can remove it with docker rm mysql1.

Storage

The easiest way to setup the storage of a MySQL container is to use a simple bind-mount to the host machine. This will need to be done for both the configuration file and the data directory of the server.

$ docker run -ti --rm -d --name mysql1 \
--mount type=bind,src=/path/to/host/dir/my.cnf,dst=/etc/my.cnf \
-- mount type=bind,src=/path/to/datadir,dst=/var/lib/mysql \
mysql/mysql-server

As with all bind mounts, the path on the host must already exist.