MySQL/MariaDB Security Configuration: Complete Hardening Guide

Introduction

Database security is paramount in today's threat landscape where data breaches and cyber attacks are increasingly common. MySQL and MariaDB, while powerful and widely-used database systems, require proper security configuration to protect sensitive data from unauthorized access, SQL injection attacks, and other security threats.

A compromised database can lead to catastrophic consequences including data theft, financial losses, regulatory penalties, and severe reputation damage. According to industry reports, the average cost of a data breach exceeds $4 million, making database security not just a technical requirement but a business imperative.

This comprehensive guide provides detailed instructions for hardening MySQL and MariaDB installations, implementing defense-in-depth strategies, and following industry best practices for database security. Whether you're managing a development environment or a production system handling sensitive customer data, this guide will help you establish robust security measures.

Security Threats to Database Systems

Common threats include:

SQL Injection: Malicious SQL code injection through application vulnerabilities
Brute Force Attacks: Automated password guessing attempts
Privilege Escalation: Exploiting vulnerabilities to gain elevated access
Data Exposure: Unauthorized data access through misconfiguration
Man-in-the-Middle Attacks: Interception of unencrypted database traffic
Insider Threats: Malicious or negligent actions by authorized users

Prerequisites

Before proceeding with security configuration:

MySQL or MariaDB already installed and running
Root or sudo access to the server
Root database password available
Basic understanding of SQL commands
Backup of existing database configuration
Understanding of your application's database requirements

Creating Configuration Backup

# Backup MySQL configuration
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup

# Backup MariaDB configuration
sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.backup

# Backup all MySQL directories
sudo cp -r /etc/mysql /etc/mysql.backup.$(date +%Y%m%d)

# Backup databases before security changes
sudo mysqldump --all-databases > /backup/all_databases_pre_security_$(date +%Y%m%d).sql

Initial Security Hardening

Running mysql_secure_installation

The mysql_secure_installation script provides essential security improvements:

# Run security script
sudo mysql_secure_installation

Script prompts and recommended responses:

Would you like to setup VALIDATE PASSWORD component? [Y/n]
Response: Y (Yes - enables password strength validation)

There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
Response: 2 (STRONG - for production environments)

Change the password for root? [Y/n]
Response: Y (if you want to update root password)

Remove anonymous users? [Y/n]
Response: Y (anonymous users are security risks)

Disallow root login remotely? [Y/n]
Response: Y (root should only login locally)

Remove test database and access to it? [Y/n]
Response: Y (test database is unnecessary in production)

Reload privilege tables now? [Y/n]
Response: Y (apply changes immediately)

Verifying Security Script Results

# Login to MySQL
sudo mysql -u root -p

# Check for anonymous users (should return empty)
SELECT User, Host FROM mysql.user WHERE User = '';

# Check for test database (should not exist)
SHOW DATABASES LIKE 'test';

# Check root user host restrictions
SELECT User, Host FROM mysql.user WHERE User = 'root';

# Exit MySQL
EXIT;

User Management and Access Control

Understanding MySQL Privilege System

MySQL uses a hierarchical privilege system:

Global privileges: Apply to all databases
Database privileges: Apply to specific database
Table privileges: Apply to specific tables
Column privileges: Apply to specific columns
Routine privileges: Apply to stored procedures and functions

Creating Users with Least Privilege

Never use root for applications. Create dedicated users with minimal required privileges:

-- Connect as root
sudo mysql -u root -p

-- Create user for specific database
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd123!';

-- Create database
CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Grant specific privileges only
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'app_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify user privileges
SHOW GRANTS FOR 'app_user'@'localhost';

Privilege Guidelines:

-- Web application (typical requirements)
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'webapp_user'@'localhost';

-- Read-only reporting user
GRANT SELECT ON database.* TO 'report_user'@'localhost';

-- Backup user (needs specific privileges)
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';

-- Developer user (limited to development database)
GRANT ALL PRIVILEGES ON dev_database.* TO 'developer'@'localhost';

-- Monitoring user (read-only system access)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'localhost';

Host-Based Access Restrictions

Restrict users to specific hosts or IP addresses:

-- User can only connect from localhost
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'password';

-- User can only connect from specific IP
CREATE USER 'remote_user'@'192.168.1.50' IDENTIFIED BY 'password';

-- User can connect from specific subnet
CREATE USER 'subnet_user'@'192.168.1.%' IDENTIFIED BY 'password';

-- User can connect from specific domain
CREATE USER 'domain_user'@'%.example.com' IDENTIFIED BY 'password';

-- Avoid using % wildcard for production
-- BAD: CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Implementing Password Policies

For MySQL 8.0+:

-- Install password validation component
INSTALL COMPONENT 'file://component_validate_password';

-- Configure password policy
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 GLOBAL validate_password.check_user_name = ON;

-- View current policy
SHOW VARIABLES LIKE 'validate_password%';

-- Make settings permanent in my.cnf
[mysqld]
validate_password.policy=STRONG
validate_password.length=12
validate_password.mixed_case_count=1
validate_password.number_count=1
validate_password.special_char_count=1

For MariaDB:

-- Install password validation plugin
INSTALL SONAME 'simple_password_check';

-- Configure password requirements
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_digits = 1;
SET GLOBAL simple_password_check_letters_same_case = 1;
SET GLOBAL simple_password_check_other_characters = 1;

-- Add to configuration file
[mariadb]
plugin_load_add = simple_password_check
simple_password_check_minimal_length = 12
simple_password_check_digits = 1
simple_password_check_letters_same_case = 1
simple_password_check_other_characters = 1

Password Expiration and Rotation

Implement password expiration policies:

-- Set global password expiration (days)
SET GLOBAL default_password_lifetime = 90;

-- Set password expiration for specific user
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Require immediate password change
ALTER USER 'user'@'localhost' PASSWORD EXPIRE;

-- Never expire password (use sparingly)
ALTER USER 'service_account'@'localhost' PASSWORD EXPIRE NEVER;

-- View password expiration status
SELECT User, Host, password_expired, password_lifetime, password_last_changed
FROM mysql.user;

Account Locking

Lock and unlock user accounts:

-- Lock user account
ALTER USER 'suspicious_user'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

-- Create user with locked account
CREATE USER 'inactive_user'@'localhost' IDENTIFIED BY 'password' ACCOUNT LOCK;

-- View locked accounts
SELECT User, Host, account_locked FROM mysql.user WHERE account_locked = 'Y';

Failed Login Tracking (MySQL 8.0.19+)

-- Configure failed login tracking
CREATE USER 'user'@'localhost'
IDENTIFIED BY 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;  -- Lock for 2 days

-- Or use UNBOUNDED for permanent lock
ALTER USER 'user'@'localhost'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME UNBOUNDED;

-- View failed login configuration
SELECT User, Host, User_attributes
FROM mysql.user
WHERE JSON_EXTRACT(User_attributes, '$.Password_locking') IS NOT NULL;

Network Security

Binding to Specific Interfaces

Configure MySQL to listen only on specific network interfaces:

# Edit configuration file
# /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian)
# /etc/my.cnf (CentOS/Rocky)

[mysqld]
# Listen only on localhost (most secure)
bind-address = 127.0.0.1

# Listen on specific IP
bind-address = 192.168.1.100

# Listen on multiple IPs (MySQL 8.0.13+)
bind-address = 127.0.0.1,192.168.1.100

# For MariaDB 10.3.3+
bind-address = 0.0.0.0  # Listen on all interfaces
# Then use firewall to restrict access

Restart MySQL:

sudo systemctl restart mysql

# Verify binding
sudo ss -tulpn | grep 3306
sudo netstat -tulpn | grep 3306

Disabling Name Resolution

Skip DNS hostname lookups to prevent DNS spoofing:

[mysqld]
skip-name-resolve

This forces MySQL to use IP addresses only. Update grants accordingly:

-- Before: grant to hostname
GRANT ALL ON database.* TO 'user'@'server.example.com';

-- After: grant to IP
GRANT ALL ON database.* TO 'user'@'192.168.1.100';

-- Revoke hostname-based grants
REVOKE ALL ON database.* FROM 'user'@'server.example.com';
DROP USER 'user'@'server.example.com';

Restart MySQL and verify:

sudo systemctl restart mysql

# Check configuration
mysql -u root -p -e "SHOW VARIABLES LIKE 'skip_name_resolve';"

Changing Default Port

Change MySQL port to reduce automated attack surface:

[mysqld]
port = 33306  # Custom port instead of 3306

Update firewall rules:

# UFW
sudo ufw delete allow 3306/tcp
sudo ufw allow 33306/tcp

# firewalld
sudo firewall-cmd --permanent --remove-service=mysql
sudo firewall-cmd --permanent --add-port=33306/tcp
sudo firewall-cmd --reload

Restart MySQL and verify:

sudo systemctl restart mysql

# Connect with custom port
mysql -u root -p -P 33306

# Verify port
sudo ss -tulpn | grep mysql

SSL/TLS Encryption

Generating SSL Certificates

Create SSL certificates for encrypted connections:

# Create directory for certificates
sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# Generate CA key and certificate
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 \
    -key ca-key.pem \
    -out ca-cert.pem \
    -subj "/C=US/ST=State/L=City/O=Organization/CN=MySQL-CA"

# Generate server certificate request and key
sudo openssl req -newkey rsa:2048 -days 3650 -nodes \
    -keyout server-key.pem \
    -out server-req.pem \
    -subj "/C=US/ST=State/L=City/O=Organization/CN=MySQL-Server"

# Process server RSA key
sudo openssl rsa -in server-key.pem -out server-key.pem

# Sign server certificate
sudo openssl x509 -req -in server-req.pem -days 3650 \
    -CA ca-cert.pem \
    -CAkey ca-key.pem \
    -set_serial 01 \
    -out server-cert.pem

# Generate client certificate request and key
sudo openssl req -newkey rsa:2048 -days 3650 -nodes \
    -keyout client-key.pem \
    -out client-req.pem \
    -subj "/C=US/ST=State/L=City/O=Organization/CN=MySQL-Client"

# Process client RSA key
sudo openssl rsa -in client-key.pem -out client-key.pem

# Sign client certificate
sudo openssl x509 -req -in client-req.pem -days 3650 \
    -CA ca-cert.pem \
    -CAkey ca-key.pem \
    -set_serial 02 \
    -out client-cert.pem

# Verify certificates
sudo openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

# Set proper permissions
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem

Configuring MySQL for SSL

Edit MySQL configuration:

[mysqld]
# SSL Configuration
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# Require SSL for all connections (optional but recommended)
require_secure_transport=ON

# TLS versions (MySQL 8.0+)
tls_version=TLSv1.2,TLSv1.3

# Cipher configuration
ssl-cipher=ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384

Restart MySQL:

sudo systemctl restart mysql

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

# Check SSL status
mysql -u root -p -e "STATUS" | grep SSL

Requiring SSL for Users

Force specific users to use SSL:

-- Require SSL for existing user
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Create user with SSL requirement
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- Require specific SSL cipher
ALTER USER 'app_user'@'%' REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Require valid client certificate
ALTER USER 'app_user'@'%' REQUIRE X509;

-- Require specific certificate subject
ALTER USER 'app_user'@'%' REQUIRE SUBJECT '/C=US/ST=State/CN=MySQL-Client';

-- Require specific certificate issuer
ALTER USER 'app_user'@'%' REQUIRE ISSUER '/C=US/ST=State/CN=MySQL-CA';

-- View user SSL requirements
SELECT User, Host, ssl_type, ssl_cipher, x509_issuer, x509_subject
FROM mysql.user
WHERE User = 'app_user';

-- Remove SSL requirement
ALTER USER 'user'@'host' REQUIRE NONE;

Connecting with SSL

# Connect with SSL
mysql -u app_user -p --ssl-mode=REQUIRED

# Connect with client certificate
mysql -u app_user -p \
    --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
    --ssl-cert=/etc/mysql/ssl/client-cert.pem \
    --ssl-key=/etc/mysql/ssl/client-key.pem

# Verify SSL connection
mysql> \s
# Look for: SSL: Cipher in use

# Or query
mysql> SHOW STATUS LIKE 'Ssl_cipher';

File System Security

Securing Configuration Files

Protect MySQL configuration files:

# Set secure permissions on config files
sudo chmod 644 /etc/mysql/my.cnf
sudo chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf
sudo chown root:root /etc/mysql/my.cnf

# If storing passwords in config file (not recommended)
sudo chmod 600 /etc/mysql/my.cnf

Securing Data Directory

Protect MySQL data directory:

# Set proper ownership
sudo chown -R mysql:mysql /var/lib/mysql

# Set secure permissions
sudo chmod 700 /var/lib/mysql
sudo chmod 660 /var/lib/mysql/*

# Verify permissions
ls -la /var/lib/mysql/

Protecting Binary Logs

Secure MySQL binary logs:

# Set ownership
sudo chown mysql:mysql /var/lib/mysql/mysql-bin.*

# Set permissions
sudo chmod 660 /var/lib/mysql/mysql-bin.*

# Configure automatic purging in my.cnf
[mysqld]
expire_logs_days = 7
max_binlog_size = 100M

Disabling LOCAL INFILE

Prevent loading data from local files:

[mysqld]
local-infile=0

Verify:

SHOW VARIABLES LIKE 'local_infile';

Firewall Configuration

UFW (Ubuntu/Debian)

# Allow MySQL from specific IP
sudo ufw allow from 192.168.1.50 to any port 3306

# Allow from specific subnet
sudo ufw allow from 192.168.1.0/24 to any port 3306

# Allow from multiple IPs
sudo ufw allow from 192.168.1.50 to any port 3306
sudo ufw allow from 192.168.1.51 to any port 3306

# Delete rule if needed
sudo ufw status numbered
sudo ufw delete [rule_number]

# Enable firewall
sudo ufw enable

# Check status
sudo ufw status verbose

firewalld (CentOS/Rocky Linux)

# Add rich rule for specific IP
sudo firewall-cmd --permanent --add-rich-rule='
rule family="ipv4"
source address="192.168.1.50"
port protocol="tcp" port="3306" accept'

# Add rich rule for subnet
sudo firewall-cmd --permanent --add-rich-rule='
rule family="ipv4"
source address="192.168.1.0/24"
port protocol="tcp" port="3306" accept'

# Remove default MySQL service
sudo firewall-cmd --permanent --remove-service=mysql

# Reload firewall
sudo firewall-cmd --reload

# Verify rules
sudo firewall-cmd --list-all
sudo firewall-cmd --list-rich-rules

iptables

# Allow from specific IP
sudo iptables -A INPUT -p tcp -s 192.168.1.50 --dport 3306 -j ACCEPT

# Allow from subnet
sudo iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT

# Drop all other MySQL traffic
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

# Save rules (Ubuntu/Debian)
sudo netfilter-persistent save

# Save rules (CentOS/Rocky)
sudo service iptables save

# View rules
sudo iptables -L -n -v

Security Logging and Monitoring

Enabling General Query Log

Log all queries for security auditing:

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

View log:

sudo tail -f /var/log/mysql/general.log

Warning: General log can grow very large. Use only for troubleshooting or short-term auditing.

Error Logging

Configure comprehensive error logging:

[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 3  # 1=errors, 2=+warnings, 3=+notes

Binary Logging for Auditing

Enable binary logging:

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1  # Sync to disk for reliability

Audit Plugin (MySQL Enterprise / MariaDB)

MariaDB Audit Plugin:

# Install audit plugin
sudo mysql -u root -p <<EOF
INSTALL SONAME 'server_audit';
EOF

Configure in my.cnf:

[mariadb]
plugin_load_add = server_audit
server_audit_logging = ON
server_audit_events = CONNECT,QUERY,TABLE
server_audit_output_type = FILE
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 1000000
server_audit_file_rotations = 9

View audit settings:

SHOW VARIABLES LIKE 'server_audit%';

Monitoring Failed Login Attempts

Check authentication failures:

# Check error log for authentication failures
sudo grep "Access denied" /var/log/mysql/error.log

# Count failed attempts by user
sudo grep "Access denied for user" /var/log/mysql/error.log | awk '{print $NF}' | sort | uniq -c | sort -rn

# Check recent failed attempts
sudo tail -100 /var/log/mysql/error.log | grep "Access denied"

Security Best Practices

Removing Anonymous Users

-- Check for anonymous users
SELECT User, Host FROM mysql.user WHERE User = '';

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

Removing Test Database

-- Drop test database
DROP DATABASE IF EXISTS test;

-- Remove test database privileges
DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;

Disabling Symbolic Links

Prevent security issues with symbolic links:

[mysqld]
symbolic-links=0

Securing Stored Procedures

Limit stored procedure creation:

-- Revoke CREATE ROUTINE from regular users
REVOKE CREATE ROUTINE ON *.* FROM 'app_user'@'localhost';

-- Only grant to specific users who need it
GRANT CREATE ROUTINE ON myapp_db.* TO 'developer'@'localhost';

Disabling SHOW DATABASES

Prevent users from seeing database list:

-- Remove SHOW DATABASES privilege
REVOKE SHOW DATABASES ON *.* FROM 'app_user'@'localhost';

-- User can still access granted databases
GRANT ALL ON specific_db.* TO 'app_user'@'localhost';

File Privilege Restrictions

Remove FILE privilege from users:

-- Check users with FILE privilege
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';

-- Revoke FILE privilege
REVOKE FILE ON *.* FROM 'user'@'host';

-- Only grant to backup users if absolutely necessary
GRANT FILE ON *.* TO 'backup_user'@'localhost';

Limiting Database Resources

Prevent resource exhaustion:

-- Create user with resource limits
CREATE USER 'limited_user'@'localhost' IDENTIFIED BY 'password'
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 500
     MAX_CONNECTIONS_PER_HOUR 100
     MAX_USER_CONNECTIONS 5;

-- Modify existing user
ALTER USER 'app_user'@'localhost'
WITH MAX_QUERIES_PER_HOUR 10000
     MAX_CONNECTIONS_PER_HOUR 500
     MAX_USER_CONNECTIONS 10;

-- View resource limits
SELECT User, Host, max_questions, max_updates, max_connections, max_user_connections
FROM mysql.user
WHERE User = 'limited_user';

Security Auditing

Regular Security Audits

Create audit script:

sudo nano /usr/local/bin/mysql-security-audit.sh

Add content:

#!/bin/bash

REPORT_FILE="/var/log/mysql/security_audit_$(date +%Y%m%d).txt"

echo "MySQL Security Audit - $(date)" > $REPORT_FILE
echo "======================================" >> $REPORT_FILE

# Check for users without passwords
echo -e "\n[CRITICAL] Users without passwords:" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;" >> $REPORT_FILE

# Check for users with wildcard host
echo -e "\n[WARNING] Users with wildcard host (%):" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host FROM mysql.user WHERE Host = '%';" >> $REPORT_FILE

# Check for anonymous users
echo -e "\n[CRITICAL] Anonymous users:" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host FROM mysql.user WHERE User = '';" >> $REPORT_FILE

# Check for users with FILE privilege
echo -e "\n[WARNING] Users with FILE privilege:" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';" >> $REPORT_FILE

# Check for users with SUPER privilege
echo -e "\n[INFO] Users with SUPER privilege:" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';" >> $REPORT_FILE

# Check SSL configuration
echo -e "\n[INFO] SSL Status:" >> $REPORT_FILE
mysql -u root -p[password] -e "SHOW VARIABLES LIKE '%ssl%';" >> $REPORT_FILE

# Check for test database
echo -e "\n[WARNING] Test database exists:" >> $REPORT_FILE
mysql -u root -p[password] -e "SHOW DATABASES LIKE 'test';" >> $REPORT_FILE

# Check authentication plugin
echo -e "\n[INFO] Authentication plugins in use:" >> $REPORT_FILE
mysql -u root -p[password] -e "SELECT User, Host, plugin FROM mysql.user;" >> $REPORT_FILE

echo -e "\nAudit completed. Report saved to: $REPORT_FILE"

Penetration Testing

Use mysql-audit for security assessment:

# Install mysql-audit
git clone https://github.com/habitissimo/mysql-audit.git
cd mysql-audit

# Run audit
./mysql-audit -u root -p password -h localhost

# Review recommendations

Incident Response

Detecting Suspicious Activity

-- Check current connections
SHOW PROCESSLIST;

-- View user connection history
SELECT User, Host, TIME, STATE, INFO
FROM information_schema.PROCESSLIST;

-- Check failed login attempts (from error log)

sudo grep "Access denied" /var/log/mysql/error.log | tail -20

Responding to Compromise

If database compromise is suspected:

-- Immediately lock suspicious accounts
ALTER USER 'suspicious_user'@'%' ACCOUNT LOCK;

-- Change all passwords
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_strong_password';

-- Review and revoke excessive privileges
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';
GRANT SELECT, INSERT, UPDATE ON specific_db.* TO 'user'@'host';

-- Kill suspicious connections
KILL CONNECTION process_id;

-- Review grants for all users
SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';

Compliance and Standards

PCI-DSS Compliance

For payment card data:

Use strong cryptography (TLS 1.2+)
Implement strong access control
Regular security audits
Log and monitor all access
Change default passwords
Disable unnecessary services

GDPR Compliance

For personal data:

Implement encryption at rest and in transit
Access logging and auditing
Data anonymization capabilities
Right to erasure procedures
Data breach notification procedures

Conclusion

Database security is an ongoing process requiring vigilance, regular audits, and staying current with security best practices and patches. This guide has provided comprehensive security hardening measures for MySQL and MariaDB installations.

Security Checklist

✓ Run mysql_secure_installation
✓ Implement strong password policies
✓ Use least privilege principle for all users
✓ Enable SSL/TLS encryption
✓ Configure firewall rules
✓ Disable remote root access
✓ Remove anonymous users and test database
✓ Enable security logging and monitoring
✓ Regular security audits
✓ Keep MySQL/MariaDB updated
✓ Secure file system permissions
✓ Document security procedures

Best Practices Summary

Never use root for applications - Create dedicated users
Always use SSL/TLS for network connections
Implement defense in depth - Multiple security layers
Monitor continuously - Regular log review and alerting
Audit regularly - Quarterly security assessments
Keep updated - Apply security patches promptly
Document everything - Security procedures and changes
Test backups - Verify restoration procedures
Limit access - Both network and privilege-based
Educate users - Security awareness training

Additional Resources

Security is not a one-time task but an continuous commitment. Regularly review and update your security posture to protect against evolving threats.

MySQL/MariaDB Security Configuration: Complete Hardening Guide

MySQL/MariaDB Security Configuration: Complete Hardening Guide

Introduction

Security Threats to Database Systems

Prerequisites

Creating Configuration Backup

Initial Security Hardening

Running mysql_secure_installation

Verifying Security Script Results

User Management and Access Control

Understanding MySQL Privilege System

Creating Users with Least Privilege

Host-Based Access Restrictions

Implementing Password Policies

Password Expiration and Rotation

Account Locking

Failed Login Tracking (MySQL 8.0.19+)

Network Security

Binding to Specific Interfaces

Disabling Name Resolution

Changing Default Port

SSL/TLS Encryption

Generating SSL Certificates

Configuring MySQL for SSL

Requiring SSL for Users

Connecting with SSL

File System Security

Securing Configuration Files

Securing Data Directory

Protecting Binary Logs

Disabling LOCAL INFILE

Firewall Configuration

UFW (Ubuntu/Debian)

firewalld (CentOS/Rocky Linux)

iptables

Security Logging and Monitoring

Enabling General Query Log

Error Logging

Binary Logging for Auditing

Audit Plugin (MySQL Enterprise / MariaDB)

Monitoring Failed Login Attempts

Security Best Practices

Removing Anonymous Users

Removing Test Database

Disabling Symbolic Links

Securing Stored Procedures

Disabling SHOW DATABASES

File Privilege Restrictions

Limiting Database Resources

Security Auditing

Regular Security Audits

Penetration Testing

Incident Response

Detecting Suspicious Activity

Responding to Compromise

Compliance and Standards

PCI-DSS Compliance

GDPR Compliance

Conclusion

Security Checklist

Best Practices Summary

Additional Resources

Latest Video

Get $20 Free Credit