Connecting to a remote MYSQL instance on a AWS EC2 instance

If you are having a “self-managed” MySQL EC2 instance, which can be connected to other EC2 instances in the same VPC or even other remote machines. In order to do this, there are a few configuration changes you need to carry out.

Here are the steps:

1. Connect to the remote MySQL remote EC2 instance. – On default you can access the MySQL using “root” user. However it is not advisable to access a MySQL instance remotely using the “root” user for security reasons.

[P.Note: Please make sure the Port 3306 is added to the inbound rules in the EC2 Security Group prior attempting this.]

2. Change the <bind-address> parameter to 0.0.0.0, allowing the access to all remote addresses. This needs to be changed in the /etc/mysql/mysql.conf.d/my.cnf file.

3. Restart the MySQL instance

mysql-ec2-instance>> sudo /etc/init.d/mysqld restart

4. Therefore, create a new MySQL user. – For this, you are required to sign in to the MySQL and execute the following command(s).

mysql-ec2-instance>> mysql -u root -p<root-password>

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'user123';

mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'user123';

mysql> GRANT ALL PRIVILEGES ON *.* to user@localhost IDENTIFIED BY 'user123' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* to user@'%' IDENTIFIED BY 'user123' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

mysql> EXIT;

5. Now exit from the EC2 MySQL instance and try to log into the MySQL EC2 instance from your local machine.

your-local-machine>> mysql -h <ec2-public-dns-name> -u user -puser123

If all fine, you should be able to sign in to the remote EC2 instance without any issue!!