> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL Security Hardening

> How to secure a MySQL server: running mysql_secure_installation, removing anonymous users, restricting root access, enforcing password policies, auditing privileges, enabling SSL/TLS, and reducing the attack surface.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

A default MySQL installation prioritizes ease of setup over security. Hardening a production server means removing defaults that made sense on a developer laptop but are inappropriate when the server is reachable by applications or the network.

## Hardening by Deployment Context

Not every hardening step carries the same risk/friction tradeoff. Use this table to prioritize:

| Step | Internet-facing production | Internal/private network | Dev / staging |
| :- | :- | :- | :- |
| Run `mysql_secure_installation` | Required | Required | Recommended |
| Remove anonymous users | Required | Required | Recommended |
| Restrict root to localhost | Required | Required | Recommended |
| Remove test database | Required | Required | Optional |
| `validate_password` component | Required | Required | Optional — can block quick iteration |
| Password expiration policy | Required | Recommended | Skip |
| Least-privilege app accounts | Required | Required | Recommended |
| Restrict host in grants (no `'%'`) | Required | Required | Optional |
| `REQUIRE SSL` on accounts | Required | Recommended | Skip |
| Custom CA-signed TLS certificates | Required | Optional — self-signed acceptable | Skip |
| `bind-address` to internal IP | Required | Recommended | Optional |
| Disable `local_infile` | Required | Recommended | Optional |
| `skip_name_resolve` | Recommended | Recommended | Optional |
| Lock or drop unused accounts | Required | Required | Optional |

**Internet-facing** means port 3306 is reachable from outside your private network, even if behind a load balancer. Apply every "Required" row without exception.

**Internal/private network** covers servers accessible only within a VPC, private subnet, or office network. Some mitigations (CA-signed certs, SSL enforcement on every account) add operational overhead for marginal gain — but strong passwords, least privilege, and bind-address restrictions are still non-negotiable.

**Dev/staging** machines are the most common source of credential leaks. Skipping password complexity or leaving `'%'` host grants in place is acceptable only if the instance is unreachable from outside your laptop or CI environment. If staging shares a network with production data, treat it as internal/private at minimum.

## mysql\_secure\_installation

Run this immediately after installing MySQL on any server that isn't a local dev machine:

```bash theme={null}
mysql_secure_installation
```

It walks through the most critical hardening steps:

* Set the root password (if not already set)
* Remove anonymous users
* Disallow remote root login
* Remove the test database
* Reload privilege tables

These four steps eliminate the most common attack surface on a fresh install.

## Root Account Restrictions

Root should only connect from `localhost`. Verify and enforce this:

```sql theme={null}
SELECT user, host FROM mysql.user WHERE user = 'root';
-- Should show only 'localhost', '127.0.0.1', '::1'
```

If root has a `'%'` host entry, remove it:

```sql theme={null}
DROP USER 'root'@'%';
```

For remote administration, create a named DBA account with limited host access instead of using root remotely.

## Remove Anonymous Users and Test Database

```sql theme={null}
-- Remove any anonymous accounts
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

-- Remove the test database (readable by anonymous users by default)
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%';
FLUSH PRIVILEGES;
```

## Password Policy

MySQL 8.0+ includes the `validate_password` component, which enforces password complexity rules. It must be installed before use — `mysql_secure_installation` offers to install it automatically. To install manually:

```sql theme={null}
INSTALL COMPONENT 'file://component_validate_password';
```

Verify it's active:

```sql theme={null}
SHOW VARIABLES LIKE 'validate_password%';
```

Configure minimum requirements:

```sql theme={null}
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.policy = 'MEDIUM';
```

Policy levels: `LOW` (length only), `MEDIUM` (length + character classes), `STRONG` (+ dictionary check).

Set password expiration for accounts that might be forgotten:

```sql theme={null}
ALTER USER 'admin'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
```

## Least-Privilege User Accounts

Application accounts should have the minimum privileges needed. See [MySQL User Management](https://villagesql.com/docs/en/guides/user-management.md) for the full GRANT syntax.

Common patterns:

```sql theme={null}
-- Read-only replica account
CREATE USER 'app_read'@'10.0.1.%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON myapp.* TO 'app_read'@'10.0.1.%';

-- Application write account (no schema changes)
CREATE USER 'app_write'@'10.0.1.%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write'@'10.0.1.%';

-- Migration account (schema changes allowed, used only by deploy scripts)
CREATE USER 'app_migrate'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_migrate'@'localhost';
```

Restrict hosts as tightly as possible — use subnet notation or specific IPs, not `'%'`.

## Auditing Existing Privileges

Review all accounts and their privileges:

```sql theme={null}
-- List all user accounts
SELECT user, host, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;

-- Check for overly broad grants
SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv,
       Super_priv, Grant_priv
FROM mysql.user
WHERE host = '%' OR Super_priv = 'Y';
```

Remove or lock accounts that aren't actively used:

```sql theme={null}
ALTER USER 'old_account'@'%' ACCOUNT LOCK;
-- Or drop entirely:
DROP USER 'old_account'@'%';
```

## SSL/TLS Connections

MySQL 8.0 enables SSL/TLS by default and auto-generates certificates on startup. Verify it's active:

```sql theme={null}
SHOW VARIABLES LIKE '%ssl%';
-- ssl_ca, ssl_cert, ssl_key should show file paths
-- have_ssl no longer exists on MySQL 8.4 (ERROR 1193). To confirm TLS is in
-- use, run SHOW STATUS LIKE 'Ssl_cipher' on an actual TLS connection.
```

Require SSL for specific accounts:

```sql theme={null}
ALTER USER 'app_write'@'%' REQUIRE SSL;
```

To verify a client connection is using SSL:

```sql theme={null}
SHOW STATUS LIKE 'Ssl_cipher';
-- Non-empty value means the current connection is encrypted
```

For production, replace the auto-generated self-signed certificates with certificates from a trusted CA.

## Network Access

Restrict MySQL to listen only on necessary interfaces. In `my.cnf`:

```ini theme={null}
[mysqld]
bind-address = 127.0.0.1     # local only
# or
bind-address = 10.0.1.5      # specific internal IP
```

If MySQL should not be reachable from the network at all (application on the same host), bind to `127.0.0.1` and never expose port 3306 externally. Firewall rules are a second layer of defense, not a substitute for restricting the bind address.

## Disabling Risky Features

Turn off features your application doesn't use:

```sql theme={null}
-- Prevent MySQL from reading/writing files on the server filesystem
-- (prevents SQL injection attacks that use LOAD DATA INFILE to read /etc/passwd)
SET GLOBAL local_infile = 0;
```

In `my.cnf`:

```ini theme={null}
[mysqld]
local_infile = 0
skip_name_resolve = 1       # don't do reverse DNS lookups; use IPs in grants
```

## Frequently Asked Questions

#### Is the default MySQL 8.4 install secure?

Better than older MySQL defaults, but still not hardened for production. MySQL 8.4 generates a random root password and enables SSL by default. You still need to: verify the root password is strong, remove anonymous users, restrict host access, and configure the `validate_password` component.

#### Should I run MySQL as root?

Never. MySQL should run as a dedicated low-privilege system user (typically `mysql`). Running the server process as root means a MySQL exploit could lead to full system compromise.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `ERROR 1045: Access denied for user 'root'@'%'` | Root remote login is correctly blocked — create a named admin user with specific host access |
| Application can't connect after adding `REQUIRE SSL` | Client needs to use SSL flags: `--ssl-mode=REQUIRED`; verify server certificates are valid |
| `validate_password` rejecting a password | Check current policy: `SHOW VARIABLES LIKE 'validate_password%'`; ensure the password meets all requirements |
| Can't find root password on a new install | MySQL 8.0 logs the temporary root password to the error log on first start — find the log path with `SELECT @@GLOBAL.log_error;`, then `grep 'temporary password' <path>` |
| Host `'%'` grants still visible after DELETE | Run `FLUSH PRIVILEGES` after any direct modifications to `mysql.user` |

## See also

* [MySQL User Management](https://villagesql.com/docs/en/guides/user-management.md) — creating users and granting least-privilege access
* [Password Hashing in MySQL](https://villagesql.com/docs/en/guides/password-hashing.md) — securing application-level credentials stored in MySQL
* [JWT Authentication in MySQL](https://villagesql.com/docs/en/guides/jwt-authentication.md) — moving credential issue and rotation to your identity provider


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.