So I start my blogging journey with this post, feedback on this will be much appreciated. I've recently moved from developing legacy applications hosted on-premise to developing on a cloud based environments. So far I have worked on both GCP and Amazon AWS, although the exposure to GCP is rather limited.One particular issue I've seen many people struggle with is getting their EC2 instances setup for MYSQL and then connecting them from MYSQL Workbench. So here is a tutorial that will help you get started.
Once your EC2 instance is up and running the first thing you need to do is upgrade the default software package provided on the instance that you just managed to fire up. Start by switching to the root using the command
sudo su
Then follow this up by first installing MYSQL on the instance by firing
apt install mysql-server
Once the installation is complete check if the mysql service is running using
systemctl status mysql
The next part is making the necessary configuration for allowing external connections to this EC2 database instance. This can be achieved by
1) Setting up the root user to connect from workbench by changing the authentication method from auth_sockect(default) to mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
or
2) Creating another user simply for external users, please note that the case is important here.
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpwd';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
Now the EC2 side part is pretty much done. Download the MYSQL Workbench and click on the (+) to add a new connection
The pop screen which appears looks something like this
In the screen you need to make the following configurations
Connection Method:- Standard TCP/IP over SSH
SSH Name :- The public IP of your Amazon EC2 instance
SSH Username :- Choose as per your EC2
SSH Password :- No change needed
SSH Key File :- This is important, you need to use the .PEM file downloaded while creating the EC2 instance and not the .PPK file because while connecting from putty we use the .PPK, do not get confused.
MYSQL Hostname :- 127.0.0.1
MYSQL Username :- Depending on what approach you took it can be either root or the newuser you created
MYSQL Password :- The password you created. Hope it's a strong one.
Default Schema :- Leave blank
Click on Test Connection and you should be all set
In this step if you get an access denied error I am pretty sure you did one of the following 3 things wrong(been there done that)
1) Your user creation and grants have not been done correctly. If you are using root user you can query the mysql.user table and check if the root user has mysql_native_password set.
2) You are not using the .PEM file but .PPK instead
3) Your password isn't correct.
Happy starting your AWS journey :)
Top comments (0)