Setting up MYSQL on UBUNTU EC2 and hassle free connectivity from MYSQL Workbench
Tags: aws, database, ubuntu, python
Published: 2019-09-24 05:55:24

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](https://docs.aws.amazon.com/lumberyard/latest/userguide/linux-set-up-ubuntu-amazon-ec2.html) 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

![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/xpbongvzo61c2z11yygb.JPG)

The pop screen which appears looks something like this

![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/ksy0d0ly2gjulv904km1.JPG)

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![Depending on what EC2 instance you've used a list of usernames can be found here](https://thepracticaldev.s3.amazonaws.com/i/3yll6haxdw3npy8rqlpb.JPG)
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
![Test Connection](https://thepracticaldev.s3.amazonaws.com/i/adomtzu0ioplh8eabjtj.JPG)

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 :)