Access Mysql Database hosted on AWS EC2

To access the database, need to set up two things.

Step 1: add rule to the security group attached to the instance

1. Edit inbound rule of the security group
2. add a rule. Type: MYSQL/Aurora Soure: custom such as 137.229.127.0/24

Step 2: update privilidge of Mysql database

1. log into the database as root or admin

2. #works for mysql 5.7.8 and up. To change a user's ip
     RENAME USER 'teau'@'51.27.292.63' TO 'teau'@'125.256.111.%';
     # for new user, need to grant permission

3. FLUSH PRIVILEGES;

Trouble shooting

If do not do the step one, most likely will see a connection timeout

If do not do the second step, will see something like

[MySQL][ODBC 8.0(w) Driver]Host '*.*.***.**' is not allowed to connect to this MySQL server
Unable to connect to the MySQL server "***.***.**.**". Check that the server is running and that you have
access privileges to the requested database.

Some useful Mysql commands

select version();

select user, host from mysql.user;

SHOW GRANTS FOR 'leo'@'52.30.111.16';