How to Secure MySQL Server on Ubuntu 22.04: Step-by-Step Guide

Introduction:

Securing your MySQL server is crucial to protect your data and prevent unauthorized access. In this step-by-step guide, we will walk you through the process of securing your MySQL server on Ubuntu 22.04.

Step 1: Update System Packages

1. Open a terminal on your Ubuntu 22.04 system.
2. Update the system packages to their latest versions by running the following command:

sudo apt update

Step 2: Access MySQL Server

1. Access the MySQL server using the following command:

sudo mysql

2. Enter the MySQL root password when prompted.

Step 3: Remove Anonymous Users

1. To enhance security, it is recommended to remove anonymous users. Run the following command within the MySQL shell:

DELETE FROM mysql.user WHERE User='';

2. After deleting the anonymous users, run the following command to apply the changes:

FLUSH PRIVILEGES;

Step 4: Disable Remote Root Login

1. By default, the MySQL root user can log in from any host. It is best to restrict the root login to the local machine only. Run the following command within the MySQL shell:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';

Replace ‘your_password’ with a strong password of your choice.

2. Next, run the following command to apply the changes:

FLUSH PRIVILEGES;

Step 5: Remove Test Database

1. The test database in MySQL is created by default and should be removed to prevent unauthorized access. Run the following command within the MySQL shell:

DROP DATABASE IF EXISTS test;

2. Apply the changes by running the following command:

FLUSH PRIVILEGES;

Step 6: Disable Remote Root Access

1. Open the MySQL configuration file using a text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Locate the line that begins with ‘bind-address’ and change the IP address to ‘127.0.0.1’ to bind MySQL to the localhost only.

3. Save the changes and exit the text editor.

Step 7: Restart MySQL Server

1. Restart the MySQL server to apply all the configuration changes:

sudo systemctl restart mysql

Step 8: Test MySQL Server Security

Test the MySQL server security by attempting to log in remotely as the root user. If the connection is refused, it indicates that the security measures have been successfully implemented.

Conclusion:

By following the steps outlined in this article, you can secure your MySQL server on Ubuntu 22.04 and protect your data from unauthorized access. These security measures, including removing anonymous users, disabling remote root login, and removing the test database, help strengthen the overall security of your MySQL server. Implement these best practices to ensure the confidentiality, integrity, and availability of your MySQL database.