Project: VPS platform

One database for all: the shared MariaDB

Two WordPress sites run on the server, and a Python app and a Spring Boot app are on their way. Until now, each WordPress site had its own database in its own container. That is clean and easy to reason about, and it costs memory I don’t have on a VPS with 4 GB.

What got replaced are the two production containers projects-mariadb-odabas-1 and projects-mariadb-waverider-1. The two infra-mariadb-* instances of the test stack kept running unchanged.

This description is a snapshot from 17 September: odabas.ch was still running as WordPress at the time. Since 27 September the site is static, and odabasdb sits unused in the instance.

The decision: one instance, several databases

A database server has a baseline cost no matter how little data it manages: buffer pool, process overhead, connection handling. In the original plan for four apps on one instance, I estimated the saving at 700 MB to 1 GB. That is not measured, and the calculation has shifted since: the Spring Boot app gets its own PostgreSQL instance, while MariaDB serves WordPress and, later, the Python app. What is certain: one instance pays its baseline once instead of twice.

I don’t give up separation between the apps in the process. Each app gets its own logically separate database and its own user, who can only access that one database. What WordPress on odabas.ch writes, WordPress on waverider52.com can neither read nor change.

What I give up for it

The price is worth stating honestly: if the shared instance fails or comes under load, several apps are affected at once. For a portfolio, a WordPress blog and two small applications with no high-load operation, I consider that acceptable. For a service with an availability promise, I would decide differently.

Back up before touching anything

Before anything moves, there is a backup, and even that step shows why assumptions need checking. The task assumed container names that didn’t exist: the old databases are called projects-mariadb-odabas-1 and projects-mariadb-waverider-1. And MariaDB 11.7.2 no longer ships mysqldump at all; the tool is now called mariadb-dump:

docker exec projects-mariadb-odabas-1 \
  mariadb-dump -u root -p"$MARIADB_ROOT_PASSWORD" odabasdb | gzip > odabasdb.sql.gz

The password came from the host shell, not from the container: the variable is already expanded on the host. The compressed dumps were small (39 KB and 260 KB) and both succeeded. Only then did I move on.

The move

The new instance mariadb-shared keeps its data in a bind mount on the host (./data/db-shared), like the other data directories. Inside it, two databases are created, each with its own user:

CREATE DATABASE odabasdb;
CREATE USER 'odabasuser'@'%' IDENTIFIED BY '…';
GRANT ALL PRIVILEGES ON odabasdb.* TO 'odabasuser'@'%';

The point is odabasdb.*: the privileges end at the boundary of the user’s own database. The same goes for waveriderdb. After that I imported the dumps and spot-checked that tables and contents had arrived.

The inconspicuous pitfall: depends_on

For the WordPress containers, switching the database host is not enough:

environment:
  WORDPRESS_DB_HOST: mariadb-shared
depends_on:
  - mariadb-shared

The second line is the one that is easy to forget. As long as depends_on still points to mariadb-odabas, Compose brings the old database back up on the next start, even though it is long unused. A corpse that resurrects itself, and you only notice once it eats memory.

How I know it’s right

“The page loads” isn’t proof enough for me, because a cached page loads even without a working database. So I checked several things:

  • The logs of both WordPress containers show no database errors since the move. There were occasional Apache notices (AH00558), unrelated to the migration.
  • Both sites return HTTP 200.
  • Logging in to wp-admin works on both. It writes session data to the database and isn’t cached, so it proves real write access.

The actual evidence for the separation came later: waverideruser can read waveriderdb.wp_comments, but gets ERROR 1142 … SELECT command denied on odabasdb.wp_comments.

The fallback stays in place

I only stopped the old containers, I didn’t remove them. They sit there with status Exited and could start again in an emergency. This deliberately deviates from the original acceptance criterion (“removed”): until the new setup has run uneventfully for a few days, a stopped container costs me nothing, and a deleted one couldn’t be brought back. Final cleanup is a separate, later step.

Takeaway

The saved baseline is the headline, but what I learned came from the edges: check names in the task instead of believing them, re-check tools after version jumps, carry depends_on along, and measure a migration against things that can’t work without a functioning database.