Docker MySQL Tutorial


In this Docker MySQL Tutorial we are going to cover how to get a working, Docker containerised instance of MySQL up and running in next to no time.

Before I go any further I want to stress that running your Database in a Docker Container may not be the best solution for you. Depending on your circumstances, it may be better to maintain a trusty physical server, or a virtual machine equivalent to host your MySQL / database. Please do your own research into Dockerising your Database if going to production with this setup.

Up until I discovered Docker I generally took one of two approaches for working with MySQL / any other database:

  • One development server running MySQL with 10's of databases on it;
  • Once I discovered Ansible: one database per development server / VM.

Neither solution is ideal.

Running all my databases on one server worked quite well. I would only need to manage one server, and this made setup quick, and backups nice and easy.

However, that did not mirror my production setup in any way, so going from dev to production in any project was always a bit of a grey area.

When I discovered Ansible I switched to provisioning a new virtual machine for each database instance, and I had Ansible manage all the user accounts, configuration, and anything else the individual case required.

This worked really well, because the dev box would be an (almost) identical copy to that which I would use in production.

However, each database requiring its own virtual machine meant I would be chomping through disk space like nobody's business, and running multiple 'full' virtual machines for each project was taxing even with 16gb of RAM.

Docker MySQL solves this problem, in a solution that combines the best of both worlds:

I get a nicely separated database server / container per database, and each one works like a standalone VM but with next to no disk usage.

Docker MySQL Image

We aren't going to reinvent the wheel.

We will instead leverage the official Docker MySQL image that's available on Docker Hub.

At the time of writing this gives us access to MySQL version 5.x, and 8.x.

One of the super nice parts about using Docker for this sort of thing is that upgrading / changing your database version becomes much, much easier than when MySQL would be installed onto your server / VM.

In theory this means you are going to get access to newer features, faster. It should also mean you get all the latest security updates and bug fixes. Assuming you keep up to date, of course.

For the purposes of this example we are going to use the latest tag, which currently points at the 5.x branch. Feel free to use version 8, as everything should be the same.

We start by using the run command on mysql:latest. If you don't have the Docker MySQL image already downloaded from Docker Hub then to begin with, the image will be pulled down for you:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

Unable to find image 'mysql:latest' locally
latest: Pulling from library/mysql

aa18ad1a0d33: Downloading [======================>                            ]  23.23MB/52.6MB
fdb8d83dece3: Download complete
75b6ce7b50d3: Download complete
ed1d0a3a64e4: Download complete
8eb36a82c85b: Downloading [================================>                  ]   6.88MB/10.71MB
41be6f1a1c40: Download complete
0e1b414eac71: Download complete
914c28654a91: Waiting
587693eb988c: Waiting
b183c3585729: Waiting
315e21657aa4: Waiting

Once each of the layers have finished downloading, a new container should be up and running:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

Unable to find image 'mysql:latest' locally
latest: Pulling from library/mysql

aa18ad1a0d33: Pull complete
fdb8d83dece3: Pull complete
75b6ce7b50d3: Pull complete
ed1d0a3a64e4: Pull complete
8eb36a82c85b: Pull complete
41be6f1a1c40: Pull complete
0e1b414eac71: Pull complete
914c28654a91: Pull complete
587693eb988c: Pull complete
b183c3585729: Pull complete
315e21657aa4: Pull complete

Digest: sha256:0dc3dacb751ef46a6647234abdec2d47400f0dfbe77ab490b02bffdae57846ed
Status: Downloaded newer image for mysql:latest

0a67b4c4a08b8a06a5769922635b032982e981cf6bd9184a6c119bc1ca080066

I've added some spacing in here for clarity.

Let's quickly recap:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

docker run allows us to start a new container based on the given image name. The image name we are using is mysql:latest.

The --name flag allows us to specify our own name for the created container. If we don't do this then Docker will create a container name for us. As a side note the way Docker generates container names is written in Go, and the code itself is full of fun trivia about each of the values.

We will see our chosen name again momentarily.

-e MYSQL_ROOT_PASSWORD=my-secret-pw

There's two interesting things happening here.

Firstly, -e is one way of passing an Environment Variable into a Docker container. This can also be done using --env, which is the same as -e, just more verbose.

Environment variables are values that we can pass to provide configuration that is particular to the container instance. If we had two running MySQL instances, each could use different environment variables to customise their setup in some way. Environment variables are not a Docker-specific thing, they are just a concept that Docker makes use of.

Secondly, MYSQL_ROOT_PASSWORD is mandatory. There's no way to deduce this from the command itself, and even if you don't provide it, or you misspell it, the command itself will still happily execute. However, the resulting container will immediately exit:

Lastly, the MySQL container starts in detached / background mode because we started the container with the -d flag. As previously covered, this means the running container won't take over the current terminal session.

docker ps -a

CONTAINER ID    IMAGE          COMMAND                  CREATED          STATUS          PORTS       NAMES
0a67b4c4a08b    mysql:latest   "docker-entrypoint..."   47 seconds ago   Up 46 seconds   3306/tcp    some-mysql

To quickly cover what happens if we make a mistake:

docker run --name some-mysql -e MYSQL_OOPS=my-secret-pw -d mysql:latest

6879cbbe1c29093f9e453fe59951773fcc904deb1f4f7c1fcce16cbb7514ab40

docker ps -a

CONTAINER ID    IMAGE          COMMAND                  CREATED          STATUS                  PORTS       NAMES
6879cbbe1c29    mysql:latest   "docker-entrypoint..."   4 seconds ago    Exited (1) 2 seconds    3306/tcp    some-mysql

docker logs 6879cbbe1c29

error: database is uninitialized and password option is not specified
  You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

Be aware that the docker run command looks like it worked, and it's only when viewing the output of a docker ps (ps = process snapshot) that we see the container itself died almost immediately after starting.

We can then make use of docker logs {container id} to (hopefully) gain a better understanding as to why.

MySQL Docker Usage

The command we've used so far isn't that useful. It shows that we can get a MySQL Docker container up and running, but there's a few major problems:

  • We can't connect to it
  • Even if we can connect, there's no usable database
  • We need to connect as root
  • Even if we connect, and there's a database, once we stop the container everything is blown away.

Fortunately we have all the necessary knowledge to fix all of these problems.

Let's revisit our command:

docker volume create crv_mysql

docker run \
    -e MYSQL_ROOT_PASSWORD=my-secret-pw \
    -e MYSQL_DATABASE=devdb \
    -e MYSQL_USER=dbuser \
    -e MYSQL_PASSWORD=dbpassword \
    --mount type=volume,src=crv_mysql,dst=/var/lib/mysql \
    -p 3306:3306 \
    -d \
    mysql:latest

I've removed the --name flag as I don't want the container name to be fixed. For demo / example purposes, this flag is actually more painful than useful, as we are constantly stopping / recreating the container, and so the name is better if it changes each time.

We start off by creating a named Docker volume. We're calling this crv_mysql because it's a MySQL volume and this is CodeReviewVideos :) Name your volume whatever you like.

Note on the docker run command I have used the unix convention of \ at the end of the line to allow the command to split over multiple lines. This makes it much easier for the command to show on your screen in this tutorial, but you can remove the \'s and have the command as a one liner.

We are passing in four environment variables. Each should be self explanatory. I knew these environment variables existed because I read about them in the documentation. Figuring out what environment variables are available is best done by checking the docs, as aside from this the only other way I am aware of is to read the source and figure them out for yourself.

The --mount command has been covered in a previous video. Here we mount the local volume crv_mysql as the path /var/lib/mysql inside the running container. Any data we now add to our devdb database should be preserved even if the container itself is removed.

We want to expose the standard MySQL port of 3306 on the container to 3306 on our docker host. Without doing this we wouldn't be able to connect.

Finally -d as mentioned already starts the container in a detached / background state, and mysql:latest is the Docker MySQL image we are using.

docker run \
>     -e MYSQL_ROOT_PASSWORD=my-secret-pw \
>     -e MYSQL_DATABASE=devdb \
>     -e MYSQL_USER=dbuser \
>     -e MYSQL_PASSWORD=dbpassword \
>     --mount type=volume,src=crv_mysql,dst=/var/lib/mysql \
>     -p 3306:3306 \
>     -d \
>     mysql:latest

90326d3804e621669db770a0ad56f4597caefba77a2bde613ac5acbeb34ac96c

docker ps -a

CONTAINER ID   IMAGE         COMMAND                 CREATED        STATUS        PORTS                   NAMES
90326d3804e6   mysql:latest  "docker-entrypoint..."  3 seconds ago  Up 2 seconds  0.0.0.0:3306->3306/tcp  agitated_goldstine

And with that we can connect to our new MySQL container on 127.0.0.1, port 3306, with the user / pass credentials supplied. Once connected we should be able to see devdb as one of the available databases.

Using An .env File

It might be that you don't want all these -e flags spewing out into your command line entry. This may be for security, or in development, maybe just for simplicity.

Docker allows the use of an .env file, which we can specify with the use of --env-file.

Start by creating an .env file in your current working directory:

touch .env

Then edit the file (use any editor of course):

vim .env

And add in the following contents:

MYSQL_ROOT_PASSWORD=my-secret-pw
MYSQL_DATABASE=devdb
MYSQL_USER=dbuser
MYSQL_PASSWORD=dbpassword

Save and exit (:wq in vim).

We can now update the command above to:

docker volume create crv_mysql

docker run \
    --env-file .env \
    --mount type=volume,src=crv_mysql,dst=/var/lib/mysql \
    -p 3306:3306 \
    -d \
    mysql:latest

Now when the command is run, the contents of the .env file are read and used as the environment variables instead.

Note that this is not a secure alternative in any way. It is possibly more convenient. For a secure alternative look into Docker secrets, which is in itself a topic in its own right.

Using Docker MySQL and Symfony (or anything else)

Lastly, how then might we use this with Symfony?

As easy as this:

# app/config/parameters.yml

parameters:
    database_host: 127.0.0.1
    database_port: 3306
    database_name: devdb
    database_user: dbuser
    database_password: dbpassword

In other words, it's just like a typical MySQL install.

You can use this with everything - not just Symfony.

You can swap out MySQL for MariaDB, or Postgres, or have multiple databases, or any other combo your project requires.

In finishing up, don't forget to remove both the container and created volume:

docker stop {container id}
docker rm {container id}
docker volume rm crv_mysql

That's how we run a MySQL Docker container using the command line.

In truth, you will use Docker Compose in the vast majority of cases. At least, I do.

We will get on to MySQL - and more - with Docker Compose shortly.

Code For This Course

Get the code for this course.

Episodes