BLOG POSTS
    MangoHost Blog / How to Secure MySQL and MariaDB Databases on a Linux VPS
How to Secure MySQL and MariaDB Databases on a Linux VPS

How to Secure MySQL and MariaDB Databases on a Linux VPS

Database security is one of those things that’ll bite you in the ass if you ignore it, and MySQL/MariaDB databases are prime targets for attackers looking for low-hanging fruit. Whether you’re running a small web application or managing enterprise-level databases on your VPS, proper security hardening isn’t optional – it’s essential. This guide walks you through the practical steps to lock down your MySQL or MariaDB installation on Linux, covering everything from initial setup to advanced security configurations that’ll keep your data safe from common attack vectors.

Understanding MySQL and MariaDB Security Fundamentals

Both MySQL and MariaDB ship with default configurations that prioritize ease of setup over security. Out of the box, you’ll typically find anonymous users, a test database accessible to everyone, and root accounts that can connect from anywhere. This convenience becomes a liability the moment your server touches the internet.

The security model in both database systems revolves around a three-tier approach: connection security (who can connect), access control (what they can do), and data protection (encryption and monitoring). Understanding this hierarchy helps you build defense in depth rather than relying on a single security measure.

MariaDB has some additional security features compared to MySQL, including more granular user account management and built-in password validation plugins. However, the core security principles apply to both systems equally.

Initial Security Hardening Steps

The first thing you should do after installing MySQL or MariaDB is run the security script. This built-in tool addresses the most glaring security issues with default installations.

# For MySQL
sudo mysql_secure_installation

# For MariaDB
sudo mysql_secure_installation

This script will prompt you through several security improvements:

  • Setting a root password (if not already set)
  • Removing anonymous user accounts
  • Disabling remote root login
  • Removing the test database
  • Reloading privilege tables

Answer ‘Y’ to all prompts unless you have specific reasons not to. Here’s what a typical session looks like:

Enter current password for root (enter for none): [Press Enter]
Set root password? [Y/n] Y
New password: [Enter strong password]
Re-enter new password: [Confirm password]
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

User Account Management and Privilege Control

Running applications with root database privileges is like giving someone administrator access to your entire system when they only need to read a single file. Create dedicated users for each application or service that needs database access.

Here’s how to create a properly restricted user account:

# Connect to MySQL/MariaDB as root
sudo mysql -u root -p

# Create a new user for your application
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'strong_password_here';

# Create a database for your application
CREATE DATABASE webapp_db;

# Grant only necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp_user'@'localhost';

# Apply the changes
FLUSH PRIVILEGES;

# Verify the user was created correctly
SELECT User, Host FROM mysql.user WHERE User = 'webapp_user';

The principle of least privilege is crucial here. Don’t grant CREATE, DROP, or ALTER permissions unless your application specifically needs them. Most web applications only need basic CRUD operations.

For applications that need to connect from remote servers, be specific about the host:

# Instead of using '%' (any host), specify the exact IP
CREATE USER 'api_user'@'192.168.1.100' IDENTIFIED BY 'another_strong_password';
GRANT SELECT ON api_db.* TO 'api_user'@'192.168.1.100';

Network Security and Connection Hardening

By default, MySQL and MariaDB listen on all network interfaces. If you don’t need remote connections, bind the service to localhost only. Edit your configuration file:

# For MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf
# For MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
bind-address = 127.0.0.1

If you need remote access, consider these security measures:

  • Use a non-standard port instead of 3306
  • Implement firewall rules to restrict access
  • Enable SSL/TLS encryption for all connections
  • Set up a VPN for administrative access

To change the default port:

[mysqld]
port = 33061
bind-address = 192.168.1.10

Configure your firewall to allow connections only from trusted sources:

# Using UFW (Ubuntu Firewall)
sudo ufw allow from 192.168.1.0/24 to any port 33061
sudo ufw deny 33061

# Using iptables
sudo iptables -A INPUT -p tcp --dport 33061 -s 192.168.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 33061 -j DROP

SSL/TLS Encryption Configuration

Encrypting database connections prevents eavesdropping and man-in-the-middle attacks. Both MySQL and MariaDB support SSL/TLS, but it requires proper certificate setup.

First, generate SSL certificates. For internal use, self-signed certificates work fine:

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

# Generate CA private key
sudo openssl genrsa 2048 > ca-key.pem

# Generate CA certificate
sudo openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

# Generate server private key and certificate request
sudo openssl req -newkey rsa:2048 -days 3600 -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 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Add SSL configuration to your MySQL/MariaDB config:

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

Restart the database service and verify SSL is working:

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

# Test SSL connection
mysql -u root -p --ssl-ca=/etc/mysql/ssl/ca-cert.pem -h localhost

# Check SSL status once connected
mysql> SHOW STATUS LIKE 'Ssl_cipher';

Force SSL for specific users:

ALTER USER 'webapp_user'@'localhost' REQUIRE SSL;

Password Security and Authentication Plugins

Weak passwords are the easiest way for attackers to compromise your database. Both MySQL and MariaDB offer password validation plugins that enforce strong password policies.

For MySQL, install and configure the validate_password plugin:

# Install the plugin
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';

# Configure password requirements
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;

For MariaDB, use the simple_password_check plugin:

# Install the plugin
mysql> INSTALL PLUGIN simple_password_check SONAME 'simple_password_check.so';

# Configure minimum 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;

Make these settings permanent by adding them to your configuration file:

[mysqld]
# MySQL
plugin-load-add=validate_password.so
validate_password.policy=STRONG
validate_password.length=12

# MariaDB
plugin-load-add=simple_password_check.so
simple_password_check_minimal_length=12

File System Security and Data Directory Protection

The database files themselves need protection at the operating system level. Ensure your data directory has proper permissions and ownership:

# Check current permissions
ls -la /var/lib/mysql/

# Set correct ownership (should already be correct)
sudo chown -R mysql:mysql /var/lib/mysql/

# Set restrictive permissions
sudo chmod 750 /var/lib/mysql/
sudo chmod 640 /var/lib/mysql/*

Consider placing your data directory on an encrypted filesystem, especially for sensitive data:

# Create encrypted partition (example using LUKS)
sudo cryptsetup luksFormat /dev/sdb1
sudo cryptsetup luksOpen /dev/sdb1 mysql_data
sudo mkfs.ext4 /dev/mapper/mysql_data

# Mount and move data
sudo mkdir /mnt/encrypted_mysql
sudo mount /dev/mapper/mysql_data /mnt/encrypted_mysql
sudo systemctl stop mysql
sudo rsync -av /var/lib/mysql/ /mnt/encrypted_mysql/
sudo mv /var/lib/mysql /var/lib/mysql.bak
sudo mkdir /var/lib/mysql
sudo mount --bind /mnt/encrypted_mysql /var/lib/mysql

Logging and Monitoring Configuration

Comprehensive logging helps you detect suspicious activity and troubleshoot issues. Enable the general query log and slow query log for monitoring:

[mysqld]
# General query log (use sparingly in production due to performance impact)
general_log = 1
general_log_file = /var/log/mysql/general.log

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Log queries that don't use indexes
log_queries_not_using_indexes = 1

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

Set up log rotation to prevent logs from consuming all disk space:

# Create logrotate configuration
sudo nano /etc/logrotate.d/mysql

/var/log/mysql/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
    endscript
}

Monitor failed login attempts using this query:

SELECT argument FROM mysql.general_log 
WHERE command_type = 'Connect' 
AND argument LIKE '%Access denied%' 
ORDER BY event_time DESC LIMIT 20;

Backup Security and Recovery Planning

Secure backups are part of your security strategy. Compromised systems are useless if your backups are also compromised or inaccessible.

Create a dedicated backup user with minimal privileges:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT LOCK TABLES, SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Use mysqldump with proper security options:

#!/bin/bash
# Secure backup script

BACKUP_USER="backup_user"
BACKUP_PASS="backup_password"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Perform backup with compression and encryption
mysqldump --user=$BACKUP_USER --password=$BACKUP_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --all-databases | \
gzip | \
gpg --cipher-algo AES256 --compress-algo 1 --symmetric \
    --output "$BACKUP_DIR/mysql_backup_$DATE.sql.gz.gpg"

# Set proper permissions
chmod 600 "$BACKUP_DIR/mysql_backup_$DATE.sql.gz.gpg"

# Remove backups older than 30 days
find $BACKUP_DIR -name "mysql_backup_*.sql.gz.gpg" -mtime +30 -delete

Advanced Security Measures

For high-security environments, consider implementing additional hardening measures:

Security Feature MySQL Support MariaDB Support Implementation Complexity
Data at Rest Encryption Yes (Enterprise) Yes (Community) Medium
Audit Logging Yes (Enterprise) Yes (Community) Low
Role-Based Access Control Yes (8.0+) Yes (10.0.5+) Medium
Password Expiration Yes Yes Low
Connection Rate Limiting Yes Yes Low

Enable data-at-rest encryption in MariaDB:

# Generate encryption key
openssl rand -hex 32 > /etc/mysql/encryption_key

# Add to configuration
[mysqld]
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption_key
file_key_management_filekey = FILE:/etc/mysql/master_key
encrypt_tables = ON
innodb_encrypt_tables = ON
innodb_encrypt_log = ON

Set up audit logging to track database access:

# MariaDB audit plugin
[mysqld]
plugin_load_add = server_audit
server_audit_logging = ON
server_audit_events = CONNECT,QUERY,TABLE
server_audit_file_path = /var/log/mysql/audit.log

Security Testing and Vulnerability Assessment

Regular security testing helps identify configuration drift and new vulnerabilities. Use tools like MySQL’s built-in security features and external scanners:

# Check for users without passwords
SELECT User, Host FROM mysql.user WHERE authentication_string = '';

# Check for users with administrative privileges
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

# List databases accessible to each user
SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv 
FROM mysql.db ORDER BY User;

# Check SSL configuration
SHOW STATUS LIKE 'Ssl%';
SHOW VARIABLES LIKE 'have_ssl';

Use external tools for comprehensive security assessment:

# Install and run mysql-security-check
wget https://raw.githubusercontent.com/mysql/mysql-utilities/master/scripts/mysql_security_check.py
python mysql_security_check.py --server=root:password@localhost:3306

Common Security Pitfalls and Troubleshooting

Even experienced administrators make mistakes. Here are the most common security issues and how to fix them:

  • Wildcard host permissions: Using ‘%’ in user creation allows connections from anywhere. Always specify exact IPs or hostnames.
  • Overprivileged application users: Applications rarely need ALL PRIVILEGES. Review and reduce permissions regularly.
  • Forgotten test accounts: Development and testing accounts often remain in production. Audit user accounts quarterly.
  • Unencrypted connections: Internal networks aren’t automatically secure. Use SSL even for localhost connections when handling sensitive data.
  • Default port usage: Port 3306 is constantly scanned by attackers. Change it and use proper firewall rules.

If you’ve locked yourself out, you can reset access using the –skip-grant-tables option:

# Stop MySQL/MariaDB
sudo systemctl stop mysql

# Start with skip-grant-tables
sudo mysqld_safe --skip-grant-tables --skip-networking &

# Connect without password
mysql -u root

# Reset root password
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

# Restart normally
sudo pkill mysqld
sudo systemctl start mysql

Performance Impact of Security Measures

Security always comes with overhead. Understanding the performance impact helps you make informed decisions:

Security Feature CPU Impact Memory Impact Network Impact Storage Impact
SSL/TLS Encryption 5-15% Minimal 5-10% None
General Query Logging 10-20% Minimal None High
Password Validation Minimal Minimal None None
Audit Logging 5-10% Low None Medium
Data Encryption 3-8% Low None 5-10%

Monitor performance after implementing security measures:

# Check current performance metrics
SHOW GLOBAL STATUS LIKE 'Queries%';
SHOW GLOBAL STATUS LIKE 'Connections%';
SHOW GLOBAL STATUS LIKE 'Ssl%';

# Monitor slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Integration with VPS Security Best Practices

Database security works best when integrated with overall server security. If you’re running your database on a VPS, consider these additional measures:

  • Use fail2ban to automatically block brute force attempts
  • Implement intrusion detection systems (IDS) like OSSEC
  • Regular security updates for the operating system
  • Network segmentation using VLANs or VPNs
  • Regular security audits and penetration testing

For larger deployments requiring dedicated resources, dedicated servers provide better isolation and control over the security environment.

Configure fail2ban for MySQL protection:

# Create /etc/fail2ban/jail.local
[mysqld-auth]
enabled = true
filter = mysqld-auth
action = iptables[name=mysql, port=3306, protocol=tcp]
logpath = /var/log/mysql/error.log
maxretry = 3
bantime = 3600

Database security isn’t a one-time setup task – it requires ongoing attention, regular updates, and continuous monitoring. The steps outlined here provide a solid foundation, but remember that security is an evolving challenge that requires staying current with new threats and mitigation strategies. Start with the basics like running mysql_secure_installation and creating proper user accounts, then gradually implement more advanced features based on your specific requirements and risk tolerance.



This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.

Leave a reply

Your email address will not be published. Required fields are marked