Securing MySQL — Hardening Checklist

Securing MySQL — Complete Hardening Checklist

Initial Setup — mysql_secure_installation

-- Run immediately after installation
sudo mysql_secure_installation

-- This will:
-- 1. Set root password
-- 2. Remove anonymous users
-- 3. Disable remote root login
-- 4. Remove test database
-- 5. Reload privilege tables

User & Privilege Hardening

-- Principle of least privilege: app users get ONLY what they need
CREATE USER 'webapp'@'127.0.0.1' IDENTIFIED BY 'StrongPass!2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'webapp'@'127.0.0.1';
FLUSH PRIVILEGES;

-- Never grant these to app users:
-- FILE, SUPER, PROCESS, RELOAD, SHUTDOWN, CREATE USER, GRANT OPTION

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

-- Remove remote root access
DELETE FROM mysql.user WHERE User='root' AND Host != 'localhost';
FLUSH PRIVILEGES;

-- Check remaining users
SELECT user, host, authentication_string, plugin FROM mysql.user;

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;

Network Hardening

# /etc/mysql/mysql.conf.d/mysqld.cnf

# Bind only to localhost (prevents external connections)
bind-address = 127.0.0.1

# Disable networking entirely if only local access needed
skip-networking

# Use Unix socket instead of TCP
# socket = /var/run/mysqld/mysqld.sock

# If remote access IS needed: use firewall instead
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw deny 3306

File System Hardening

# Restrict file operations to a safe directory (or disable completely)
# In /etc/mysql/mysql.conf.d/mysqld.cnf:
secure_file_priv = /var/lib/mysql-files   # only allow in this directory
# OR
secure_file_priv = NULL                    # completely disable LOAD_FILE / OUTFILE

# Disable LOAD DATA LOCAL INFILE
local_infile = OFF

# Run MySQL as non-root dedicated user (default: mysql)
user = mysql

# Verify
SHOW VARIABLES LIKE 'secure_file_priv';
SHOW VARIABLES LIKE 'local_infile';

Authentication Hardening

# Use strong authentication plugin (MySQL 8.0+)
# In mysqld.cnf:
default_authentication_plugin = caching_sha2_password

# Enforce password policy
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.policy = STRONG;
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 password expiry
ALTER USER 'webapp'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

# Lock accounts after failed attempts (MySQL 8.0.19+)
ALTER USER 'webapp'@'localhost' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Logging & Monitoring

# Enable general query log (audit trail — careful, impacts performance)
general_log = ON
general_log_file = /var/log/mysql/mysql.log

# Enable slow query log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Enable error log
log_error = /var/log/mysql/error.log

# Audit plugin (MySQL Enterprise or MariaDB audit)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

# Monitor for suspicious activity:
-- Multiple failed auth attempts
-- Queries using LOAD_FILE(), INTO OUTFILE
-- Queries to information_schema by app users
-- SLEEP() in queries (blind SQLi indicator)

Connection Security

# Enforce SSL/TLS for all connections
# In mysqld.cnf:
require_secure_transport = ON
ssl_ca = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key = /etc/mysql/certs/server-key.pem

# Create user that requires SSL
CREATE USER 'secure_app'@'%' IDENTIFIED BY 'pass' REQUIRE SSL;

# Verify SSL is working
SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE 'Ssl_cipher';

Hardening Checklist

-- Run this audit script to check your configuration:

SELECT 'Empty password users' AS check_name, count(*) AS result
FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL
UNION
SELECT 'Anonymous users', count(*) FROM mysql.user WHERE user = ''
UNION
SELECT 'Remote root access', count(*) FROM mysql.user WHERE user='root' AND host != 'localhost'
UNION
SELECT 'FILE privilege users', count(*) FROM mysql.user WHERE file_priv='Y' AND user != 'root'
UNION
SELECT 'Insecure file priv', IF(@secure_file_priv='','INSECURE','OK');

-- Also check:
SHOW VARIABLES LIKE 'local_infile';      -- should be OFF
SHOW VARIABLES LIKE 'bind_address';      -- should NOT be 0.0.0.0
SELECT @version;                        -- should be latest patched