How to run MySQL Docker container

Monday, 17 January 2022

How to run MySQL Docker container

Docker hub contains MySQL images. To run the container with the latest version of MySQL on the local machine:

$ docker run \

--name my-test-mysql \

-p 3306:3306 \

-e MYSQL_ROOT_PASSWORD=root \

-d mysql:latest

02179364ced0e65912ab0d23dd8451e42d9e4ca679507abe711a86d0ac564219

We are here naming this container my-test-mysql, opening default MySQL port 3306 on container (so we can connect to DB from outside the container), set the password (e.g. root) for the default MySQL user (root) and making a choice which MySQL version to run (the latest).

To check that this container is running:

$ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
...

02179364ced0 mysql:latest "docker-entrypoint.s…" 3 days ago Up 3 days 0.0.0.0:3306->3306/tcp, 33060/tcp my-test-mysql
...

---

If for some reason container won't run (it starts and then stops), the reason can be found in docker logs for that container:

$ docker container logs my-test-mysql

NOTE: make sure that container is preserved after docker run command - don't run it with --rm option.

---

We can test connectivity to this DB via MySQL workbench:

Setting MySQL configuration when creating the container

The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d.[source]

It is possible to set a variable in MySQL configuration file when starting the container. To see the full list of such variables we need to run:

$ docker run -it --rm mysql:latest --verbose --help
mysqld Ver 8.0.27 for Linux on x86_64 (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.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-8.0
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
...

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                             Value (after reading options)
------------------------------------------------------------ -------------
abort-slave-event-count                                      0
activate-all-roles-on-login                                  FALSE
...
sql-mode                                                     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
...

windowing-use-high-precision TRUE

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.

Let's say we want to set sql_mode in order to suppress error invalid default value when setting '0000-00-00 00:00:00' as default value for column with datetime type. By default NO_ZERO_IN_DATE,NO_ZERO_DATE are included in sql_mode value in my.cnf but we want to exlude them:

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

To set this variable when running the container:

$ docker run \

--name wp-admin-mysql \

-p 3306:3306 \

-e MYSQL_ROOT_PASSWORD=root \

-d mysql:latest \

--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

To verify that the value is correctly set we can connect to DB and execute the following command:

SELECT @@GLOBAL.sql_mode;

DB Config and Data Persistence

Each time we run the MySQL container it sets up DB from scratch. Once container is stopped all configurations, schemas, tables and data are lost forever. To persist this data between MySQL container sessions we can use:

Docker volume (preferred way)
directory on the host

Data Persistence with named Docker volume

Named Docker volume is like a blob that can be attached to a container and where container persists its data.

We first need to create it:

$ docker volume create mysql-volume
mysql-volume

After it's created it will be listed among other Docker volumes:

$ docker volume ls
DRIVER VOLUME NAME
local mysql-volume

We can now mount it to /var/lib/mysql inside the container, where MySQL by default will write its data files: -v data-volume:/var/lib/mysql

$ docker run \

--name my-test-mysql \

--rm \

-p 3306:3306 \

-e MYSQL_ROOT_PASSWORD=root \

-v mysql-volume:/var/lib/mysql \

-d mysql:latest \

--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Any change on the database from now will be persisted inside the volume and each subsequent run of MySQL database (from another MySQL container) will pick it up.