Logo

How to Secure MySQL with mysql_secure_installation Script

Try this guide with our instant dedicated server for as low as 40 Euros

MySQL is a leading open-source relational database management system for data storage, retrieval, and manipulation. It is the backbone of many web applications, powering everything from content management systems to e-commerce platforms. 

Securing the MySQL database server is crucial to protect data from unauthorized access and potential threats. There are several ways to secure MySQL database servers and  mysql_secure_installation scrip is an important one. 

The mysql_secure_installation script is a built-in MySQL tool that helps users swiftly and efficiently configure critical security settings for the MySQL server. 

In this tutorial, we will discuss how to secure MySQL with the MySQL_Secure_Installation script. However, before that, let us take a quick look at the prerequisites. 

The Prerequisites

Before moving in, ensure you have the following: 

  • A MySQL server installed on your system
  • A user account with sudo or administrative privilege.

Table Of Contents

  1. How to Secure MySQL with mysql_secure_installation Script
    1. Step #1: Access the Command Line
    2. Step #2: Set the Root Password
    3. Step #3: Password Strength Validation Setup
    4. Step #4: Remove Anonymous Users
    5. Step #5: Disallow Remote Root Login
    6. Step #6: Remove the Test Database
    7. Step #7: Reload Privilege Tables
  2. How to automate the mysql_secure_installation script?
  3. Conclusion
  4. FAQs

How to Secure MySQL with mysql_secure_installation Script

Securing MySQL is crucial for protecting sensitive data and maintaining the integrity of applications. Let us look at how to secure MySQL with the mysql_secure_installation script.

Step #1: Access the Command Line

Open the terminal and execute the mysql_secure_installation script with the following command:

# sudo mysql_secure_installation

Step #2: Set the Root Password

Next, you will be prompted to enter a password for the root account, if not already set.

If a root password is already in place, enter the current password.

Enter current password for root (enter for none):

sudo mysql secure installation

Step #3: Password Strength Validation Setup

Next, you will be prompted to configure the password validation component. 

Press Y to validate the password.

validate password component

You will be now prompted to choose a password policy level.

There are three levels of password validation policy:

  • LOW: Only checks the length of the password.
  • MEDIUM: Checks the length, whether it includes numeric, mixed case, and special characters.
  • STRONG: Enforces more complex rules like the number of characters and the presence of dictionary words.

password validation policy levels

Choose the corresponding number (0, 1, or 2) for your desired policy level.

The script enforces this policy on new passwords. If any passwords do not meet the criteria, you’ll be prompted to enter new passwords that comply.

estimated password strength

Step #4: Remove Anonymous Users

Once you have set up the password, you will be prompted to remove anonymous user accounts. 

Anonymous users pose a security risk as they can access the MySQL server without a username and password. 

Press Y to remove the anonymous user. 

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

anonymous user mysql

Step #5: Disallow Remote Root Login

We recommend disabling remote root login to enhance security. This prevents anyone from logging into the MySQL server as a root user from a remote location. However, you should carefully consider the impact of this limitation on your remote operations. 

If you prefer disabling remote root login, press Y.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

localhost connection

Step #6: Remove the Test Database

As part of enhancing security, we recommend removing the test database.

The test database is used for testing purposes. Removing it prevents unauthorized access and potential misuse.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

remove test database

Step #7: Reload Privilege Tables

Next, reload the privilege tables so that the changes take effect. 

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

reload previlige tables

A message indicating the successful execution of the script will be displayed.

Your MySQL installation is now secure with the hardening activities carried out by the mysql_secure_installation script.

How to automate the mysql_secure_installation script?

There are cases where you might want to automate the mysql_secure_installation execution. 

While the script does not provide official options for fully non-interactive execution, you can achieve this by echoing answers to the script using a here-document or similar method in your shell script. 

For instance, consider the following example that illustrates how to automate the mysql_secure_installation script using a here-document in a shell script:

sudo mysql_secure_installation <<EOF

Y
new_password
new_password
Y
Y
Y
Y
EOF

Here,

  • sudo mysql_secure_installation: Runs the script with administrative privileges.
  • <<EOF: Here-document marker, signifies the start of the predefined input.
  • Y: Confirms actions like setting a password, removing users, etc.
  • new_password: Sets the desired new root password (entered twice).
  • EOF: Here-document marker, signifies the end of the predefined input.

Conclusion

Securing a MySQL server is an ongoing process that involves several steps. Using the mysql_secure_installation script is a great starting point to enhance your server’s security. 

Follow the steps mentioned in this tutorial to secure your using MySQL server mysql_secure_installation.

FAQs

Q. What does the mysql_secure_installation script do?

The mysql_secure_installation script helps secure your MySQL server by setting a root password, removing anonymous users, disallowing remote root login, removing the test database, and reloading privilege tables.

Q. Why should I disallow remote root login?

Disallowing remote root login prevents unauthorized access to your MySQL server from remote locations, enhancing security.

Q. How often should I update my MySQL server?

You should regularly update your MySQL server to ensure you have the latest security patches and improvements.

Q. Can I automate mysql_secure_installation?

While automating mysql_secure_installation is possible, it’s important to note that the process may vary based on your specific requirements. You can create custom scripts to automate the necessary steps for securing your MySQL/MariaDB installation.

Q. What are the steps involved in the mysql_secure_installation script?

The common steps in the mysql_secure_installation script include changing the root password, removing anonymous users, disallowing remote root login, removing the test database, and reloading privilege tables using the commands provided.

Q. How to update the root password using the mysql_secure_installation script?

To update the root password using the mysql_secure_installation script, you can use the password update mysql.user set command followed by the appropriate steps to set a new secure password.

Q. What is the importance of securing the MySQL installation?

Securing your MySQL installation is important to enhance security, protect sensitive data, and prevent unauthorized access. 

Try this guide with our instant dedicated server for as low as 40 Euros