Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide 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:
StepInternet-facing productionInternal/private networkDev / staging
Run mysql_secure_installationRequiredRequiredRecommended
Remove anonymous usersRequiredRequiredRecommended
Restrict root to localhostRequiredRequiredRecommended
Remove test databaseRequiredRequiredOptional
validate_password componentRequiredRequiredOptional — can block quick iteration
Password expiration policyRequiredRecommendedSkip
Least-privilege app accountsRequiredRequiredRecommended
Restrict host in grants (no '%')RequiredRequiredOptional
REQUIRE SSL on accountsRequiredRecommendedSkip
Custom CA-signed TLS certificatesRequiredOptional — self-signed acceptableSkip
bind-address to internal IPRequiredRecommendedOptional
Disable local_infileRequiredRecommendedOptional
skip_name_resolveRecommendedRecommendedOptional
Lock or drop unused accountsRequiredRequiredOptional
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: 
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: 
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: 
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

-- 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: 
INSTALL COMPONENT 'file://component_validate_password';
Verify it’s active: 
SHOW VARIABLES LIKE 'validate_password%';
Configure minimum requirements: 
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: 
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 for the full GRANT syntax. Common patterns: 
-- 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: 
-- 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: 
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: 
SHOW VARIABLES LIKE '%ssl%';
-- ssl_ca, ssl_cert, ssl_key should show file paths
-- have_ssl should be 'YES'
Require SSL for specific accounts: 
ALTER USER 'app_write'@'%' REQUIRE SSL;
To verify a client connection is using SSL: 
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: 
[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: 
-- 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: 
[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

ProblemSolution
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 SSLClient needs to use SSL flags: --ssl-mode=REQUIRED; verify server certificates are valid
validate_password rejecting a passwordCheck current policy: SHOW VARIABLES LIKE 'validate_password%'; ensure the password meets all requirements
Can’t find root password on a new installMySQL 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 DELETERun FLUSH PRIVILEGES after any direct modifications to mysql.user

​
See also