Installing MySQL Database Server on AWS EC2 - Part 2

Introduction

This is the second part of my blog about Installing MySQL Database Server—Community Edition on an AWS EC2 instance. In part one, we encountered a crucial issue where our database server stopped listening on port 3306 for security reasons, which was preventing our backend code from connecting to it. This part will provide the solution to this significant issue.

If you missed part one, you can still check it here.

Now, let's dive into the solution!

Solution

The solution is simple; we only have to change the root user's password and remove the --skip-grant-tables we added in the previous part.

Steps

1. Ensure you have completed the steps in part one of this blog (you can find it here).
2. Connect to MySQL server using:
   mysql -uroot
3. Execute the below SQL statements:
   1. FLUSH PRIVILEGES;
   2. ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
   3. exit;
4. Let's now try to connect the same way we did before:
   mysql -uroot
   You will get the below error:
   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
   That means our solution works!
   Now try to log in with the password you specified in the previous step:
   mysql -uroot -p
   you should be able to log in!
5. Now, we need to remove the --skip-grant-tables we added earlier in the /etc/my.cnf because they are no longer required.
6. Restart mysql:
   sudo systemctl restart mysqld
7. Run
   sudo lsof -i :3306
   to ensure MySQL Server is now listening on port 3306. You should be able to see the process running.
8. Login to MySQL:
   mysql -uroot -p
   and then enter your new password
9. That's it! You have changed the root password, and your instance can now receive requests from your backend code!

Summary

In this blog, we saw how to install MySQL Database Server—Community Edition on AWS EC2 instances and change the root account's password.
Remember to remove the --skip-grant-tables!

References

• How to Reset the Root Password
• Installing MySQL Database Server on AWS EC2 - Part 1