Quick start: 5-minute MySQL security hardening checklist
Just spun up a fresh MySQL node on Ubuntu or Rocky Linux to connect to your backend? Run through these 3 essential steps before provisioning credentials to your team or promoting it to staging.
1. Run the default security script
sudo mysql_secure_installationThis interactive wizard guides you through the baseline setup. We recommend the following configurations:
- Enable the password strength validation component (VALIDATE PASSWORD) set to MEDIUM or STRONG.
- Remove Anonymous Users created by default during installation.
- Disallow root login remotely.
- Drop the default test database and reload the privilege tables.
2. Bind MySQL exclusively to internal private interfaces
On several Linux distributions, MySQL defaults to listening on all available network interfaces (0.0.0.0). Open the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf on RHEL/Rocky) and update the bind-address directive:
[mysqld]
# Only allow local connections from the same host:
bind-address = 127.0.0.1
# Or if application servers reside within the same internal VPC subnet, bind to your private network interface IP:
# bind-address = 10.0.1.15Save the file and restart the service to apply changes:
sudo systemctl restart mysql3. Restrict port 3306 at the OS firewall layer
Application-level configurations can occasionally be overlooked during infrastructure refactoring. Always enforce a perimeter boundary using the host firewall.
# Always ensure SSH port is allowed before enabling ufw to avoid being locked out!
sudo ufw allow 22/tcp
sudo ufw default deny incoming
# Only allow traffic to 3306 from your App Server subnet (e.g., 10.0.2.0/24)
sudo ufw allow from 10.0.2.0/24 to any port 3306 proto tcp
sudo ufw reloadIn-depth: 3 defensive layers for production databases
Once initial surface cleanup is complete, implement deeper defensive controls. The core premise: even if an attacker compromises a web tier, they remain trapped and isolated from your database assets.
Layer 1: Lock down the engine and prevent data exfiltration
Exploiting SQL Injection (SQLi) vulnerabilities to read sensitive OS files like /etc/passwd or drop web shells via INTO OUTFILE represents a classic attack vector. You can neutralize this exploit chain directly within MySQL configurations.
Append the following directives under the [mysqld] section in your config file:
[mysqld]
# Disallow clients from pushing local files to the database
local_infile = 0
# Restrict import/export operations strictly to this path, or leave empty to disable completely
secure_file_priv = /var/lib/mysql-files
# Disable symbolic links to prevent reading files outside datadir
skip_symbolic_links = 1When secure_file_priv points to a restricted directory, attackers attempting SELECT ... INTO OUTFILE cannot write web shells into public directories such as /var/www/html to execute remote commands.
Layer 2: Segregate accounts and enforce the principle of least privilege
The most frequent security pitfall is assigning GRANT ALL PRIVILEGES directly to backend users. Under this setup, a single SQLi vulnerability in an API endpoint allows attackers to DROP entire schemas in seconds.
The production standard is to segregate database access into at least two distinct roles:
-- 1. Dedicated runtime account for backend services (Node.js, Spring, Laravel, etc.)
CREATE USER 'app_runtime'@'10.0.2.%' IDENTIFIED BY 'Tr0ng-P@ss#Prod2026';
-- Grant Data Manipulation Language (DML) permissions only. Strictly forbid DROP, ALTER, CREATE, or TRUNCATE
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'app_runtime'@'10.0.2.%';
-- 2. Dedicated account reserved for CI/CD pipelines to run schema migrations
CREATE USER 'app_migrator'@'10.0.2.50' IDENTIFIED BY 'Migrate_Secr3t#2026';
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON production_db.* TO 'app_migrator'@'10.0.2.50';
FLUSH PRIVILEGES;This isolation model dramatically reduces your blast radius. The runtime application user can only read and write data, lacking privileges to modify underlying schema structures.
Layer 3: Enforce resource quotas per database user
A rogue web scraper or an unbounded query loop leaking database connections can rapidly exhaust MySQL host CPU and thread limits. Throttle usage thresholds at the user level:
ALTER USER 'app_runtime'@'10.0.2.%' WITH
MAX_QUERIES_PER_HOUR 500000
MAX_UPDATES_PER_HOUR 100000
MAX_CONNECTIONS_PER_HOUR 10000
MAX_USER_CONNECTIONS 150;The MAX_USER_CONNECTIONS = 150 ceiling should be calculated against your global max_connections setting (typically 500–1000 depending on host RAM) and backend application connection pool sizing.
Audit logging and query history management
Slow queries degrade performance, but unauthorized queries under the cover of night lead to data breaches. Keeping robust access audit trails is critical for post-incident forensics.
1. Monitor suspicious login activity
On MySQL Community Edition, the quickest way to capture brute-force authentication attempts is to increase error logging verbosity:
[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 3Verbosity level 3 logs all system warnings, authentication errors, source IP addresses, and specific access denied reasons directly into your error log.
2. Disable MySQL CLI history recording
By default, interactive CLI operations record commands into ~/.mysql_history. Account creation statements containing cleartext passwords will persist there completely exposed.
Mitigate this risk by symlinking the history file directly to /dev/null:
# Remove the existing history file and redirect it permanently to /dev/null
rm -f ~/.mysql_history
ln -s /dev/null ~/.mysql_history
# Configure system-wide environment variable
echo "export MYSQL_HISTFILE=/dev/null" | sudo tee -a /etc/profile.d/mysql_history.sh
source /etc/profile.d/mysql_history.shOperational best practices
Keep these fundamental checkpoints in mind when managing database infrastructure in production:
- Never grant SUPER or FILE privileges to non-admin accounts: These capabilities permit users to modify global runtime variables and read/write host filesystem resources. Reserve them exclusively for local
root. - Enforce in-transit TLS encryption: Even inside isolated VPC networks, unencrypted traffic remains vulnerable to packet sniffing if a neighboring host is compromised. Add
require_secure_transport = ONunder the[mysqld]section to reject non-SSL/TLS connections. - Configure automated log rotation (logrotate): Verify your log rotation policy under
/etc/logrotate.d/mysql-server. Without periodic compression and cleanup, runaway error logs will saturate your root partition (/), triggering abrupt emergency shutdowns due to lack of disk space.
