Project Phoenyx – Part 9: The Central Memory (MariaDB & Tools)

Category: Core Stack / Database | Read Time: approx. 15-20 minutes

Introduction: Why a Central Database?

Almost every service you will host in the future needs a memory. WordPress stores its articles there, Nextcloud its file indices, and Gitea its code history. This memory is usually an SQL database.

The Problem with "All-in-One" Containers

Many beginners use ready-made docker-compose.yml files from the internet that bring their own database for each service. If you have 10 services, then 10 database servers run in parallel. This wastes massive amounts of RAM and CPU power because every database process has its own overhead.

Our approach is more efficient: We start one central, powerful database server (MariaDB). All our applications (except Authentik, which needs Postgres) are allowed to share this server. We simply create a separate "drawer" (database) and a separate key (user) for each app on this server.


Step 1: Understanding the MariaDB Configuration

Let's look at the file mariadb-docker-compose.yml closely. I'll explain every line so you understand what we are actually doing.

services:
  mariadb-global:
    image: mariadb:11.3 
    container_name: mariadb-global
    restart: always

We use MariaDB instead of MySQL. MariaDB is a fork of MySQL that is developed by the community, performs better, and is more openly licensed. Version 11.3 is a current, stable version. The container is named mariadb-global so we can find it easily later.

Environment Variables (The Root Password)

    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - TZ=Europe/Berlin

Here we define the password for the "God Mode" of the database (root). Important: We do not write the password directly into the file! We use the variable ${MYSQL_ROOT_PASSWORD}. This means Docker fetches the real password from your .env file. This is safer in case you accidentally share your compose files.

Security through Isolation (The Network)

    networks:
      - database_internal
    # ports:
    #   - "127.0.0.1:3306:3306"  <-- Commented out!

This is the most important security aspect. We put the database into the network database_internal. Since this network is defined as internal: true (see Part 4), the database is invisible from the internet.

Even more: We commented out or omitted the ports section. This means you cannot even access port 3306 directly from the host server. The only way to the database is through the Docker network.

Persistence (Volumes)

    volumes:
      - mariadb-global-data:/var/lib/mysql
      - /opt/containers/mariadb/config:/etc/mysql/conf.d:ro

Containers are forgetful. If we restart MariaDB, all data would be gone. That's why we mount a volume. mariadb-global-data is a Docker volume where the actual database files reside. Even if we delete the container, the data remains safe in this volume.


Step 2: The Helper Script (db_create.sh)

Now we have a running database server. But how do we get data into it? Normally, you would have to log into the container via console, start the SQL client, and type cryptic commands like CREATE USER 'hans'@'%' IDENTIFIED BY 'secret';. Typos are guaranteed.

That's why I wrote the script db_create.sh for you. It takes this work off your hands.

What does the script do?

  1. It asks you interactively for credentials (root password, new DB name, new user).
  2. It checks if you entered everything correctly (password confirmation).
  3. It assembles the perfect SQL command in the background.
  4. It sends this command via docker exec directly into the running container.

The Speciality: The "%" Host

In the script, you find this line:

CREATE USER IF NOT EXISTS `${DB_USER}`@'%' ...

The @'%' is crucial. In SQL, you can restrict which IP a user is allowed to come from. Since container IPs (e.g., from WordPress) can change in Docker, % allows access from any IP. However, since our network database_internal is hermetically sealed, this is secure: Only containers in this specific net can knock at all.


Step 3: Practical Scenario – Setting up an App

Let's play this through. Suppose you want to install WordPress next week.

1. Start Script

You are on your server (via SSH) and start the script:

./db_create.sh

2. Enter Data

The script queries you. You enter:

3. Result

If everything works, you see a green success message. The script has created the database. Now go to your wordpress-docker-compose.yml and enter exactly these data:

services:
  wordpress:
    image: wordpress:latest
    environment:
      WORDPRESS_DB_HOST: mariadb-global  <-- The Container Name!
      WORDPRESS_DB_USER: wp_user
      WORDPRESS_DB_PASSWORD: super-secure-wp-password
      WORDPRESS_DB_NAME: wordpress_db
    networks:
      - traefik_public    (So it is reachable)
      - database_internal (So it gets to the DB)

This is the secret: WordPress uses the container name mariadb-global as the hostname. Docker resolves this internally to the correct IP.


Conclusion of the Core Stack

Congratulations! You made it. In 9 parts, we have built an infrastructure that meets professional standards:

From now on, we can dedicate ourselves to the fun part: Installing applications (apps). Since the foundation is solid, this will be child's play.