OneLinersCommand workbench
Guides
Security / Databases & Data

Configure hostname-verifying TLS for MySQL and MariaDB clients

Issue identities from a trusted CA, protect private keys, require encrypted accounts, configure hostname verification, rotate certificates, and test downgrade resistance.

70 min8 stepsChanges system stateRevision 2
Save or explore
#mysql#mariadb#tls#certificates#x509#verification
Save to collectionCreate a collection in the sidebar first.
0 of 8 steps completed
Goal

Prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption.

Supported environments
  • MySQL 8.4 LTS
  • 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 hostname-verifying TLS for MySQL and MariaDB clients” 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
  • Prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. 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 private or public CA signs the database server identity. The server presents a certificate whose SAN matches the client hostname; clients trust only the intended CA and use identity verification, not encryption-only mode. Application accounts may require SSL or X.509 attributes. A staged dual-trust rotation introduces the next certificate/CA before removing the old one, with packet/listener and application checks proving no plaintext fallback.

CA and server certificateBind the database hostname to an approved trust root.
Server TLS policyOffers only encrypted authenticated connections on intended listeners.
Client verificationChecks chain and hostname instead of silently accepting any certificate.
Rotation overlapIntroduces the next trust material without an outage or plaintext fallback.
  1. Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
  2. Prepare the client tls candidate using only official MariaDB interfaces, protected secret delivery and the narrowest necessary scope.
  3. Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
  4. 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

{{app}}

Short application identifier used in service, pool, directory, and configuration names.

Example: billing
{{caFile}}

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

Example: /etc/mysql/ca/organization-root.pem
{{caSource}}

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

Example: example-casource
{{clientHost}}

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

Example: 192.0.2.44
{{databaseService}}

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

Example: mariadb
{{databaseTlsConfig}}

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

Example: /etc/mysql/conf.d/90-tls.cnf
{{dbHost}}

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

Example: db-primary.example.com
{{dbIp}}

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

Example: 192.0.2.30
{{nextServerCert}}

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

Example: /etc/mysql/tls/server-next.pem
{{serverCertSource}}

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

Example: example-servercertsource
{{serverKeySource}}

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

Example: example-serverkeysource
{{user}}

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

Example: billing_app

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.
01

instruction

Define stable database identities

read-only

Choose DNS names present in certificate SANs, list client networks, CA ownership, certificate lifetime, and rotation overlap.

Why this step matters

Define stable database identities is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Choose DNS names present in certificate SANs, list client networks, CA ownership, certificate lifetime, and rotation overlap. 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 “Define stable database identities”. The scope must remain limited to the selected application and supported host.

Syntax explained

getent
Invokes the principal tool or configuration operation for “Define stable database identities”; 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.
|
Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.
Command
Fill variables0/1 ready

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

getent ahosts {{dbHost}}; openssl s_client -connect {{dbHost}}:3306 -starttls mysql -servername {{dbHost}} </dev/null 2>/dev/null | openssl x509 -noout -subject -issuer -ext subjectAltName -dates
Example output / evidence
DNS name, SAN, issuer, and validity are recorded.

Checkpoint: Checkpoint: Define stable database identities

Continue whenDNS name, SAN, issuer, and validity are recorded. 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 getent 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 “Define stable database identities” 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 getent 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.
02

config

Install CA, certificate, and private key securely

caution

Place the server key outside application-readable paths, owned by mysql with restrictive mode, and preserve CA chain separately.

Why this step matters

Install CA, certificate, and private key securely is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Place the server key outside application-readable paths, owned by mysql with restrictive mode, and preserve CA chain separately. 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 “Install CA, certificate, and private key securely”. The scope must remain limited to the selected application and supported host.

Syntax explained

install
Invokes the principal tool or configuration operation for “Install CA, certificate, and private key securely”; 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.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
Fill variables0/3 ready

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

sudo install -o mysql -g mysql -m 0600 {{serverKeySource}} /etc/mysql/tls/server.key; sudo install -o mysql -g mysql -m 0644 {{serverCertSource}} /etc/mysql/tls/server.crt; sudo install -o root -g root -m 0644 {{caSource}} /etc/mysql/tls/ca.crt
Example output / evidence
TLS files have intended ownership and modes.

Checkpoint: Checkpoint: Install CA, certificate, and private key securely

Continue whenTLS files have intended ownership and modes. 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 install 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 “Install CA, certificate, and private key securely” 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 install 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.
03

config

Configure server TLS paths and minimum policy

caution

Set CA, cert, key, require_secure_transport or MariaDB equivalent, and an approved protocol/cipher policy compatible with managed clients.

Why this step matters

Configure server TLS paths and minimum policy is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Set CA, cert, key, require_secure_transport or MariaDB equivalent, and an approved protocol/cipher policy compatible with managed clients. 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 “Configure server TLS paths and minimum policy”. The scope must remain limited to the selected application and supported host.

Syntax explained

sudoedit
Invokes the principal tool or configuration operation for “Configure server TLS paths and minimum policy”; 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.
{{databaseTlsConfig}}
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
File {{databaseTlsConfig}}
Configuration
Fill variables0/1 ready

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

sudoedit {{databaseTlsConfig}}
Example output / evidence
Server configuration names the installed identity and requires secure transport.

Checkpoint: Checkpoint: Configure server TLS paths and minimum policy

Continue whenServer configuration names the installed identity and requires secure transport. 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 “Configure server TLS paths and minimum policy” 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

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.
04

verification

Restart and inspect effective TLS

read-only

Validate configuration, restart during a maintenance window, and inspect SSL variables plus error log.

Why this step matters

Restart and inspect effective TLS is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Validate configuration, restart during a maintenance window, and inspect SSL variables plus error log. 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 “Restart and inspect effective TLS”. The scope must remain limited to the selected application and supported host.

Syntax explained

systemctl
Invokes the principal tool or configuration operation for “Restart and inspect effective 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.

sudo systemctl restart {{databaseService}}; mysql --protocol=socket -e "SHOW VARIABLES LIKE '%ssl%'; SHOW STATUS LIKE 'Ssl_%';"; sudo journalctl -u {{databaseService}} -n 50 --no-pager
Example output / evidence
The server starts, TLS is available, and certificate paths match.

Checkpoint: Checkpoint: Restart and inspect effective TLS

Continue whenThe server starts, TLS is available, and certificate paths match. 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 systemctl 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 “Restart and inspect effective 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 systemctl 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.
05

command

Require TLS on remote accounts

caution

Apply REQUIRE SSL or REQUIRE X509 to approved remote accounts and keep local recovery accounts separately controlled.

Why this step matters

Require TLS on remote accounts is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Apply REQUIRE SSL or REQUIRE X509 to approved remote accounts and keep local recovery accounts separately controlled. 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 “Require TLS on remote accounts”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Require TLS on remote accounts”; 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.

mysql --protocol=socket -e "ALTER USER '{{user}}'@'{{clientHost}}' REQUIRE SSL; SHOW CREATE USER '{{user}}'@'{{clientHost}}';"
Example output / evidence
The remote account cannot authenticate over plaintext.

Checkpoint: Checkpoint: Require TLS on remote accounts

Continue whenThe remote account cannot authenticate over plaintext. 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 mysql 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 “Require TLS on remote accounts” 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 mysql 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.
06

config

Configure CA and hostname verification on clients

caution

Use MySQL VERIFY_IDENTITY or MariaDB certificate verification in protected client configuration; do not use skip-ssl-verify-server-cert.

Why this step matters

Configure CA and hostname verification on clients is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use MySQL VERIFY_IDENTITY or MariaDB certificate verification in protected client configuration; do not use skip-ssl-verify-server-cert. 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 “Configure CA and hostname verification on clients”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Configure CA and hostname verification on clients”; 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.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
Fill variables0/3 ready

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

mysql --host={{dbHost}} --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/{{app}}/db-ca.crt --user={{user}} --password -e 'STATUS;'
Example output / evidence
The client verifies the certificate chain and hostname.

Checkpoint: Checkpoint: Configure CA and hostname verification on clients

Continue whenThe client verifies the certificate chain and hostname. 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 mysql 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 CA and hostname verification on clients” 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 mysql 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.
07

verification

Prove wrong identity and plaintext are rejected

read-only

Test with a wrong hostname, untrusted CA, and disabled TLS from an isolated client; each must fail without changing production accounts.

Why this step matters

Prove wrong identity and plaintext are rejected is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Test with a wrong hostname, untrusted CA, and disabled TLS from an isolated client; each must fail without changing production accounts. 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 “Prove wrong identity and plaintext are rejected”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Prove wrong identity and plaintext are rejected”; 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.
|
Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.
Command
Fill variables0/4 ready

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

mysql --host={{dbIp}} --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/{{app}}/db-ca.crt --user={{user}} --password -e 'SELECT 1' || echo 'wrong identity rejected'; mysql --host={{dbHost}} --ssl-mode=DISABLED --user={{user}} --password -e 'SELECT 1' || echo 'plaintext rejected'
Example output / evidence
Both negative tests fail as designed.

Checkpoint: Checkpoint: Prove wrong identity and plaintext are rejected

Continue whenBoth negative tests fail as designed. 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 mysql 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 “Prove wrong identity and plaintext are rejected” 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 mysql 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.
08

instruction

Document and rehearse certificate rotation

read-only

Issue a replacement with overlapping validity, distribute CA chain first, rotate server identity, reconnect pools, and remove old trust only after telemetry is clean.

Why this step matters

Document and rehearse certificate rotation is a separate checkpoint because it establishes one auditable part of prevent passive interception and wrong-server connections instead of merely negotiating unauthenticated encryption. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Issue a replacement with overlapping validity, distribute CA chain first, rotate server identity, reconnect pools, and remove old trust only after telemetry is clean. 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 “Document and rehearse certificate rotation”. The scope must remain limited to the selected application and supported host.

Syntax explained

openssl
Invokes the principal tool or configuration operation for “Document and rehearse certificate rotation”; 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.

openssl x509 -in {{nextServerCert}} -noout -subject -issuer -dates -ext subjectAltName; openssl verify -CAfile {{caFile}} {{nextServerCert}}
Example output / evidence
The replacement certificate validates and the rotation runbook has owners and dates.

Checkpoint: Checkpoint: Document and rehearse certificate rotation

Continue whenThe replacement certificate validates and the rotation runbook has owners and dates. 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 openssl 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 “Document and rehearse certificate rotation” 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 openssl 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.

Finish line

Verification checklist

Verified sessionmysql --host={{dbHost}} --ssl-mode=VERIFY_IDENTITY --ssl-ca={{caFile}} --user={{user}} --password -e "SHOW STATUS LIKE 'Ssl_cipher';"An approved non-empty cipher is shown after hostname verification.
Secure transport enforcementmysql --host={{dbHost}} --ssl-mode=DISABLED --user={{user}} --password -e 'SELECT 1'The plaintext connection fails.

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

Restore the prior certificate paths and account TLS policy only through an approved emergency change.

  1. Restore the previous valid certificate and key, validate permissions, and restart the service.
  2. Keep require_secure_transport and account TLS requirements unless they are the confirmed outage cause.
  3. If temporarily relaxed, restrict network access further and set an immediate expiry for the exception.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23
MySQL 8.4 encrypted connectionsofficialMariaDB secure connections and TLSofficialMySQL 8.4 account management statementsofficial