MariaDB GTID Replication on AlmaLinux 10: Primary and Replica

MySQL/MariaDB on AlmaLinux series, part 4. Part 1 installed the server, part 2 set up accounts and remote access, and part 3, backups with mariadb-dump and mariadb-backup, turned on the binary log. This part uses that binary log and a backup to build a replica.

Replication keeps a second MariaDB server continuously up to date with every change made on the first. The replica can serve read-only reports, take backups without loading the primary, and be promoted if the primary dies. With GTID (global transaction ID) replication, every transaction carries an ID such as 0-1-4521, so the replica tracks which transactions it has applied instead of a fragile binary log file name and byte offset. That makes it much easier to re-point a replica or promote it later. This guide builds a primary and a replica on AlmaLinux 10, seeds the replica from a consistent backup, starts GTID replication, and covers monitoring, a planned switchover and the errors you are most likely to hit.

Applies to: AlmaLinux 10.x with MariaDB 10.11 (mariadb-server) or 11.8 (mariadb11.8-server) from AppStream on both hosts. MariaDB GTIDs are not compatible with MySQL GTIDs; a section near the end shows the MySQL 8.4 equivalent.

Lab layout

Host	IP	Role	server_id
`db1.example.lan`	192.168.1.40	Primary (existing server from parts 1-3)	1
`db2.example.lan`	192.168.1.41	New replica	2

Prerequisites

Both hosts on the same MariaDB major version, installed as in part 1. The replica may be newer than the primary, never older.
On the primary, the binary log enabled with server_id = 1 in /etc/my.cnf.d/zz-binlog.cnf and the backup@localhost account and /root/.db-backup.cnf from part 3.
TCP 3306 reachable from db2 to db1, and SSH between the hosts to copy the backup.
Time synchronised with chrony on both hosts (chronyc tracking), so lag figures and logs make sense.
Enough disk on db2 for the full data directory plus a copy of the backup.

Step 1: Prepare the primary for GTID replication

MariaDB always writes GTIDs into the binary log, so there is no gtid_mode switch to flip as there is on MySQL. You only need a unique server_id, the binary log, and a few safety settings. Extend the file from part 3 on db1:

sudo tee /etc/my.cnf.d/zz-binlog.cnf >/dev/null <<'EOF'
[mariadb]
server_id                  = 1
log_bin                    = mariadb-bin
binlog_format              = ROW
binlog_expire_logs_seconds = 1209600
# replication
gtid_domain_id             = 0
gtid_strict_mode           = ON
log_slave_updates          = ON
sync_binlog                = 1
EOF
sudo systemctl restart mariadb
sudo mariadb -e "SELECT @@server_id, @@log_bin, @@gtid_strict_mode, @@gtid_binlog_pos;"

gtid_strict_mode makes the server refuse out-of-order or duplicate GTIDs instead of silently diverging.
log_slave_updates is harmless on the primary and means the same file works if this host is ever turned into a replica.
sync_binlog = 1 flushes the binary log on every commit, so a crash cannot lose transactions the replica already received.
@@gtid_binlog_pos shows the last GTID written, for example 0-1-4521 (domain 0, server 1, sequence 4521).

Step 2: Create the replication account

The replica connects as a normal client, so it needs an account limited to the replica host and the single privilege REPLICATION SLAVE (also accepted as REPLICATION REPLICA), following the least-privilege pattern from part 2. On db1:

sudo mariadb <<'SQL'
CREATE USER 'repl'@'192.168.1.41' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.41';
SHOW GRANTS FOR 'repl'@'192.168.1.41';
SQL

Make sure MariaDB listens on the LAN address (the bind-address = 192.168.1.40 line in zz-local.cnf from part 2) and let db2 through the firewall. If you created the dbclients zone in part 2, add db2 as another source:

sudo firewall-cmd --permanent --zone=dbclients --add-source=192.168.1.41/32
sudo firewall-cmd --reload
sudo firewall-cmd --zone=dbclients --list-all

From db2, prove the account works before going further (install the client with sudo dnf install -y mariadb if the server is not installed yet):

mariadb -h 192.168.1.40 -u repl -p -e "SELECT CURRENT_USER();"
# expected: repl@192.168.1.41

Step 3: Configure the replica

On db2, give the server its own ID, make it read-only for ordinary users, and keep a binary log so it can be promoted later:

sudo tee /etc/my.cnf.d/zz-replica.cnf >/dev/null <<'EOF'
[mariadb]
server_id                  = 2
log_bin                    = mariadb-bin
binlog_format              = ROW
binlog_expire_logs_seconds = 1209600
gtid_domain_id             = 0
gtid_strict_mode           = ON
log_slave_updates          = ON
relay_log                  = db2-relay-bin
read_only                  = ON
# don't start replicating before we have set the position
skip_slave_start           = ON
EOF

read_only blocks writes from application accounts while still letting the replication thread apply changes. On MariaDB 10.11 and later, only accounts with the READ_ONLY ADMIN privilege can write while it is on; SUPER alone no longer bypasses it. skip_slave_start keeps replication from starting automatically after the restore, before you have pointed it at the right GTID; you can remove it once the replica is running.

Step 4: Seed the replica from a consistent backup

A replica must start from an exact copy of the primary at a known GTID. Use one of the two methods from part 3. The physical method is faster for anything beyond a few gigabytes.

Option A: mariadb-backup (recommended)

On db1, take and prepare a fresh full backup, then copy it to db2:

STAMP=$(date +%F_%H%M)
sudo mariadb-backup --defaults-extra-file=/root/.db-backup.cnf --backup \
     --target-dir=/srv/backup/mysql/phys/seed-$STAMP
sudo mariadb-backup --prepare --target-dir=/srv/backup/mysql/phys/seed-$STAMP
sudo rsync -aP /srv/backup/mysql/phys/seed-$STAMP/ root@192.168.1.41:/srv/seed/

On db2, the data directory must be empty and MariaDB stopped. Copy the files back, fix ownership and SELinux labels, and start the server:

sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.empty-$(date +%F)   # keep until replication works
sudo install -d -o mysql -g mysql -m 755 /var/lib/mysql
sudo mariadb-backup --copy-back --target-dir=/srv/seed
sudo chown -R mysql:mysql /var/lib/mysql
sudo restorecon -Rv /var/lib/mysql
sudo systemctl start mariadb

The GTID position of the backup is in the backup directory. The file is xtrabackup_binlog_info (newer releases may name it mariadb_backup_binlog_info, so list the directory). It holds the binary log file, the position and the GTID:

sudo cat /srv/seed/*binlog_info
# mariadb-bin.000012   1874   0-1-4521

Write down the third field, here 0-1-4521. Because the backup also copied the primary’s mysql database, the replica now has the same accounts as the primary, including repl and backup.

Option B: mariadb-dump

For small databases, a logical dump works too. --master-data=2 with --gtid records the GTID as a comment at the top of the dump:

# on db1
sudo mariadb-dump --defaults-extra-file=/root/.db-backup.cnf --all-databases \
     --single-transaction --routines --events --triggers \
     --master-data=2 --gtid | zstd > /srv/backup/mysql/dump/seed.sql.zst
zstd -dc /srv/backup/mysql/dump/seed.sql.zst | head -40 | grep -i gtid_slave_pos
# -- SET GLOBAL gtid_slave_pos='0-1-4521';

# copy it, then on db2 (an empty, freshly installed server)
zstd -dc seed.sql.zst | sudo mariadb

The dump account in part 3 has BINLOG MONITOR, which is what lets mariadb-dump read the binary log position.

Step 5: Point the replica at the primary and start it

On db2, set the starting GTID from Step 4, define the connection and start replication. MASTER_USE_GTID = slave_pos tells the replica to ask the primary for everything after the GTIDs it has applied:

sudo mariadb <<'SQL'
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL gtid_slave_pos = '0-1-4521';
CHANGE MASTER TO
  MASTER_HOST     = '192.168.1.40',
  MASTER_PORT     = 3306,
  MASTER_USER     = 'repl',
  MASTER_PASSWORD = 'Paste-A-Long-Random-Password',
  MASTER_USE_GTID = slave_pos,
  MASTER_CONNECT_RETRY = 10;
START SLAVE;
SQL

MariaDB 10.5 and later also accept START REPLICA, STOP REPLICA and SHOW REPLICA STATUS; the older spellings shown here work on every version. The password is stored in /var/lib/mysql/master.info, readable only by the mysql user. To encrypt the replication traffic, set up TLS on the primary and add MASTER_SSL = 1 (and MASTER_SSL_CA = '/path/ca.pem' to verify the certificate).

Step 6: Verify replication

sudo mariadb -e "SHOW SLAVE STATUS\G" | grep -E \
  'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Using_Gtid|Gtid_IO_Pos|Last_.*Error'
#             Slave_IO_Running: Yes
#            Slave_SQL_Running: Yes
#        Seconds_Behind_Master: 0
#                   Using_Gtid: Slave_Pos
#                  Gtid_IO_Pos: 0-1-4530

Both threads must say Yes. The I/O thread downloads events from the primary into the relay log; the SQL thread applies them. Then do an end-to-end test. On db1:

sudo mariadb -e "CREATE DATABASE repltest; CREATE TABLE repltest.t (id INT PRIMARY KEY, note VARCHAR(40));
INSERT INTO repltest.t VALUES (1, NOW()); SELECT @@gtid_binlog_pos;"

On db2, the row and the GTID should appear within a second:

sudo mariadb -e "SELECT * FROM repltest.t; SELECT @@gtid_slave_pos;"
# the replica should report read_only = 1
sudo mariadb -e "SELECT @@read_only;"

Application accounts on db2 now get ERROR 1290 ... running with the --read-only option if they try to write. The local root account still can, because it holds every privilege, so never make changes on the replica as root. Clean up on db1 with DROP DATABASE repltest; and watch it disappear on db2. Once this works you can remove skip_slave_start from zz-replica.cnf and delete /var/lib/mysql.empty-* and /srv/seed.

Step 7: Monitor the replica

Check replication from cron, your monitoring system or a systemd timer. A small script that exits non-zero when a thread stops or lag grows is enough to start with:

sudo tee /usr/local/sbin/check-replica.sh >/dev/null <<'EOF'
#!/bin/bash
# exit 2 = broken, 1 = lagging, 0 = OK
MAXLAG=${1:-60}
S=$(mariadb -e "SHOW SLAVE STATUS\G")
io=$(awk '/Slave_IO_Running:/ {print $2}' <<<"$S")
sql=$(awk '/Slave_SQL_Running:/ {print $2}' <<<"$S")
lag=$(awk '/Seconds_Behind_Master:/ {print $2}' <<<"$S")
if [ "$io" != Yes ] || [ "$sql" != Yes ]; then
  echo "CRITICAL io=$io sql=$sql"; awk '/Last_(IO|SQL)_Error:/' <<<"$S"; exit 2
fi
if [ "$lag" = NULL ] || [ "$lag" -gt "$MAXLAG" ]; then echo "WARNING lag=$lag"; exit 1; fi
echo "OK lag=${lag}s gtid=$(mariadb -Nse 'SELECT @@gtid_slave_pos')"
EOF
sudo chmod 700 /usr/local/sbin/check-replica.sh
sudo /usr/local/sbin/check-replica.sh 60

Useful views: SHOW PROCESSLIST on db1 shows a Binlog Dump thread per connected replica, and SHOW ALL SLAVES STATUS\G on db2 covers multi-source setups. Seconds_Behind_Master measures how old the event being applied is, so it reads 0 when the replica is idle and can jump during one long transaction; compare @@gtid_binlog_pos on db1 with @@gtid_slave_pos on db2 for an exact answer.

Step 8: Back up from the replica

Running the nightly backup from part 3 on db2 takes the load off the primary. For a physical backup on a replica, add --slave-info (records the primary’s position, so the backup can seed further replicas) and --safe-slave-backup (pauses the SQL thread while temporary tables are open):

sudo mariadb-backup --defaults-extra-file=/root/.db-backup.cnf --backup \
     --slave-info --safe-slave-backup \
     --target-dir=/srv/backup/mysql/phys/full-$(date +%F_%H%M)

A replica is not a backup on its own: a DROP TABLE on the primary is replicated within a second. Keep the scheduled backups and binary logs.

Step 9: Planned switchover (promote the replica)

With GTIDs, swapping roles is a short, checkable procedure. Do it in a maintenance window and stop the applications first.

# 1. db1: stop writes and note the final GTID
sudo mariadb -e "SET GLOBAL read_only = ON; SELECT @@gtid_binlog_pos;"

# 2. db2: wait until it has applied exactly that GTID (returns 0 when reached)
sudo mariadb -e "SELECT MASTER_GTID_WAIT('0-1-4602', 60);"

# 3. db2: become the primary
sudo mariadb -e "STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only = OFF;"

# 4. db1: become a replica of db2 (needs a repl account on db2 for 192.168.1.40)
sudo mariadb <<'SQL'
CHANGE MASTER TO MASTER_HOST='192.168.1.41', MASTER_USER='repl',
  MASTER_PASSWORD='Paste-A-Long-Random-Password', MASTER_USE_GTID=current_pos;
START SLAVE;
SQL

Because the mysql database was copied in Step 4, create the reverse account on db1 before the switchover (CREATE USER 'repl'@'192.168.1.40' ...); it replicates to db2 like any other change. current_pos is used on the old primary because its own last GTIDs are in its binary log rather than in gtid_slave_pos. Finally update the config files (read_only on the new replica, off on the new primary) and move the application’s connection string or virtual IP. An unplanned failover follows the same steps 3 and 4, but any transactions the replica never received are lost, and the old primary must not rejoin until you have compared GTIDs; with gtid_strict_mode it will refuse to replicate if it has transactions the new primary does not.

Troubleshooting

Slave_IO_Running: Connecting

Last_IO_Error with error 2003 or 2002: network, bind-address or firewalld. Test with mariadb -h 192.168.1.40 -u repl -p from db2 and check sudo ss -ltnp | grep 3306 on db1.
Error 1045 access denied: wrong password or the account host does not match the replica’s source address (check with SELECT user,host FROM mysql.user WHERE user='repl';).
Error 1130 host not allowed: same cause, or the primary resolves db2 to a different name; use IP-based accounts or skip_name_resolve.

Error 1236: could not find GTID state requested by slave

The replica asked for GTIDs the primary no longer has in its binary log (purged by binlog_expire_logs_seconds), or gtid_slave_pos was set wrongly. Compare SELECT @@gtid_slave_pos on db2 with SHOW BINARY LOGS and SELECT BINLOG_GTID_POS('mariadb-bin.000012', 4) on db1. If the binlogs are gone, re-seed the replica from a new backup; there is no safe shortcut. To prevent it, keep binary logs longer than the longest time a replica may be offline.

Error 1062 duplicate key or 1032 row not found

The SQL thread stops because the replica’s data no longer matches the primary, usually because someone wrote to the replica directly or the seed was not consistent. Read Last_SQL_Error, find the table, and fix the data. As a last resort, skipping one transaction is possible:

sudo mariadb -e "STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;"

Every skip leaves the replica different from the primary. If you need to skip more than once, re-seed it instead, and check that read_only is on and that no application account has READ_ONLY ADMIN or SUPER.

MySQL 8.4 differences

MySQL uses a different GTID format (server_uuid:sequence) and different commands. The overall flow is identical:

# both servers, /etc/my.cnf.d/zz-replication.cnf
[mysqld]
server_id                = 1        # 2 on the replica
log_bin                  = mysql-bin
gtid_mode                = ON
enforce_gtid_consistency = ON
# replica only:
# read_only = ON
# super_read_only = ON

-- primary
CREATE USER 'repl'@'192.168.1.41' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.41';

-- replica, after restoring a dump made with
--   mysqldump --all-databases --single-transaction --source-data=2 --set-gtid-purged=ON
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.40',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'Paste-A-Long-Random-Password',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
START REPLICA;
SHOW REPLICA STATUS\G

In MySQL 8.4 the old CHANGE MASTER TO and START SLAVE forms are removed, and the default caching_sha2_password plugin needs a TLS connection (SOURCE_SSL = 1) or GET_SOURCE_PUBLIC_KEY = 1. For physical seeding use Percona XtraBackup or the MySQL clone plugin instead of mariadb-backup.

Quick reference

Task	MariaDB command
Current GTID on primary	`SELECT @@gtid_binlog_pos;`
Applied GTID on replica	`SELECT @@gtid_slave_pos;`
Set start position	`SET GLOBAL gtid_slave_pos = '0-1-N';`
Connect replica	`CHANGE MASTER TO MASTER_HOST=..., MASTER_USE_GTID=slave_pos;`
Start / stop	`START SLAVE;` / `STOP SLAVE;` (or `REPLICA`)
Status	`SHOW SLAVE STATUS\G`
Wait for a GTID	`SELECT MASTER_GTID_WAIT('0-1-N', 60);`
Forget the primary	`STOP SLAVE; RESET SLAVE ALL;`
Connected replicas (on primary)	`SHOW PROCESSLIST;` (Binlog Dump threads)

Next in the series

Part 5 moves from one-way replication to synchronous multi-primary clustering with MariaDB Galera Cluster. Earlier parts: part 3, backups and point-in-time recovery, part 2, users, grants and remote access and part 1, install and secure MariaDB/MySQL. For the PostgreSQL equivalent, see PostgreSQL streaming replication and failover on AlmaLinux; for the classic two-way setup on older systems, see configuring MySQL multi-master replication.

Official references: MariaDB: Global Transaction ID, MariaDB: CHANGE MASTER TO, MariaDB: setting up a replica with mariadb-backup, MariaDB: SHOW REPLICA STATUS and MySQL 8.4: setting up replication using GTIDs.

MariaDB GTID Replication on AlmaLinux 10: Primary and Replica Setup