
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.