/PLAYBOOK · 2026-07-28

MySQL Security Hardening Practical Configuration Guide: Six-Step Database Defense

This tutorial covers the full MySQL hardening flow across account security, network config, access control, transport encryption, and audit logs. Core ops include mysql_secure_installation, bind-address, password policy, SSL, and failed-login limits.

Use Cases

MySQL is the most popular open-source relational database, widely used in web apps, CMS systems, and enterprise business systems. Default installs leave many gaps: root with no password or a weak one, anonymous accounts not removed, test database not cleaned up, listen range too broad, SSL not enabled. This tutorial is for ops engineers, security admins, and developers doing full MySQL hardening in production.

Prerequisites

MySQL 5.7+ or MySQL 8.0+ installed (this tutorial uses MySQL 8.0)
MySQL root or sudo
OS: CentOS 7+/Ubuntu 20.04+
Basic Linux CLI

Principles

MySQL hardening is least privilege + defense in depth. Least privilege: each DB user gets only the rights needed for the task. Defense in depth: controls at OS, network, app, and database layers at once. This tutorial builds the stack across six dimensions: account security, network config, access control, transport encryption, audit logs, and patch management.

Steps

Step 1: Run the Secure Installation Script

MySQL's mysql_secure_installation script handles most baseline security in one pass:

`# Run the secure-install script
sudo mysql_secure_installation

# Follow the prompts:
# 1. Set the root password (enter VALIDATE PASSWORD COMPONENT)
# 2. Remove anonymous users → Y
# 3. Disallow remote root login → Y
# 4. Drop the test database → Y
# 5. Reload privilege tables → Y`

Step 2: Bind Local Listen Address

Default MySQL listens on 0.0.0.0:3306, so any reachable host can try to connect. Restrict bind-address to 127.0.0.1:

`# Edit the MySQL config
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf   # Ubuntu/Debian
# or
sudo vim /etc/my.cnf                           # CentOS/RHEL

# Find or add the following line
bind-address = 127.0.0.1

# Restart the MySQL service
sudo systemctl restart mysql`

If app server and DB are split (not on the same machine), set bind-address to the app server's internal IP. Never use 0.0.0.0.

Step 3: Remove Default Databases and Anonymous Accounts

Clean security risk from the default install:

`# Log in to MySQL
sudo mysql -u root -p

# View the current user list
SELECT User, Host, authentication_string FROM mysql.user;

# Remove anonymous users (accounts with empty User)
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'$(hostname)';

# Drop the test database
DROP DATABASE IF EXISTS test;

# Remove default access privileges for the test database
DELETE FROM mysql.db WHERE Db LIKE 'test%';

# Apply changes
FLUSH PRIVILEGES;`

Step 4: Create Dedicated App Accounts (Least Privilege)

Do not use the root account to connect. Create a dedicated account per app and grant least privilege:

`-- Create an application account (allow connections from localhost only)
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'YourStrongP@ssw0rd!';

-- Grant only the privileges the application needs on its database
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp'@'localhost';

-- If remote connections are needed (from the application server), specify the application server internal IP
CREATE USER 'myapp'@'192.168.1.%' IDENTIFIED BY 'YourStrongP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp'@'192.168.1.%';

FLUSH PRIVILEGES;

-- View account privileges
SHOW GRANTS FOR 'myapp'@'localhost';`

Step 5: Enable Password Policy

MySQL 8.0 ships a password validation component. Enforce complexity:

`-- View the current password policy
SHOW VARIABLES LIKE 'validate_password%';

-- Set password policy level (0=LOW, 1=MEDIUM, 2=HIGH)
SET GLOBAL validate_password.policy = 1;   -- MySQL 8.0
# or
SET GLOBAL validate_password_policy = MEDIUM;  -- MySQL 5.7

-- Set minimum password length
SET GLOBAL validate_password.length = 12;

-- Require digits, mixed-case letters, and special characters
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;`

To make settings persist, add under [mysqld] in my.cnf:

`[mysqld]
validate_password.policy = 1
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1`

Step 6: Enable SSL/TLS Transport Encryption

On untrusted networks, unencrypted MySQL can be sniffed by a MITM. Enable SSL:

`# Generate a self-signed certificate (or use a CA-issued certificate)
sudo mysql_ssl_rsa_setup --uid=mysql

# Enable SSL in my.cnf
[mysqld]
ssl_ca = /var/lib/mysql/ca.pem
ssl_cert = /var/lib/mysql/server-cert.pem
ssl_key = /var/lib/mysql/server-key.pem

# Restart MySQL
sudo systemctl restart mysql

# Verify SSL status
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"`

Force SSL connections: REQUIRE SSL when creating the user:

`ALTER USER 'myapp'@'192.168.1.%' REQUIRE SSL;
FLUSH PRIVILEGES;`

Step 7: Enable Audit Logging

MySQL Enterprise Audit or the general query log can record database operations:

`-- Enable the general query log (development environment)
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

-- [Recommended] Enable the slow query log to record queries that take too long
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;  -- queries exceeding 2 seconds

-- Permanent configuration in my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1`

Step 8: Limit Failed Login Attempts

Install the connection_control plugin to limit failed logins:

`-- Install the plugin
INSTALL PLUGIN connection_control SONAME 'connection_control.so';
INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'connection_control.so';

-- Settings: after 3 consecutive failures, delay each subsequent attempt by 1 second
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;  -- milliseconds

-- View failed login records
SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;`

Verification

After the above, verify each control:

Verification 1: bind-address
netstat -tlnp | grep 3306
# Expected: 127.0.0.1:3306 or an internal IP:3306; must never be 0.0.0.0:3306

Verification 2: anonymous accounts
mysql -u root -p -e "SELECT User,Host FROM mysql.user WHERE User='';"
# Expected: empty (no anonymous users)

Verification 3: password policy
mysql -u root -p -e "SHOW VARIABLES LIKE 'validate_password%';"

Verification 4: SSL connection test
mysql -u myapp -p -h 127.0.0.1 --ssl-mode=REQUIRED -e "STATUS;"
# Expected: SSL shows "Cipher in use is ..."

Verification 5: external connection restriction
# Test from another machine (should be rejected)
mysql -u root -p -h  -e "SELECT 1;"
# Expected: Access denied for user (remote root login is forbidden)

FAQ

Q1: App cannot connect to MySQL after setting bind-address. How to debug?

First confirm the app's DB host is 127.0.0.1. Same machine: set the app host to 127.0.0.1. Split deploy: bind-address should be the app server's internal IP. Also check the firewall allows 3306: `sudo ufw allow from <app_ip> to any port 3306`.

Q2: After enabling password policy, changing passwords always errors with password does not satisfy. How to fix?

Check current policy. New password must include mixed case, digits, special chars, and meet min length. Use a password manager. Temporary: set validate_password.policy to 0 (LOW), change the password, then restore.

Summary

MySQL hardening is ongoing. This tutorial covers eight key steps from baseline to advanced: secure install script, bind-address restriction, cleanup of default accounts, least-privilege accounts, password policy, SSL, audit logs, and login limits. Put these into server bootstrap scripts or config management (Ansible) so new instances inherit the baseline. Check MySQL security advisories regularly (monthly) and patch.