WIUNIX ACADEMY
Install MariaDB primary-replica replication with GTID and TLS
Page 3 of 6. Review the earlier steps and prerequisites before continuing.
YOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment3 editable values
LET’S BUILD
Restart and inspect the empty replica
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.
Replica
(
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;"
102, read_only1, domain 0; empty replication status; only system schemas and loopback3306.
LET’S BUILD
Open a history-free primary SQL session
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.
Primary terminal
sudo env MYSQL_HISTFILE=/dev/null mariadb
Local primary MariaDB administrator prompt is open with client history disabled.
LET’S BUILD
Create only a restricted replication identity
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.
Primary SQL session
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';
A new host-restricted account with only replication privilege and REQUIRE SSL; no secret printed.
LET’S BUILD
Create the small InnoDB dataset and pause writes
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.
Primary SQL session
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;
One InnoDB row, read_only1; no writers remain; return to the shell.
LET’S BUILD
Take a consistent seed with its own GTID
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.
Primary
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
Successful dump and SHA256 read-back; exactly one commented snapshot GTID. On error retain files, stop, diagnose; do not import a partial seed.
LET’S BUILD
Create a protected receiving directory
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.
Replica shell as dbadmin
( umask 077; mkdir "$HOME/academy-seed" )
New dbadmin-owned receiving directory exists with0700 mode.