; Date: September 20, 2020
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:
Type | Sample | Discussion |
---|---|---|
Fully qualified domain name | foo.example.com |
The incoming connection can only come from the named host |
Fully qualified IP address | 172.20.3.5 | The incoming connection can only come from the named IP address |
Wildcard domain name | %.example.com , %.example.% |
The incoming connection can come from any host that matches the domain name |
Wildcard IP address | 172.%, 198.51.100.0/255.255.255.0 | The incoming connection can come from any host with a matching IP address |
Wildcard | % | Any host name or IP address will match |
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.