Skip to main content

Best Practices

Use Strong Authentication & Passwords​

  • Always use strong, complex passwords.
  • Enforce password policies (length, complexity, expiration).
  • Never leave the root account with no password.
ALTER USER 'john'@'localhost' IDENTIFIED BY 'Secur3#Pass!';

This ensures the user has a strong password.

You can also enforce password expiration:

ALTER USER 'john'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Follow the Principle of Least Privilege (PoLP)​

  • Users should only get the minimum privileges required for their job.
  • Avoid granting ALL PRIVILEGES unless absolutely necessary.
  • Never give SUPER or GRANT OPTION to normal users.
GRANT SELECT, INSERT ON company.employees TO 'hr_user'@'localhost';

hr_user can read and add employee data, but cannot delete or drop tables.

Remove Anonymous & Unused Accounts​

MySQL by default may create anonymous accounts (''@'localhost'). These are dangerous.

DROP USER ''@'localhost';
DROP USER ''@'%';

Removes potential backdoors.

Also, remove test databases if not needed:

DROP DATABASE test;

Use Encrypted Connections (SSL/TLS)​

  • Protect sensitive data by encrypting client-server communication.
  • Require SSL for critical users.
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'StrongPass123' REQUIRE SSL;

Ensures that secure_user can only connect via SSL/TLS.

Secure the Root Account​

  • Don’t use root for application connections.
  • Restrict root login to localhost.
CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY 'Adm1n#Pass!';
GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'localhost' WITH GRANT OPTION;

Use dbadmin for administration and disable root remote login:

ALTER USER 'root'@'%' ACCOUNT LOCK;

Regularly Apply Updates & Patches​

  • Always run the latest MySQL version to patch vulnerabilities.
  • Keep OS and MySQL-related libraries updated.

Example command (Linux):

sudo apt update && sudo apt upgrade mysql-server

Enable Logging & Auditing​

Enable the general log or audit log plugin to monitor suspicious activity.

Monitor failed login attempts.

[mysqld]
log_error = /var/log/mysql/error.log
general_log = 1
general_log_file = /var/log/mysql/general.log

This helps trace what queries were executed and by whom.

Backup Data Securely​

  • Regular backups protect against accidental loss and ransomware.
  • Store backups encrypted.

Example backup with encryption:

mysqldump -u backup_user -p company | openssl enc -aes-256-cbc -out backup.sql.enc

Restrict Network Access​

  • Bind MySQL to localhost if not needed remotely.
  • Use firewalls to restrict which IPs can connect.

Example (my.cnf):

[mysqld]
bind-address = 127.0.0.1

This ensures MySQL is only accessible locally.

For remote access, allow specific IPs only:

CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'AppPass#1';

Use Data-at-Rest Encryption​

  • Encrypt sensitive tables or entire storage using MySQL Transparent Data Encryption (TDE) or OS-level disk encryption.

Example (per-table encryption):

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
) ENCRYPTION='Y';