Securing a MySQL installation is a crucial step in safeguarding your data and the integrity of your database. This guide covers security practices for modern MySQL deployments with current best practices for DevOps environments.
mysql_secure_installation ScriptThis script comes with the MySQL installation and helps secure the database server with several prompts to configure security-related settings.
sudo mysql_secure_installation
During the process, you’ll be prompted to:
After running the secure installation script, apply additional security measures:
-- Connect to MySQL as root
mysql -u root -p
-- Remove any remaining anonymous users (double-check)
DELETE FROM mysql.user WHERE User='';
-- Remove test database if it still exists
DROP DATABASE IF EXISTS test;
-- Flush privileges to apply changes
FLUSH PRIVILEGES;
-- Create a dedicated administrative user instead of using root for daily operations
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'strong_admin_password_here';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
-- For remote administrative access (if absolutely necessary), use specific IPs
CREATE USER 'admin_user'@'specific_ip_address' IDENTIFIED BY 'strong_admin_password_here';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'specific_ip_address' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Modern MySQL installations should use the caching_sha2_password authentication plugin:
-- Check current authentication plugin
SELECT user, host, plugin FROM mysql.user;
-- Change root user to use caching_sha2_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_new_strong_password';
FLUSH PRIVILEGES;
Configure password validation for stronger security:
-- Install the validate_password plugin if not already installed
INSTALL COMPONENT 'file://component_validate_password';
-- Set password policy in MySQL configuration
-- Add to /etc/mysql/mysql.conf.d/mysqld.cnf:
[mysqld]
validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1
Restrict MySQL to listen only on specific interfaces:
# In /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# For local-only access
bind-address = 127.0.0.1
# For specific network interface (replace with your internal IP)
bind-address = 10.0.0.10
# Avoid binding to all interfaces unless absolutely necessary
# bind-address = 0.0.0.0
Configure system firewall to restrict access:
# Using UFW (Ubuntu)
sudo ufw allow from 10.0.0.0/8 to any port 3306
sudo ufw allow from 192.168.1.0/24 to any port 3306
# Using firewalld (RHEL-family)
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='10.0.0.0/8' port protocol='tcp' port='3306' accept"
sudo firewall-cmd --reload
# Using iptables
sudo iptables -A INPUT -p tcp -s 10.0.0.0/8 --dport 3306 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
Enable SSL/TLS for encrypted connections:
# In /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON
Generate SSL certificates:
# Create SSL directory
sudo mkdir -p /etc/mysql/ssl
sudo chown mysql:mysql /etc/mysql/ssl
# Generate CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
# Generate server certificate
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Generate client certificate
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
# Set proper permissions
sudo chown mysql:mysql ca.pem server-*.pem client-*.pem
sudo chmod 600 ca.pem server-*.pem client-*.pem
Create users with minimal required permissions:
-- Application user with specific database access
CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'strong_application_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON application_db.* TO 'app_user'@'10.0.0.%';
FLUSH PRIVILEGES;
-- Read-only user for reporting
CREATE USER 'report_user'@'10.0.0.%' IDENTIFIED BY 'strong_reporting_password';
GRANT SELECT ON application_db.* TO 'report_user'@'10.0.0.%';
FLUSH PRIVILEGES;
-- Backup user with specific privileges
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_backup_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
-- Monitoring user with minimal privileges
CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'strong_monitoring_password';
GRANT SELECT ON performance_schema.* TO 'monitor_user'@'%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';
FLUSH PRIVILEGES;
Manage account security with expiration and locking:
-- Create user with password expiration
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'temporary_password' PASSWORD EXPIRE INTERVAL 90 DAY;
-- Lock an account temporarily
ALTER USER 'user_name'@'host' ACCOUNT LOCK;
-- Unlock an account
ALTER USER 'user_name'@'host' ACCOUNT UNLOCK;
-- Force password change on next login
ALTER USER 'user_name'@'host' PASSWORD EXPIRE;
Protect MySQL configuration files:
# Set proper permissions on configuration files
sudo chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf
sudo chown root:root /etc/mysql/mysql.conf.d/mysqld.cnf
# Protect data directory
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
Add security-focused settings to configuration:
# In /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Disable local file loading
local-infile = 0
# Disable symbolic links (on Unix systems)
symbolic-links = 0
# Disable dangerous functions
set-variable = local-infile=0
# Limit file operations
secure-file-priv = /var/lib/mysql-files
# Disable loading of local files
local-infile = 0
Install and configure audit plugin:
-- Install audit plugin (if available in your version)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
-- Or configure general log for auditing (use cautiously in production)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
Regularly check logs for suspicious activity:
# Check MySQL error log
sudo tail -f /var/log/mysql/error.log
# Check slow query log for potential attacks
sudo tail -f /var/log/mysql/mysql-slow.log
# Monitor connection attempts
mysql -u root -p -e "SELECT * FROM performance_schema.events_sessions_current WHERE processlist_user != 'root';"
Apply security patches regularly:
# On Debian/Ubuntu
sudo apt update && sudo apt upgrade mysql-server
# On RHEL-family
sudo dnf update mysql-server
# Check current version
mysql --version
Perform periodic security reviews:
-- Check for accounts with excessive privileges
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';
-- Check for accounts with empty passwords
SELECT User, Host FROM mysql.user WHERE authentication_string = '';
-- Review all users
SELECT User, Host, authentication_string IS NULL AS has_password, password_last_changed FROM mysql.user;
mysql_secure_installation after installationIf you suspect a security breach: