How to Secure MySQL on Ubuntu 22.04 With Lamp/Lemp Stack Templates

A LAMP stack is a bundle of open-source software commonly installed together, transforming a server into a hub for hosting dynamic websites and PHP-based web applications. The acronym breaks down into the Linux operating system and the Apache web server; site data finds its home in a MySQL database, while PHP takes the lead in processing dynamic content.

To authenticate to MySQL with a root user using a password, follow these steps 👇

First, open up the MySQL prompt:

sudo mysql

Then, run the following ALTER USER command to change the root authentication method. The following example changes the authentication method to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Finally, exit the MySQL prompt:

exit

Following that, we recommend that you run a script that comes pre-installed with MySQL to configure the access and security of MySQL as you like. It is an interactive script that requires you to answer Yes or No to the provided conditions.

Start the script by running the following command:

sudo mysql_secure_installation

This will ask if you want to configure VALIDATE_PASSWORD_PLUGIN:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to set up VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:

Note that if enabled, passwords that don’t match the specified criteria will be rejected by MySQL with an error.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary              file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

You will also be asked about the following:

Prompt	Explanation
Remove Anonymous Users	The script will ask if you want to remove anonymous users: Press Y to remove (recommended), or N to keep them It's generally a good idea to remove anonymous users for security reasons
Disallow Root Login Remotely	You'll be asked if you want to disallow root login remotely: Press Y to disallow it (recommended), or N if you need to log in remotely as the root user For security reasons, it's recommended to disallow remote root login
Remove the Test Database	The script will ask if you want to remove the test database: Press Y to remove (recommended), or N to keep it It's advisable to remove the test database, especially in a production environment
Reload Privilege Tables	The script will reload the privilege tables to apply the changes you made. Press Y to reload the tables.

And that's it! You've successfully secured MySQL on an Ubuntu 22.04 Lamp/Lemp Stack VPS provided by Hostinger.

How to Create a New MySQL Database in cPanel

How to Use the MEAN Stack VPS Template