Essential Steps to Secure MySQL: From Access Controls to Patch Management

MySQL is renowned for performance and ease of use, but many deployments overlook security, leaving databases vulnerable to attacks and performance issues. The following best‑practice checklist helps secure MySQL installations.

1. Restrict Remote Access

Grant privileges only to specific hosts instead of using a wildcard:

GRANT ALL ON *.* TO 'root'@'localhost';

GRANT ALL ON *.* TO 'root'@'myip.athome';

FLUSH PRIVILEGES;

This keeps full access but limits it to designated IPs.

2. Perform Regular Backups

Treat backups as a daily operational task to ensure rapid recovery from crashes or breaches.

3. Disable or Limit Remote Connections

Use firewalls (iptables, TCP wrappers) or MySQL options such as skip-networking or bind-address=127.0.0.1 in the [mysqld] section of my.cnf / my.ini. For selective remote access, grant limited rights:

GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

4. Secure the Root Account

Set a strong password and optionally rename the root user:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

mysqladmin -u root password new_password

USE mysql; UPDATE user SET user='another_username' WHERE user='root'; FLUSH PRIVILEGES;

5. Remove the Default test Database

DROP DATABASE test;

6. Disable LOCAL INFILE

Prevent unauthorized file reads by adding set-variable=local-infile=0 to [mysqld].

7. Delete Anonymous and Unused Accounts

Check for empty users and drop them:

SELECT * FROM mysql.user WHERE user='';

DROP USER ''@'localhost';

8. Reduce System Privileges

Ensure MySQL data directories and binaries are owned by the mysql user and group, and that only root or mysql can write to them.

9. Limit Database User Privileges

Grant only the necessary rights, e.g.,

GRANT SELECT ON billing.dianshang TO 'user1'@'localhost'; FLUSH PRIVILEGES;

Revoke when no longer needed:

REVOKE SELECT ON billing.ecommerce FROM 'user1'@'localhost'; FLUSH PRIVILEGES;

10. Remove or Disable .mysql_history

Redirect history to /dev/null and delete the file:

export MYSQL_HISTFILE=/dev/null

rm ~/.mysql_history

11. Apply Security Patches

Keep MySQL up to date to avoid known vulnerabilities.

12. Enable Logging

Configure error and general logs in my.cnf (e.g., log=/var/log/mylogfile) and restrict log file access to root and mysql.

13. Use a chroot Jail

Run MySQL inside a dedicated directory (e.g., /chroot/mysql) and set the client socket accordingly to limit filesystem exposure.

14. Re‑disable LOCAL INFILE (redundant safety)

Confirm the setting set-variable=local-infile=0 is present in the server configuration.

Implementing these measures significantly reduces the attack surface of MySQL deployments and safeguards critical data.