How to Create a MySQL Database in AWS and Manage It, Using MySQL-Workbench?
You’re reading this blog so I am hoping that you already know what is MySQL and what we want to achieve in this blog. So, starting right away with all the steps:
- Create an AWS Account: While signing up it will ask for your credit/debit card, but don't worry you will only be charged after a certain amount of usage of the services your AWS account provides. To avoid any charges check out the billing option after setting up your AWS account so that whenever Amazon starts charging you it will alert you beforehand.
- RDS DB Instance: To create a DB on AWS, we have to make an RDS DB Instance, to do that, there is a Services(Drop-down menu) in the navbar on the AWS management console page. Select the RDS option under Database.
- Create DB Instance: Click on the Create Database option. You will see a page like this:
You can see 6 types of SQL Databases listed there. Select anyone, for now, let’s say we want to create a MySQL DB instance, so select MySQL.
Now, I’ll list the changes that need to be done for the successful hosting of the database so that you can access it from anywhere, eg: a Python script, MySQL-Workbench, etc
- Select Standard Create as done in the image above.
- Select MySQL as the Engine type(or any other from the listed ones, your choice)
3. Select MySQL version(let it be the latest version, which is already chosen by RDS), and click the option of the free tier(for free use, obviously it will have some limits, for more features you have to pay for it)
4. Under settings, Enter the DB instance identifier, anything you want to, and then the main part comes, Enter the master username and password, these 2 will be used to get access to your MySQL database instance that you will be hosting in 2 mins. Enter both and move forward.
5. In the storage options, let it be default 20 GB, more than that may lead to some charges when you start using it.
6. Select the Yes option under Public Access, so that devices outside the VPC can connect to your database. (This is just a beginner-level blog, I'm just showing how to just a basic DB instance, we will cover VPC Security groups and everything in some other blog.)
Everything else remains the same. Hit the Create Database option at the bottom.
It will take some time to create your DB. (I have one DB aws-simplified already, aws-demo is the one I just made.
After it’s done, open aws-demo, you’ll see a window like this,
Voila!, your MySQL DB is hosted now on AWS. Now we’ll see how to manage and interact with this DB.
Interacting with MySQL DB with MySQL-Workbench
MySQL Workbench is a visual tool for database developers. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, etc. MySQL Workbench is available on all operating systems like Windows, macOS, or Linux.
Download it from the link below:
MySQL :: Download MySQL Workbench
MySQL Workbench provides DBAs and developers an integrated tools environment for: Database Design & Modeling SQL…
To interact with your database you first need the endpoint of your DB instance, see the below image to see how to get that:
Copy this link, this will be used to access your database. Now open MySQL-Workbench in your system.
Click the add button first that appears after opening workbench, see the image below for reference,
You will see a dialog box, in which you will be filling the details of your database to connect your database. The master username and password that you entered while creating DB on AWS will come into use now. Enter the master username in front of the username, to enter the password click Store vault, enter your password, and press OK. Keep the port number as mentioned on the AWS management console of your DB. Also, you can keep the connection name anything you want, No need to keep it as same as the DB Identifier name from AWS RDS.
Hit OK. You will be now seeing your connection name mentioned in the workbench like in the image below(name: aws-demo)
Click on aws-demo, if it opens successfully, you are done with connecting with your MySQL DB hosted on AWS.( otherwise there was some problem with the username or password or you did any mistake while following the steps, in that case, you can comment on this blog).
Run any queries now, it will take effect on the DB hosted directly. You can check it by making some tables, printing them using SELECT *. But this doesn't confirm that the same changes are being done on the server too, do one thing, connect with your DB with python(Practice for you, you can refer to W3Schools for that), and then print your DB, you’ll be then satisfied.
This was all for creating a DB instance on AWS and interacting with it. In case of any problems, you can always comment on this blog.