Set up MySQL Docker image on your laptop, then verify it works using phpMyAdmin

By: (plus.google.com) +David Herron; Date: November 30, 2017

Tags: Docker » Docker MAMP

Setting up MySQL on Docker is fairly simple, and the MySQL team has done a credible job creating a flexible Docker image that can be used in many circumstances. Once the MySQL container is set up, you need a method to verify it can be accessed from other containers, and to manage the database it contains. Enter the mysql-client and phpMyAdmin Docker images. Both are easy to set up, and easy to use. Typically when deployed as part of an application stack, the MySQL container won't be visible to the public Internet but does need to be visible to other containers in your deployed application. Hence, there must be a private bridge network the containers use to communicate with each other, and the only ports published are what's required to supply the service to the public.

Unlike the MAMP product, what's shown here is equally applicable to macOS and Windows laptops.

The process shown here is likely to be the first step of developing an application that will later be deployed to a server. Rather than setting up a general-purpose MySQL server to contain all your databases, consider one database server per application. Then you bring the servers up and down based on which application you're developing at a given time.

In a later step you should use a Docker Compose file to coordinate bringing all the services up and down together. That way, when you turn to project A, you run docker-compose up in the directory containing project A, and all its services start up together.

But what's here is a pre-requisite step. To set up the containers and understand how to configure them.

Links for everything are at the bottom.

The general architecture for Docker service deployment

This is what we're aiming for - encapsulating several Docker containers such that minimal access is available for outsiders. It's claimed this improves security by limiting the access points miscreants can use to break into your service infrastructure.

With Docker we can create this architecture both on our laptop and on a server.

The MySQL container

The MySQL team has several containers it publishes as the Official MySQL Docker image. The MariaDB team, for what its worth, has published several containers with similar characteristics to the MySQL containers. You can consider using them interchangeably.

What I've used is the mysql/mysql-server image.

Download that image as so:

$ docker pull mysql/mysql-server:tag

As of this writing, MySQL 5.7 is the current GA release, so you would use mysql/mysql-server:5.7 as the tagged image name.

Or you can immediately run the image as so:

$ docker run --name=mysql2 --env MYSQL_ROOT_PASSWORD=f00bar  mysql/mysql-server:5.7
Initializing database
Database initialized
MySQL init process in progress...
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

/entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*


MySQL init process done. Ready for start up.


This starts a MySQL server using default configuration. The server ports are not published anywhere. It's run in the foreground so you can view the server output.

We get a running Docker image:

$ docker ps -a
CONTAINER ID        IMAGE                        COMMAND                  CREATED              STATUS                       PORTS                               NAMES
374ab77b0778        mysql/mysql-server:5.7       "/entrypoint.sh mysq…"   About a minute ago   Up About a minute            3306/tcp, 33060/tcp                 mysql2

Notice that the ports are not published. Running the MySQL client on your laptop will fail:

$ mysql -u root -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

We didn't expose any port from the MySQL container, but we can do this:

$ docker exec -it mysql2 mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.01 sec)

mysql> 

In other words, we can reach into the running container and run the MySQL command-line client within that container to access the database.

$ docker exec -it mysql2 bash
[root@374ab77b0778 /]# ps ax
  PID TTY      STAT   TIME COMMAND
    1 ?        Ssl    0:00 mysqld
  113 pts/0    Ss     0:00 bash
  126 pts/0    R+     0:00 ps ax

We can also reach into the container and get a regular BASH command-line prompt to do whatever we wish.

That got our feet wet, in that we have a running MySQL instance. Obviously the current status isn't what we need because:

  • The access permissions and password is incompletely set up
  • The data will disappear as soon as the container is deleted -- because the database is stored inside the container
  • The default configuration has very limited buffer sizes and won't have good performance
  • It is not set up to be accessed by other containers

Cleaning up - and the ephemeral nature of Docker images

Now that our little experiment is done, lets clean up the laboratory:

$ docker stop mysql2
mysql2
$ docker rm mysql2
mysql2

In normal circumstances we can use docker stop container-name and docker start container-name to start/stop the container as needed. In this case we want to delete the comtainer because we don't need it at all. One thing which happens is the database files are deleted along with the container.

A database is a thing that might last for decades. For example I have a pair of websites ( (visforvoltage.org) visforvoltage.org and davidherron.com)running on Drupal which were built well over 10 years ago. The same MySQL database instance has been running those sites for the whole time.

Docker images are meant to be created and destroyed freely. That's incompatible with databases, and therefore it's necessary to store the data and configuration outside the Docker container. We'll get to that over the next couple sections.

Configuring access

The password configured above might be too guessable, and a miscreant might be able to get into your server. Try this to generate a randomized password:

$ docker run --name=mysql2 --env MYSQL_RANDOM_ROOT_PASSWORD=true  mysql/mysql-server:5.7
Initializing database
Database initialized
MySQL init process in progress...
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
GENERATED ROOT PASSWORD: .IxXet13n0ssyhIvX@l+uplaDb

/entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*


MySQL init process done. Ready for start up.

You can grab the generated password programmatically as so:

$ docker logs mysql2 2>&1 | grep GENERATED
GENERATED ROOT PASSWORD: .IxXet13n0ssyhIvX@l+uplaDb

There are several environment variables which can be used to customize the MySQL instance that's started:

MYSQL_RANDOM_ROOT_PASSWORD: As shown earlier, this generates a randomized password that is printed to the console while initializing the container.

MYSQL_USER and MYSQL_PASSWORD: Together these set up a user name other than root to use with the server. You may prefer this rather than having the user-name be root.

MYSQL_DATABASE: Sets the database name to initialize in the server. The root user, and the user specified in MYSQL_USER, are given superuser access (GRANT ALL) to this database.

MYSQL_ROOT_PASSWORD Sets the root password as we saw earlier.

MYSQL_ROOT_HOST Sets a pattern for the allowed hosts to connect to the server. By default root@localhost is allowed to access the database. That's sufficient to connect to the database from inside the container. But, the most likely use-case is access to the database from other containers. For example --env MYSQL_ROOT_HOST=% allows access from any IP address. To allow access from the host on which the container is running, --env MYSQL_ROOT_HOST=172.17.0.1. To allow access from any Docker container, --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0.

MYSQL_LOG_CONSOLE: Sends the error output to the console so it can be viewed using the docker logs command.

MYSQL_ALLOW_EMPTY_PASSWORD: Allow the root password to be blank.

Container initialization is done the first time around

The first time the Docker image is run, MySQL initializes its database and the user access rights onto the database. The environment variables mentioned earlier are consulted during that initialization and then are not used later.

There's a concern that security configuration information -- the root password -- can "leak" and become known by miscreants if it's specified on the command line. Therefore it's preferable to use the MYSQL_RANDOM_ROOT_PASSWORD feature, and extract the password during the first time the database is run.

External configuration with customized my.cnf

The default /etc/my.cnf in the image is limited. It specifies small buffer sizes that won't provide good performance under load. You'll almost certainly want to customize the configuration file.

One way to proceed is to create a custom Dockerfile. In a directory containing a Dockerfile, also have a my.cnf file with whatever settings you desire.

FROM mysql/mysql-server:5.7
COPY my.cnf /etc/my.cnf
EXPOSE 3306 33060
CMD ["mysqld"]

Another simpler way, that doesn't involve creating a custom Docker image, is to mount the configuration file into the container using the --volume option.

$ docker run --name mysql3 \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    mysql/mysql-server:5.7

The --volume command is a general purpose method of mounting data from outside the container into the container. It was meant for mounting directory structures within the container, but it also works with individual files as shown here.

It's beyond the scope of this tutorial to show you how to customize the MySQL configuration file. Whole books and reams of software tools exist for that purpose. Among the links below are relavent sections in the MySQL manual.

External data

The next thing to solve is storing the database outside the container. As it stands, the database files disappear as soon as we delete the container. Containers are ephemeral, while databases want to be immortal. Those are not compatible, and thus it's necessary to use the --volume option to store the data elsewhere.

MySQL stores its data in the directory /var/lib/mysql. Using --volume or the VOLUME feature in Dockerfile's we can cause a directory on the host computer to be visible within the container.

$ docker run --name mysql4 \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --env MYSQL_DATABASE=foodb \
    --volume `pwd`/mysql-data:/var/lib/mysql \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    mysql/mysql-server:5.7

Access by other containers - using a custom bridge network

So far we haven't addressed the issue that the database is not accessible from anywhere. The most likely scenario is a database that's deployed as part of a whole application. For the purposes of this tutorial we'll consider a single MySQL instance (no mirrored servers) on the same host computer as the application.

Docker provides a virtual network facility. You can easily create a private network that exists virtually within the host computer, and is only accessible by containers on that host. Docker can also create virtual networks that span multiple host computers, but that's outside our scope for now.

The point of creating a virtual private network between Docker containers is security. You want the containers to be able to communicate with each other, but you don't want outside access to the database.

Most advice about connecting Docker containers is to use the --link option. With that option the containers are attached together and can create IP networking connections between each other. However, that option is deprecated and may be removed in the future.

What's preferred now is to create a bridge network and attach the containers to that network.

$ docker network create --driver bridge dbnet

First we create the bridge network. These networks are cheap and easy to create and feel free to create as many as required for your service architecture.

We named this one dbnet because it's where the database server lives. Any container wishing to access the database should be attached to this network.

$ docker run --name mysql5 \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0 \
    --env MYSQL_DATABASE=foodb \
    --volume `pwd`/mysql-data:/var/lib/mysql \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    --network dbnet \
    mysql/mysql-server:5.7

Any other container you wish to have access to this network also requires the --network dbnet parameter.

Within the private network the container will have a DNS host name of whatever you specified in the --name parameter.

Docker sets up customized DNS servers within the infrastructure it creates. Service discovery is meant to be done using the DNS host name.

Hence, the other containers need to be configured to access the database server by its DNS name. The DNS name will simply be the name you specified (in this case mysql5), not mysql5.mumbeletyfoo.com or anything like that. Since Docker runs custom DNS servers, it can configure them so that simple DNS names like mysql5 resolve correctly.

Testing access using mysql-client container

We now have a MySQL server with a good user access controls, external data that can live for a long time, an external configuration, that is accessible from other containers on the given network. We need to test that's actually the case. Penetration testing is outside the scope of this tutorial, but at least we can demonstrate access by other containers.

One way to verify access from other containers is to run the mysql command line in another container. We've already run that command within the container, but we need to demonstrate access to the MySQL database from another container.

$ docker network create --driver bridge foodbnet
29069d2c7a321914263d58628a6e619cf6117fa2221e504c764a4b7807479dd5
$ mkdir mysql-data
$ docker run --name foodb \
    --env MYSQL_RANDOM_ROOT_PASSWORD=true \
    --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0 \
    --env MYSQL_DATABASE=foodb \
    --volume `pwd`/mysql-data:/var/lib/mysql \
    --volume `pwd`/my.cnf:/etc/my.cnf \
    --network foodbnet \
    mysql/mysql-server:5.7
Initializing database
Database initialized
MySQL init process in progress...
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
GENERATED ROOT PASSWORD: EPRYJnIJ,oPv0f3t0jtEDYw3c]3
mysql: [Warning] Using a password on the command line interface can be insecure.

/entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

MySQL init process done. Ready for start up.

Let's use that command to create a database named foodb on foodbnet with hostname foodb.

$ docker run -it --network foodbnet imega/mysql-client mysql -h foodb -u root -p foodb
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [foodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foodb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.08 sec)

MySQL [foodb]> 

And then we connect to it as so. There are several Docker containers available offering the MySQL client programs. I picked imega/mysql-client because it was the most popular.

The important fact is that this is a separate container from the one containing the database.

$ docker inspect foodbnet
[
    {
        "Name": "foodbnet",
        "Id": "29069d2c7a321914263d58628a6e619cf6117fa2221e504c764a4b7807479dd5",
        "Created": "2017-11-24T02:47:26.390619431Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "192.168.16.0/20",
                    "Gateway": "192.168.16.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "8efdda2de0fba44feea57ed2ce32c075f2a502a0a2a6a16a0426fb4a19ca9af1": {
                "Name": "musing_mestorf",
                "EndpointID": "1d156dc12444968c18774dd78945f1855c7cd5f04369285b222b7bbc51a4f932",
                "MacAddress": "02:42:c0:a8:10:03",
                "IPv4Address": "192.168.16.3/20",
                "IPv6Address": ""
            },
            "cdfef5f82a4911b270b158bf9bfeaa28731de4c5fbb81fbcb591d1cf21b361bc": {
                "Name": "foodb",
                "EndpointID": "bf0dcb1aa35c57bbe41085fc2ee4caa53f0797f4ab1be0fb040c3d0173297d42",
                "MacAddress": "02:42:c0:a8:10:02",
                "IPv4Address": "192.168.16.2/20",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

The docker inspect proves there are two containers connected to foodbnet.

$ docker ps
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS                       PORTS                               NAMES
8efdda2de0fb        imega/mysql-client           "/with-wait.sh mysql…"   3 minutes ago       Up 3 minutes                                                     musing_mestorf
cdfef5f82a49        mysql/mysql-server:5.7       "/entrypoint.sh mysq…"   4 minutes ago       Up 4 minutes                 3306/tcp, 33060/tcp                 foodb

Another thing to notice is that foodbnet is on subnet 192.168.16.0/20. This is an important detail because --env MYSQL_ROOT_HOST=172.0.0.0/255.0.0.0 did not allow for access to the container. This setting would allow connection from any IP address in the 172.0.0.0 network, but our container is in the 192.168.16.0 network instead.

$ docker run -it --network foodbnet imega/mysql-client mysql -h foodb -u root -p foodb
Enter password: 
ERROR 1130 (HY000): Host '192.168.16.3' is not allowed to connect to this MySQL server

Instead, this is what we were told. Indeed, that address was not configured to be allowed access to the server.

One approach is that, when creating the foodbnet, to configure the subnet for that network. Something like this:

$ docker network create \
  --driver=bridge \
  --subnet=172.28.0.0/16 \
  --ip-range=172.28.5.0/24 \
  --gateway=172.28.5.254 \
  foodbnet

What I chose to do instead is to recreate the foodb container with --env MYSQL_ROOT_HOST=%, instead. This allows any IP address to connect to the server. This may not be the best strategy, instead it may be more correct to explicitly configure the network IP address particulars, and fix everything correctly.

phpMyAdmin

The next level of proof is to run phpMyAdmin since it not only allows us to verify access to the container, we have a nice GUI to manage the database.

$ docker run --rm --network foodbnet --env PMA_HOST=foodb -p 9080:80 phpmyadmin/phpmyadmin
2017-11-24 03:19:37,245 CRIT Supervisor running as root (no user in config file)
2017-11-24 03:19:37,245 WARN Included extra file "/etc/supervisor.d/nginx.ini" during parsing
2017-11-24 03:19:37,246 WARN Included extra file "/etc/supervisor.d/php.ini" during parsing
2017-11-24 03:19:37,264 INFO RPC interface 'supervisor' initialized
2017-11-24 03:19:37,264 CRIT Server 'unix_http_server' running without any HTTP authentication checking
2017-11-24 03:19:37,265 INFO supervisord started with pid 1
2017-11-24 03:19:38,270 INFO spawned: 'php-fpm' with pid 20
2017-11-24 03:19:38,274 INFO spawned: 'nginx' with pid 21
2017-11-24 03:19:39,383 INFO success: php-fpm entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2017-11-24 03:19:39,384 INFO success: nginx entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)

This is relatively easy to set up, just follow the instructions on the Docker Hub page for the container. In this model we connect the container to foodbnet and tell it to access the host at foodb for the database. We turn our browser to http://localhost:9080 and log in with the user credentials for this server.

And, voila, we have phpMyAdmin communicating with the database server

The phpMyAdmin instance is started with --rm on the command line. This option causes the container to be automatically thrown away when it exits. This way we don't have to clean up the containers after use. That particular one does not require any long-term state to be kept around and instead can be thrown away at will.

MySQL Workbench

Another popular tool for managing a MySQL database is MySQL Workbench. It's a nice desktop GUI that offers a lot of powerful features.

As it stands MySQL Workbench cannot access foodb because there is no port to access. The foodb server does not export a port, because we declared the need to keep it walled off so that miscreants can't wreak havoc.

The only method is to have foodb publish a port so that MySQL Workbench can access that port. Doing so is as simple as adding -p 3306:3306 to the command line which initializes the MySQL container.

It seems that MySQL Workbench supports accessing a remote MySQL instance over an SSH tunnel. But, an SSH tunnel cannot reach into a Docker container, unless you configure the container to export an SSH port. Doing so creates another access port through which a miscreant could potentially reach into the container and wreak havoc.

Summary

We've covered a lot of material here. We've established the ground on which we can build a model for application stacks deployed with Docker.

With tongue fully in cheek we can call this the DAMP Stack. That is, Docker, Apache, MySQL, and the P languages.

Links

MySQL containers:

MySQL configuration:

Docker

Clients

MariaDB

« Bypassing the NY Times paywall, and read NY Times content for free Does a larger SSD drive have longer lifetime expectancy? »
2016 Election Acer C720 Ad block AkashaCMS Amazon Amazon Kindle Amiga Android Anti-Fascism AntiVirus Software Apple Apple Hardware History Apple iPhone Apple iPhone Hardware April 1st Arduino ARM Compilation Astronomy Asynchronous Programming Authoritarianism Automated Social Posting Ayo.JS Bells Law Big Brother Big Finish Bitcoin Mining Black Holes Blade Runner Blockchain Blogger Blogging Books Botnet Botnets Cassette Tapes Cellphones Christopher Eccleston Chrome Chrome Apps Chromebook Chromebooks Chromebox ChromeOS CIA CitiCards Citizen Journalism Civil Liberties Clinton Cluster Computing Command Line Tools Comment Systems Computer Hardware Computer Repair Computers Cross Compilation Crouton Cryptocurrency Curiosity Rover Cyber Security Cybermen Daleks Darth Vader Data backup Data Storage Database Database Backup Databases David Tenant DDoS Botnet Detect Adblocker Developers Editors Digital Photography Diskless Booting Disqus DIY DIY Repair DNP3 Do it yourself Docker Docker MAMP Docker Swarm Doctor Who Doctor Who Paradox Drobo Drupal Drupal Themes DVD E-Books E-Readers Early Computers Election Hacks Electric Bicycles Electric Vehicles Electron Emdebian Encabulators Energy Efficiency Enterprise Node EPUB ESP8266 Ethical Curation Eurovision Event Driven Asynchronous Express Facebook Fake News Fedora VirtualBox File transfer without iTunes FireFly Fraud Freedom of Speech Gallifrey git Gitlab GMAIL Google Google Chrome Google Gnome Google+ Government Spying Great Britain Heat Loss Hibernate Hoax Science Home Automation HTTPS Human ID I2C Protocol Image Analysis Image Conversion Image Processing ImageMagick In-memory Computing InfluxDB Infrared Thermometers Insulation Internet Internet Advertising Internet Law Internet of Things Internet Policy Internet Privacy iOS Devices iPad iPhone iPhone hacking Iron Man Iternet of Things iTunes Java JavaScript JavaScript Injection JDBC John Simms Journalism Joyent Kaspersky Labs Kindle Kindle Marketplace Lets Encrypt LibreOffice Linux Linux Hints Linux Single Board Computers Logging Mac OS Mac OS X Machine Readable ID macOS MacOS X setup Make Money Online MariaDB Mars Matt Lucas MEADS Anti-Missile Mercurial Michele Gomez Micro Apartments Military Hardware Minification Minimized CSS Minimized HTML Minimized JavaScript Missy Mobile Applications MODBUS Mondas MongoDB Mongoose Monty Python MQTT Music Player Music Streaming MySQL NanoPi Nardole NASA Net Neutrality Node Web Development Node.js Node.js Database Node.js Testing Node.JS Web Development Node.x North Korea npm NY Times Online advertising Online Community Online Fraud Online Journalism Online Video Open Media Vault Open Source Governance Open Source Licenses Open Source Software OpenAPI OpenVPN Paywalls Personal Flight Peter Capaldi Photography PHP Plex Plex Media Server Political Protest Postal Service Power Control Privacy Production use Public Violence Raspberry Pi Raspberry Pi 3 Raspberry Pi Zero Recycling Refurbished Computers Remote Desktop Republicans Retro-Technology Reviews Right to Repair River Song Robotics Rocket Ships RSS News Readers rsync Russia Russia Troll Factory Russian Hacking SCADA Scheme Science Fiction Search Engine Ranking Season 1 Season 10 Season 11 Security Security Cameras Server-side JavaScript Shell Scripts Silence Simsimi Skype Social Media Social Media Warfare Social Networks Software Development Space Flight Space Ship Reuse Space Ships SpaceX Spear Phishing Spring Spring Boot SQLite3 SSD Drives SSD upgrade SSH SSH Key SSL Swagger Synchronizing Files Telescopes Terrorism The Cybermen The Daleks The Master Time-Series Database Torchwood Total Information Awareness Trump Trump Administration Trump Campaign Ubuntu UDOO Virtual Private Networks VirtualBox VLC VNC VOIP Web Applications Web Developer Resources Web Development Web Development Tools Web Marketing Website Advertising Weeping Angels WhatsApp Window Insulation Wordpress YouTube YouTube Monetization