Sign in Subscribe
Mariadb

The Full Stack Migration: Moving Ghost and Matomo from MariaDB to MySQL

Vinay Tulluru

Vinay Tulluru

— 3 min read
The Full Stack Migration: Moving Ghost and Matomo from MariaDB to MySQL

If you're running a self-hosted Ghost blog, you might eventually face a critical decision: aligning your stack with the officially supported MySQL 8. This was my situation. To ensure long-term stability for my blog, I decided to migrate my entire data stack—including my Ghost blog and my Matomo analytics instance—from MariaDB to MySQL.

It sounds simple. But as I discovered, a database migration is rarely just about the data. It’s a full-stack challenge that tests your understanding of databases, applications, and the subtle ways they talk to each other.

Here’s my journey through the migration gauntlet, from cryptic database errors to application-level face-plants, and the solutions that finally brought everything back online.

Part 1: The Database Gauntlet - Migrating the Data

The first task was to move the raw data. I thought a simple mysqldump would suffice, but I immediately ran into a wall of compatibility errors.

Challenge #1: The "Syntax Error" from Another Dialect

My first attempt to import a standard MariaDB dump into MySQL failed instantly.

The Error: ERROR 1064 (42000): ... near 'PAGE_CHECKSUM=1 TRANSACTIONAL=0'

The Problem: MariaDB and MySQL are no longer drop-in replacements. MariaDB has its features (like the Aria storage engine) that MySQL doesn't understand. My backup file was speaking a MariaDB-specific dialect.

Challenge #2: The "Unknown Collation" Brick Wall

Even after fixing the first issue, a more insidious error appeared.

The Error: ERROR 1273 (HY000): Unknown collation: 'utf8mb4_uca1400_ai_ci'

The Problem: Collation rules (how text is sorted and compared) are different between the two systems. My dump was using a modern MariaDB collation that was gibberish to MySQL. This caused every CREATE TABLE command to fail, which in turn caused every INSERT command to fail.

The Database Solution: The "Clean and Correct" Dump

After much trial and error, I landed on a single, powerful command to create a perfectly compatible backup file. This command solves both problems at once.

On the source MariaDB server:

# Replace with your actual database names
mariadb-dump -p --no-table-options --databases ghost matomo \
| sed 's/utf8mb4_uca1400_ai_ci/utf8mb4_general_ci/g' \
> clean_and_final_backup.sql

What this does:

  1. --no-table-options: Strips the MariaDB-specific syntax.
  2. --databases ...: Explicitly selects only the databases I need, avoiding any corrupt or system tables.
  3. | sed 's/.../.../g': This is the magic. It pipes the dump output through a stream editor to find and replace the incompatible MariaDB collation with a universally understood one (utf8mb4_general_ci) on the fly.

With this clean backup file, the import into the new MySQL server was finally silent, smooth, and successful.

Part 2: The Application Fallout - When the App Still Speaks the Wrong Language

My Ghost blog fired up perfectly. Success! However, I then checked my Matomo analytics dashboard and was greeted with a 500 Internal Server Error. The migration wasn't over.

I checked the Matomo container logs:

podman logs matomo

The Error:

Error in Matomo: SQLSTATE[42000]: Syntax error or access violation: 1064 ... near 'max_statement_time=7200 FOR SELECT ...'

The Problem: The data was migrated, but the Matomo application itself still thought it was talking to a MariaDB server. It was generating MariaDB-specific SQL syntax (max_statement_time), which my new MySQL server correctly rejected as an error.

The application was speaking the wrong dialect to the database.

The Application Solution: Re-configuring Matomo

The fix wasn't in the database, but in Matomo's configuration file. Matomo needs to be explicitly told which database "adapter" to use.

  1. I shelled into my running Matomo container: podman exec -it matomo /bin/bash.
  2. I opened the configuration file for editing: nano /var/www/html/config/config.ini.php.
  3. After saving the file and restarting the Matomo container (podman restart matomo), the application began generating MySQL-compatible SQL. The errors vanished, and my analytics dashboard came back to life.

I replaced that outdated setting with the modern, explicit adapter key for MySQL:

[database]
host = "..."
username = "..."
password = "..."
dbname = "matomo"
adapter = "PDO_MYSQL"
charset = "utf8mb4"

Inside, I found the [database] section. The problem was this line:

schema = "Mariadb"

Final Takeaways from the Trenches

  1. A Migration is a Full-Stack Problem: Don't just plan for the data. Plan for re-configuring your applications to speak the correct database dialect.
  2. Compatibility is More Than Syntax: It's also about features, character sets, and collations. What works on one system can be a fatal error on another.
  3. Create the Cleanest Possible Artifact: The "dump and fix" workflow is key. Producing a universally compatible backup file from the start prevents a cascade of downstream errors.
  4. Application Logs are Your Best Friend: When an application fails after a migration, the error isn't always in the data. The application logs will tell you exactly what it was trying to do when it failed, often pointing directly to a configuration issue.

By addressing issues at both the database and application layers, I successfully completed a stable migration. My journey through this gauntlet helps you navigate yours with less friction. Happy migrating

Read more