docker

Dockerfile Cookbook Recipes Part 02: mysql

In this basic example we take a good strong look at building and rolling your own mysql containers with various options and a good guide on how it all works!

thinkmelt@protonmail.com

Jun 19, 2024 • 4 min read

Prerequisites: If you need a quick script to get your docker/docker-ce working:

We always try to take great care to make guides super simple so you can understand and get running as quickly as possible. It is also beneficial to always document some diagnostic techniques to troubleshoot your work. We are going to make a working mysql image that can make a quick and easy mysql container.

Part 1: Build a base image from an existing docker mysql:latest image

It must be noted that the main mysql Docker file is actually quite complex.
By using the 'FROM mysql:latest' we are effectively adding a layer to the existing mysql image with the settings we prefer (MYSQL_ROOT_PASSWORD)

Create a folder and inside it make a Dockerfile and put into it:

FROM mysql:latest
LABEL maintainer="Who Created Application"
LABEL description="Very small mysql image!"
RUN chown -R mysql:root /var/lib/mysql/
ENV MYSQL_ROOT_PASSWORD=<passsword>      
EXPOSE 3306

Note: Docker has done a really good job of building very small docker images. Typically Dockerfile examples are built off of the base images.

To build your base image:

docker build -t mysql_local:latest .

This will make a small image:

We have deliberately called the image 'mysql_local' to not compete with the online docker hub 'mysql'

Part 2: Running your image into a container

A. Diagnostic method (To see what is going one while it runs)

docker run -it --name mysql mysql_local:latest

This will produce LOTS of logs.
Note the container will not be visible to the host ports as it is mapped to the internal 172.X.X.X of the docker local network.
You cannot stop this by hitting ctrl-c. Instead open another terminal and call 'docker stop <name>'
You can also see more information on what is going on with:

docker events

B. Local Method - to the 172.X.X.X address of docker container.

docker run -d --name mysql mysql_local:latest

If you do not add the option -p 3306:3306 it will not port the docker container to the host outside world.
If you add the option -d it runs in a semi-permanent state.

--restart-unless-stopped

Option added will make it run forever.
This is very good from a security standpoint as the port is only visible to other internal containers. Not only that but the mysql itself is also isolated from the main system.
To find it list your containers then inspect the one you want.

docker ps

docker inspect mysql

The key of where this docker lives will be in the "bridge" section:

When we nmap the 172.17.0.2 we find the 3306 port.

sudo apt install nmap -y && nmap 172.17.0.2

Connecting into the mysql

You may need to install the mysql-client to the local host

sudo apt install mysql-client -y

mysql -u root -p -h 172.17.0.2

C. Exposed Method - where the 3306 is binding to the host and externally visible

docker run -it -d --restart-unless-stopped -p 3306:3306 --name mysql mysql_local:latest

Now the container will bind it's 3306 to the 3306 of the external host. It is also set for -d daemon mode and --restart-unless-stopped effectively means 'run forever.'
You will also have all the security issues with an exposed port.

D. Docker with Database Persistence

None of the above containers will persist the database without adding a volume, therefore add this option

-v $HOME/mysql-data:/var/lib/mysql

As in a full example:

docker run -it -d --name mysql -v $HOME/mysql-data:/var/lib/mysql mysql_local

Part 3: Setting up a docker-compose.yaml

A docker-compose.yaml is effectively a file that does the same as the command line:

version: "3"

services:
  mysql:
    image: mysql:8.0
    environment:
      - MYSQL_ROOT_PASSWORD="some_password"
    volumes:
      - mysql:/var/lib/mysql

volumes:
  mysql:

And it can be stood up with (diagnostic mode)

docker-compose up

Or permanent run mode:

docker-compose up -d

Part 4: Adding root access to external accounts:

once you can mysql into the server one can do

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* IDENTIFIED BY 'root'@'%'  WITH GRANT OPTION;
FLUSH PRIVILEGES;

This will allow from '%' which means any ip address.
AFAIK the mysql typically binds only to localhost. One had to modify /etc/mysql/my.cnf typically and add 'bind-address=0.0.0.0' but in the instance where it lives inside a container that can often now be ignored but you will still need to create the following permission above for remote logins.

Summary: We did not really actually create a Dockerfile from scratch but basically added a layer to the existing mysql:latest. It is actually quite complex

One could effectively build FROM debian:latest in their Dockerfile but would probably get a image / container that is much larger and less-efficient than these custom tuned ones. Grant you it still does warrant investigation