Part 1 explained the model and the pitfalls. This part is the hands-on walkthrough: prepare the database, inspect and import the dump, wire Matomo to it, align the schema, and do the post-migration configuration the dump never carried.

Placeholders used throughout: containers mariadb / matomo, database/user matomo / matomo, public domain analytics.example.com, web user www-data. All commands assume Podman; substitute docker if that’s your runtime. In a rootless Podman setup, run every podman command as the container-owning user — never with sudo, or you’ll drive a different set of containers.

Two shortcuts used below:

DB_EXEC="podman exec -i mariadb"            # SQL commands
APP_EXEC="podman exec -u www-data matomo"   # Matomo console

---

Step 1 — Prepare the MariaDB database

1a. Import-time server settings

These durable settings belong to the container configuration (ask your infra team):

max_allowed_packet      = 1G
innodb_buffer_pool_size = 4G          # ~50-70% of allocated RAM
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

Remember the collation coupling from Part 1: utf8mb4_general_ci must be identical here, in the CREATE DATABASE below, and as the target of the import sed.

One optimization can be applied hot, without restarting the container, to speed up the import:

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" -e "SET GLOBAL innodb_flush_log_at_trx_commit=2;"

Set this back to 1 as soon as the import finishes (see Step 5b). Leaving it at 2 means a hard server crash can lose ~1s of committed transactions — fine for a replayable import, not acceptable once you’re collecting production traffic.

1b. Create the database, user and grants

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" << 'SQL'
CREATE DATABASE IF NOT EXISTS matomo
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- '%' because the app container connects over the internal container network
CREATE USER IF NOT EXISTS 'matomo'@'%' IDENTIFIED BY '<APP_PWD>';

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, DROP, ALTER, INDEX,
      CREATE TEMPORARY TABLES, LOCK TABLES,
      CREATE VIEW, SHOW VIEW, TRIGGER, EXECUTE
  ON matomo.* TO 'matomo'@'%';
FLUSH PRIVILEGES;
SQL

The DDL grants (CREATE, DROP, ALTER, INDEX) are required because core:update modifies the schema; CREATE TEMPORARY TABLES / LOCK TABLES are required for archiving.

---

Step 2 — Receive and inspect the dump

These operations happen on the host — the dump never needs to enter a container (the import streams in via stdin).

2a. Detect the real format — the extension lies

Support sometimes ships a file named .sql.tar.gz that is actually a plain gzip of the .sql, not a tar archive. tar then fails silently (empty output, exit 1), and listing a real tar would need to decompress the whole 20 GB. One-second test:

file "$DUMP"
gunzip -c "$DUMP" | head -3      # "-- MySQL dump ..." => plain gzip

• Plain gzip → use gunzip -c.
• Real tar archive → use tar -xzOf.

2b. Read the prefix (and prepare to read the version)

gunzip -c "$DUMP" | grep -m1 -i 'CREATE TABLE'   # first table -> the prefix

matomo_log_visit → prefix matomo_; log_visit or access → empty prefix. This is Invariant 1 from Part 1 — get it wrong and Matomo shows an empty database.

2c. One-pass compatibility scan (MySQL 8 → MariaDB)

A MySQL 8 dump can contain constructs MariaDB doesn’t support. This single pass lists them and grabs the version along the way:

gunzip -c "$DUMP" | awk '
  index($0,"utf8mb4_0900_ai_ci")>0 {c++}      # MySQL 8 collation (must convert)
  index($0,"DEFAULT_GENERATED")>0  {dg++}      # generated columns
  index($0," VISIBLE")>0           {vis++}     # invisible columns (MySQL 8)
  index($0,"/*!80")>0              {v80++}     # 8.0-gated syntax
  index($0,"CHECK (")>0            {chk++}     # CHECK constraints
  index($0,"CONSTRAINT")>0         {cons++}    # foreign keys
  { p=index($0,"version_core"); if (p>0 && !v) { print "version_core => " substr($0,p,60); v=1 } }
  END{ printf "0900_ai_ci=%d generated=%d invisible=%d gated80=%d check=%d fk=%d\n",
       c,dg,vis,v80,chk,cons }'

Only utf8mb4_0900_ai_ci should be > 0 (handled in Step 3). Everything else — generated / invisible / gated80 / check / fk — must be 0 for a direct import; if not, resolve them before importing. On a ~22 GB dump this scan takes ~3 minutes.

---

Step 3 — Convert the collation

utf8mb4_0900_ai_ci doesn’t exist in MariaDB (Invariant 3). A single substitution to utf8mb4_general_ci fixes it. The validated method is in-stream during the import — no intermediate file, no extra 20 GB on disk. You’ll see it inline in the next step.

If you do need a converted dump on disk (for multiple replays):

gunzip -c "$DUMP" | sed 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' | gzip > dump.maria.sql.gz

---

Step 4 — Import the dump

The dump stays on the host and is pushed to podman exec‘s stdin — the -i flag is mandatory. The dump already sets UNIQUE_CHECKS=0 and FOREIGN_KEY_CHECKS=0 in its header. Run this inside tmux or screen — the import takes over an hour and must survive an SSH disconnect.

First, confirm the target database is empty — CREATE DATABASE IF NOT EXISTS does not fail on a populated database, and importing over existing content can leave it incoherent:

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" -N -e \
 "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='matomo';"
# Expected: 0

Then import, converting the collation in-stream:

tmux new -s matomo
time (gunzip -c "$DUMP" \
  | sed 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' \
  | podman exec -i mariadb \
      mariadb --default-character-set=utf8mb4 -u root -p"<ROOT_PWD>" matomo)

For a plain .sql, replace gunzip -c "$DUMP" with cat "$DUMP". The --default-character-set=utf8mb4 matters — without it, accents come back corrupted.

Track progress from another session. The table counter freezes for a long time while the big log_* tables load, so volume size is a better progress indicator than table count:

watch -n 60 "podman exec -i mariadb mariadb -u root -p'<ROOT_PWD>' -N -e \
 \"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='matomo';\""

As an order of magnitude: ~430 tables and ~22 GB imported in about 1h45. If the import is interrupted, start over from an empty database (DROP DATABASE matomo; then recreate it) — a partial import is not reliable.

---

Step 5 — Verify the import and restore durability

5a. Check the data landed

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" matomo <<'SQL'
SELECT COUNT(*) AS nb_tables FROM information_schema.tables WHERE table_schema='matomo';
SELECT option_value AS version_core FROM `option` WHERE option_name='version_core';
SELECT COUNT(*) AS sites FROM `site`;
SELECT COUNT(*) AS log_visit FROM log_visit;
SELECT DATE(MAX(visit_last_action_time)) AS last_data FROM log_visit;
-- collation coherence: MUST return a single row
SELECT table_collation, COUNT(*) FROM information_schema.tables
 WHERE table_schema='matomo' GROUP BY table_collation;
SQL

With an empty prefix, remember the backticks on `option` and `site`. The last query is the collation check from Part 1: a single row (utf8mb4_general_ci) means healthy; multiple rows mean a mixed estate you must fix now, before future archive_* tables inherit the wrong collation and throw Illegal mix of collations weeks later.

5b. Restore durability immediately

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" -e \
 "SET GLOBAL innodb_flush_log_at_trx_commit=1; SELECT @@innodb_flush_log_at_trx_commit;"
# Expected: 1

Do this now, not later — the =2 optimization was import-only, and the instance is about to start collecting real traffic. This value is volatile; have your infra team make it durable in the container config so a restart can’t reset it.

---

Step 6 — Verify the Matomo image version

The image is built and provided by your infra team; this is a verification, and it’s the check that decides whether core:update will succeed:

$APP_EXEC php -r 'require "/var/www/html/core/Version.php"; echo Piwik\Version::VERSION, "\n";' \
  2>/dev/null || $APP_EXEC grep -m1 "const VERSION" /var/www/html/core/Version.php

The value must be ≥ the dump’s version_core (ideally identical, which makes core:update a no-op). If it’s lower, stop — the image must be rebuilt on the correct pinned build (Invariant 2, Part 1). Don’t proceed; core:update will refuse.

---

Step 7 — Wire Matomo to the imported database

Write config.ini.php into the volume mounted at /var/www/html/config/, as the container’s web user.

Derive the prefix from the dump — don’t type it from memory. Get the first table and map it to a prefix, and stop if it isn’t recognized:

FIRST_TABLE=$(gunzip -c "$DUMP" | grep -m1 -i 'CREATE TABLE' | sed -E 's/.*`([^`]+)`.*/\1/')
case "$FIRST_TABLE" in
  access)   export TABLE_PREFIX="" ;;
  *_access) export TABLE_PREFIX="${FIRST_TABLE%access}" ;;
  *)        echo "!! Unrecognized prefix -> STOP, inspect the dump" ;;
esac
echo "TABLE_PREFIX=[$TABLE_PREFIX]"

SALT=$(openssl rand -hex 16)
podman exec -i -u www-data matomo sh -c 'cat > /var/www/html/config/config.ini.php' <<EOF

[database]

host = « mariadb » username = « matomo » password = « <APP_PWD> » dbname = « matomo » tables_prefix = « $TABLE_PREFIX » charset = « utf8mb4 » adapter = « PDO\MYSQL » [General] trusted_hosts[] = « analytics.example.com » salt = « $SALT » force_ssl = 1 proxy_client_headers[] = « HTTP_X_FORWARDED_FOR » proxy_host_headers[] = « HTTP_X_FORWARDED_HOST » EOF podman exec -u root matomo chmod 640 /var/www/html/config/config.ini.php # Re-read to confirm the prefix that was actually written $APP_EXEC grep tables_prefix /var/www/html/config/config.ini.php

Four things that matter here:

• host is the database container name on the internal network — not localhost.
• tables_prefix uses the derived value. A wrong prefix shows an empty database while the data is right there. And re-read the file: automated generation can silently drop an empty-string prefix (Part 1).
• The two proxy_* lines are mandatory behind the reverse proxy, or every visit carries the proxy IP. The header names must match what your proxy actually sends.
• This file must live on a persistent volume. Written to the ephemeral container layer, it vanishes on the first restart and Matomo reruns its installer.

---

Step 8 — Align the schema (core:update)

$APP_EXEC ./console core:update --yes
$APP_EXEC ./console core:clear-caches
$APP_EXEC ./console core:version

• « Everything is already up to date » = versions aligned (the expected result when your image matches the dump’s build).
• « database created by a more recent version » = your image is older than Cloud → back to Step 6, the image must be rebuilt.

---

Step 9 — Post-migration configuration

Nothing below is in the dump. This is where a migration that imported cleanly becomes a migration that actually works.

9a. Tag Manager — the critical one

If your sites use Tag Manager, this step keeps them tracked (Part 1 explains why). Enable the plugin, regenerate every published container, and verify a 200:

$APP_EXEC ./console plugin:activate TagManager
$APP_EXEC ./console tagmanager:regenerate-released-containers

# Verify a container is actually served (must be HTTP 200, ~100 KB)
IDC=$($DB_EXEC mariadb -u root -p"<ROOT_PWD>" matomo -N -e \
      "SELECT idcontainer FROM tagmanager_container WHERE status='active' LIMIT 1;" | tr -d '\r')
curl -sS -o /dev/null -w "container %{http_code} / %{size_download} bytes\n" \
     "https://analytics.example.com/js/container_${IDC}.js"

A 404 means the files weren’t generated or /var/www/html/js/ isn’t reachable — do not cut over, tracking would be down. Remember these files must be on a persistent volume.

9b. Geolocation (GeoIP)

The GeoIP database is not in the dump. In the UI: Administration → Geolocation → GeoIP 2, pick a provider (DB-IP Lite is free, auto-downloaded, no account), and enable automatic updates. In a container, the downloaded database must land on a persistent volume or geolocation silently reverts after the next recreation.

Historical visits keep the geolocation computed while on Cloud (it’s stored in the DB, so it migrated) — only new traffic uses the on-premise database. Re-attributing history is a heavy operation (./console usercountry:attribute over millions of visits) and usually unnecessary.

9c. Premium plugins

Inventory what was active on Cloud, and check actual volume (rows in the DB), not just activation:

SELECT REPLACE(option_name,'LastPluginActivation.','') AS plugin,
       FROM_UNIXTIME(option_value) AS last_activation
  FROM `option` WHERE option_name LIKE 'LastPluginActivation.%'
 ORDER BY option_value DESC;

Premium plugins that carry real data (FormAnalytics, ActivityLog, …) will lose their reports in the UI until licensed — but no data is deleted; the tables stay intact and re-adding the license later restores everything, history included. The only required action is to tell users which reports are temporarily unavailable. Plugins that are Cloud-only (Cloud, Billing, CDN, OAuth2, …) are meaningless self-hosted — don’t try to reinstall them. Any plugin installed outside the image must live on a persistent volume.

9d. Users, passwords and 2FA — nothing to do

Users, hashed passwords and 2FA secrets are all in the dump. Everyone logs back in with their existing Cloud credentials; no mass reset. A couple of admin commands for edge cases:

$APP_EXEC ./console twofactorauth:disable-2fa-for-user --login=<login>   # lost 2FA device
$APP_EXEC ./console login:unblock-blocked-ips                            # brute-force lockout

(Note: core:create-superuser / user:set-password do not exist in this version — create or reset users via the UI.)

9e. GDPR / privacy settings

Retention, IP anonymization and Do-Not-Track are in-database settings, so they migrated with the dump. But they engage compliance — verify them explicitly:

$DB_EXEC mariadb -u root -p"<ROOT_PWD>" matomo -e \
 "SELECT option_name, option_value FROM \`option\` \
   WHERE option_name LIKE 'PrivacyManager%' ORDER BY option_name;"

One subtlety worth understanding: the log-purge setting (delete_logs_enable) was already active and running on Cloud. Self-hosting doesn’t create the purge — it can only prevent it, because these tasks only run if you schedule archiving (Part 3). During the migration itself it’s common to disable the purge to rule out any concurrent deletion, then re-enable it after stabilization. IP anonymization and Do-Not-Track settings inherited from Cloud should be confirmed with your DPO.

9f. Security files, SMTP and scheduled reports

Generate the security files:

$APP_EXEC ./console core:create-security-files

SMTP and scheduled reports are wired last, and in a specific order — this is a cutover concern, covered in Part 3. The rule to remember now: never connect SMTP before you’ve decided what happens to the migrated scheduled reports, or the next archive run blasts emails to real recipients.

---

Where this leaves us

At this point the database is imported and verified, Matomo is wired to it and schema- aligned, Tag Manager serves live containers, and geolocation, plugins, users and privacy settings are handled. What’s left is timing — doing all of this in the right order around a go-live so tracking never drops and no stray email goes out.

Part 3 covers exactly that: the day-before / day-of / stabilization timeline, archiving via systemd, backups, the reverse-proxy IP check, GDPR re-activation, and rollback.

A three-part, hands-on series on moving a large Matomo analytics instance from Matomo Cloud to a self-hosted, containerized install. This first part explains what the migration actually is, the three invariants that silently break it, and the landmines that aren’t in any dump. Parts 2 and 3 cover the full procedure and the cutover.

All figures below (≈13 M visits, 23 sites, ≈22 GB dump, ≈430 tables, ≈1h45 import) come from a real migration but are given only as orders of magnitude. Client-specific names have been replaced by generic placeholders (analytics.example.com, mariadb, matomo). Re-measure everything on your dump.

---

What « migrating off Matomo Cloud » really means

The instinct is to picture a clean export/import of a running application. It isn’t. The single most important fact to internalize:

Matomo Cloud support gives you a SQL dump of the database only. The Cloud config.ini.php is never included. You regenerate it on the self-hosted side.

Everything else follows from that. The database carries your history, your sites, your users (including hashed passwords and 2FA secrets), and most in-database settings. But the application wiring — database connection, security salt, trusted hosts, proxy headers, the list of enabled plugins, geolocation database, SMTP — is not in the dump. You rebuild it.

So the migration is really four moves:

1. Import the Cloud dump into a fresh database.
2. Point a self-hosted Matomo at that database (write config.ini.php).
3. Run core:update to align the schema to your (≥ Cloud) Matomo version.
4. Verify, then reconfigure everything the dump didn’t carry.

Target architecture

The setup this series targets is two containers on an internal network:

• mariadb — MariaDB 10.11 LTS, with a persistent volume for /var/lib/mysql.
• matomo — a Matomo image (version pinned, see below), persistent volume for /var/www/html, sitting behind a reverse proxy that terminates HTTPS.

MariaDB — not MySQL — is deliberate, and it’s the source of the nastiest invariant below.

---

The three invariants that break the migration if you get them wrong

These three are not « best practices ». Each one, set wrong, produces a broken or misleading result — sometimes silently. Check all three before you import.

Invariant 1 — The table prefix must match the dump exactly

Matomo prefixes every table (matomo_log_visit, matomo_option, …). The prefix is a config value (tables_prefix in config.ini.php). If it doesn’t match the prefix baked into the dump, Matomo sees an empty database — even though every row is present.

The prefix is often matomo_, but it can be empty. Read it from the first CREATE TABLE in the dump; don’t assume:

gunzip -c "$DUMP" | grep -m1 -i 'CREATE TABLE'
# matomo_access  -> prefix is "matomo_"
# access         -> prefix is EMPTY  ->  tables_prefix = ""

The empty-prefix trap. When the prefix is empty, some tables collide with SQL reserved words (option, site). Every SQL statement must backtick them: `option`, `site`. Worse, automated config generation (e.g. the official Matomo Docker image driven by env vars) can silently omit tables_prefix when the value is an empty string — the empty variable simply isn’t written. Matomo then falls back to its internal default matomo_ and fails with Table 'matomo.matomo_option' doesn't exist behind a misleading « Matomo is already installed » page. Always re-read the written config.ini.php and confirm the literal line tables_prefix = "" is present before touching the UI.

Invariant 2 — Your Matomo version must be ≥ the Cloud version

core:update migrates the schema forward. If your self-hosted Matomo is older than the version that produced the dump, it refuses: « database created by a more recent version ». The Cloud version is stored in the dump as the version_core option.

Here’s the catch that surprises everyone: Matomo Cloud permanently runs on the alpha channel, roughly one minor version ahead of the latest stable. Waiting for a stable release that is ≥ Cloud never converges — the moment a stable catches up, Cloud has moved on.

The consequence:

• You must run the same alpha build as Cloud on the self-hosted side.
• No alpha image is published to the container registry — a check of the official matomo image found hundreds of tags, zero containing « alpha », only stable versions. The alpha exists only as an archive on builds.matomo.org.
• So you build your own image: start from the official image and swap the sources for the pinned alpha archive. Pin the exact build (nightlies change daily), and keep a copy of the archive — builds.matomo.org purges old nightlies.

If the go-live slips and Cloud advances past your dump, the image must be re-qualified against the new dump’s version_core.

Invariant 3 — MySQL 8 collation doesn’t exist in MariaDB

Matomo Cloud runs on MySQL 8. Its dumps often carry the collation utf8mb4_0900_ai_ci, which does not exist in MariaDB. Import aborts immediately with:

Unknown collation 'utf8mb4_0900_ai_ci'

The fix is a single global substitution to utf8mb4_general_ci, done in-stream during the import so you don’t write a second 20 GB file to disk:

gunzip -c "$DUMP" | sed 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' | ...import...

The coupling you must never break. utf8mb4_general_ci has to be the same value in three places: the server default, the CREATE DATABASE, and the target of the sed. Why it matters beyond cosmetics: imported tables carry their collation explicitly, but Matomo creates new tables constantly — a fresh archive_numeric_YYYY_MM / archive_blob_YYYY_MM pair every month — and those inherit the database default, not the imported tables’ collation. If the two values diverge, the database slowly becomes mixed and joins between old and new tables fail with Illegal mix of collations. The symptom appears weeks later, at the first month rollover — nearly impossible to trace back to the migration. After import, confirm a single collation across all tables:

SELECT table_collation, COUNT(*) FROM information_schema.tables
 WHERE table_schema='matomo' GROUP BY table_collation;

One row = healthy. Multiple rows = mixed estate, fix before cutover.

---

The landmines that aren’t in the dump

The three invariants above are the ones that abort the import. The following are worse in a way: they let the migration appear to succeed, then break tracking or reporting later. None of them are in the dump.

Tag Manager: the #1 risk to live tracking

If your sites are tracked via Matomo Tag Manager, this is the single most dangerous step. Two independent problems:

1. Tag Manager is present in the image but not enabled. It’s a free bundled plugin, but the list of enabled plugins lived in the Cloud config.ini.php — which you don’t have. So it defaults to inactive.
2. The container JS files are not in the dump. Files like container_XXXX.js are generated on the filesystem when a release is published. The dump only holds the database. So the releases exist in the DB, but no container_*.js exists on disk → https://analytics.example.com/js/container_XXXX.js returns 404 and every site using that container stops being tracked.

The fix (Part 2 details it): enable the plugin, regenerate all released containers, and verify a container actually serves a 200 — not a 404. And these files live in /var/www/html/js/, which must be on a persistent volume or they vanish on the next container recreation.

Reverse-proxy headers: get them wrong and every visit is falsified

Behind a reverse proxy, Matomo sees the proxy’s IP on every request unless you tell it which forwarded-for header to trust:

[General]
proxy_client_headers[] = "HTTP_X_FORWARDED_FOR"
proxy_host_headers[]   = "HTTP_X_FORWARDED_HOST"

Miss this and 100% of visits carry the proxy IP from the very first minute — data that is falsified and unrecoverable. The header name must match what your proxy actually sends.

Persistent volumes: the silent config wipe

config.ini.php, installed plugins, the GeoIP database, and the generated js/container_*.js files must all live on persistent volumes. If any of them is written to the container’s ephemeral layer, it disappears on the first restart — and Matomo either reruns its installer or serves 404 containers, silently. The only reliable check is to podman restart before cutover and confirm everything survives.

First archiving must run the day before go-live

On a large history (millions of visits) the first full archive run takes hours. If you start it the morning of go-live, it won’t finish before users log in — reports would be slow and incomplete exactly when everyone looks. This isn’t recoverable inside the cutover window. It has to run the evening before.

Scheduled reports fire themselves

The Cloud’s scheduled email reports are migrated in the database and trigger automatically at the end of the first archive run. Two failure modes:

• With a working SMTP relay, reports go out to the real recipients during your rehearsal — embarrassing at best.
• Without SMTP, the send fails and core:archive exits with code 1 even though archiving succeeded — a false alarm every day.

So: decide the fate of scheduled reports before any archive run, and never wire up SMTP before you’ve dealt with them.

Premium plugins: nothing is lost, but reports disappear

Premium plugins (FormAnalytics, ActivityLog, Heatmaps, Funnels, …) that were part of the Cloud subscription are not licensed on your self-hosted instance by default. Crucially:

A missing premium plugin deletes nothing. Its tables stay intact in the database; the reports simply become invisible in the UI. Re-adding the license later restores full access, history included — no re-import, no data manipulation.

The only action required is to tell users which reports are temporarily unavailable.

---

What migrates vs what you rebuild

A useful mental split before Part 2:

Carried by the dump	Rebuilt on self-hosted (not in the dump)
Historical data (visits, actions, conversions)	config.ini.php (DB connection, salt, trusted hosts)
Sites, goals, segments, dashboards	Geolocation (GeoIP) database
Users + passwords + 2FA secrets	SMTP / email relay
In-database settings (option, plugin_setting)	Premium plugins & licenses
Scheduled reports (⚠️ fire on their own)	Scheduled archiving (systemd timer)

---

Where this leaves us

The migration is: import a DB-only dump, fix the three invariants (prefix, version, collation), point Matomo at it, core:update, then rebuild everything the dump never carried — with Tag Manager, proxy headers, and persistent volumes as the traps most likely to bite after everything looks fine.

Part 2 walks the full procedure step by step: preparing MariaDB, inspecting and importing the dump, writing config.ini.php, running core:update, and the complete post-migration configuration. Part 3 covers the cutover timeline, archiving, GDPR/retention, backups, and rollback.