# database hardening basics: MySQL edition

## TL;DR

Fresh MySQL installs ship with loose defaults, so the basics are: kill anonymous users and the test database, lock down root, require TLS for remote connections, and give every app its own least-privilege account. Takes about fifteen minutes and closes the holes scanners find first. Written for MySQL 8.0.

```text
database hardening basics: MySQL edition
```

## Use this when

- You just provisioned a MySQL server and it is still on defaults
- You inherited a database and need a quick security pass
- A scanner flagged your MySQL config
- You are writing a runbook for new database servers

## Not for this skill when

- You run Postgres (there is a separate skill for that)
- You need performance tuning, not security
- You are on a managed database like RDS (some of these are handled for you, check first)

## Steps

### 1. Run mysql_secure_installation as a first pass

The bundled script handles the obvious stuff: root password, anonymous users, remote root, test database.

```bash
sudo mysql_secure_installation
```

Expected: prompts walk you through setting a root password, removing anonymous users, disallowing remote root login, and dropping the test database. Answer yes to all of them.

### 2. Verify no anonymous or wildcard-host users remain

Trust but verify. List every account and its allowed hosts.

```bash
mysql -u root -p -e "SELECT user, host FROM mysql.user;"
```

Expected: no rows with an empty user, and no app accounts with host `%` unless you genuinely need remote access from anywhere. Root should show the loopback host only.

### 3. Create least-privilege accounts per app

No app should log in as root. Give each service its own user with only the privileges it needs, on only the databases it uses.

```bash
mysql -u root -p -e "CREATE USER 'shopapp'@'app-host' IDENTIFIED BY '[a strong password]'; GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'shopapp'@'app-host'; FLUSH PRIVILEGES;"
```

Expected: `SHOW GRANTS FOR 'shopapp'@'app-host';` shows only the four data privileges on shopdb, nothing global.

### 4. Require TLS for remote connections

Sniffing database traffic on a network is trivial, so force encryption for anything not on the same host. MySQL 8.0 enables TLS by default; make sure clients actually use it.

```bash
mysql -u root -p -e "SHOW VARIABLES LIKE 'require_secure_transport';"
```

Expected: value `ON`. If it is OFF, set `require_secure_transport=ON` in the server config and restart. Then confirm from the app host that connections report TLS in use.

### 5. Turn on the audit log

You want a record of who connected and what they ran, especially failed logins. Enable the audit log plugin and check it writes.

```bash
mysql -u root -p -e "INSTALL PLUGIN audit_log SONAME 'audit_log.so'; SHOW VARIABLES LIKE 'audit_log_file';"
```

Expected: the plugin installs and a log file path is reported. Tail the file after a test login to confirm entries appear.

## Why this happens

MySQL's defaults prioritize getting started quickly over being safe, which is fair for local dev but dangerous on a server. Anonymous users, remote root, and cleartext traffic are the first things any scanner or attacker checks. The steps above do not make MySQL bulletproof, they just close the doors that ship open.

## Edge cases and pitfalls

- Managed databases (RDS, Cloud SQL) handle root and TLS differently; check their docs before applying these steps blindly.
- Changing the root auth method can break existing backup scripts that connect as root; update those first.
- `require_secure_transport=ON` breaks old clients without TLS support; test app connections before enforcing.
- Audit logs grow fast on busy servers; set up log rotation or you will fill the disk.
- Do not forget replicas: every read replica needs the same hardening, attackers love the forgotten replica.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XdgDaPUYsX7A8lSYzU7WNg