How to secure MySQL server on Linux
How to Secure MySQL Server on Linux
Database security is a critical aspect of system administration that cannot be overlooked. MySQL, being one of the most popular relational database management systems, requires proper security configuration to protect sensitive data from unauthorized access, data breaches, and malicious attacks. This comprehensive guide will walk you through essential steps to secure your MySQL server on Linux systems, covering everything from initial installation security to advanced hardening techniques.
Table of Contents
1. [Prerequisites and Requirements](#prerequisites-and-requirements)
2. [Initial MySQL Security Setup](#initial-mysql-security-setup)
3. [User Account Management](#user-account-management)
4. [Network Security Configuration](#network-security-configuration)
5. [File System Security](#file-system-security)
6. [SSL/TLS Encryption](#ssltls-encryption)
7. [Logging and Monitoring](#logging-and-monitoring)
8. [Firewall Configuration](#firewall-configuration)
9. [Advanced Security Measures](#advanced-security-measures)
10. [Common Issues and Troubleshooting](#common-issues-and-troubleshooting)
11. [Best Practices and Tips](#best-practices-and-tips)
12. [Conclusion](#conclusion)
Prerequisites and Requirements
Before beginning the MySQL security hardening process, ensure you have:
- A Linux server with MySQL installed (Ubuntu 20.04/22.04, CentOS 7/8, or RHEL 7/8)
- Root or sudo privileges on the server
- Basic understanding of Linux command line
- MySQL server running and accessible
- Backup of existing MySQL configuration and databases
System Requirements
- Minimum 2GB RAM for production environments
- At least 10GB free disk space
- Network connectivity for updates and patches
- SSH access to the server
Initial MySQL Security Setup
Running mysql_secure_installation
The first and most crucial step in securing MySQL is running the `mysql_secure_installation` script, which comes with every MySQL installation.
```bash
sudo mysql_secure_installation
```
This script will guide you through several security-related configurations:
1. Set Root Password
```bash
If no root password is set, you'll be prompted to create one
Enter current password for root (enter for none):
Set root password? [Y/n] Y
New password: [Enter strong password]
Re-enter new password: [Confirm password]
```
Best Practice: Use a strong password with at least 12 characters, including uppercase, lowercase, numbers, and special characters.
2. Remove Anonymous Users
```bash
Remove anonymous users? [Y/n] Y
```
Anonymous users can access your database without authentication, posing a significant security risk.
3. Disable Root Remote Login
```bash
Disallow root login remotely? [Y/n] Y
```
This prevents the root account from connecting remotely, reducing attack surface.
4. Remove Test Database
```bash
Remove test database and access to it? [Y/n] Y
```
The test database is accessible to all users and should be removed in production environments.
5. Reload Privilege Tables
```bash
Reload privilege tables now? [Y/n] Y
```
This ensures all changes take effect immediately.
Verifying Initial Security Setup
After running the security script, verify the changes:
```bash
Connect to MySQL
mysql -u root -p
Check for anonymous users (should return empty)
SELECT User, Host FROM mysql.user WHERE User = '';
Verify test database removal
SHOW DATABASES;
Check root user privileges
SELECT User, Host FROM mysql.user WHERE User = 'root';
```
User Account Management
Proper user management is fundamental to MySQL security. Never use the root account for applications or regular database operations.
Creating Application-Specific Users
```sql
-- Create a new user for application use
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password_here';
-- Create user that can connect from specific IP
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'strong_password_here';
-- Create user with password expiration
CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 90 DAY;
```
Implementing Principle of Least Privilege
Grant only the minimum permissions necessary for each user:
```sql
-- Grant specific privileges to specific database
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';
-- Grant read-only access
GRANT SELECT ON reporting.* TO 'readonly_user'@'localhost';
-- Grant specific column access
GRANT SELECT (id, name), UPDATE (name) ON users TO 'limited_user'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;
```
Password Security Policies
Configure MySQL to enforce strong password policies:
```sql
-- Install password validation plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
-- Configure password policy (MySQL 8.0+)
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;
```
Regular User Auditing
Regularly review and audit user accounts:
```sql
-- List all users and their hosts
SELECT User, Host, account_locked, password_expired FROM mysql.user;
-- Check user privileges
SHOW GRANTS FOR 'username'@'hostname';
-- Remove unused accounts
DROP USER 'unused_user'@'localhost';
```
Network Security Configuration
Binding to Specific Interfaces
By default, MySQL might listen on all interfaces. Restrict this for security:
```bash
Edit MySQL configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add or modify the bind-address directive
[mysqld]
bind-address = 127.0.0.1 # For localhost only
bind-address = 192.168.1.10 # For specific IP
Restart MySQL service
sudo systemctl restart mysql
```
Changing Default Port
Change the default MySQL port (3306) to reduce automated attacks:
```bash
Edit configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add or modify port setting
[mysqld]
port = 3307 # Use non-standard port
Restart MySQL
sudo systemctl restart mysql
```
Disabling Remote Root Access
Ensure root cannot connect remotely:
```sql
-- Check current root access
SELECT User, Host FROM mysql.user WHERE User = 'root';
-- Remove remote root access if it exists
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;
```
File System Security
Setting Proper File Permissions
Secure MySQL data directory and configuration files:
```bash
Set ownership of MySQL data directory
sudo chown -R mysql:mysql /var/lib/mysql
Set restrictive permissions
sudo chmod 700 /var/lib/mysql
sudo chmod 600 /var/lib/mysql/*
Secure configuration files
sudo chown root:root /etc/mysql/mysql.conf.d/mysqld.cnf
sudo chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf
```
Securing Log Files
```bash
Set proper permissions for MySQL logs
sudo chown mysql:mysql /var/log/mysql/
sudo chmod 750 /var/log/mysql/
sudo chmod 640 /var/log/mysql/*
```
Disabling Symbolic Links
Prevent symbolic link attacks:
```bash
Add to MySQL configuration
[mysqld]
symbolic-links = 0
```
SSL/TLS Encryption
Generating SSL Certificates
Create SSL certificates for encrypted connections:
```bash
Create SSL directory
sudo mkdir /var/lib/mysql/ssl
cd /var/lib/mysql/ssl
Generate CA private key
sudo openssl genrsa 2048 > ca-key.pem
Generate CA certificate
sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
Generate server private key and certificate request
sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
Remove passphrase from server key
sudo openssl rsa -in server-key.pem -out server-key.pem
Generate server certificate
sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Set proper ownership and permissions
sudo chown mysql:mysql /var/lib/mysql/ssl/*
sudo chmod 600 /var/lib/mysql/ssl/*
```
Configuring SSL in MySQL
```bash
Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add SSL configuration
[mysqld]
ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
ssl-cert=/var/lib/mysql/ssl/server-cert.pem
ssl-key=/var/lib/mysql/ssl/server-key.pem
require_secure_transport=ON
Restart MySQL
sudo systemctl restart mysql
```
Requiring SSL for Users
```sql
-- Require SSL for specific user
ALTER USER 'app_user'@'%' REQUIRE SSL;
-- Create user that requires SSL
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
-- Verify SSL status
SHOW STATUS LIKE 'Ssl_cipher';
```
Logging and Monitoring
Enabling Security-Related Logging
Configure comprehensive logging for security monitoring:
```bash
Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add logging configuration
[mysqld]
General query log
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
Error log
log_error = /var/log/mysql/mysql-error.log
Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
Binary logging for replication and recovery
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
```
Monitoring Failed Login Attempts
Enable connection logging to track failed authentication attempts:
```sql
-- Enable connection logging
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log = 'ON';
-- Query failed connections from general log
SELECT * FROM mysql.general_log
WHERE argument LIKE '%Access denied%'
ORDER BY event_time DESC;
```
Setting Up Log Rotation
Configure log rotation to manage disk space:
```bash
Create logrotate configuration
sudo nano /etc/logrotate.d/mysql-server
Add configuration
/var/log/mysql/*.log {
daily
missingok
rotate 52
compress
delaycompress
notifempty
create 640 mysql mysql
postrotate
mysqladmin flush-logs
endscript
}
```
Firewall Configuration
Using UFW (Ubuntu/Debian)
```bash
Enable UFW
sudo ufw enable
Allow SSH (important - don't lock yourself out)
sudo ufw allow ssh
Allow MySQL only from specific IP
sudo ufw allow from 192.168.1.0/24 to any port 3306
Allow MySQL on custom port
sudo ufw allow from 192.168.1.0/24 to any port 3307
Check firewall status
sudo ufw status verbose
```
Using firewalld (CentOS/RHEL)
```bash
Start and enable firewalld
sudo systemctl start firewalld
sudo systemctl enable firewalld
Create rich rule for MySQL access
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='192.168.1.0/24' port protocol='tcp' port='3306' accept"
Reload firewall
sudo firewall-cmd --reload
List active rules
sudo firewall-cmd --list-all
```
Using iptables
```bash
Allow MySQL from specific network
sudo iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
Drop all other MySQL connections
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
Save iptables rules
sudo iptables-save > /etc/iptables/rules.v4
```
Advanced Security Measures
Implementing Connection Limits
Prevent connection flooding attacks:
```sql
-- Set global connection limits
SET GLOBAL max_connections = 100;
SET GLOBAL max_user_connections = 50;
-- Set per-user connection limits
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password'
WITH MAX_CONNECTIONS_PER_HOUR 100
MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 500;
```
Configuring Query Cache Security
Disable query cache if not needed to prevent information leakage:
```bash
In MySQL configuration
[mysqld]
query_cache_type = 0
query_cache_size = 0
```
Implementing Database-Level Security
```sql
-- Disable LOAD DATA LOCAL INFILE
SET GLOBAL local_infile = 0;
-- Disable file operations
-- Add to configuration file:
secure-file-priv = /var/lib/mysql-files/
```
Setting Up MySQL Enterprise Audit (Commercial)
For MySQL Enterprise users:
```sql
-- Install audit plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
-- Configure audit policy
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';
```
Common Issues and Troubleshooting
Connection Issues After Security Hardening
Problem: Cannot connect to MySQL after security configuration.
Solution:
```bash
Check MySQL status
sudo systemctl status mysql
Check error logs
sudo tail -f /var/log/mysql/mysql-error.log
Verify bind address
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf
Test connection
mysql -u root -p -h 127.0.0.1
```
SSL Connection Problems
Problem: SSL connections failing or not working.
Solution:
```sql
-- Check SSL status
SHOW STATUS LIKE 'Ssl%';
-- Verify SSL configuration
SHOW VARIABLES LIKE '%ssl%';
-- Test SSL connection
mysql -u username -p -h hostname --ssl-mode=REQUIRED
```
Permission Denied Errors
Problem: Users getting permission denied errors.
Solution:
```sql
-- Check user privileges
SHOW GRANTS FOR 'username'@'hostname';
-- Verify user exists
SELECT User, Host FROM mysql.user WHERE User = 'username';
-- Check if account is locked
SELECT User, Host, account_locked FROM mysql.user WHERE User = 'username';
```
Performance Issues After Security Changes
Problem: MySQL performance degraded after security implementation.
Solution:
```sql
-- Check slow query log
-- Review /var/log/mysql/mysql-slow.log
-- Monitor connection usage
SHOW PROCESSLIST;
-- Check system resources
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connection%';
```
Best Practices and Tips
Regular Security Maintenance
1. Update MySQL Regularly
```bash
Ubuntu/Debian
sudo apt update && sudo apt upgrade mysql-server
CentOS/RHEL
sudo yum update mysql-server
```
2. Regular Security Audits
```sql
-- Review user accounts monthly
SELECT User, Host, password_expired, account_locked FROM mysql.user;
-- Check for unused privileges
SELECT * FROM information_schema.user_privileges WHERE GRANTEE LIKE '%unused%';
```
3. Backup Security
```bash
Secure backup with encryption
mysqldump --single-transaction --routines --triggers --all-databases | \
openssl enc -aes-256-cbc -out backup.sql.enc
```
Security Monitoring Scripts
Create automated security monitoring:
```bash
#!/bin/bash
mysql-security-check.sh
Check for failed login attempts
echo "=== Failed Login Attempts ==="
grep "Access denied" /var/log/mysql/mysql-error.log | tail -10
Check for root connections
echo "=== Root Connections ==="
mysql -u root -p -e "SELECT * FROM mysql.general_log WHERE user_host LIKE 'root%' ORDER BY event_time DESC LIMIT 10;"
Check for users without passwords
echo "=== Users Without Passwords ==="
mysql -u root -p -e "SELECT User, Host FROM mysql.user WHERE authentication_string = '';"
```
Configuration Template
Here's a secure MySQL configuration template:
```ini
[mysqld]
Basic security settings
bind-address = 127.0.0.1
port = 3307
symbolic-links = 0
local-infile = 0
SSL Configuration
ssl-ca = /var/lib/mysql/ssl/ca-cert.pem
ssl-cert = /var/lib/mysql/ssl/server-cert.pem
ssl-key = /var/lib/mysql/ssl/server-key.pem
require_secure_transport = ON
Connection limits
max_connections = 100
max_user_connections = 50
Logging
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
log_error = /var/log/mysql/mysql-error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
Binary logging
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
Security enhancements
secure-file-priv = /var/lib/mysql-files/
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
```
Automated Security Checks
Implement regular automated security checks:
```bash
#!/bin/bash
Daily security check script
Check for weak passwords
mysql -u root -p -e "SELECT User, Host FROM mysql.user WHERE authentication_string = '' OR LENGTH(authentication_string) < 20;"
Verify SSL is enabled
mysql -u root -p -e "SHOW STATUS LIKE 'Ssl_cipher';"
Check for anonymous users
mysql -u root -p -e "SELECT User, Host FROM mysql.user WHERE User = '';"
Monitor connection attempts
tail -100 /var/log/mysql/mysql-error.log | grep -i "access denied"
```
Conclusion
Securing a MySQL server on Linux requires a comprehensive approach involving multiple layers of security measures. From initial installation security through advanced hardening techniques, each step plays a crucial role in protecting your database infrastructure.
The key security measures covered in this guide include:
- Running the initial security script and removing default vulnerabilities
- Implementing proper user management with the principle of least privilege
- Configuring network security through interface binding and port changes
- Setting up SSL/TLS encryption for data in transit
- Implementing comprehensive logging and monitoring
- Configuring firewall rules for network-level protection
- Applying advanced security measures like connection limits and audit logging
Next Steps
After implementing these security measures, consider:
1. Regular Security Assessments: Conduct monthly security reviews
2. Disaster Recovery Planning: Ensure secure backup and recovery procedures
3. Compliance Requirements: Implement additional measures for regulatory compliance
4. Advanced Monitoring: Consider enterprise monitoring solutions
5. Team Training: Ensure all team members understand security procedures
Ongoing Maintenance
Security is not a one-time setup but an ongoing process. Regularly:
- Update MySQL and system packages
- Review and audit user accounts and privileges
- Monitor logs for suspicious activities
- Test backup and recovery procedures
- Stay informed about new security threats and patches
By following this comprehensive guide and maintaining good security practices, you'll significantly enhance the security posture of your MySQL server and protect your valuable data from potential threats. Remember that security is an ongoing process that requires regular attention and updates to remain effective against evolving threats.