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.