Tags: Docker »»»» Docker MAMP »»»» Docker MySQL »»»» MySQL

Docker excels at supporting what's now called micro-services. In practice this means the best practice is for each container to provide a single well defined service. Applying that model to a database means a Docker-deployed database contain exactly handle one database instance. What we mean is that in the micro-service world, it is theoretically a best practice for each database server to contain one database instance. And, indeed, that's what the MySQL Docker container supports.

The case I'm working on currently involves replicating the web hosting service I have rented from a web hosting provider. Namely, I have a VPS (virtual private server) onto which I've deployed several websites running either Drupal or WordPress. Each of course requires a database instance to hold the content and configuration. The web hosting provider offers managed MySQL servers in which we can store databases.

I have set up a VPS on a different hosting provider, and am using Docker Swarm to host the required services. There will be an Apache instance configured to run PHP, to provide the application layer. We then need a database layer to support three websites, two running on Drupal and the third on WordPress.

Out of the box the Docker MySQL container supports a single database instance. The obvious simple approach is to create a docker-compose.yml with three database servers, one for each website. Then adding another website would necessitate adding another database server. That's not terribly scalable due to the memory resource impact of running multiple database servers. It's more desirable, in my mind, to host all databases in the same database server.

To make this concrete, let's look a simplified Docker Compose file (Stack file) for the system I'm building.

version: '3.8'

services:

    db:
        image: "mysql/mysql-server:5.7"
        container_name: db
        networks:
           - dbnet
        volumes:
           - /opt/server/db:/var/lib/mysql
        restart: always
        environment:
           MYSQL_ROOT_PASSWORD: "ROOT PASSWORD"
           MYSQL_ROOT_HOST: "10.%"
           MYSQL_USER: USER-NAME
           MYSQL_PASSWORD: PASSWORD
           MYSQL_DATABASE: DATABASE-NAME

    phpmyadmin:
        image: phpmyadmin/phpmyadmin
        networks:
            - dbnet
        environment:
            PMA_HOST: db
            PMA_USER: root
            PMA_PASSWORD: "ROOT PASSWORD"
            PHP_UPLOAD_MAX_FILESIZE: 1G
            PHP_MAX_INPUT_VARS: 1G
        ports:
            - "8001:80"

    drupal:
        image: "robogeek/drupal-php-7.1"
        container_name: drupal
        networks:
            - dbnet
        ports:
            - "80:80"
        restart: always
        volumes:
            - /opt/server/roots:/var/www:rw
            - /opt/server/sites-enabled:/etc/apache2/sites-enabled:rw

networks:
    dbnet:
        driver: overlay

The drupal container is built using an image I've developed, drupal-php-7.1, which is a PHP container, with Apache, that is customized for use with Drupal websites. We don't need to go into the details, so consider that the primary content of this container is an Apache instance, and that instance can of course be configured to support multiple websites.

Because the drupal container supports multiple websites, it is best if the db container supports multiple databases. But as you see here, the mysql/mysql-server container is configured using environment variables, and those variables only allow the declaration of a single database.

Additionally, we also set up a PHPMyAdmin container. Such a container works best to connect to the database server using the root user ID to have full control over the server. But, the MySQL container by default supports connecting with the root user only from the local container, and not from other containers.

In both cases there are a few simple SQL commands that let us declare new databases, declare a user ID to access each database, and to declare a root user that can connect from other containers.

Connecting as root to a MySQL database from other containers

In the docker-compose.yml shown here we include a PHPMyAdmin container. This is a popular tool for managing MySQL servers. For full effect it is best if PHPMyAdmin is connected to a MySQL account that has ALL privileges to the server. For the mysql/mysql-server container, that fully privileged account has the name root.

By default this container only allows root connections only from the local host. But to support this PHPMyAdmin container, we need to support connections from other hosts. Remember that in Docker each container instance acts like its own host machine.

In MySQL, an account is created by adding an entry to the mysql.user table. One way to do that is with the CREATE USER command in SQL. The MySQL documentation on adding accounts and assigning priveleges is worth your time to read.

The mysql/mysql-server container has two relevant environment variables for setting up a fully privileged user. That user has the name root and by default one sets the MYSQL_ROOT_PASSWORD variable to set up the password for the root account. If you use no other variable, the root account will only support contact from localhost, which is a best practice. It is good form to have extremely limited access to the fully privileged user.

Using solely MYSQL_ROOT_PASSWORD is equivalent to the following SQL:

CREATE USER 'root'@'localhost'
    IDENTIFIED BY 'PASSWORD';
GRANT ALL 
    ON *.* 
    TO 'root'@'localhost';

The MySQL documentation on validating an incoming connection describes precisely how this works. There are three pieces of information at play - a) the IP address of the source of the incoming connection, b) the specified user ID, c) the supplied password. The specified user ID and the IP address are matched against the User and Host columns of entries in the mysql.user table. For any row which matches the incoming connection, the passwords are compared to see if they match. The first row that fully matches becomes the active user, and otherwise the connection attempt fails.

The GRANT command then determines the level of access the identified account has. Because it is the root user, we use GRANT ALL to give the account full access to every last everything.

The MYSQL_ROOT_HOST variable is how we support fully privileged access from another host. This variable changes things so the following SQL is executed:

CREATE USER 'root'@'MYSQL_ROOT_HOST'
    IDENTIFIED BY 'PASSWORD';
GRANT ALL 
    ON *.* 
    TO 'root'@'MYSQL_ROOT_HOST';

Then, what value do we supply to this variable to get the desired effect? Here's a few examples:

In the example above we specified 10.% as the wildcard domain name. This is because in the example we saw the dbnet network was in the 10.0.0.0/8 IP address range.

Creating multiple databases in mysql/mysql-server

We just learned about MySQL user accounts and validating access to a MySQL server. From this point we could run this:

CREATE DATABASE appdb;

We could then do anything to that database using our root user because it has the GRANT ALL privilege. But this is by no means a best practice. Instead the best practice is to limit access rights to limit the scope of potential damage in case of intrusion.

That means application code should be using a limited MySQL account that has access solely to the database(s) required for the application.

We can easily extrapolate from what we just discussed and see what to do:

CREATE USER 'appuser'@'%'
    IDENTIFIED BY 'PASSWORD';
GRANT ALL 
    ON database.* 
    TO 'appuser'@'%';

This is a good start, because it limits access to a specific table. But there are several other possible GRANT settings to use besides ALL.

For example a more common arrangement might be:

CREATE USER 'appuser'@'%'
    IDENTIFIED BY 'PASSWORD';
GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE
    ON database.* 
    TO 'appuser'@'%';

This allows for typical application operations (create, read, update, delete) on database table rows, as well as the ability to create and delete tables.

Now we can think about how to implement this in the mysql/mysql-server container. In the example above we set MYSQL_ROOT_PASSWORD and MYSQL_ROOT_HOST to control access to the root account. We also set a MYSQL_USER and MYSQL_PASSWORD that will have access to the database named in MYSQL_DATABASE.

The latter caused this SQL to execute:

CREATE USER 'USER-NAME'@'%'
    IDENTIFIED BY 'PASSWORD';

That is, the specified user ID is allowed to connect from anywhere using the password.

We can also inspect what GRANT setting this used when creating the user account:

mysql> SHOW GRANTS FOR 'USER-NAME'@'%';
+--------------------------------------------------------------+
| Grants for USER-NAME@%                                         |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'USER-NAME'@'%'                          |
| GRANT ALL PRIVILEGES ON `DATABASE-NAME`.* TO 'USER-NAME'@'%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

The SHOW GRANTS FOR command does what it sounds like, it shows the access grants assigned to the specified user ID. This tells us the SQL used to create that account was this:

CREATE USER 'USER-NAME'@'%'
    IDENTIFIED BY 'PASSWORD';
GRANT ALL 
    ON DATABASE-NAME.* 
    TO 'USER-NAME'@'%';

This just happens to be the SQL we showed earlier.

Finally, this tells us the simple step we do to create another database and user ID. Namely:

CREATE USER 'USER-NAME'@'%'
    IDENTIFIED BY 'PASSWORD';
CREATE DATABASE newdb;
GRANT ALL 
    ON newdb.* 
    TO 'USER-NAME'@'%';

That is, we first investigated what the mysql/mysql-server container does to create the initial user ID and database. We then used the same pattern to create another database and user ID, assigning it similar privileges.

Even though the mysql/mysql-server container doesn't directly support multiple users and databases, a few simple SQL commands lets us configure the database server to our liking.

About the Author(s)

David Herron : David Herron is a writer and software engineer focusing on the wise use of technology. He is especially interested in clean energy technologies like solar power, wind power, and electric cars. David worked for nearly 30 years in Silicon Valley on software ranging from electronic mail systems, to video streaming, to the Java programming language, and has published several books on Node.js programming and electric vehicles.