Docker and Database(s)

If you are reading this, I assume that you are comfortable with docker, docker compose and SQL databases like MariaDB.

Most docker compose files which use a database create one instance of a database service for each stack. This is done so that each database instance is isolated and cannot be accessed by other apps. I find this rather unnecessary and inefficient on smaller systems such as a Raspberry Pi. This post aims to address this problem by specifying commands that can be used to keep a single instance of database that multiple apps can use.

My Raspberry Pi, on which this site is running, has multiple apps running which need a database. WordPress and Nextcloud are two such common apps which are self hosted using docker and require a database. Docker compose file of Nextcloud and WordPress looks like this at Docker Hub.

version: '2'

volumes:
  nextcloud:
  db:

services:
  db:
    image: mariadb
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW
    restart: always
    volumes:
      - db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_PASSWORD=
      - MYSQL_DATABASE=nextcloud
      - MYSQL_USER=nextcloud

  app:
    image: nextcloud
    ports:
      - 8080:80
    links:
      - db
    volumes:
      - nextcloud:/var/www/html
    restart: always

version: '3.1'

services:

  wordpress:
    image: wordpress
    restart: always
    ports:
      - 8080:80
    environment:
      WORDPRESS_DB_HOST: db
      WORDPRESS_DB_USER: exampleuser
      WORDPRESS_DB_PASSWORD: examplepass
      WORDPRESS_DB_NAME: exampledb
    volumes:
      - wordpress:/var/www/html

  db:
    image: mariadb
    restart: always
    environment:
      MYSQL_DATABASE: exampledb
      MYSQL_USER: exampleuser
      MYSQL_PASSWORD: examplepass
      MYSQL_RANDOM_ROOT_PASSWORD: '1'
    volumes:
      - db:/var/lib/mysql

volumes:
  wordpress:
  db:

If you look at the compose files, you can see that each compose file creates a database service and the main application stores its data in this database. If you were to run these applications, you would see two different instances of Mariadb running on your server. If your server has limited capacity such as a Raspberry Pi, you can avoid this by creating a single database instance. Here is how it works –

• First, create a database instance of the database required by the applications. I use Mariadb which is SQL compliant and open source and used by many apps that I have deployed. My docker compose file looks like this –

# Docker compose for Maria DB - MySQL replacement
version: '3.5'

services:
  mariadb:
    image: linuxserver/mariadb
    restart: always
    container_name: mariadb
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: examplepass
    volumes:
      - ./mariadb/data:/config
    networks:
      - mariadb

networks:
  mariadb:
    name: mariadb

Notice the extra networks portion in the compose file. This is of importance. The network section in the docker compose file instructs docker to create a virtual LAN network on which this container will run. We can add multiple apps on the same network so that they can discover each other using service name. The service name for this container is “mariadb” and the same will be used by other applications to talk to the database.

• Next, we need to create an instance of our application and link the Mariadb network to the application so that it can talk to this external database. Lets look at WordPress docker compose file as an example –

# Docker compose for WordPress
# Bringup DB container before this
version: '3.1'

services:
  wordpress:
    image: wordpress
    container_name: wordpress
    restart: always
    ports:
      - 8000:80
    environment:
      WORDPRESS_DB_HOST: mariadb
      WORDPRESS_DB_USER: wordpress
      WORDPRESS_DB_PASSWORD: examplepass
      WORDPRESS_DB_NAME: wordpress
    volumes:
      - ./wordpress/data/:/var/www/html

networks:
  default:
    external:
      name: mariadb

If you see, the network section of this docker compose file lists the mariadb network so that it is placed on the same network.

But we are not done here. If you look at the environment variables of the WordPress container, you can see that the application expects a database which has a user “wordpress” created and that user has access to “wordpress” database. This user and database creation is usually done when the database container is brought up. But since we are using a single database container for multiple applications, we need to create these users and databases on our own before the applications can be created.

• In order to create our own users and databases, we need to fire some SQL command on the Mariadb container.

First lets find out the docker container name for Mariadb. I gave the mariadb as the container name.

$ docker ps -a | grep mariadb
eba3e884934a        linuxserver/mariadb              "/init"                  7 weeks ago         Up 11 days             0.0.0.0:3306->3306/tcp                           mariadb

Now that we know that the container is running, lets exec into it.

$ docker exec -it mariadb bash

You should have a bash prompt on your container now. We can see that mysqld is running inside the container.

root@eba3e884934a:/# ps -ax
    PID TTY      STAT   TIME COMMAND
      1 ?        Ss     0:00 s6-svscan -t0 /var/run/s6/services
     35 ?        S      0:00 s6-supervise s6-fdholderd
    272 ?        S      0:00 s6-supervise mariadb
    274 ?        Ss     0:00 bash ./run
    283 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
    499 ?        Sl    24:17 /usr/sbin/mysqld --basedir=/usr --datadir=/config/databases --plugin-dir=/usr/lib/mysql/plugin --skip-log-error --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
    500 ?        S      0:01 logger -t mysqld -p daemon error
   1980 pts/0    Ss     0:00 bash
   1992 pts/0    R+     0:00 ps -ax

We can now login into the SQL shell using the root user and the password that was used to create the database container. On this SQL shell, we will fire SQL queries to create users and databases.

root@eba3e884934a:/# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2503
Server version: 10.4.13-MariaDB-1:10.4.13+maria~bionic-log mariadb.org binary distribution

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

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

• Lets create a user for our application. For WordPress, the username is wordpress.

MariaDB [(none)]> 
CREATE USER 'wordpress'@'%' IDENTIFIED BY 'examplepass';

MariaDB [(none)]> select user from mysql.user;
+-----------+
| user      |
+-----------+
| root      |
| wordpress |
+-----------+
2 rows in set (0.002 sec)

Notice the special char ‘%’ after the username. This signifies that the user can login to the database from any IP or domain. By default, the user can login only from localhost or local machine. This is not true for our application container as it will be isolated from the database container. For security purposes, you can restrict ‘%’ to a specific hostname or an IP address.

• Once the user is created, we need to create a database “wordpress” and give the user permissions to perform operations on the created database. This can be done as follows –

MariaDB [(none)]> create database wordpress;
Query OK, 1 row affected (0.302 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wordpress          |
+--------------------+

MariaDB [(none)]> GRANT USAGE ON wordpress.* TO 'wordpress'@'%' IDENTIFIED BY 'examplepass';
Query OK, 0 rows affected (0.005 sec)

We restrict the user access to the newly created database only. This will help in isolating this user to a single database.

That’s it. We have now created a new user with a password and a database to operate on. The application can now be brought up and can communicate with the Mariadb container.

References –

1. Docker hub – For docker compose files
2. MySQL reference page – For SQL commands