How to Secure MySQL Server

24 Jan 2025

MySQL is the best choice for relational database management, but its widespread use also makes it an appealing target for potential attackers. This tutorial outlines essential steps to fortify your MySQL server, protecting your data from unauthorized access and activities that are malicious.

Prerequisites

Prior to diving into security configurations, ensure the prerequisites mentioned below:

MySQL is Installed and Running: Consider reading the official MySQL documentation if needed.
Access to Administrator Privileges: sudo or root access is required for many steps.
Familiarity with MySQL Commands: Basic command-line knowledge will be helpful.

Understanding Threat Landscape

MySQL databases face various threats, including:

Unauthorized Logins: Weak credentials or misconfigurations can allow attackers to access your data.
SQL Injection Attacks: Unvalidated inputs in applications can expose your database to manipulation.
Insufficient Network Security: Open ports and weak firewalls leave the server vulnerable.

Proactively securing the database reduces the likelihood of such incidents.

Step 1: Update the Server and MySQL

As new updates provide new security features and also fixes bugs, it is highly important that your system stays updated because if will fix known vulnerabilities and leverage latest security enhancements. Utilize the commands mentioned below to update both the operating system and MySQL.

sudo apt update
sudo apt upgrade -y

Verify the current MySQL version:

mysql --version

Keep an eye on official release notes to understand updates and security patches.

Step 2: Secure Initial Configuration

The mysql_secure_installation script is an essential tool for strengthening the security of your MySQL server's. It streamlines sensitive tasks such as:

Setting a root password
Removing anonymous user accounts
Disabling remote root logins
Deleting the test database

To run the script:

sudo mysql_secure_installation

Follow the prompts to implement the recommended security measures.

Step 3: Configure User Authentication

Effective user management is essential for database security.

Use Unique Users for Applications

Create separate users for each application, and assign only the permissions they need. For example:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT ON app_database.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

This ensures that a breach in one application doesn’t compromise the entire database.

Enable Authentication Plugins

MySQL provides plugins like caching_sha2_password for secure authentication. To enable it:

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'SecurePassword!';

Step 4: Enforce Password Policies

If passwords are strong then the possibility of brute force attacks may not be of any help for the hackers. Install the password validation plugin if it isn’t already enabled:

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Configure password strength rules in the MySQL configuration file:

validate_password_policy=STRONG
validate_password_length=12

These settings enforce strong, hard-to-guess passwords.

Don’t forget to automate your backups—our How to Create a MySQL Database Dump article shows you how to use mysqldump and phpMyAdmin to snapshot your data before enforcing those strict password rules.

Step 5: Restrict Remote Access

Unnecessary remote access exposes the database to risks.

Disable Remote Root Access

By default, MySQL allows root logins from remote systems. Disable this feature by editing the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set the bind address to localhost:

bind-address = 127.0.0.1

Restart the MySQL service:

sudo systemctl restart mysql

Use Firewalls for Remote Connections

If remote access is necessary then configure firewalls. This will only allow traffic from trusted IPs.

sudo ufw allow from <trusted_ip> to any port 3306
sudo ufw enable

Step 6: Secure Data Transmission

Encryption will make sure that data transferred between the client and server is safe from eavesdropping.

Enable SSL/TLS

Certificates for MySQL are generated automatically at the time of installation.

Add the following lines to /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
require_secure_transport = ON

Restart MySQL to apply changes. Use tools like OpenSSL to verify encrypted connections.

Step 7: Audit and Monitor Database Activity

Monitoring user activity can help detect unauthorized actions.

Enable Logs

Activate general logs and error logs in the configuration file:

general_log=1
log_error=/var/log/mysql/error.log

Analyze Access Patterns

Periodically review logs to identify anomalies or suspicious activity. Use automated tools like Percona Monitoring and Management for advanced analytics.

Step 8: Protect Against SQL Injection

SQL injection is a common attack vector for web-based MySQL applications. Mitigate this risk by:

Using Parameterized Queries: This prevents attackers from injecting malicious SQL commands
Validating User Inputs: Sanitize and validate inputs at the application level.
Implementing a Web Application Firewall (WAF): Tools like ModSecurity can block malicious queries.

Step 9: Regular Backups and Disaster Recovery

Prepare for the unexpected by setting up automated backups.

Full Database Backups

Use mysqldump to create complete backups:

mysqldump -u root -p --all-databases > backup.sql

Incremental Backups

For large databases, consider incremental backups using tools like Percona XtraBackup.

Step 10: Disable Unused Features

Reducing the database’s attack surface is crucial.

Disable Unused Plugins

Run the following command to list active plugins:

SHOW PLUGINS;

Unload any unnecessary plugins

UNINSTALL PLUGIN plugin_name;

Remove Sample Databases

Delete test databases that come pre-installed with MySQL:

DROP DATABASE test;

Step 11: Use Security Updates and Best Practices

Regularly update MySQL to address vulnerabilities and ensure you’re using the most secure version. Stay connected with the MySQL Community for updates, news, and security alerts.

Advance Practices: Segmentation

Isolate your database server from other services using network segmentation to reduce the risk of unauthorized access.

Intrusion Detection Systems (IDS). Use tools like Snort to monitor for unusual activity and detect potential intrusions in real-time.
Two-Factor Authentication (2FA). Enable 2FA for administrative accounts to add an extra layer of protection against unauthorized access.

Conclusion

Securing a MySQL server requires a comprehensive approach, combining careful configuration, continuous monitoring, and proactive management. If you utilize these practices then your database can be safeguarded very effectively, protecting sensitive data as well as mitigating the chance of unauthorized access. You must evaluate and update the security measures regularly so that you can stay prepared for emerging threats.

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS.

How to Secure MySQL Server

Prerequisites Copy link

Understanding Threat Landscape Copy link

Step 1: Update the Server and MySQL Copy link

Step 2: Secure Initial Configuration Copy link

Step 3: Configure User Authentication Copy link