MySQL on Docker

Introduction

MySQL is a well-known open-source relational database management system (RDBMS). It makes use of SQL (Structured Query Language), which you’ll briefly see during the blog post. MySQL mainly:

implements a relational database in host’s storage system,
manages users that can access the database’s entities,
allows for network access,
helps to test database integrity and creation of backups

Dealing with MySQL’s user management and versioning is one of the most annoying side of a backend. Moreover, what if two web-applications reside on the same server and need a different version of MySQL? It’s where Docker comes in help.

Docker makes development efficient and predictable. It takes away repetitive and boring configuration tasks and allows for a fast, easy and portable application development.

Containers & Images

A container is a sandboxed process on a host machine that is isolated from all other processes. In short, a container:

is a runnable instance of an image,
can be run on local machines, virtual machines or deployed to the cloud,
is portable (can be run on any OS),
is isolated from other containers and runs its own software, binaries, and configurations

A running container uses an isolated filesystem. This custom filesystem is provided by a container image, which must hold everything needed to run an application.

Docker Compose

The Compose specification allows to define a platform-agnostic container-based application. An application is a set of containers that run together with shared resources and communication channels.

The default path for a Compose file is compose.yaml (preferred) or compose.yml in working directory. It should also support docker-compose.yaml and docker-compose.yml for backward compatibility.

In short, the main cores of Compose are:

Services: computing components,
Networks: establish an IP route between containers within connected services,
Volumes: high-level filesystem mount where to store and share persistent data,
Configs: configuration data that is dependent on the runtime or platform,
Secret: flavor of configuration for sensitive data that should not be exposed without security consideration,
Project: individual deployment of an application specification on a platform.

Dockerfile

Docker can build images automatically by reading the instructions from a Dockerfile, which is a text document that contains all the commands a user could call on the command line to assemble an image. The instructions are not case-sensitive. However, convention is for them to be uppercase to distinguish them from arguments more easily:

# Comment
INSTRUCTION arguments

If the docker-compose.yaml has more than a service, the Dockerfiles should have as extension the name of the service. Given a compose like:

services:
  service_1:
    build:
      context: .
      dockerfile: Dockerfile.service_1
    # ...
  service_2:
    build:
      context: .
      dockerfile: Dockerfile.service_2
    # ...
  service_3:
    build:
      context: .
      dockerfile: Dockerfile.service_3
    # ...

The files on the working directory should be at least: compose.yaml, Dockerfile.service_1, Dockerfile.service_2 and Dockerfile.service_3.

A couple of useful commands to remove all containers and images are:

# delete all containers including its volumes
docker rm -vf $(docker ps -aq)

# delete all the images
docker rmi -f $(docker images -aq)

Common setup

Let’s replace the default root user inside the container. Locate your configuration file (.bashrc, .zshrc, …) and add the following line:

export DOCKER_USER="$(id -u):$(id -g)"

Apply the changes with source command, for instance:

source ~/.zshrc

DOCKER_USER is a string holding the currently logged-in’s User ID and Group ID separated by a : sign. When setting $DOCKER_USER to user field of a container, it will use those UID and GID instead of root user information. This will prevent root to create directories or manipulate files from within the container.

Please refer to UNIX intro blog post to learn more.

MySQL without secrets

After this long introduction about Compose and Dockerfile, let’s move forward to write the first application. Its aim is to be the simplest as possible, so it will not have any .env file where to store sensitive data. The required elements are:

compose.yaml: define a single service called mysql
data directory: where the /var/lib/mysql volume will be mounted
Dockerfile.mysql: copies the database schema and expose port 3306
schema.sql: define the database schema with mydatabase database and a couple of tables

Let’s open a terminal and set up the project:

# move to a directory where to save all the projects
cd ~/Documents/

# create the project directory and move into
mkdir mysql-base && cd mysql-base

# create the directory where to mount the volume
mkdir data

# create the files
touch compose.yaml Dockerfile.mysql schema.sql

There’s no need to create data directory: il will be created by Dockerfile.mysql during image build process. If you then make any changes to the files mentioned above, remind to clear the volume by deleting all its content:

sudo rm -rf ~/Documents/mysql-base/data/*
sudo rm -rf ~/Documents/mysql-base/data/.*

Let’s take a closer look at files definitions. compose.yaml file is defined as:

services:
  mysql: # https://hub.docker.com/_/mysql/
    build:
      context: .
      dockerfile: Dockerfile.mysql
    image: mysql:8.0
    ports:
      - 3306:3306
    volumes:
      - ./data/mysql:/var/lib/mysql
    environment:
      TERM: xterm
      MYSQL_USER: batman
      MYSQL_PASSWORD: password123
      MYSQL_DATABASE: mydb
      MYSQL_ROOT_PASSWORD: root
    container_name: mysql-nosecret
    restart: always
    user: $DOCKER_USER

Dockerfile.mysql file is defined as:

FROM mysql:8.0

COPY schema.sql /docker-entrypoint-initdb.d/schema.sql
EXPOSE 3306

schema.sql file is defined as:

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- Add more SQL statements as needed
INSERT INTO users (name, email) VALUES ("Pietro", "pietro@blog.com");

You can now use the following commands to build the image and run the container (without attaching the terminal):

docker compose build
docker compose up -d

Please Note: Depending on the Docker installation process you followed, you could need to use docker-compose command instead.

You can now attach a shell to the running container. You can then log into MySQL using the credentials provided into compose.yaml’s environment section:

docker exec -it mysql-nosecret sh
# a new shell will be opened
mysql --user=batman --password=password123

# exit mysql
quit

# exit mysql-nosecret container
exit

You can also directly execute MySQL on the container:

docker exec -it mysql-nosecret mysql --user=batman --password=password123

From MySQL monitor you can then run SQL commands like:

SHOW DATABASES;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mydb               |
# | performance_schema |
# +--------------------+
# 3 rows in set (0.00 sec)

SELECT * FROM mydb.users;
# +----+--------+-----------------+
# | id | name   | email           |
# +----+--------+-----------------+
# |  1 | Pietro | pietro@blog.com |
# +----+--------+-----------------+
# 1 row in set (0.01 sec)

DESCRIBE mydb.users;
# +-------+--------------+------+-----+---------+----------------+
# | Field | Type         | Null | Key | Default | Extra          |
# +-------+--------------+------+-----+---------+----------------+
# | id    | int          | NO   | PRI | NULL    | auto_increment |
# | name  | varchar(50)  | NO   |     | NULL    |                |
# | email | varchar(100) | NO   |     | NULL    |                |
# +-------+--------------+------+-----+---------+----------------+
# 3 rows in set (0.00 sec)

To stop and remove service containers use:

docker compose rm -fs

Where -f stands for force (don’t ask to confirm removal) and -s stands for stop (stop the containers, if required, before removing).

MySQL with secrets

The weakness of the previous project is that sensitive information are clearly available to anyone. If you plan to upload the project on GitLab, you’ll share your database’s username and password, which is an awful practice. To overcome this security issue, Docker makes use of .env file.

Please Note: .env file should NEVER be versioned. To do so, add them to .gitignore file (as I’ll show you later).

The required elements are:

compose.yaml: define a single service called mysql
data directory: where the /var/lib/mysql volume will be mounted
init directory: where to put initialization files
- schema.sql: define the database schema
Dockerfile.mysql: copies the database schema and expose port 3306
.env, .env.example and .gitignore: secure sensitive information

Let’s open a terminal and set up the project:

# move to a directory where to save all the projects
cd ~/Documents/

# create the project directory and move into
mkdir mysql-intermediate
cd mysql-intermediate

# create the directory where to mount the volume
mkdir data init

# create the files
touch compose.yaml Dockerfile.mysql init/schema.sql .env .env.example .gitignore

If you then make any changes to the files, remove the whole data directory:

sudo rm -rf ~/Documents/mysql-intermediate/data/

.env.example acts as a template for your secrets.

DB_VERSION=N.n
DB_USER=AAA
DB_PWD=aaa
DB_ROOT_PWD=abc

From the file above, you can write your own and private .env file. I’ll “leak” mine in order for you to understand how compose and Dockerfile behave.

DB_VERSION=8.0
DB_USER=batman
DB_PWD=batcave
DB_ROOT_PWD=r@@t

.gitignore file should include lots of directories, here is only the initial part of the file:

.env
data/

# Docker project generated files to ignore if you want to ignore files created by your editor/tools,
# please consider a global .gitignore https://help.github.com/articles/ignoring-files

compose.yaml file is defined as:

services:
  mysql:
    build:
      context: .
      dockerfile: Dockerfile.mysql
      args:
        - DB_VERSION=${DB_VERSION}
    image: mysql:${DB_VERSION}
    ports:
      - 3306:3306
    volumes:
      - ./data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d
    environment:
      TERM: xterm
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PWD}
      MYSQL_DATABASE: mydb
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PWD}
    container_name: mysql-mid
    restart: always

As you can see, the file references to .env variables using ${VAR_NAME} way. One of the few things excluded from the secrets is the database name: we’ll discuss this topic later.

Dockerfile.mysql file is defined as:

ARG DB_VERSION
FROM mysql:${DB_VERSION}

ARG USERNAME=mysql-user
ARG USER_UID=1000
ARG USER_GID=$USER_UID
RUN groupadd --gid $USER_GID $USERNAME && useradd --uid $USER_UID --gid $USER_GID -m $USERNAME
USER $USERNAME

COPY init/schema.sql /docker-entrypoint-initdb.d/schema.sql
EXPOSE 3306

init/schema.sql file is defined as above. It’s a good practice to put it in a subdirectory in order to keep the project’s root clear. If you have to manage a Docker with lots of services, the number of configuration files could start being out of control.

In the same way of above’s section:

# build the image and run the container
docker compose build && docker compose up -d

# log into MySQL
docker exec -it mysql-mid mysql --user=batman --password=batcave

MySQL with custom database name

The last project about MySQL on Docker adds an important security capability. As you may have notices, the database name (environment’s MYSQL_DATABASE variable) is hard-coded to mydb. That’s because .sql files (like schema.sql) cannot read variables from .env file.

To overcome this huge limitation, you need to automatically generate .sql files from within mysql-entrypoint.sh (a custom entrypoint). Since you only need to auto-generate the files that contains references to MYSQL_DATABASE, the project makes use of db_tables.sql support file. This file contains the core part of the database definition and will be appended to schema.sql from mysql-entrypoint.sh.

Let’s open a terminal and set up the project:

# move to a directory where to save all the projects
cd ~/Documents/

# create the project directory and move into
mkdir mysql-advanced && cd mysql-advanced
mkdir data
touch compose.yaml Dockerfile.mysql db_tables.sql mysql-entrypoint.sh .env.example .gitignore

# give execution permission to entrypoint
chmod +x mysql-entrypoint.sh

If you then make any changes to the files, remove the whole data and initdb directories:

sudo rm -rf ~/Documents/mysql-advanced/data/
sudo rm -rf ~/Documents/mysql-advanced/initdb/

.env.example acts as a template for your secrets.

DB_VERSION=N.n
DB_USER=AAA
DB_PWD=aaa
DB_NAME=abc_abc
DB_ROOT_PWD=abc

From the file above, you can write your own and private .env file. As I did above, I’ll “leak” mine.

DB_VERSION=8.0
DB_USER=batman
DB_PWD=batcave
DB_NAME=pietropoluzzi_db
DB_ROOT_PWD=r@@t

Let’s take a closer look at the files. compose.yaml file is defined as:

services:
  mysql: # https://hub.docker.com/_/mysql/
    build:
      context: .
      dockerfile: Dockerfile.mysql
      args:
        - DB_VERSION=${DB_VERSION}
    image: mysql:${DB_VERSION}
    ports:
      - 3306:3306
    volumes:
      - ./data:/var/lib/mysql # /data/mysql must be empty when running `docker-compose build`
      - ./initdb:/docker-entrypoint-initdb.d/
    environment:
      TERM: xterm
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PWD}
      MYSQL_DATABASE: ${DB_NAME}
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PWD}
    container_name: mysql-adv
    restart: always

Dockerfile.mysql file is defined as:

ARG DB_VERSION
FROM mysql:${DB_VERSION}

# adding a custom user cause:
# ./mysql-entrypoint.sh: line 11: schema.sql: Permission denied
# ./mysql-entrypoint.sh: line 12: schema.sql: Permission denied
# ./mysql-entrypoint.sh: line 15: schema.sql: Permission denied
# rm: cannot remove 'db_tables.sql': Permission denied
# mv: cannot stat 'schema.sql': No such file or directory

# ARG USERNAME=mysql-user
# ARG USER_UID=1000
# ARG USER_GID=$USER_UID
# RUN groupadd --gid $USER_GID $USERNAME && useradd --uid $USER_UID --gid $USER_GID -m $USERNAME
# USER $USERNAME

COPY db_tables.sql /
COPY mysql-entrypoint.sh /

ENTRYPOINT ["sh", "./mysql-entrypoint.sh"]
CMD ["mysqld"]

db_tables.sql is defined as:

CREATE TABLE IF NOT EXISTS imu (
  id INT PRIMARY KEY AUTO_INCREMENT,
  acc_x INT NOT NULL COMMENT 'Accelerometer on x axes',
  acc_y INT NOT NULL COMMENT 'Accelerometer on y axes',
  acc_z INT NOT NULL COMMENT 'Accelerometer on z axes',
  gyro_x INT NOT NULL COMMENT 'Gyroscope on x axes',
  gyro_y INT NOT NULL COMMENT 'Gyroscope on y axes',
  gyro_z INT NOT NULL COMMENT 'Gyroscope on z axes',
  mag_x INT NOT NULL COMMENT 'Magnetometer on x axes',
  mag_y INT NOT NULL COMMENT 'Magnetometer on y axes',
  mag_z INT NOT NULL COMMENT 'Magnetometer on z axes',
  time BIGINT UNSIGNED NOT NULL COMMENT 'time when the data was captured'
);

In this scenario you need to define your own custom entrypoint, called mysql-entrypoint.sh:

#!/usr/bin/env sh

# environment variables are defined in compose.yaml
# generate database with the name of the specified environment variable
echo "CREATE DATABASE IF NOT EXISTS ${MYSQL_DATABASE};" > schema.sql
echo "USE ${MYSQL_DATABASE};" >> schema.sql

# concatenate the rest of the database definition
cat db_tables.sql >> schema.sql
rm db_tables.sql

# copy the schema into default directory docker-entrypoint-initdb.d
# cp schema.sql /docker-entrypoint-initdb.d/schema.sql
mv schema.sql /docker-entrypoint-initdb.d/

# continue with the default entrypoint behavior
/entrypoint.sh "$@"

In the same way of above’s section, build the image and run the container:

# still some issues with Docker default user
sudo chown -R $USER:$USER data init

docker compose build
docker compose up -d

Then, login into MySQL with:

docker exec -it mysql-adv mysql --user=batman --password=batcave

From MySQL monitor you can then run SQL commands like:

SHOW DATABASES;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | pietropoluzzi_db   |
# | performance_schema |
# +--------------------+
# 3 rows in set (0.01 sec)

USE pietropoluzzi_db;
# Reading table information for completion of table and column names
# You can turn off this feature to get a quicker startup with -A
# Database changed

SHOW TABLES;
# ...

DESCRIBE imu;
# ...

Conclusio

There are some topics that are still under development like:

port customization,
Docker user to set in compose.

I hope you can take inspiration from this post! I’ll share other ideas and improvements very soon.

Describe databases in MySQL

You should have seen DESCRIBE command in some SQL statements to describe a database’s table. There is no statement that describes all tables at once. Taking database information_schema, StackOverflow comes in help with some solutions:

SELECT * FROM information_schema.columns WHERE table_schema = 'information_schema';
SELECT * FROM information_schema.columns WHERE table_schema = 'information_schema' ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_COMMENT, ORDINAL_POSITION FROM information_schema.columns WHERE table_schema = 'information_schema' ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT * FROM information_schema.columns WHERE table_schema != 'information_schema';

Documentation

Here is the list of guides and tutorials I used to write this article: