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.

Stopping the container


$ docker container stop my-test-mysql
my-test-mysql

References:

Connect to MySQL running in Docker container from a local machine | by Md Kamaruzzaman | Towards Data Science

1 comment:

Taylor said...

I just have to introduce this hacker that I have been working with him on getting my credit score been boosted across the Equifax, TransUnion and Experian report. He made a lot of good changes on my credit report by erasing all the past eviction, bad collections and DUI off my credit report history and also increased my FICO score above 876 across my three credit bureaus report you can contact him for all kind of hacks . Email him here support@wavedrive.tech go on their website wavedrive.tech for more details,Whatsapp No:+14106350697 if you want to chat them up,One thing i can assure you would not regret this at all he is 100% legit