How to Access AWS RDS From MySQL Workbench?
One can easily handle and do operations on AWS RDS from an AWS server, but what if you want to access the database from your system or your MySQL workbench? In this article, we will understand how can we establish such a connection.
Generally, when we deploy an application in the AWS EC2 server we create a VPC (Virtual Private Cloud) in which we have a subnet and inside that, we deploy our server and application, and then we connect it to AWS RDS (which has no public access). Let’s demonstrate how to do this:
Note: To carry out this practical, you will have to install MySQL Workbench and OpenVPN (download link: https://openvpn.net/community-downloads/) in your system.
Step1: Login to your AWS Console → Choose a region (For example Mumbai) → In the console page, search for as follows, VPC → Go to the VPC page → Click on “Create VPC” → Click on VPC only → Give a name to your VPC (my-vpc), IPv4 CIDR range(10.0.0.0/16), Tenancy-default → Click on “Create VPC”.
Step 2: Search subnet → Click on “Create subnet” → Fill in the details (Choose above created VPC, give a Name, Availability zone, and CIDR range-10.0.1.0/24) → Click on create a subnet.
After creating, click on the created subnet assign → Click on enable public IP.
Step 3: Go to the internet gateway, click on create internet gateway → Give it a name and create → click on created gateway (Actions → attach with VPC → give the above created VPC).
Step 4: Go to route table → Click on create route → Fill in the details (Give it name and choose VPC created on step-1) → Click on create.
Step 5: Add a route entry for Internet gateway → Select Route table → Go to Routes → Click Edit Routes → Add Route (give the internet gateway created) → Save.
Associate route table with the subnet→ Select Route table → Subnet Associations → Edit Subnet Associations → Select above created Subnet → Save.
Step 6: Now launch an OpenVPN server: Search OpenVPN server→ Fill in the details (Type: t2.micro, Network – myvpc (created in step1), Storage: Default, Tags – Name: give a name, Security: Allow port 22 for your own IP and All TCP and ICMP-IPv4 from sources, Key pair: your existing key pair or create new if you don’t have an existing key pair) → Launch instance.
Step 7: Now SSH into the server → Give the key pair → It will ask to enter yes for agreement → write yes and press enter.
Step 8: Now you will be asked to log in as OpenVPN. SSH again root as “openvpnas”.
Step 9: After that, give the following command.
$ sudo passwd openvpn
You will be asked to give your OpenVPN a password. Re-enter the password again to confirm.
Step 10: Save the Admin and Client URL for further use:
Now, the OpenVPN server setting is done. We need to create a database in AWS in order to access it through MySQL Workbench.
Step 11: Go to AWS console → Search RDS → Click on Create Database → In database Creation Method, Choose Standard Create → For Engine options, Choose MySQL
Step 12: In Template: Choose Free Tire → Give the database a name. Give a password to use for authentication.
Keep everything as it is. Set the maximum storage threshold to 25 GB.
Step 13: Choose a VPC → In public access, set it to No (Generally databases are not given public access).
Step 14: For authentication – Choose Password based. In an additional setting, give initial database name, this will create an initial database inside (Here we created gfg).
Step 15: Enable automated backups → Give backup retention policy: 7 days. The rest of other things will be as it is until and unless you need some customize setting do not change it. Click on create (wait for some time until it is created)
Step 16: Now, after being created, click on the database name → copy the endpoint, and save it in notepad.
Step 17: Go to Open VPN app, paste the client URL you copied in step 9 → Click Next → Click Accept.
Step 18: Give username openvpn and password you set in the openvpn server in step 8 → Click Import → Click Accept.
Step 19: To enable open vpn → click on that enabling button → Enter the password again.
Step 20: Go to MySQL workbench: Click on + sign to add connection → Give connection a name → Give the endpoint in hostname. Give the connection a name → port: 3306 → Give username: admin → Click OK. Give a password set by you while configuring.
Now you can see the initial database you created in step13.
Please Login to comment...