Introduction

MySQL is one of the most widely used relational database management systems, powering numerous web applications and services. Securing MySQL in a production environment is crucial to safeguard sensitive data, maintain system integrity, and protect against potential security threats. In this article, we will discuss best practices for MySQL hardening, accompanied by practical examples to help you secure your MySQL database for production usage.

1. Update MySQL to the Latest Version:
   Keeping your MySQL installation up-to-date is the first step in securing your database. Regularly check for the latest MySQL releases and apply updates promptly to benefit from the latest security enhancements and bug fixes.

Example:
Suppose you are using MySQL 8.0.1, and the latest stable version is 8.0.20. Upgrade your MySQL installation using the official MySQL installer or package manager:

sudo apt update
sudo apt upgrade mysql-server

2. Secure the MySQL Root Account:
   The MySQL root account has elevated privileges, making it a prime target for attackers. To enhance security, it’s essential to set a strong password for the root account and avoid using it for day-to-day operations.

Example:
To set a new password for the root account, use the following SQL command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

3. Create Dedicated MySQL User Accounts:
   Avoid using the root account for regular tasks. Instead, create dedicated user accounts with minimal privileges for specific applications or services.

Example:
Create a new user ‘myappuser’ with limited access to the ‘myapp’ database:

CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myappuser'@'localhost';

4. Enable MySQL Firewall:
   MySQL comes with a built-in firewall that can help restrict access to your database from unwanted sources.

Example:
Enable the MySQL firewall and add an exception for your application server’s IP address:

INSTALL PLUGIN firewalld SONAME 'mysql_firewall.so';
SET GLOBAL mysql_firewall_mode = 'ON';
INSERT INTO mysql.firewall_whitelist (host, port) VALUES ('app_server_ip', 3306);

5. Limit Network Accessibility:
   Restricting access to your MySQL server based on IP addresses can prevent unauthorized access from external sources.

Example:
Edit your MySQL configuration file (my.cnf or my.ini) and bind MySQL to a specific IP address:

bind-address = 127.0.0.1

6. Encrypt MySQL Connections:
   Encrypting MySQL connections using SSL/TLS ensures that data exchanged between clients and the server remains confidential and protected from eavesdropping.

Example:
Generate SSL certificates and configure MySQL to use SSL:

mysql_ssl_rsa_setup --datadir=/path/to/mysql/datadir

Edit the MySQL configuration file to enable SSL:

[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

7. Enable Two-Factor Authentication:
   Implementing two-factor authentication (2FA) for MySQL users adds an extra layer of security.

Example:
Install the MySQL Authentication Plugin:

INSTALL PLUGIN two_factor;

Configure the plugin for specific users:

ALTER USER 'user'@'localhost' REQUIRE TWO_FACTOR;

Conclusion:

MySQL hardening is a critical aspect of maintaining a secure production environment. By following the best practices outlined in this article, such as updating MySQL regularly, securing the root account, using dedicated user accounts, enabling the firewall, restricting network accessibility, encrypting connections, and implementing two-factor authentication, you can significantly enhance the security of your MySQL database. Remember to regularly review and audit your security measures to adapt to evolving threats and ensure ongoing protection of your data.