; Date: September 20, 2020
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
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.
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.
root to a MySQL database from other containers
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
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.
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.
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.
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.
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:
|Fully qualified domain name||
||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||
||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
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
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_HOST to control access to the
root account. We also set a
MYSQL_PASSWORD that will have access to the database named in
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)
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.