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!!
Comments are closed.