Securing a MySQL installation is a crucial step in safeguarding your data and the integrity of your database. MySQL provides a built-in utility to simplify this process, which is called mysql_secure_installation. This command helps you to remove insecure default settings, such as weak passwords, test databases, and anonymous users. Here’s how to use it:
Run the mysql_secure_installation Script:
This script comes with the MySQL installation and helps secure the database server with several prompts to configure security-related settings.
sudo mysql_secure_installation
Set Root Password:
You will be prompted to set a root password if it hasn’t already been set. It’s highly recommended to use a strong password that follows security best practices (e.g., minimum length, special characters, etc.).
Remove Anonymous Users:
MySQL installation might have an anonymous user that allows anyone to log in without a password. It’s advisable to remove this user to prevent unauthorized access.
You will be prompted with:
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
Type Y to remove the anonymous users.
Disallow Remote Root Login:
By default, the root user can log in remotely, which could be a security risk. Disabling remote root access helps prevent unauthorized access.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
Type Y to disable root login from remote locations.
Remove Test Databases:
MySQL installation includes a test database, which can be accessed by anyone. It is best to remove it.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
Type Y to remove the test database.
Reload Privilege Tables:
Finally, the script will ask to reload the privilege tables to ensure that all changes are applied immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :
Type Y to reload the privileges.
Disable Unused Features:
Disable any unused features and components, such as the INNODB storage engine if not needed.
Use Strong Authentication Methods:
Use plugins like caching_sha2_password instead of weaker, outdated methods such as mysql_native_password.
Use Firewall:
Ensure that your MySQL server is behind a firewall and only accessible from allowed IP addresses.
Limit User Privileges:
Always grant the minimum required privileges to MySQL users to reduce risk. Use the GRANT command to limit permissions.
GRANT SELECT, INSERT ON database.* TO 'user'@'host';
Regular Updates:
Keep your MySQL installation up-to-date with the latest security patches and updates.
Enable Logging:
Enable MySQL logs like the general query log and error logs to monitor unusual activities.