Install MariaDB primary-replica replication with GTID and TLS

Create the selected OS include on the replica; use the replica contents in the following panel, not the primary contents.

sudo nano /etc/mariadb/mariadb.conf.d/90-academy-replication.cnf

Replica file contents. read_only blocks ordinary writers but not replication and privileged administrators (READ_ONLY ADMIN/SUPER). Never give application accounts those bypass privileges, and do not point application writes here. MariaDB 10.11/11.8 semantics are used; no MySQL-only super_read_only assumption. Binary logging plus log_slave_updates preserves the applied stream for a separately planned future topology; it does not promote this node.

[mariadb]
server_id=102
bind_address=127.0.0.1
log_bin=/var/lib/mariadb/replica-bin
relay_log=/var/lib/mariadb/replica-relay
binlog_format=ROW
gtid_domain_id=0
gtid_strict_mode=ON
log_slave_updates=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1
binlog_expire_logs_seconds=604800
read_only=ON

Replica only. Restart is disruptive; the lab has no application connection. Confirm the identity, read-only flag and loopback binding. There must be no existing replication channel or application schema. A pre-existing channel means stop; do not reset it.

(
set -euo pipefail
sudo mariadbd --verbose --help >/dev/null
sudo systemctl restart mariadb
systemctl is-active mariadb
sudo mariadb -e 'SELECT VERSION(),@@server_id,@@read_only,@@gtid_domain_id; SHOW DATABASES; SHOW SLAVE STATUS\G'
sudo ss -lntp 'sport = :3306'
)
sudo mariadb -e "SELECT @@datadir;"

Primary terminal. The next SQL panels run INSIDE this interactive session, not at a Bash prompt. MYSQL_HISTFILE disables client history. Retrieve a unique long random replication secret from your secret manager and substitute the password placeholder inside the SQL session; never put it in shell arguments, shell history, screenshots or tickets. Do not use the literal placeholder. Use a protected terminal and review any session recording/audit policy; replication metadata stores a recoverable credential and belongs in protected backups. Use a64-character random hexadecimal secret (0-9/a-f only) so its SQL literal needs no quote escaping.

sudo env MYSQL_HISTFILE=/dev/null mariadb

Inside the primary SQL session. Restrict the account to the replica’s real source IP (no NAT mismatch, no wildcard) and require TLS. Only REPLICATION SLAVE is granted, no application/admin privileges. SSL is required by the account; server-name verification is explicitly required on the replica. No FLUSH PRIVILEGES is necessary after CREATE USER/GRANT.

CREATE USER 'academy_repl'@'10.20.0.11' IDENTIFIED BY 'REPLACE_WITH_UNIQUE_SECRET' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'academy_repl'@'10.20.0.11';

Primary SQL session. This dedicated lab has no app, jobs or other privileged writer. Create the only user schema and set read_only before the snapshot; leave it on until row verification. read_only does not fence privileged users, so coordinate the maintenance window and do not issue any further primary writes until the later release step. Never apply this sequence to an existing production database.

CREATE DATABASE academy_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE academy_lab.replication_probe (id INT PRIMARY KEY, note VARCHAR(80) NOT NULL) ENGINE=InnoDB;
INSERT INTO academy_lab.replication_probe VALUES (1,'consistent-seed');
SET GLOBAL read_only=ON;
SELECT @@read_only;
SELECT * FROM academy_lab.replication_probe;
EXIT;

Primary Bash. This creates a NEW protected seed directory and refuses a repeat instead of overwriting a previous seed. --single-transaction provides an InnoDB snapshot; --master-data=2 and --gtid record its exact replication position as comments. The brief initial global read lock can wait for long transactions. Do not run concurrent DDL, nontransactional writes, account changes or privileged writes. This is a seed for the dedicated single user schema, not a full-instance disaster-recovery backup. Do not take a later live GTID and combine it with this snapshot.

sudo bash -eu <<'SEED'
umask 077
mkdir /root/academy-seed
mariadb-dump --single-transaction --quick --master-data=2 --gtid   --routines --events --triggers --skip-add-drop-table   --databases academy_lab > /root/academy-seed/seed.sql
test -s /root/academy-seed/seed.sql
grep -E "^-- SET GLOBAL gtid_slave_pos='[0-9,-]+';" /root/academy-seed/seed.sql
cd /root/academy-seed
sha256sum seed.sql > SHA256SUMS
sha256sum -c SHA256SUMS
SEED

Replica, logged in as dbadmin. The directory must not already exist. Keep the data transfer on existing authenticated SSH with a verified host key; do not disable host-key verification.

( umask 077; mkdir "$HOME/academy-seed" )

Primary Bash, logged in as the same dbadmin administrator. Each remote write refuses to overwrite an existing file. pipefail propagates local read and SSH failures. Passwordless sudo for these specific reads or a prior local sudo authentication is required; no secrets are echoed. Verify SSH host fingerprints by your existing trusted channel before this operation.

(
set -euo pipefail
sudo -v
sudo cat /root/academy-seed/seed.sql | ssh -l dbadmin 10.20.0.11 'umask 077; set -C; cat > "$HOME/academy-seed/seed.sql"'
sudo cat /root/academy-seed/SHA256SUMS | ssh -l dbadmin 10.20.0.11 'umask 077; set -C; cat > "$HOME/academy-seed/SHA256SUMS"'
)

Replica Bash as dbadmin. This guard refuses any pre-existing academy_lab schema, any configured replication channel, any user table in other schemas or any applied GTID. It does not erase them. Verify checksum BEFORE import. Restore runs with session binary logging disabled so local seed writes do not create a competing domain 0 history; read_only stays ON because privileged bootstrap bypass is intentional here. Import errors stop immediately and leave the partial state for diagnosis; do not force or rerun on top of it.

(
set -euo pipefail
cd "$HOME/academy-seed"
sha256sum -c SHA256SUMS
test "$(sudo mariadb -NBe "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='academy_lab';")" = 0
test "$(sudo mariadb -NBe "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');")" = 0
channels=$(sudo mariadb -NBe 'SHOW SLAVE STATUS;')
test -z "$channels"
applied=$(sudo mariadb -NBe 'SELECT @@GLOBAL.gtid_slave_pos;')
test -z "$applied"
{ printf 'SET SESSION sql_log_bin=0;\n'; cat seed.sql; } | sudo mariadb
sudo mariadb -e 'SELECT @@read_only; SELECT * FROM academy_lab.replication_probe;'
)

Replica Bash. Extract exactly one commented GTID line, reject a malformed/domain-mismatched position and send only that reviewed statement to the local administrator connection. It must be domain 0 with primary server_id101 and a positive sequence. Do not use current_pos, invent a GTID or copy the primary’s current position after the backup. This snapshot has only one GTID domain; multi-source histories need a different plan.

(
set -euo pipefail
cd "$HOME/academy-seed"
sha256sum -c SHA256SUMS
position=$(sed -n "s/^-- SET GLOBAL gtid_slave_pos='\(0-101-[1-9][0-9]*\)';$/\1/p" seed.sql)
if [[ ! "$position" =~ ^0-101-[1-9][0-9]*$ ]]; then
  echo 'Missing, multiple or unexpected snapshot GTID; STOP' >&2; exit 1
fi
printf "SET GLOBAL gtid_slave_pos='%s';\n" "$position" | sudo mariadb
sudo mariadb -NBe 'SELECT @@GLOBAL.gtid_slave_pos;'
)