Mastering MySQL & MariaDB Binlog Replication: Snapshot Restores & Grafana Telemetry

If you've managed database clusters, you know that MySQL and MariaDB replication behaves completely differently than PostgreSQL.

While PostgreSQL streams physical byte blocks automatically using pg_basebackup, MySQL and MariaDB rely on asynchronous Binary Logs (binlogs) executed by two independent internal threads: an I/O thread pulling relay logs from the source, and an SQL thread replaying transactions locally.

This dual-thread architecture introduces unique operational challenges:

The I/O thread can report OK / running while the SQL thread silently crashes due to a duplicate key error (Error 1062).
Restoring a snapshot overwrites the mysql system database, locking out sidecars like mysqld-exporter until privileges are flushed.
Unindexed queries (like SELECT COUNT(*) scans on Gitea or Uptime Kuma) spike CPU, fill temporary tables on disk, and stall relay log execution.

In this guide, we'll walk through the battle-tested manual restore workflow for MySQL/MariaDB replicas, configure mysqld-exporter with --collect.perf_schema.eventsstatements, and analyze a unified Grafana Dashboard designed to catch binlog drift and query bottlenecks before production breaks.

1. Architectural Reality: MySQL vs. MariaDB Binlog Replication

In our setup, the replica host (last-labs) maintains two isolated replication streams from the primary node (10.1.1.57):

MariaDB Stream: mariadb-0:3306 (Source) → last-labs:3306 (Replica) running Uptime Kuma & Gitea.
MySQL Stream: mysql-0:3307 (Source) → last-labs:3307 (Replica) running Ghost DB.

Engine Command Cheat Sheet: MariaDB vs. MySQL 8.0+

Because MariaDB diverged from MySQL, their administrative SQL interfaces use different commands and status metrics:

Task	MariaDB Syntax	MySQL 8.0+ Syntax
Check Thread Status	`SHOW SLAVE STATUS\G`	`SHOW REPLICA STATUS\G`
Start Replication	`START SLAVE;`	`START REPLICA;`
Stop Replication	`STOP SLAVE;`	`STOP REPLICA;`
Configure Coordinates	`CHANGE MASTER TO ...`	`CHANGE REPLICATION SOURCE TO ...`
I/O Thread Metric	`Slave_IO_Running`	`Replica_IO_Running`
SQL Thread Metric	`Slave_SQL_Running`	`Replica_SQL_Running`
Replication Lag	`Seconds_Behind_Master`	`Seconds_Behind_Source`

2. The Manual Snapshot & Restoration Workflow

Because MySQL and MariaDB replicas do not auto-clone data directories on boot, initializing or re-syncing a replica requires importing a database dump (.sql or .sql.gz) and manually setting binary log coordinates.

Step 0: Creating the Snapshot: Automated Dump Script

Before a replica can be restored, you need a consistent snapshot from the source. The naive approach running mysqldump by hand, breaks under load because it doesn't lock InnoDB tables consistently and doesn't capture the exact binlog position needed for CHANGE MASTER TO. The script below solves both problems in one shot.

if ! mariadb-dump \
  -h "${MASTER_HOST}" \
  -P "${MASTER_PORT}" \
  -u "${REPLICATION_USER}" \
  --all-databases \
  --ignore-database=information_schema \
  --ignore-database=performance_schema \
  --ignore-database=sys \
  --single-transaction \
  --master-data=2 \
  --triggers \
  --routines \
  --events \
  | gzip > "${DUMP_FILE}"; then
  echo "ERROR: mariadb-dump failed against ${MASTER_HOST}:${MASTER_PORT}" >&2
  exit 1
fi

Flag-by-Flag Breakdown

Flag	Why It's Here
`--all-databases`	Captures every schema in one pass, so a replica rebuild doesn't miss a database you forgot to list.
`--ignore-database=information_schema/performance_schema/sys`	These are engine-internal, regenerated automatically on any server dumping them just bloats the file and can cause import errors.
`--single-transaction`	Wraps the dump in one InnoDB transaction using MVCC, so tables are read from a consistent point-in-time snapshot without taking a global read lock. This is what keeps the source server writable and responsive during the dump.
`--master-data=2`	This is the critical flag for replication. It records the source's exact `CHANGE MASTER TO` coordinates (binlog file + position) as a commented-out `SET` statement at the top of the dump commented so you can inspect it before deciding to use it. This is exactly what Step 4 later greps for with `grep -i "CHANGE MASTER"`.
`--triggers --routines --events`	`--single-transaction` alone only guarantees table data consistency it does not include stored procedures, triggers, or scheduled events by default. Without these three flags, a replica restored from this dump would be missing application logic silently.

Step 1: Container & Volume Mapping (`docker-compose.yml`)

The replica container maps /root/restore from the host to allow instant access to snapshot dumps without copying files into container layers.

services:
  mariadb:
    image: mariadb:latest
    container_name: mariadb-replica
    restart: unless-stopped
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/replica.cnf:ro
      - /root/restore:/root/restore
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

  mysqld-exporter:
    image: prom/mysqld-exporter:latest
    container_name: mariadb-exporter
    restart: unless-stopped
    entrypoint:
      - sh
      - -c
      - |
        cat <<EOF > /tmp/.my.cnf
        [client]
        user=exporter_user
        password=your_secure_password
        host=mariadb
        port=3306
        EOF
        exec /bin/mysqld_exporter --config.my-cnf=/tmp/.my.cnf --collect.perf_schema.eventsstatements
    ports:
      - "9104:9104"
    depends_on:
      - mariadb

volumes:
  mariadb_data:
    name: mariadb_data

Step 2: Exec into Container & Disable Read-Only Protections

Replica nodes are configured with read_only = ON and super_read_only = ON in my.cnf. To import a snapshot, temporarly disable read-only flags:

docker exec -it mariadb-replica bash
mysql -uroot -pyour_secure_password

Inside the MySQL prompt:

-- Temporarily allow table writes
SET GLOBAL read_only = OFF;
EXIT;

Step 3: Stream the Snapshot & Flush Privileges

Import the compressed snapshot directly into the MySQL process:

# For compressed SQL dumps:
zcat /root/restore/dump.sql.gz | mysql -uroot -pyour_secure_password

# For uncompressed SQL dumps:
mysql -uroot -pyour_secure_password < /root/restore/dump.sql

After the import completes, log back in to reload grant tables and re-enable write protection:

-- CRITICAL: Reload in-memory privileges if snapshot included the 'mysql' database
FLUSH PRIVILEGES;

-- Re-enforce read-only protection
SET GLOBAL read_only = ON;

The Exporter Lockout Gotcha: Restoring dumps that include the mysql user tables overwrites running privileges. Without FLUSH PRIVILEGES;, sidecar containers like mysqld-exporter will fail authentication with Access denied for user 'root'@'localhost'.

Step 4: Point Replica to Binlog Coordinates & Start Replication

Inspect the header of your snapshot file to find the LOG_FILE and LOG_POS coordinates generated at dump time:

zcat /root/restore/dump.sql.gz | head -n 30 | grep -i "CHANGE MASTER"

Then configure the replication coordinates inside the database shell:

For MariaDB:

CHANGE MASTER TO
  MASTER_HOST='10.1.1.57',
  MASTER_PORT=3306,
  MASTER_USER='root',
  MASTER_PASSWORD='your_secure_password',
  MASTER_LOG_FILE='mariadb-bin.000014',
  MASTER_LOG_POS=784201;

START SLAVE;
SHOW SLAVE STATUS\G

For MySQL 8.0+:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.1.1.57',
  SOURCE_PORT=3307,
  SOURCE_USER='root',
  SOURCE_PASSWORD='your_secure_password',
  SOURCE_LOG_FILE='mysql-bin.000038',
  SOURCE_LOG_POS=104520;

START REPLICA;
SHOW REPLICA STATUS\G

Verify that both Slave_IO_Running: Yes and Slave_SQL_Running: Yes (or Replica_IO_Running / Replica_SQL_Running) show active execution.

3. Configuring `mysqld-exporter` for Performance Schema Profiling

Standard MySQL metrics tell you that the database is busy, but they won't tell you which query is causing the issue. To capture exact query digests, we pass --collect.perf_schema.eventsstatements to mysqld-exporter.

Grafana Alloy Scrape Job (`config.alloy`)

Add both exporters to your Grafana Alloy configuration:

prometheus.scrape "mysqld_exporter" {
  targets = [
    { "__address__" = "last-labs:9104", "instance" = "mariadb-0" },
    { "__address__" = "last-labs:9105", "instance" = "last-labs" },
  ]
  forward_to = [prometheus.remote_write.default.receiver]
  scrape_interval = "15s"
}

4. Deep-Dive: The Unified MySQL + MariaDB Grafana Dashboard

To monitor both engines simultaneously, I engineered a MySQL + MariaDB Overview (Unified) Grafana dashboard divided into 4 operational inspection zones:

Zone 1: Performance & Connection Health

Monitors overall system workload and InnoDB memory efficiency:

Queries / sec: Displays live transaction velocity.
Active Connections: Tracks client threads connected to each engine.
Buffer Pool Hit Ratio: Gauges memory cache performance.
Aborted Connects & Deadlocks: Monitors dropped network connections and lock deadlocks.
Query Throughput by Command: Color-coded timelines separating SELECT, INSERT, UPDATE, and DELETE commands.
Network Throughput & New TCP Connections: Monitors network traffic and socket generation rates.

Performance Overview - Queries, Active Connections & Buffer Pool Hit Ratio

Zone 2: Replication State, Log Position Drift & Relay Storage

Tracks binary log execution and detects thread divergence before lag impacts readers:

Server Role & Read-Only Flags: Auto-detects last-labs as Replica with read_only = ON, and mariadb-0 as Source with read_only = OFF.
IO & SQL Thread Health Status: Displays green status banners (OK / running) for both replication threads.
Replication Lag (Seconds Behind Source): Real-time monitoring at 0 seconds, with historical timeline charts capturing lag spikes.
Log Position Drift (Read vs. Executed): Measures the byte gap between downloaded relay logs and executed SQL transactions.
Relay Log Space Used: Tracks local disk consumed by relay logs against max retention caps.

Replication Health - Thread Status, Lag Spikes & Log Position Drift

Zone 3: Query Profiling & Unindexed Full Table Scans (`perf_schema`)

Leveraging Performance Schema digests (events_statements_summary_by_digest), this zone surfaces problematic queries across all databases:

Top Queries by Total Latency: Surfaces high cumulative overhead queries.
Top Queries by Average Latency per Call: Pinpoints high per-execution latency.
Queries Examining Most Rows (Missing Index Candidates): Pinpoints full table scans that degrade performance.

Zone 4: InnoDB Storage & Internals

Focuses on storage engine metrics and internal lock contention:

Disk I/O (InnoDB Reads / Writes per sec): Displays physical storage operations per second.
Temp Tables Created on Disk Ratio: Tracks the percentage of in-memory temporary tables converted to disk tables.
Open Tables vs table_open_cache: Monitors table handle allocation efficiency.
Row Lock & Table Lock Waits: Real-time monitoring for table lock contention and transaction blocking.

InnoDB Internals - Temp Table Disk Creation, Lock Waits & Cache Usage

5. Troubleshooting Cheat Sheet for MariaDB/MySQL Operators

Issue 1: `Slave_SQL_Running: No` (Error 1062 / Duplicate Key)

If a write hit the replica while read_only was disabled, the SQL thread will crash on key collision.

-- Fix the underlying data discrepancy, then skip the broken transaction:
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS\G

Issue 2: `Access denied for user 'replica'@'localhost'` on Exporter

Occurs when a snapshot restore overwrites user tables.

-- Fix inside MySQL prompt:
FLUSH PRIVILEGES;

Issue 3: Relay Log Disk Filling Up

If the SQL thread stalls while the I/O thread continues downloading logs, relay logs can fill up the host disk.

-- Limit max relay log size in my.cnf:
max_relay_log_size = 512M

Conclusion

By understanding the dual-thread nature of MariaDB and MySQL replication, enforcing a clean manual snapshot restoration workflow, and leveraging Grafana Alloy with Performance Schema digests, you turn unpredictable binlog replication into a fully visible, resilient database infrastructure.