Andrew Mercer
on this page

MariaDB replication with db-adm

This guide sets up asynchronous primary → replica replication using GTIDs, seeds the replica from a consistent dump, and monitors it. Each step shows the db-adm mariadb command and the SQL it runs, so you can also do it by hand.

Examples use db1 (primary, 10.0.0.11) and db2 (replica, 10.0.0.12), with config profiles [primary] and [replica1].

1. How it works

  • The primary writes every change to its binary log (binlog). Each transaction gets a GTID domain-server_id-sequence, e.g. 0-1-1042.
  • The replica's IO thread connects to the primary as a replication user and copies binlog events into its relay log.
  • The replica's SQL thread applies the relay log and records the last applied GTID in gtid_slave_pos.
  • With GTIDs the replica asks for "everything after gtid_slave_pos", so you never have to track binlog file names and offsets. That makes reseeding and failover much easier.

The replica only needs a consistent starting point: a copy of the data plus the exact GTID that copy corresponds to.

2. Server configuration

Both servers need a unique server_id and binary logging. Put this in /etc/my.cnf.d/replication.cnf (RHEL/Fedora) or /etc/mysql/mariadb.conf.d/60-replication.cnf (Debian/Ubuntu) and restart.

Primary:

[mariadb]
server_id                      = 1
log_bin                        = mariadb-bin
log_basename                   = db1          # stable binlog/relay-log names if the hostname changes
binlog_format                  = ROW
gtid_domain_id                 = 0
sync_binlog                    = 1
innodb_flush_log_at_trx_commit = 1
bind_address                   = 0.0.0.0      # or the replication interface
binlog_expire_logs_seconds     = 604800       # 7 days; must exceed your longest replica outage

Replica:

[mariadb]
server_id         = 2
log_bin           = mariadb-bin               # lets the replica be promoted later
log_basename      = db2
binlog_format     = ROW
log_slave_updates = ON                        # replicated changes also go to its binlog
read_only         = ON                        # stops app writes; replication and admins still write
relay_log_recovery = ON

Check the primary:

db-adm mariadb --profile primary repl primary-setup --check-only

SETTING                         VALUE      CHECK  NOTE
log_bin                         ON         ok
server_id                       1          warn   default value; make sure every replica uses a different id
binlog_format                   ROW        ok
bind_address                    0.0.0.0    ok
sync_binlog                     1          ok
innodb_flush_log_at_trx_commit  1          ok
gtid_domain_id                  0          info

It exits 2 if replication can't work (log_bin off, server_id 0, skip_networking), and prints the config snippet to add.

3. Replication account

Create it on the primary, restricted to the replicas' network:

db-adm mariadb --profile primary repl primary-setup \
    --repl-user repl --repl-host '10.0.0.%' \
    --generate --store-pass homelab/mariadb/repl [--require-ssl]

SQL equivalent:

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '…';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

The tool hashes the password client-side, stores it in pass before creating the account, and prints the current gtid_binlog_pos.

Firewall: allow TCP 3306 from the replica to the primary.

4. Seed the replica

The replica must start with the same data the primary had at a known GTID.

Option A: logical dump (most homelab-sized databases)

On the primary:

db-adm mariadb --profile primary backup --all-in-one --master-data --dir /var/tmp/seed

This runs mariadb-dump --all-databases --single-transaction --master-data=2 --gtid --routines --triggers --events --hex-blob. --single-transaction gives a consistent InnoDB snapshot without locking writers. --master-data=2 --gtid records the position as a comment:

-- SET GLOBAL gtid_slave_pos='0-1-1042';

(MariaDB 10.x/11.x writes that line at the end of the dump; --gtid-pos-from-dump scans the whole file.)

Copy the dump to the replica and load it:

scp /var/tmp/seed/all-databases/db1-all-databases-*.sql.zst db2:/var/tmp/
ssh db2 'zstdcat /var/tmp/db1-all-databases-*.sql.zst | sudo mariadb'

The dump includes the mysql schema, so accounts (including repl) come across too.

Option B: physical copy (large databases)

For hundreds of GB, use mariadb-backup --backup --slave-info on the primary, then --prepare and --copy-back on the replica. The GTID is in xtrabackup_binlog_info; pass it with --gtid-pos.

5. Point the replica at the primary

On the replica:

db-adm mariadb --profile replica1 repl replica-setup \
    --primary-host 10.0.0.11 --repl-user repl \
    --repl-pass-entry homelab/mariadb/repl \
    --gtid-pos-from-dump /var/tmp/db1-all-databases-2026.10.09.021500Z.sql.zst \
    --read-only

Before changing anything, it logs in to the primary as repl, from where the tool runs. That checks the network, the account's host pattern and the password, and refuses to continue if both servers have the same server_id or the primary has log_bin off. It then runs:

SET GLOBAL gtid_slave_pos = '0-1-1042';
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.11', MASTER_PORT = 3306,
  MASTER_USER = 'repl', MASTER_PASSWORD = '…',
  MASTER_CONNECT_RETRY = 10,
  MASTER_USE_GTID = slave_pos;
SET GLOBAL read_only = ON;
START SLAVE;

It then waits up to 10 seconds for both threads to report Yes:

gtid_slave_pos from /var/tmp/db1-all-databases-….sql.zst: 0-1-1042
preflight: reached primary 10.0.0.11:3306 (server_id 1, 11.4.9-MariaDB-log, gtid_binlog_pos 0-1-1057)
replica configured: primary 10.0.0.11:3306 as repl
replication running: IO=Yes SQL=Yes lag=0s position 0-1-1057

Other options:

  • --master-ssl sets MASTER_SSL=1 (pair with --require-ssl on the account).
  • --gtid current-pos is for a server that may have binlogged its own writes, e.g. a former primary rejoining as a replica.
  • --log-file/--log-pos is classic file/position replication (implies --gtid no).
  • --connection NAME is for multi-source: one replica, several primaries.
  • --dry-run prints the SQL with the password masked.
  • --force reconfigures an existing setup (it runs STOP SLAVE first).

Brand-new primary with no data yet? Skip seeding. With an empty gtid_slave_pos the replica asks for the binlogs from the beginning, and the tool warns about that.

6. Verify

db-adm mariadb --profile replica1 repl status -v

REPL OK - default: IO=Yes SQL=Yes lag=0s | 'lag_default'=0s;60;300

CONNECTION  STATE  PRIMARY         IO   SQL  LAG  POSITION
default     OK     10.0.0.11:3306  Yes  Yes  0    0-1-1057

Smoke test: write on the primary, read on the replica.

7. Monitoring

repl status follows the Nagios plugin conventions: the first line is the summary with perfdata, and the exit code is 0 OK, 1 WARNING, 2 CRITICAL or 3 UNKNOWN.

Condition State
IO and SQL threads Yes, lag < --warn-lag OK
lag ≥ --warn-lag (default 60s) WARNING
lag ≥ --crit-lag (default 300s) CRITICAL
IO thread No/Connecting, SQL thread stopped, lag NULL CRITICAL, with Last_IO_Error/Last_SQL_Error
replication not configured CRITICAL
can't connect / no privilege UNKNOWN

Use a least-privilege login for checks:

db-adm mariadb --profile primary user create monitor@localhost --role monitor --generate --store-pass homelab/mariadb/monitor

Because it's created on the primary, it replicates to the replica.

Icinga/Nagios command:

object CheckCommand "mariadb_repl" {
  command = [ "/usr/local/bin/db-adm", "mariadb", "--profile", "$profile$", "repl", "status",
              "--warn-lag", "$warn_lag$", "--crit-lag", "$crit_lag$" ]
}

No monitoring system? Have a timer mail you instead. This replaces the old cron + mail script:

# /etc/systemd/system/mariadb-repl-check.service
[Service]
Type=oneshot
User=dbbackup
ExecStart=/usr/local/bin/db-adm mariadb --profile replica1 repl status --notify [email protected]
SuccessExitStatus=1 2 3

--notify (or notify_email in the config) sends mail through the local sendmail/mail only when the state isn't OK.

8. When it breaks

Symptom in repl status Cause Fix
IO=Connecting … Can't connect Primary down, firewall, bind_address Fix connectivity; the IO thread retries every 10s on its own
IO=No … Access denied Wrong password or host pattern repl replica-setup --force … with the right --repl-pass-entry, or fix the account on the primary
IO=No … 1236 … could not find GTID / binlog purged Replica was offline longer than binlog retention Reseed (step 4) and raise binlog_expire_logs_seconds
SQL=No … Duplicate entry / 1032 Can't find record Data drift: someone wrote on the replica Find and fix the drifted rows, then repl start. Keep read_only=ON. Last resort for one bad event: STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE; then check consistency (e.g. pt-table-checksum)
server_id clash at setup Cloned VM or default config Unique server_id in my.cnf, restart
Lag keeps climbing Big transactions, slow replica disk, single-threaded apply slave_parallel_threads=4, slave_parallel_mode=optimistic; check slowlog summary on the replica

Start over cleanly on the replica:

db-adm mariadb --profile replica1 repl reset --yes     # STOP SLAVE; RESET SLAVE ALL

9. Promoting the replica (planned switchover)

  1. On the primary: SET GLOBAL read_only = ON; and wait for apps to drain.
  2. On the replica: wait for repl status lag 0 and gtid_slave_pos = primary's gtid_binlog_pos.
  3. On the replica: db-adm mariadb repl reset --yes then SET GLOBAL read_only = OFF;
  4. Point applications at the replica.
  5. Optional, old primary as the new replica: db-adm mariadb repl replica-setup --primary-host <new primary> --gtid current-pos …

10. Galera instead?

Galera is synchronous multi-primary, which is a different trade-off: no lag and no failover step, but every write pays a cluster round-trip and you need 3+ nodes for quorum. For Galera nodes use db-adm mariadb galera status --expect-size 3 instead of repl status.