Securing your MySQL server is essential to protect your data and prevent unauthorized access. In this article, we will walk through the steps to ensure your MySQL server is in Amazon Linux 2.

First, updating your system to the latest version of MySQL is important. This will ensure that any security vulnerabilities are patched and your server is as secure as possible. To update MySQL, run the following command:

sudo yum update mariadb-server

Next, we will set a strong root password for the MySQL server. The root user has full access to all databases and tables, so it is crucial to use a solid and unique password for this user. To set a new root password, run the following command:

sudo mysqladmin -u root password newpassword

It is also a good practice to remove any unnecessary users or accounts that have been created. To view all users and their privileges, run the following command:

mysql> SELECT User, Host FROM mysql.user;

To delete a user, use the following command:

mysql> DROP USER 'username'@'host';

Another important step in securing your MySQL server is to limit access to your databases. By default, MySQL allows all hosts to connect to the server. To limit access, you can use the ‘GRANT’ command to specify which hosts and IP addresses are allowed to connect to the server. For example, to allow only the localhost and IP address 192.168.1.100 to connect to the server, use the following command:

mysql> GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO 'username'@'192.168.1.100' IDENTIFIED BY 'password';

It is also important to disable remote access to the MySQL service by editing my.cnf file. To do this, open the file with your text editor and add the following line:

bind-address = 127.0.0.1

This will prevent any remote connections to the MySQL server.

You should also consider enabling logging to track any suspicious activity on your MySQL server. By default, MySQL does not log any activity, so you will need to enable it by adding the following lines to your my.cnf file:

log-error = /var/log/mysql/error.log
log-queries-not-using-indexes = 1
long_query_time = 2

Finally, it is important to keep your MySQL server software and operating system updated with the latest security patches. This will ensure that any known vulnerabilities are patched and your server is as secure as possible. To update your operating system, run the following command:

sudo yum update

In conclusion, securing your MySQL server in Amazon Linux 2 is crucial to protect your data and prevent unauthorized access. By updating your system, setting a strong root password, removing unnecessary users, limiting access, disabling remote access, enabling logging, and keeping your server updated, you can ensure that your MySQL server is as secure as possible.