Install and secure MySQL on Ubuntu
Install MySQL, remove unsafe defaults, create a least-privilege application database, optionally allow restricted TLS network access, and prove backup readiness.
Provide a working MySQL service whose administrative, application, network, and recovery boundaries are explicit and testable.
- Ubuntu Server 24.04 LTS
- MySQL 8.x
- Recovery point Snapshot the host or capture a verified database backup before changing an existing installation.
- Access plan Prefer local Unix-socket access. If remote access is required, identify one private bind address and the smallest client subnet.
ip -brief address && ip route - Password handling Generate application credentials outside shell history and store them in the application secret manager.
- Port check Confirm port 3306 is not already owned by another database process.
sudo ss -lntp 'sport = :3306'
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
- An Ubuntu-supported MySQL service whose package version, data path, systemd state, local administrative socket, listener, and capacity have explicit acceptance evidence.
- Separate localhost and optional private-network application identities with schema-scoped privileges, interactive password handling, required TLS for remote access, and no routine remote root login.
- A source-restricted network path, validated configuration restart, encrypted client proof, and first logical artifact that leads into the dedicated recovery rehearsal.
- MySQL starts automatically, answers through its local socket, listens only on localhost or one selected private address, and has no anonymous accounts or test database.
- The application account can perform only its approved operations on appdb; a remote account, when needed, is separate and cannot authenticate without encrypted transport.
- Configuration rollback, account revocation, firewall removal, password rotation, package maintenance, monitoring, and restore testing are documented instead of being left as post-installation assumptions.
Architecture
How the parts fit together
Ubuntu manages mysqld through systemd and packaged configuration includes. Local administration uses the Unix socket, while the application receives a schema-scoped identity. Remote access is optional and adds three independent gates: a private bind address, a host-pattern-specific MySQL account requiring TLS, and a source-restricted firewall rule. A logical dump proves export capability but is not called a backup until an isolated restore and application validation pass.
- Inventory existing MySQL/MariaDB packages, port ownership, storage, memory, repositories, backups, and application dependencies before package changes.
- Install the Ubuntu package, record the exact server version, and prove local systemd plus socket health.
- Remove installation-only defaults, then create a dedicated schema and localhost account with only application operations.
- Prove local login and negative privilege behavior before considering any remote path.
- If remote access is required, create a separate host-scoped identity, bind one private address, require secure transport, validate, restart, and add the narrow firewall rule.
- Prove encrypted remote identity and cipher, export a first logical artifact, then schedule the full independent restore rehearsal.
Assumptions
- The target is Ubuntu Server 24.04 LTS and the supported repository provides a MySQL 8.x release compatible with the application.
- Existing installations are not overwritten: their data directory, package origin, plugins, accounts, replication, encryption keys, and verified backups are inventoried first.
- Local Unix-socket application access is preferred. Remote database access exists only when a private application tier cannot use the local socket.
- Passwords are generated outside shell history, stored in an approved secret manager, delivered to the application through a protected channel, and rotated through a tested process.
- The selected private server address and client CIDR are stable, routed, non-public, and also represented in provider or network firewall policy.
- A logical dump is suitable for this initial schema proof; large, high-write, replicated, encrypted, or strict-RTO databases need a separately designed backup method.
Key concepts
- Account identity
- MySQL matches both user and host; `appuser@localhost` and `appuser@10.%` are different principals.
- Authentication plugin
- The mechanism used to verify an account credential; client compatibility must be checked before changing it.
- Least privilege
- Grant only operations and schemas the workload actually needs, with administrative capabilities held separately.
- Secure transport
- A server/account requirement that rejects unencrypted TCP sessions rather than merely preferring TLS.
- Bind address
- The local interface address on which mysqld accepts TCP; it is an exposure boundary, not an access-control list.
- Logical dump
- A portable SQL representation created through the server; recoverability requires parsing, checksum, retention, and restore testing.
Before you copy
Values used in this guide
{{strongPassword}}secretUnique high-entropy application password entered through an interactive or protected channel.
Example: generated-in-secret-manager{{clientSubnet}}MySQL host pattern corresponding to the approved private client range.
Example: 10.20.30.0/255.255.255.0{{privateAddress}}Exact private server address on which mysqld may listen.
Example: 10.20.30.10{{clientCidr}}Network firewall CIDR allowed to reach privateAddress TCP/3306.
Example: 10.20.30.0/24Security and production boundaries
- Do not place MySQL passwords in command arguments, shell history, process listings, repository files, CI logs, or the generated guide URL.
- A host wildcard is not a firewall. Use the narrowest MySQL host identity, private listener, network firewall, and TLS identity verification together.
- `mysql_secure_installation` is an interactive assistant, not a compliance certificate. Query accounts, grants, schemas, listeners, TLS, and effective variables afterward.
- Do not grant global `*.*`, GRANT OPTION, FILE, PROCESS, SUPER-equivalent, replication, or plugin-management capabilities to an application account without a specific reviewed need.
- REQUIRE SSL encrypts transport but does not itself authenticate the server hostname. Production clients should verify the CA and server identity with managed trust material.
- Protect database files, binary logs, dumps, option files, TLS private keys, and secret-manager exports as sensitive data.
Stop before continuing if
- Stop if MariaDB, another MySQL origin, an existing data directory, replication, encryption, or port owner is discovered unexpectedly.
- Do not run the security assistant or account DDL until a recoverable snapshot or verified backup exists for an existing service.
- Do not restart after configuration edits unless `mysqld --validate-config` succeeds and console/local socket recovery is available.
- Do not open the firewall until the private bind, host-specific account, TLS requirement, and allowed source are all proven.
- Do not call a dump a backup until checksum, protected retention, isolated restore, object comparison, and application validation succeed.
verification
Inspect packages, ports, storage, and memory
Determine whether MySQL or MariaDB is already installed and confirm enough capacity before package changes.
Why this step matters
Preflight prevents package installation from colliding with an existing MySQL/MariaDB service, data directory, port owner, or insufficient storage and memory.
What to understand
Inspect packages by origin and version, all mysqld/mariadbd processes, TCP/3306, `/etc/mysql`, `/var/lib/mysql`, mounts, backups, free bytes/inodes, and memory.
An empty dpkg glob is not proof of a clean host; containers, tarball installations, sockets, and disabled services may still own database state.
For an existing server, record users, grants, plugins, replication, encryption, binary logs, and a successfully restored backup before modifying packages.
System changes
- No intended persistent change; reads MySQL, systemd, network, package, or artifact state for evidence.
Syntax explained
dpkg-query -W- Reports installed packages matching MySQL and MariaDB names.
ss -lntp 'sport = :3306'- Identifies a process already listening on the default TCP port.
df -h /var/lib- Checks capacity where the packaged data directory normally resides.
free -h- Displays memory and swap capacity before allocating database workload.
dpkg-query -W 'mysql*' 'mariadb*' 2>/dev/null || true; sudo ss -lntp 'sport = :3306'; df -h /var/lib; free -hNo listener on :3306 Filesystem /var/lib has 38G available Mem: 7.7Gi
Checkpoint: Approve the installation boundary
Continue whenNo conflicting service exists, or the existing installation and recoverable state are fully understood.
Stop whenObserved identity, state, privilege, listener, TLS, or artifact evidence differs from the reviewed design.
If this step fails
The check returns incomplete or unexpected evidence.
Likely causeInsufficient privilege, the wrong server/account, stale client defaults, or an unplanned existing installation changes the result.
sudo mysql --execute='SELECT @@hostname, @@port, VERSION();'systemctl status mysql --no-pager
ResolutionVerify the target and client option sources, capture the complete error, and resolve the first unexplained difference before continuing.
Security notes
- Redact account host patterns, private addresses, paths, and server metadata before external sharing.
Alternatives
- Capture equivalent evidence through an approved database inventory and monitoring system.
Stop conditions
- Do not accept a check whose target server and authenticated identity are uncertain.
command
Install the Ubuntu MySQL server package
Install from the configured Ubuntu repositories, then record the exact server version before applying version-specific settings.
Why this step matters
Installing from a signed supported Ubuntu repository establishes a maintainable package, service unit, security-update path, and known version baseline.
What to understand
Review `apt-cache policy mysql-server` before installation so a third-party origin cannot silently replace the expected package.
Record client and server versions after installation; documentation and configuration behavior can differ across 8.0 and 8.4 releases.
Treat repository signature, dependency, disk, and interrupted-dpkg errors as blockers instead of forcing installation.
System changes
- Installs MySQL server/client packages, creates service configuration and data files, and may start mysqld automatically.
Syntax explained
apt update- Retrieves and verifies current repository metadata.
apt install mysql-server- Installs Ubuntu's supported MySQL server dependency set.
--yes- Accepts the reviewed package transaction without hiding errors.
mysql --version- Records the installed client release for compatibility evidence.
sudo apt update && sudo apt install --yes mysql-server && mysql --versionmysql Ver 8.0.x for Linux on x86_64
Checkpoint: Record package provenance
apt-cache policy mysql-server && mysql --versionContinue whenThe candidate comes from the approved Ubuntu origin and the expected MySQL 8.x version is installed.
Stop whenOrigin, version, signature, dependency plan, or package configuration is unexpected.
If this step fails
APT wants to remove another database package.
Likely causeConflicting MySQL/MariaDB origins or packages already own common files.
apt-cache policy mysql-server mariadb-serverdpkg -l | grep -E 'mysql|mariadb'
ResolutionStop and design a supported migration with backup/restore; never approve destructive package replacement casually.
Security notes
- Do not add vendor repositories or signing keys without explicit provenance and lifecycle approval.
Alternatives
- Use the official MySQL APT repository only when a required supported release is unavailable from the approved Ubuntu channel.
Stop conditions
- Do not continue after any package provenance or data-ownership ambiguity.
verification
Verify local service health
Confirm systemd considers MySQL active and that the local administrative socket answers before changing accounts.
Why this step matters
Local service and socket health must be proven before account or network changes so later failures have a known-good comparison point.
What to understand
Check enabled and active state separately: one describes future boots, the other the current process.
`mysqladmin ping` through sudo validates local server response without creating remote administrative credentials.
Also inspect recent journal/error-log records for crash recovery, corruption, deprecation, or permission messages.
System changes
- No intended persistent change; reads MySQL, systemd, network, package, or artifact state for evidence.
Syntax explained
is-enabled- Checks whether MySQL is configured to start at boot.
is-active- Checks whether systemd currently considers MySQL running.
mysqladmin ping- Requests a lightweight server liveness response.
systemctl is-enabled mysql && systemctl is-active mysql && sudo mysqladmin pingenabled active mysqld is alive
Checkpoint: Accept local service health
Continue whenThe service is enabled and active, the socket answers `mysqld is alive`, and logs contain no unexplained error.
Stop whenObserved identity, state, privilege, listener, TLS, or artifact evidence differs from the reviewed design.
If this step fails
The check returns incomplete or unexpected evidence.
Likely causeInsufficient privilege, the wrong server/account, stale client defaults, or an unplanned existing installation changes the result.
sudo mysql --execute='SELECT @@hostname, @@port, VERSION();'systemctl status mysql --no-pager
ResolutionVerify the target and client option sources, capture the complete error, and resolve the first unexplained difference before continuing.
Security notes
- Redact account host patterns, private addresses, paths, and server metadata before external sharing.
Alternatives
- Capture equivalent evidence through an approved database inventory and monitoring system.
Stop conditions
- Do not accept a check whose target server and authenticated identity are uncertain.
command
Remove unsafe installation defaults
Run the vendor security assistant interactively. Remove anonymous users and the test database; keep remote root login disabled.
Why this step matters
The vendor assistant removes installation-only accounts and schemas, but its choices must be reviewed interactively and verified with explicit SQL afterward.
What to understand
Remove anonymous users and the test database, reject remote root login, and retain only deliberate administrative authentication.
Password validation policy is an operational decision: ensure application generators and rotation can satisfy it before enabling a strict profile.
Query mysql.user and SHOW DATABASES after the assistant; success text alone does not prove the intended final state.
System changes
- May change root authentication, password validation policy, anonymous users, test schemas, remote-root accounts, and privilege tables.
Syntax explained
mysql_secure_installation- Runs MySQL's interactive post-installation security assistant.
Remove anonymous users- Deletes accounts that can authenticate without a named identity.
Disallow root login remotely- Removes broad network origins for the root account.
Remove test database- Deletes the default test schema and matching privileges.
sudo mysql_secure_installationRemove anonymous users? Success. Disallow root login remotely? Success. Remove test database? Success. Reload privilege tables now? Success.
Checkpoint: Verify defaults were removed
sudo mysql --execute="SELECT user,host FROM mysql.user ORDER BY user,host; SHOW DATABASES;"Continue whenNo anonymous user, remote-root wildcard, or test database remains; intended local administration still works.
Stop whenAn unexpected account/schema exists or local administrative recovery fails.
If this step fails
Local root access stops working.
Likely causeThe selected authentication change does not match Ubuntu's local administrative model.
sudo mysql --protocol=socketsudo journalctl -u mysql --since '10 minutes ago' --no-pager
ResolutionUse console and the documented MySQL recovery procedure; do not expose or weaken root authentication remotely.
Security notes
- Do not paste administrative passwords into the assistant from visible logs or shared terminals.
Alternatives
- Apply equivalent reviewed SQL idempotently through secure configuration management, then run the same verification queries.
Stop conditions
- Stop when local administrative recovery or account inventory is not understood.
config
Create a local least-privilege database account
Create a dedicated database and localhost-only user. Replace the password placeholder at the prompt and grant rights only on the application schema.
Why this step matters
A dedicated schema and host-specific identity constrain application compromise and make credential rotation independent from database administration.
What to understand
Use utf8mb4 with an application-tested collation. Collation choice affects comparison, ordering, indexes, and migrations.
The example includes common runtime plus migration privileges. Split CREATE/ALTER/INDEX into a migration account when production runtime does not need them.
Enter the generated password through a protected interactive workflow and immediately store only the secret-manager reference in application configuration.
System changes
- Creates appdb, creates `appuser@localhost`, and grants schema-scoped data plus DDL privileges.
Syntax explained
CREATE DATABASE ... utf8mb4- Creates a Unicode-capable application schema with explicit collation.
CREATE USER ...@localhost- Creates an identity matching only local connections.
IDENTIFIED BY- Sets the account secret; the placeholder must be replaced securely.
ON appdb.*- Limits grants to objects inside the application schema.
Values stay on this page and are never sent or saved.
sudo mysql
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '{{strongPassword}}';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON appdb.* TO 'appuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost';
EXIT;GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON `appdb`.* TO `appuser`@`localhost`
Checkpoint: Review exact grants
sudo mysql --execute="SHOW GRANTS FOR 'appuser'@'localhost';"Continue whenOnly intended appdb privileges appear and no global administrative grant or GRANT OPTION is present.
Stop whenThe host, plugin, schema, password handling, or privilege set is broader than reviewed.
If this step fails
The application needs a privilege not in the initial grant.
Likely causeRuntime and migration responsibilities were not inventoried.
sudo mysql --execute="SHOW GRANTS FOR 'appuser'@'localhost';"
ResolutionIdentify the exact denied operation and grant it to a separate migration identity when possible; never grant ALL globally.
Security notes
- Never reuse the application password for remote, backup, replication, monitoring, or administrative accounts.
Alternatives
- Create separate runtime and migration accounts immediately for stronger production separation.
Stop conditions
- Do not grant a privilege whose application operation and revocation plan are unknown.
verification
Prove the application login
Use an interactive password prompt so the credential is not placed on the command line or in shell history.
Why this step matters
A real password-authenticated application connection proves account matching, secret delivery, schema selection, and current privileges before network exposure is considered.
What to understand
Use `--password` without a value so the client prompts instead of exposing the secret in process arguments.
CURRENT_USER shows the account MySQL actually matched, while DATABASE proves the connection selected appdb.
Add a negative query for a global administrative action in the controlled commissioning test to prove denial.
System changes
- No intended persistent change; reads MySQL, systemd, network, package, or artifact state for evidence.
Syntax explained
--user=appuser- Selects the named application principal.
--password- Prompts interactively without placing the value on the command line.
--database=appdb- Selects the intended schema immediately after authentication.
--execute- Runs a bounded identity query and exits.
mysql --user=appuser --password --database=appdb --execute='SELECT CURRENT_USER(), DATABASE();'CURRENT_USER() DATABASE() appuser@localhost appdb
Checkpoint: Prove local identity
Continue whenCURRENT_USER is appuser@localhost, DATABASE is appdb, expected work succeeds, and an administrative negative test is denied.
Stop whenObserved identity, state, privilege, listener, TLS, or artifact evidence differs from the reviewed design.
If this step fails
The check returns incomplete or unexpected evidence.
Likely causeInsufficient privilege, the wrong server/account, stale client defaults, or an unplanned existing installation changes the result.
sudo mysql --execute='SELECT @@hostname, @@port, VERSION();'systemctl status mysql --no-pager
ResolutionVerify the target and client option sources, capture the complete error, and resolve the first unexplained difference before continuing.
Security notes
- Redact account host patterns, private addresses, paths, and server metadata before external sharing.
Alternatives
- Capture equivalent evidence through an approved database inventory and monitoring system.
Stop conditions
- Do not accept a check whose target server and authenticated identity are uncertain.
decision
Add a separate restricted remote identity only if required
Do not widen the localhost account. Create a distinct identity limited to the intended private client subnet and require encrypted transport.
Why this step matters
A separate remote principal avoids widening the trusted origins, credential, privileges, and audit meaning of the proven localhost identity.
What to understand
MySQL account host syntax is its own matching system; document how the chosen subnet notation maps to actual clients and name resolution.
REQUIRE SSL rejects plaintext for this account. Production clients should additionally verify CA and server hostname.
Start with runtime-only privileges; use a distinct migration route rather than granting remote DDL by default.
System changes
- Optionally creates `appremote` scoped to the client subnet and grants appdb runtime privileges with mandatory encrypted transport.
Syntax explained
'appremote'@'host-pattern'- Defines a principal distinct from localhost by both username and connection origin.
REQUIRE SSL- Rejects this account when transport is not encrypted.
SELECT, INSERT, UPDATE, DELETE- Grants runtime data operations without schema or global administration.
SHOW GRANTS- Displays the exact resulting authorization for review.
Values stay on this page and are never sent or saved.
sudo mysql
CREATE USER 'appremote'@'{{clientSubnet}}' IDENTIFIED BY '{{strongPassword}}' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appremote'@'{{clientSubnet}}';
SHOW GRANTS FOR 'appremote'@'{{clientSubnet}}';
EXIT;GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `appremote`@`10.20.30.0/255.255.255.0` REQUIRE SSL
Checkpoint: Approve optional remote identity
sudo mysql --execute="SHOW CREATE USER 'appremote'@'{{clientSubnet}}'; SHOW GRANTS FOR 'appremote'@'{{clientSubnet}}';"Continue whenThe exact host pattern, authentication, SSL requirement, and appdb-only runtime grants are visible.
Stop whenRemote access lacks a business need, source range is broad, TLS is optional, or grants exceed runtime scope.
If this step fails
The connection matches another appremote host row.
Likely causeOverlapping MySQL host patterns or DNS behavior selects a more specific account.
sudo mysql --execute="SELECT user,host,plugin FROM mysql.user WHERE user='appremote';"
ResolutionRemove ambiguous rows and retain one documented origin mapping; verify CURRENT_USER from the client.
Security notes
- Treat MySQL host matching as defense in depth, not a replacement for network ACLs.
Alternatives
- Keep MySQL local and connect through an application service, private proxy, or host-local architecture.
Stop conditions
- Skip all remote steps when local access satisfies the application design.
config
Bind MySQL to the private service address
Skip this step for local-only applications. Back up the vendor file, bind one private address rather than all interfaces, and require secure transport.
Why this step matters
Binding one private address and requiring secure transport turns remote access into an explicit interface decision rather than publishing mysqld on every host address.
What to understand
The local include isolates the change from vendor files and the archive provides rollback. Check include precedence for duplicate settings.
`bind-address` controls the listener, while `require_secure_transport` rejects insecure TCP globally; account-level REQUIRE SSL remains useful evidence.
Confirm server TLS certificate provenance and client hostname-verification design before production use.
System changes
- Archives packaged configuration and creates a local mysqld include that changes TCP listener and secure-transport policy after restart.
Syntax explained
[mysqld]- Scopes following options to the server daemon.
bind-address- Selects the exact local interface address for TCP/3306.
require_secure_transport = ON- Rejects unencrypted TCP connections to the server.
90-oneliners-network.cnf- Keeps reviewed local overrides separate from packaged configuration.
/etc/mysql/mysql.conf.d/90-oneliners-network.cnfValues stay on this page and are never sent or saved.
sudo cp --archive /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.before-oneliners && printf '[mysqld]\nbind-address = {{privateAddress}}\nrequire_secure_transport = ON\n' | sudo tee /etc/mysql/mysql.conf.d/90-oneliners-network.cnf[mysqld] bind-address = 10.20.30.10 require_secure_transport = ON
Checkpoint: Review effective network intent
sudo mysqld --validate-config && sudo grep -R --line-number -E 'bind-address|require_secure_transport' /etc/mysqlContinue whenValidation passes and no conflicting later include overrides the one private address or secure transport.
Stop whenThe address is not assigned, duplicate options conflict, TLS is unavailable, or rollback copy is missing.
If this step fails
Validation reports an unknown or duplicate option.
Likely causeThe installed release uses another syntax or multiple includes set the same key incompatibly.
sudo mysqld --validate-configsudo my_print_defaults mysqld
ResolutionRemove the unvalidated local include or correct it using documentation for the installed version; do not restart.
Security notes
- A private address can still be reachable by a large internal network; authentication and firewall restriction remain mandatory.
Alternatives
- Keep the default loopback listener when every client is local.
Stop conditions
- Do not restart unless configuration validation and rollback evidence pass.
warning
Validate configuration before restart
Reject an invalid configuration before interrupting the database, then restart and confirm the expected listener.
Why this step matters
Pre-validation reduces avoidable downtime, while a controlled restart and exact listener check prove the on-disk policy is actually active.
What to understand
Capture current connections and maintenance approval before restarting an existing service; restart interrupts client sessions.
Run validation immediately before systemctl restart so the exact current files are checked.
After restart, inspect active state, journal, socket address, effective variables, and a local administrative query.
System changes
- Restarts mysqld, interrupting active sessions, and activates the new listener and secure-transport configuration.
Syntax explained
mysqld --validate-config- Parses server options without starting the normal service.
systemctl restart mysql- Stops and starts MySQL under systemd.
ss ... sport = :3306- Shows the exact active listener and owning process.
sudo mysqld --validate-config && sudo systemctl restart mysql && systemctl is-active mysql && sudo ss -lntp 'sport = :3306'active
LISTEN 0 151 10.20.30.10:3306 users:(("mysqld",pid=1842,fd=22))Checkpoint: Accept the restarted server
systemctl is-active mysql && sudo ss -lntp 'sport = :3306' && sudo mysql --execute="SELECT @@bind_address, @@require_secure_transport;"Continue whenService is active, only the intended listener exists, secure transport is ON, and local administration works.
Stop whenValidation/restart fails, address is wrong, unexpected public listeners exist, or local socket recovery fails.
If this step fails
MySQL does not return after restart.
Likely causeInvalid effective configuration, unavailable address, port conflict, storage/permission error, or recovery failure blocks startup.
sudo systemctl status mysql --no-pagersudo journalctl -u mysql --since '10 minutes ago' --no-pager
ResolutionRestore the archived/include configuration from console, validate it, restart, and preserve failure evidence.
Security notes
- Do not weaken authentication or file permissions as a generic startup fix.
Alternatives
- Test the complete configuration on a disposable restore host before the production maintenance window.
Stop conditions
- Do not open firewall access until post-restart local health is fully proven.
warning
Allow only the database client subnet
Skip this for local-only access. Preview and add a source-restricted rule; never expose MySQL to the public internet.
Why this step matters
A dry-run and exact source/destination rule ensure TCP/3306 is reachable only from the approved application network rather than the internet or every private host.
What to understand
Confirm the client source as observed by the database host, including NAT, VPN, and IPv6 behavior.
Preview generated rules, add the narrow rule, verify order, and test both an allowed and denied source.
Keep provider/network firewall policy aligned; UFW is a host layer, not the whole path.
System changes
- Adds a persistent UFW allow rule for clientCidr to privateAddress TCP/3306.
Syntax explained
--dry-run- Shows generated firewall changes without applying them.
from clientCidr- Limits the source network accepted by this rule.
to privateAddress port 3306- Limits destination address and MySQL TCP port.
proto tcp- Prevents an unintended UDP rule.
Values stay on this page and are never sent or saved.
sudo ufw --dry-run allow proto tcp from {{clientCidr}} to {{privateAddress}} port 3306 && sudo ufw allow proto tcp from {{clientCidr}} to {{privateAddress}} port 3306 && sudo ufw status numbered[ 2] 10.20.30.10 3306/tcp ALLOW IN 10.20.30.0/24
Checkpoint: Prove network restriction
sudo ufw status numbered && sudo ss -lntp 'sport = :3306'Continue whenOne narrow rule matches the private listener; allowed client connects and a denied source cannot.
Stop whenThe rule is broad, source/destination is wrong, IPv6 bypasses policy, or denied-source test succeeds.
If this step fails
Allowed client times out.
Likely causeRouting, upstream ACL, UFW order, NAT source, listener address, or address-family mismatch blocks it.
sudo ufw status numberedsudo tcpdump -ni any tcp port 3306 -c 20
ResolutionTrace the intended path and correct the smallest boundary; never add an Anywhere rule for diagnosis.
Security notes
- Never expose 3306 directly to the public internet.
Alternatives
- Use private networking or an authenticated tunnel and leave MySQL unreachable from general networks.
Stop conditions
- Do not apply the rule without console access and a reviewed client-source inventory.
verification
Test an encrypted remote session
From an allowed client, require TLS, use an interactive password prompt, and confirm the authenticated account plus cipher.
Why this step matters
An end-to-end client proof confirms routing, firewall, account host matching, schema, password delivery, encrypted transport, and the negotiated cipher together.
What to understand
Run from an allowed client and prompt for the secret. CURRENT_USER identifies the matched account rather than the submitted name alone.
`--ssl-mode=REQUIRED` rejects plaintext. For production, use VERIFY_IDENTITY with a managed CA and correct server hostname.
Repeat from a denied source and with plaintext prohibited to prove negative controls.
System changes
- No intended persistent change; reads MySQL, systemd, network, package, or artifact state for evidence.
Syntax explained
--host- Forces TCP to the selected private listener.
--ssl-mode=REQUIRED- Refuses an unencrypted session.
@@ssl_cipher- Displays the cipher negotiated for the current connection.
CURRENT_USER()- Shows the exact user@host account selected by the server.
Values stay on this page and are never sent or saved.
mysql --host={{privateAddress}} --user=appremote --password --ssl-mode=REQUIRED --database=appdb --execute='SELECT CURRENT_USER(), @@ssl_cipher;'CURRENT_USER() @@ssl_cipher appremote@10.20.30.0/255.255.255.0 TLS_AES_256_GCM_SHA384
Checkpoint: Prove encrypted remote identity
Continue whenThe expected remote account/schema/cipher appear; plaintext and denied-source attempts fail.
Stop whenObserved identity, state, privilege, listener, TLS, or artifact evidence differs from the reviewed design.
If this step fails
The check returns incomplete or unexpected evidence.
Likely causeInsufficient privilege, the wrong server/account, stale client defaults, or an unplanned existing installation changes the result.
sudo mysql --execute='SELECT @@hostname, @@port, VERSION();'systemctl status mysql --no-pager
ResolutionVerify the target and client option sources, capture the complete error, and resolve the first unexplained difference before continuing.
Security notes
- Redact account host patterns, private addresses, paths, and server metadata before external sharing.
Alternatives
- Capture equivalent evidence through an approved database inventory and monitoring system.
Stop conditions
- Do not accept a check whose target server and authenticated identity are uncertain.
verification
Create a first logical recovery artifact
Use the dedicated backup and restore Guide for scheduling and retention. Here, prove that a consistent schema dump can be created and parsed.
Why this step matters
A first logical export catches missing dump privileges and establishes an artifact format, but the tutorial explicitly withholds the word recoverable until isolated restoration succeeds.
What to understand
`--single-transaction` provides a consistent snapshot for transactional tables without global read locking; nontransactional tables need separate handling.
Include routines and triggers when they are part of application behavior, capture stderr and exit status, and checksum the finished artifact.
Move immediately to the dedicated backup/restore guide for a separate backup identity, encryption, retention, scheduling, restore, comparison, and cleanup.
System changes
- No intended persistent change; reads MySQL, systemd, network, package, or artifact state for evidence.
Syntax explained
mysqldump- Exports logical schema and data through the MySQL server.
--single-transaction- Starts one consistent transaction for transactional tables.
--routines --triggers- Includes stored routines and triggers in the artifact.
test -s- Rejects an empty output file before superficial inspection.
mysqldump --user=appuser --password --single-transaction --routines --triggers appdb > appdb-baseline.sql && test -s appdb-baseline.sql && head -n 5 appdb-baseline.sql-- MySQL dump -- Host: localhost Database: appdb -- Server version 8.0.x
Checkpoint: Hand off to restore testing
Continue whenDump exits zero, file is non-empty, header identifies the intended server/schema, checksum is recorded, and isolated restore is scheduled.
Stop whenObserved identity, state, privilege, listener, TLS, or artifact evidence differs from the reviewed design.
If this step fails
The check returns incomplete or unexpected evidence.
Likely causeInsufficient privilege, the wrong server/account, stale client defaults, or an unplanned existing installation changes the result.
sudo mysql --execute='SELECT @@hostname, @@port, VERSION();'systemctl status mysql --no-pager
ResolutionVerify the target and client option sources, capture the complete error, and resolve the first unexplained difference before continuing.
Security notes
- Redact account host patterns, private addresses, paths, and server metadata before external sharing.
Alternatives
- Capture equivalent evidence through an approved database inventory and monitoring system.
Stop conditions
- Do not accept a check whose target server and authenticated identity are uncertain.
Finish line
Verification checklist
sudo mysqladmin ping && sudo ss -lntp 'sport = :3306'MySQL is alive and listens only on the intended local or private address.sudo mysql --execute="SHOW GRANTS FOR 'appuser'@'localhost';"Shows application-schema grants without global administrative privileges.test -s appdb-baseline.sql && grep -m1 'Database: appdb' appdb-baseline.sqlThe dump is non-empty and identifies appdb.Recovery guidance
Common problems and safe checks
The mysql service fails after installation or restart.
Likely causeAn invalid option, port conflict, ownership problem, full filesystem, damaged data directory, or unsupported package/plugin prevents startup.
sudo mysqld --validate-configsudo systemctl status mysql --no-pagersudo journalctl -u mysql --since '15 minutes ago' --no-pager
ResolutionRestore the saved include or correct the first concrete server error; do not initialize over or delete an existing data directory.
The application receives `Access denied` despite a correct-looking password.
Likely causeA more specific user@host account matched, the host pattern differs after name resolution, privileges are absent, or the client uses incompatible authentication.
sudo mysql --execute="SELECT user,host,plugin,account_locked FROM mysql.user WHERE user IN ('appuser','appremote');"sudo mysql --execute="SHOW GRANTS FOR 'appuser'@'localhost';"
ResolutionIdentify CURRENT_USER from a controlled successful path, correct the exact account/grants, and never broaden host matching to `%` blindly.
Remote TCP connects but TLS-required login fails.
Likely causeThe server lacks usable TLS material, client trust is wrong, transport is plaintext, the wrong account matched, or a proxy terminates unexpectedly.
sudo mysql --execute="SHOW VARIABLES LIKE 'have_ssl'; SHOW VARIABLES LIKE 'require_secure_transport';"mysql --host=10.20.30.10 --ssl-mode=REQUIRED --user=appremote --password
ResolutionRestore valid server TLS and client trust, verify account selection and cipher, and keep the firewall narrow while troubleshooting.
mysqldump exits successfully but the artifact is tiny or misses objects.
Likely causeThe account lacks privileges, wrong database was selected, routines/events were omitted, tables use incompatible consistency behavior, or stderr was not retained.
test -s appdb-baseline.sql && wc -c appdb-baseline.sqlgrep -E '^-- (Host|Server version)' appdb-baseline.sql | head
ResolutionUse a dedicated backup identity and the complete recovery guide; create a new artifact and prove it through an isolated restore.
Reference
Frequently asked questions
Should MySQL listen on 0.0.0.0?
Normally no. Bind the exact private service address. A firewall reduces reachability, but a narrow listener prevents accidental exposure when another layer changes.
Can the application use root?
No. Root combines unrelated administrative powers, expands compromise impact, and prevents useful separation of duties.
Is REQUIRE SSL sufficient TLS verification?
It requires encryption for that account, but client-side CA and hostname verification are still needed to authenticate the server.
Why keep a separate remote account?
Local and network paths then have independent host matching, credentials, privileges, rotation, revocation, and audit evidence.
Recovery
Rollback
Restore the previous listener and authentication configuration without deleting application data.
- Remove /etc/mysql/mysql.conf.d/90-oneliners-network.cnf and restore the saved mysqld.cnf when it was changed.
- Run mysqld --validate-config, restart MySQL, and verify local socket access.
- Delete only the UFW rule added for port 3306 and confirm the service is no longer remotely reachable.
- Drop the appremote account if remote access is abandoned; retain appdb and its local account unless a verified backup and explicit deletion decision exist.
Evidence