Databases & Data / High Availability & Reliability

Configure MariaDB replication with GTID and TLS

Prepare compatible primary and replica servers, create a TLS-bound replication user, seed data, configure GTID replication, monitor lag, and test recovery.

120 min8 stepsChanges system stateRevision 2

Save or explore

#mariadb #replication #gtid #tls #replica

0 of 8 steps completed

Goal

Operate encrypted, observable replication without using it as a substitute for backups.

Supported environments

MariaDB 10.11 LTS, 11.4 LTS

Prerequisites

Recovery access and maintenance window Keep console access and schedule write-affecting work so an interrupted package, grant, backup, or replication change can be recovered.
Verified backup Create a fresh backup and prove that its format, encryption key, and restore procedure are available before changing production data.
Capacity and compatibility Check database version, storage headroom, character sets, application driver compatibility, and expected downtime.df -h; df -i; free -m

Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System

A production-ready MariaDB implementation of “Configure MariaDB replication with GTID and TLS” with explicit identities, configuration ownership, observable checkpoints and a tested reversal path.
A before-state, native validation sequence and concrete evidence set covering the database service, client behavior, data or administrative surface affected by this procedure.
A least-privilege operational runbook that keeps secrets out of commands and verifies both the required success path and an expected denial or failure boundary.

Observable outcome

Operate encrypted, observable replication without using it as a substitute for backups. The result is demonstrated through the guide's native verification commands and representative application or restore evidence.
Unrelated databases, accounts, listeners and services remain unchanged; operators can identify the exact revision, artifact and rollback action for this change.

Architecture

How the parts fit together

A source records ordered changes and a replica retrieves them over mutually authenticated TLS using a dedicated replication identity. GTID or recorded coordinates establish continuity, while a separately verified seed supplies the initial state. Native replication status exposes receiver/applier health, lag and errors. A canary write proves end-to-end flow; promotion remains a separate runbook with fencing and application-routing controls.

Source change logRecords ordered transactions with durable identities or coordinates.

TLS replication channelAuthenticates and encrypts source-to-replica traffic.

Verified seedStarts the replica from a known consistent dataset and position.

Receiver and applierTransport and replay changes while exposing lag/errors.

Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
Prepare the replication candidate using only official MariaDB interfaces, protected secret delivery and the narrowest necessary scope.
Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
Perform end-to-end success and expected-failure tests; retain rollback evidence and schedule recurring verification.

Assumptions

The installed MariaDB family and version are supported by the official sources linked below, and package/configuration provenance is known.
A maintenance or canary window, current recoverable backup and console-level recovery path exist before state-changing database work begins.
Operator, application, backup, replication and web-administration identities are separate; secrets are supplied by protected option files, environment injection with appropriate controls, or a secret manager.
Representative schema size, workload, storage capacity, network policy and recovery objectives are documented for the target environment.

Key concepts

user@host identity: MySQL-family authorization matches both an account name and its permitted client origin, not the name alone.
effective configuration: The final runtime values after packaged defaults and ordered configuration fragments have been combined.
consistent state: Data and metadata captured at one valid transactional or recovery point rather than a mixture of moments.
recovery objective: The acceptable data-loss window and restoration time that drive backup, replication and validation design.
positive and negative verification: Proving an intended operation succeeds and an operation outside the granted/security boundary fails.

Before you copy

Values used in this guide

{{canary}}

Operator-reviewed value used by this tutorial for canary; derive it from the target environment and never from untrusted request data.

Example: oneliners-replication-canary

{{database}}

Application database name; use an identifier approved by the database naming policy.

Example: billing

{{primary}}

Operator-reviewed value used by this tutorial for primary; derive it from the target environment and never from untrusted request data.

Example: db-primary.example.com

{{protectedSecret}}

Operator-reviewed value used by this tutorial for protectedSecret; derive it from the target environment and never from untrusted request data.

Example: example-protectedsecret

{{replUser}}

Operator-reviewed value used by this tutorial for replUser; derive it from the target environment and never from untrusted request data.

Example: replication_agent

{{replica}}

Operator-reviewed value used by this tutorial for replica; derive it from the target environment and never from untrusted request data.

Example: db-replica.example.com

{{replicaAddress}}

Operator-reviewed value used by this tutorial for replicaAddress; derive it from the target environment and never from untrusted request data.

Example: 192.0.2.31

{{restoreSeedCommand}}

Operator-reviewed value used by this tutorial for restoreSeedCommand; derive it from the target environment and never from untrusted request data.

Example: restore-the-verified-seed

{{seedChecksum}}

Operator-reviewed value used by this tutorial for seedChecksum; derive it from the target environment and never from untrusted request data.

Example: sha256:verified-seed-digest

Security and production boundaries

Never place database passwords, encryption keys, recovery codes or private certificates directly in reusable shell history or repository files.
Do not use `%`, global grants, disabled TLS verification, public administrative interfaces or world-readable dumps as troubleshooting shortcuts.
Treat backups, slow logs and phpMyAdmin sessions as sensitive data; encrypt, restrict, rotate and monitor them according to the same or stronger policy as production.

Stop before continuing if

Stop if the server version/config family is ambiguous, the backup cannot be restored, secrets would be exposed, or a proposed command affects databases/accounts beyond the declared scope.
Do not continue when native validation, representative workload, expected-denial test, TLS identity, replication continuity or storage capacity evidence is incomplete for this procedure.
Immediately isolate or revert a change that broadens a listener/grant, breaks application health, introduces replication error, loses recoverability or produces unbounded sensitive logs.

instruction

Verify topology and version compatibility

read-only

Record server IDs, versions, UUIDs, time, DNS, firewall path, storage, and expected write source; avoid circular or ambiguous topology.

Why this step matters

Verify topology and version compatibility is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Record server IDs, versions, UUIDs, time, DNS, firewall path, storage, and expected write source; avoid circular or ambiguous topology. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Verify topology and version compatibility”. The scope must remain limited to the selected application and supported host.

Syntax explained

for: Invokes the principal tool or configuration operation for “Verify topology and version compatibility”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Primary{{primary}}Operator-reviewed value used by this tutorial for primary; derive it from the target environment and never from untrusted request data.Replica{{replica}}Operator-reviewed value used by this tutorial for replica; derive it from the target environment and never from untrusted request data.

for h in {{primary}} {{replica}}; do ssh "$h" "mariadb -NBe 'SELECT VERSION(),@@server_id;'; timedatectl show -p NTPSynchronized"; done

Example output / evidence

Versions, unique server IDs, and synchronized time are confirmed.

Checkpoint: Checkpoint: Verify topology and version compatibility

Continue whenVersions, unique server IDs, and synchronized time are confirmed. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when for reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Verify topology and version compatibility” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v for 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

config

Enable durable binary logging and GTID settings

caution

Configure unique server_id, log_bin, row-based format, log retention, and crash-safe settings on the primary; make replica settings explicit.

Why this step matters

Enable durable binary logging and GTID settings is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Configure unique server_id, log_bin, row-based format, log retention, and crash-safe settings on the primary; make replica settings explicit. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Enable durable binary logging and GTID settings”. The scope must remain limited to the selected application and supported host.

Syntax explained

sudoedit: Invokes the principal tool or configuration operation for “Enable durable binary logging and GTID settings”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
configuration file: Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.

Configuration

sudoedit /etc/mysql/mariadb.conf.d/60-replication.cnf

Example output / evidence

Primary and replica configuration names unique IDs and intended GTID behavior.

Checkpoint: Checkpoint: Enable durable binary logging and GTID settings

Continue whenPrimary and replica configuration names unique IDs and intended GTID behavior. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when sudoedit reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Enable durable binary logging and GTID settings” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v sudoedit 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

config

Configure trusted TLS on both servers

caution

Install CA, server certificate, and private key with mysql ownership and restrictive modes; require certificate verification.

Why this step matters

Configure trusted TLS on both servers is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Install CA, server certificate, and private key with mysql ownership and restrictive modes; require certificate verification. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Configure trusted TLS on both servers”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Configure trusted TLS on both servers”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
configuration file: Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.

Configuration

sudo mariadb -e "SHOW VARIABLES LIKE 'have_ssl'; SHOW VARIABLES LIKE 'ssl_%';"

Example output / evidence

Both servers report TLS enabled with intended certificate paths.

Checkpoint: Checkpoint: Configure trusted TLS on both servers

Continue whenBoth servers report TLS enabled with intended certificate paths. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Configure trusted TLS on both servers” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Create a source-restricted replication account

caution

Grant REPLICATION SLAVE only from the replica address and require SSL or X509.

Why this step matters

Create a source-restricted replication account is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Grant REPLICATION SLAVE only from the replica address and require SSL or X509. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Create a source-restricted replication account”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Create a source-restricted replication account”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/4 ready

Values stay on this page and are never sent or saved.

Primary{{primary}}Operator-reviewed value used by this tutorial for primary; derive it from the target environment and never from untrusted request data.Repl User{{replUser}}Operator-reviewed value used by this tutorial for replUser; derive it from the target environment and never from untrusted request data.Replica Address{{replicaAddress}}Operator-reviewed value used by this tutorial for replicaAddress; derive it from the target environment and never from untrusted request data.Protected Secret{{protectedSecret}}Operator-reviewed value used by this tutorial for protectedSecret; derive it from the target environment and never from untrusted request data.

sudo mariadb --host={{primary}} --protocol=tcp --ssl -e "CREATE USER '{{replUser}}'@'{{replicaAddress}}' IDENTIFIED BY '{{protectedSecret}}' REQUIRE SSL; GRANT REPLICATION SLAVE ON *.* TO '{{replUser}}'@'{{replicaAddress}}';"

Example output / evidence

The account is host-scoped, least-privilege, and TLS-required.

Checkpoint: Checkpoint: Create a source-restricted replication account

Continue whenThe account is host-scoped, least-privilege, and TLS-required. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

If this step fails

The command for “Create a source-restricted replication account” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Seed the replica from a consistent backup

caution

Use mariadb-backup or a consistent logical dump including replication metadata; verify checksum before restore.

Why this step matters

Seed the replica from a consistent backup is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use mariadb-backup or a consistent logical dump including replication metadata; verify checksum before restore. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Seed the replica from a consistent backup”. The scope must remain limited to the selected application and supported host.

Syntax explained

sha256sum: Invokes the principal tool or configuration operation for “Seed the replica from a consistent backup”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Seed Checksum{{seedChecksum}}Operator-reviewed value used by this tutorial for seedChecksum; derive it from the target environment and never from untrusted request data.Restore Seed Command{{restoreSeedCommand}}Operator-reviewed value used by this tutorial for restoreSeedCommand; derive it from the target environment and never from untrusted request data.

sha256sum -c {{seedChecksum}}; {{restoreSeedCommand}}

Example output / evidence

The replica contains the verified seed and remains stopped or read-only.

Checkpoint: Checkpoint: Seed the replica from a consistent backup

Continue whenThe replica contains the verified seed and remains stopped or read-only. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when sha256sum reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Seed the replica from a consistent backup” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v sha256sum 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Configure GTID replication over TLS

caution

Set the primary host, port, protected credentials, GTID position, CA, certificate verification, and retry policy without exposing secrets in shell history.

Why this step matters

Configure GTID replication over TLS is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Set the primary host, port, protected credentials, GTID position, CA, certificate verification, and retry policy without exposing secrets in shell history. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Configure GTID replication over TLS”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Configure GTID replication over TLS”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Replica{{replica}}Operator-reviewed value used by this tutorial for replica; derive it from the target environment and never from untrusted request data.

sudo mariadb --host={{replica}} --protocol=socket

Example output / evidence

CHANGE MASTER TO MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_USE_GTID=slave_pos, MASTER_SSL=1, and MASTER_SSL_CA succeeds.

Checkpoint: Checkpoint: Configure GTID replication over TLS

Continue whenCHANGE MASTER TO MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_USE_GTID=slave_pos, MASTER_SSL=1, and MASTER_SSL_CA succeeds. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

If this step fails

The command for “Configure GTID replication over TLS” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

verification

Start replication and inspect both threads

caution

Start the replica, inspect SHOW REPLICA STATUS, and block promotion on I/O, SQL, TLS, or GTID errors.

Why this step matters

Start replication and inspect both threads is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Start the replica, inspect SHOW REPLICA STATUS, and block promotion on I/O, SQL, TLS, or GTID errors. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Start replication and inspect both threads”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Start replication and inspect both threads”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Replica{{replica}}Operator-reviewed value used by this tutorial for replica; derive it from the target environment and never from untrusted request data.

sudo mariadb --host={{replica}} --protocol=socket -e "START REPLICA; SHOW REPLICA STATUS\G"

Example output / evidence

I/O and SQL threads run, TLS is in use, and no last error is present.

Checkpoint: Checkpoint: Start replication and inspect both threads

Continue whenI/O and SQL threads run, TLS is in use, and no last error is present. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

If this step fails

The command for “Start replication and inspect both threads” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

verification

Replicate a controlled canary transaction

read-only

Insert a timestamped marker on the primary, verify it on the replica, measure lag, then remove it through normal replication.

Why this step matters

Replicate a controlled canary transaction is a separate checkpoint because it establishes one auditable part of operate encrypted, observable replication without using it as a substitute for backups. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Insert a timestamped marker on the primary, verify it on the replica, measure lag, then remove it through normal replication. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Replicate a controlled canary transaction”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Replicate a controlled canary transaction”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/4 ready

Values stay on this page and are never sent or saved.

Primary{{primary}}Operator-reviewed value used by this tutorial for primary; derive it from the target environment and never from untrusted request data.Database{{database}}Application database name; use an identifier approved by the database naming policy.Canary{{canary}}Operator-reviewed value used by this tutorial for canary; derive it from the target environment and never from untrusted request data.Replica{{replica}}Operator-reviewed value used by this tutorial for replica; derive it from the target environment and never from untrusted request data.

mariadb --host={{primary}} --ssl {{database}} -e "CREATE TABLE IF NOT EXISTS replication_canary(id VARCHAR(64) PRIMARY KEY, created_at TIMESTAMP); INSERT INTO replication_canary VALUES('{{canary}}',NOW());"; mariadb --host={{replica}} --ssl {{database}} -e "SELECT * FROM replication_canary WHERE id='{{canary}}';"

Example output / evidence

The marker appears on the replica within the stated lag objective.

Checkpoint: Checkpoint: Replicate a controlled canary transaction

Continue whenThe marker appears on the replica within the stated lag objective. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

If this step fails

The command for “Replicate a controlled canary transaction” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

Finish line

Verification checklist

Replication healthmariadb --host={{replica}} --ssl -e 'SHOW REPLICA STATUS\G'Both threads run, Seconds_Behind_Master is acceptable, and Last_IO_Error/Last_SQL_Error are empty.

TLS verificationmariadb --host={{replica}} --ssl -e "SHOW STATUS LIKE 'Ssl_cipher';"A non-empty approved TLS cipher is reported.

Recovery guidance

Common problems and safe checks

The native client reports access denied even though a similarly named account has grants.

Likely causeDatabase identities include a host component and authentication plugin; the connection matched another user@host row or transport.

Safe checks

mysql -e 'SELECT USER(), CURRENT_USER();'
mysql -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"
mysql -e 'SHOW GRANTS FOR CURRENT_USER;'

ResolutionIdentify the exact matched account and create or modify only the intended user@host identity; do not broaden it to '%' as a shortcut.

TLS is enabled but the client still connects with an unexpected identity or without hostname verification.

Likely causeThe client uses encryption-only mode, connects by an address absent from the SAN, trusts a broad system CA set, or another option file overrides policy.

Safe checks

mysql --print-defaults
openssl s_client -starttls mysql -connect db.example.com:3306 -servername db.example.com </dev/null
mysql -e "SHOW STATUS LIKE 'Ssl_%';"

ResolutionUse the documented hostname, intended CA and VERIFY_IDENTITY-equivalent mode; remove conflicting client options and retest expected failure with a wrong hostname.

A change passes syntax checks but application errors, lag, latency or disk use increases.

Likely causeThe candidate is syntactically valid but workload, lock, cache, rotation, privilege or version assumptions are wrong.

Safe checks

mysqladmin ping
mysql -e 'SHOW PROCESSLIST;'
journalctl -u mariadb -u mysql -p warning --since '-10 min' --no-pager

ResolutionStop promotion, preserve evidence, revert the smallest changed configuration/account/routing layer and reproduce with a safe representative workload.

After the procedure

Alternatives and next steps

Consider these alternatives

Use a managed database only after private connectivity, identity, encryption, monitoring, backup restoration and responsibility boundaries are proven.
Use immutable configuration automation and a disposable recovery environment when it produces stronger review, repeatability and rollback than manual host edits.

Operate it safely

Automate health, capacity, certificate, replication, backup and restore-test alerts with an owner and response runbook.
Review accounts, grants, package support, encryption material and recovery evidence after every major release and at least every 90–180 days.
Practice the rollback or restore from recorded artifacts on an isolated host so the documented time and dependencies remain credible.

Reference

Frequently asked questions

Does a successful command mean the procedure is complete?

No. Database tools may return success before application compatibility, replica replay, restore integrity, TLS identity or expected denials are proven. Use every checkpoint and final verification.

Can an administrator use the application account for convenience?

No. Separate identities make least privilege, rotation, audit and incident containment possible; the application should not hold administrative capabilities.

Why test restoration or negative access separately?

A backup that cannot restore and a grant that was never tested for denial are assumptions, not controls. Isolation makes those tests safe and repeatable.

Recovery

Rollback

Stop replication and restore the prior replica configuration; do not write to an uncertain replica.

Run STOP REPLICA on the replica and preserve SHOW REPLICA STATUS plus error logs.
Restore the archived replication configuration and restart only after validating server identity and GTID state.
Revoke the new replication user if the topology is abandoned; keep the seed backup.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23

MariaDB standard replicationofficial MariaDB secure connections and TLSofficial MariaDB Backup overviewofficial