Infrastructure at your Service

Elisa Usai

Deploy a MySQL Server in Docker containers

We hear about Docker every day. By working on MySQL Server, I am curious to test this platform which makes it possible to create containers independent of the OS to deploy virtualized applications.
So let’s try to deploy a MySQL Server with Docker!


Here is the architecture we will put in place:
MySQL on Docker
So we will run a Docker container for MySQL within a VM.

I’m working on a CentOS 7 installed on a VirtualBox Machine:

[root@node4 ~]# cat /etc/*release*
CentOS Linux release 7.5.1804 (Core)
Derived from Red Hat Enterprise Linux 7.5 (Source)

I install Docker on my VM and enable the Docker service:

[root@node4 ~]# yum install docker
[root@node4 ~]# systemctl enable docker.service

I start the Docker service:

[root@node4 ~]# systemctl status docker.service
● docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: http://docs.docker.com
[root@node4 ~]# systemctl stop docker.service
[root@node4 ~]# systemctl status docker.service
● docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: http://docs.docker.com
[root@node4 ~]# systemctl start docker.service
[root@node4 ~]# systemctl status docker.service
● docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2018-10-05 16:42:33 CEST; 2s ago
     Docs: http://docs.docker.com
 Main PID: 1514 (dockerd-current)
   CGroup: /system.slice/docker.service
           ├─1514 /usr/bin/dockerd-current --add-runtime docker-runc=/usr/libexec/docker/docker-runc-current --default-runtime=docker-runc --exec-opt nati...
           └─1518 /usr/bin/docker-containerd-current -l unix:///var/run/docker/libcontainerd/docker-containerd.sock --metrics-interval=0 --start-timeout 2...
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.561072533+02:00" level=warning msg="Docker could not enable SELinux on the...t system"
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.597927636+02:00" level=info msg="Graph migration to content-addressability... seconds"
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.598407196+02:00" level=info msg="Loading containers: start."
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.642465451+02:00" level=info msg="Firewalld running: false"
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.710685631+02:00" level=info msg="Default bridge (docker0) is assigned with... address"
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.762876995+02:00" level=info msg="Loading containers: done."
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.780275247+02:00" level=info msg="Daemon has completed initialization"
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.780294728+02:00" level=info msg="Docker daemon" commit="8633870/1.13.1" gr...on=1.13.1
Oct 05 16:42:33 node4 systemd[1]: Started Docker Application Container Engine.
Oct 05 16:42:33 node4 dockerd-current[1514]: time="2018-10-05T16:42:33.799371435+02:00" level=info msg="API listen on /var/run/docker.sock"
Hint: Some lines were ellipsized, use -l to show in full.

I check my network:

[root@node4 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 08:00:27:f3:9e:fa brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global noprefixroute dynamic enp0s3
       valid_lft 85959sec preferred_lft 85959sec
    inet6 fe80::a00:27ff:fef3:9efa/64 scope link
       valid_lft forever preferred_lft forever
3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 08:00:27:45:62:a7 brd ff:ff:ff:ff:ff:ff
    inet 192.168.56.204/24 brd 192.168.56.255 scope global noprefixroute enp0s8
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe45:62a7/64 scope link
       valid_lft forever preferred_lft forever
4: docker0:  mtu 1500 qdisc noqueue state DOWN group default
    link/ether 02:42:b0:bf:02:d6 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 scope global docker0
       valid_lft forever preferred_lft forever
[root@node4 ~]# docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
b32241ce8931        bridge              bridge              local
9dd4a24a4e61        host                host                local
f1490ec17c17        none                null                local

So I have a network bridge named docker0 to which an IP address is assigned.

To obtain some information about the system, I can run the following command:

[root@node4 ~]# docker info
Containers: 0
 Running: 0
 Paused: 0
 Stopped: 0
Images: 0
Server Version: 1.13.1
Storage Driver: overlay2
 Backing Filesystem: xfs
 Supports d_type: false
 Native Overlay Diff: true
Logging Driver: journald
Cgroup Driver: systemd
Plugins:
 Volume: local
 Network: bridge host macvlan null overlay
Swarm: inactive
Runtimes: docker-runc runc
Default Runtime: docker-runc
Init Binary: /usr/libexec/docker/docker-init-current
containerd version:  (expected: aa8187dbd3b7ad67d8e5e3a15115d3eef43a7ed1)
runc version: 5eda6f6fd0c2884c2c8e78a6e7119e8d0ecedb77 (expected: 9df8b306d01f59d3a8029be411de015b7304dd8f)
init version: fec3683b971d9c3ef73f284f176672c44b448662 (expected: 949e6facb77383876aeff8a6944dde66b3089574)
Security Options:
 seccomp
  WARNING: You're not using the default seccomp profile
  Profile: /etc/docker/seccomp.json
Kernel Version: 3.10.0-862.3.2.el7.x86_64
Operating System: CentOS Linux 7 (Core)
OSType: linux
Architecture: x86_64
Number of Docker Hooks: 3
CPUs: 1
Total Memory: 867.7 MiB
Name: node4
ID: 6FFJ:Z33K:PYG3:2N4B:MZDO:7OUF:R6HW:ES3D:H7EK:MFLA:CAJ3:GF67
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Experimental: false
Insecure Registries:
 127.0.0.0/8
Live Restore Enabled: false
Registries: docker.io (secure)

For the moment I have no containers:

[root@node4 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

Now I can search the Docker Hub for MySQL images, and I pull the first one in my example (I normally chose an official build with the biggest number of stars):

[root@node4 ~]# docker search --filter "is-official=true" --filter "stars=3" mysql
INDEX       NAME                DESCRIPTION                                     STARS     OFFICIAL   AUTOMATED
docker.io   docker.io/mysql     MySQL is a widely used, open-source relati...   7075      [OK]
docker.io   docker.io/mariadb   MariaDB is a community-developed fork of M...   2267      [OK]
docker.io   docker.io/percona   Percona Server is a fork of the MySQL rela...   376       [OK]
[root@node4 ~]# docker pull docker.io/mysql
Using default tag: latest
Trying to pull repository docker.io/library/mysql ...
latest: Pulling from docker.io/library/mysql
802b00ed6f79: Pull complete
30f19a05b898: Pull complete
3e43303be5e9: Pull complete
94b281824ae2: Pull complete
51eb397095b1: Pull complete
54567da6fdf0: Pull complete
bc57ddb85cce: Pull complete
d6cd3c7302aa: Pull complete
d8263dad8dbb: Pull complete
780f2f86056d: Pull complete
8e0761cb58cd: Pull complete
7588cfc269e5: Pull complete
Digest: sha256:038f5f6ea8c8f63cfce1bce9c057ab3691cad867e18da8ad4ba6c90874d0537a
Status: Downloaded newer image for docker.io/mysql:latest

I create my container for MySQL named mysqld1:

[root@node4 ~]# docker run -d --name mysqld1 docker.io/mysql
b058fba64c7e585caddfc75f5d96076edb3e80b31773f135d9e44a3487724914

But if I list it, I see that I have a problem, it has exited with an error:

[root@node4 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                      PORTS               NAMES
b058fba64c7e        docker.io/mysql     "docker-entrypoint..."   55 seconds ago      Exited (1) 54 seconds ago                       mysqld1
[root@node4 ~]# docker logs mysqld1
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

That means that I forgot password assignment for the ‘root’ user account of MySQL Server. So I stop and the remove the container, and create it again with some additional options:

[root@node4 ~]# docker stop b058fba64c7e
b058fba64c7e
[root@node4 ~]# docker rm b058fba64c7e
b058fba64c7e
[root@node4 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
[root@node4 ~]# docker run --name mysqld1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=manager -d docker.io/mysql
46a2020f58740d5a87288073ab6292447fe600f961428307d2e2727454655504

Now my container is up and running:

[root@node4 ~]#  docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
46a2020f5874        docker.io/mysql     "docker-entrypoint..."   5 seconds ago       Up 5 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   mysqld1

I can execute a bash shell on the container in an interactive mode to open a session on it:

[root@node4 ~]# docker exec -it mysqld1 bash
root@46a2020f5874:/#

And try to connect to MySQL Server:

root@46a2020f5874:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

Great news, everything works well! In a few minutes I have a MySQL Server on his latest version up and running in a Docker container.

 

2 Comments

  • Olivier says:

    Hi Elisa,
    nice blog post!

    As you may know, an alternative is to use the MySQL Docker image created, maintained and supported by the MySQL team. The command is almost the same:

    docker pull mysql/mysql-server

    I’m not a Docker expert, thus while I find the bash option very useful for admin tasks on the “host” (e.g. configuration, log analysis,…)
    I prefer use directly the mysql client to connect to the server
    e.g.
    daz:~$ docker ps -a
    CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
    8a4e34436787 mysql/mysql-server:8.0.12 “/entrypoint.sh mysq…” 5 minutes ago Up 5 minutes (healthy) 3306/tcp, 33060/tcp mysqld1

    daz:~$ docker exec -it mysqld1 mysql -uroot -p

    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 59
    Server version: 8.0.12 MySQL Community Server – GPL

    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

    mysql>

    Or even better use MySQL Shell :)

    daz:~$ docker exec -it mysqld1 mysqlsh root@localhost -S/var/lib/mysql/mysql.sock –sql

    Creating a session to ‘root@/var%2Flib%2Fmysql%2Fmysql.sock’
    Please provide the password for ‘root@/var%2Flib%2Fmysql%2Fmysql.sock': ****
    Fetching schema names for autocompletion… Press ^C to stop.
    Your MySQL connection id is 64
    Server version: 8.0.12 MySQL Community Server – GPL
    No default schema selected; type \use to set one.
    MySQL Shell 8.0.12

    Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type ‘\help’ or ‘\?’ for help; ‘\quit’ to exit.

    MySQL localhost SQL >

    Do you see any advantages to use the bash option over the client option?

    Olivier

     
  • Elisa Usai says:

    Hi Olivier!
    Thank you for following my blog posts.
    And for sure, we can use MySQL official images and MySQL client/shell to connect to the server :)
    Elisa

     

Leave a Reply

Elisa Usai
Elisa Usai