Master DevOps Tools & Best Practices - DevOps Knowledge Hub Master DevOps Tools & Best Practices - DevOps Knowledge Hub

Best Practices for Hardening MySQL Server Security Configurations

Harden MySQL with least-privilege users, host restrictions, TLS, safer my.cnf settings, patching, and basic auditing.

Best Practices for Hardening MySQL Server Security Configurations

MySQL hardening starts with a simple question: what would an attacker gain if one application password leaked? If the answer is "every database from anywhere," your server needs tighter users, network rules, and configuration.

Use these practices to reduce the blast radius of a compromise without making daily administration painful.

1. User and Access Management: The Principle of Least Privilege

Effective user management is foundational to MySQL security. Granting users only the permissions they absolutely need is paramount.

Create Specific Users for Specific Applications

Avoid using the root user for application connections. Instead, create dedicated users with granular permissions.

CREATE USER 'my_app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `your_database`.* TO 'my_app_user'@'localhost';

Enforce Strong Passwords

Strong, unique passwords are your first line of defense. Utilize MySQL's built-in password validation plugin if available.

  • Complexity: Mix uppercase, lowercase, numbers, and symbols.
  • Length: Aim for at least 12-16 characters.
  • Uniqueness: Never reuse passwords.
  • Rotation: Implement a policy for regular password changes.

You can enable the validate_password component (MySQL 8.0+) or plugin (MySQL 5.7+):

INSTALL COMPONENT 'file://component_validate_password';
-- OR for older versions
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configure strength policy (e.g., MEDIUM for 8+ chars, mix case, numbers, special)
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;

Remove Default and Unused Users

MySQL may include administrative and internal accounts. Keep required system accounts such as mysql.session and mysql.sys intact, but remove anonymous accounts and any human or application accounts you no longer use.

-- To identify anonymous users:
SELECT user, host FROM mysql.user WHERE user = '';
-- To drop an anonymous user (if found):
DROP USER ''@'localhost';

-- To remove test databases (if they exist):
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;

Restrict Host Access

Limit user accounts to connect only from specific IP addresses or hostnames. Avoid using % as a wildcard for host unless absolutely necessary and coupled with other strong security controls.

-- User can only connect from localhost
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AnotherStrongPass!';

-- User can only connect from a specific IP address
CREATE USER 'backup_user'@'192.168.1.100' IDENTIFIED BY 'BackupPass!';

Be Cautious with GRANT OPTION

The WITH GRANT OPTION clause allows a user to grant their own privileges to other users. This can be a significant security risk if granted to untrusted users. Use it sparingly and only for administrative accounts that genuinely require this capability.

-- A user with ability to grant privileges (use with extreme caution)
CREATE USER 'superadmin'@'localhost' IDENTIFIED BY 'SuperAdminPass!';
GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost' WITH GRANT OPTION;

2. Network Security: Isolating Your Database

Network-level controls are critical to preventing unauthorized external access to your MySQL server.

Configure Firewalls

Allow connections to MySQL's default port (3306) only from trusted IP addresses or networks. Block all other inbound connections to this port.

Example (UFW on Linux):

sudo ufw enable
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw deny 3306
sudo ufw status

Example (CentOS/RHEL with firewalld):

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --permanent --remove-port=3306/tcp --zone=public # Ensure it's not open globally
sudo firewall-cmd --reload

Secure Connections with SSL/TLS

Encrypt all traffic between clients and the MySQL server using SSL/TLS to prevent eavesdropping and Man-in-the-Middle (MitM) attacks. This is especially crucial for connections over untrusted networks.

To enable SSL/TLS, you typically need to generate SSL certificates and keys, then configure your my.cnf:

# my.cnf
[mysqld]
ssl_ca=/etc/mysql/certs/ca.pem
ssl_cert=/etc/mysql/certs/server-cert.pem
ssl_key=/etc/mysql/certs/server-key.pem

Clients should then be configured to connect using SSL/TLS, often with REQUIRE SSL in the user's GRANT statement:

CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'SSLUserPass!';
GRANT SELECT ON `your_database`.* TO 'ssl_user'@'%' REQUIRE SSL;

Bind to Specific IP Addresses

By default, MySQL might listen on all available network interfaces (0.0.0.0). Restrict it to listen only on the interfaces that need to accept connections (e.g., localhost for local applications, or a private network IP for internal connections).

# my.cnf
[mysqld]
bind-address = 127.0.0.1  # For local connections only
# OR
bind-address = 192.168.1.10  # For a specific internal IP

Tip: If your application and MySQL server are on the same machine, bind-address = 127.0.0.1 (localhost) is the most secure option, as it prevents any external connections entirely.

3. Configuration File Hardening (my.cnf / my.ini)

The MySQL configuration file (my.cnf on Linux, my.ini on Windows) offers numerous parameters to enhance security.

Disable Unused Features

Minimize the attack surface by disabling features not required for your deployment.

  • local_infile = 0: Disables LOAD DATA LOCAL INFILE, which can otherwise read files from the client host when both client and server allow it.
    [mysqld]
local_infile = 0
  • skip-networking: If your database is only accessed by applications on the same server, disable networking entirely. This forces all connections to use the Unix socket or named pipes.
    [mysqld]
skip-networking
  • symbolic-links = 0: Prevents the use of symbolic links for database tablespaces, which can be exploited to access files outside the MySQL data directory.
    [mysqld]
symbolic-links = 0
  • secure_file_priv: Restricts the directory used by file import and export operations such as LOAD DATA INFILE and SELECT ... INTO OUTFILE.
    [mysqld]
secure_file_priv = /var/lib/mysql-files

Set secure_file_priv to a dedicated directory owned by the MySQL service account. Do not point it at /tmp, an application upload directory, or a path that untrusted users can write to.

Avoid Plaintext Secrets in Option Files

Client option files can accidentally expose database passwords to anyone who can read them. If you must store credentials for automation, restrict file permissions and use a dedicated low-privilege account.

chmod 600 /home/backup/.my.cnf
chown backup:backup /home/backup/.my.cnf

For interactive administration, prefer prompting for the password:

mysql -u admin -p

4. Patch and Verify the Server

Security configuration will not save an unpatched server. Keep MySQL, client libraries, and the operating system current through your normal package or vendor update process.

After changing my.cnf, validate and restart during a maintenance window:

mysqld --validate-config
sudo systemctl restart mysql
sudo systemctl status mysql

Package names and service names vary by distribution. Some systems use mysqld instead of mysql.

5. Monitor Access and Review Privileges

Hardening is not finished after the first pass. Review accounts and grants regularly, especially after application changes or staff turnover.

SELECT user, host, account_locked FROM mysql.user ORDER BY user, host;
SHOW GRANTS FOR 'my_app_user'@'localhost';

Watch for accounts that use % hosts, broad *.* privileges, FILE, SUPER, SYSTEM_USER, or WITH GRANT OPTION. Those privileges may be valid for a small number of administrative accounts, but they should never appear on routine application users.

Takeaway

Harden MySQL in layers. Start with dedicated least-privilege users, restrict where they can connect from, encrypt traffic when it crosses a network, disable risky features you do not use, and keep the server patched. Then schedule privilege reviews so old access does not quietly become your next incident.