Step-by-Step Guide: Access MySQL Instance using Workbench and Perform CRUD Operations in AWS RDS

Step-by-Step Guide: Access MySQL Instance using Workbench and Perform CRUD Operations in AWS RDS
Prerequisites:
• An AWS account (Free Tier eligible).
• MySQL Workbench installed on your local machine.
• Basic knowledge of SQL and AWS RDS concepts.
Step 1: Create a MySQL Instance on AWS RDS
1. Log in to AWS Console:
o Go to the AWS Management Console: https://aws.amazon.com/console/.
2. Open RDS:
o From the AWS Console, search for RDS and open the RDS dashboard.
3. Create a Database:
o Click on Create database.
o Select MySQL as the engine.
o Choose Free tier under templates to avoid charges.
4. Configure Database Settings:
o DB instance identifier: Name your instance (e.g., mydb-instance).
o Master username: Set a username (e.g., admin).
o Master password: Set a secure password or use an auto-generated one.
5. Additional Settings:
o Under Database options, set the Initial database name as mydb.
6. Backup & Maintenance:
o Leave the settings at default (you can configure them if needed).
7. Connectivity & Security:
o Choose the VPC as default.
o Under Additional connectivity configuration, select an existing EC2 Security Group or create a new one to allow inbound traffic. Ensure the port is set to 3306 (default MySQL port).
8. Security Group Inbound Rules:
o Go to the EC2 Security Groups section in the AWS Console.
o Edit the inbound rules to allow MySQL/Aurora and set the Source as Anywhere (0.0.0.0/0).
9. Create the Database:
o Click Create database. It will take a few minutes for the instance to be created.
10. Get the RDS Endpoint:
o Once the instance is ready, go to the Connectivity & Security tab.
o Copy the Endpoint. This will be used to connect to the MySQL instance using MySQL Workbench.
Step 2: Install MySQL Workbench
1. Download MySQL Workbench:
o Go to https://dev.mysql.com/downloads/workbench/ and download the appropriate version for your operating system.
2. Install MySQL Workbench:
o Follow the installation prompts to complete the installation.
Step 3: Connect to the MySQL Instance using MySQL Workbench
1. Open MySQL Workbench:
o Launch MySQL Workbench on your local machine.
2. Create a New Connection:
o In the top-left corner, click on the + icon to create a new connection.
3. Configure the Connection:
o Connection Name: Give your connection a name (e.g., AWS RDS MySQL).
o Hostname: Paste the RDS Endpoint you copied earlier.
o Port: Ensure it is set to 3306.
o Username: Enter the username you set during RDS instance creation (e.g., admin).
o Password: Click on Store in Vault... and enter the password you set for the RDS instance.
4. Test the Connection:
o Click on Test Connection. If the connection is successful, you'll see a confirmation message.
o Click OK to save the connection.
5. Connect to the Database:
o In the list of connections, select the one you just created and click on it to connect.
Step 4: Perform CRUD Operations in MySQL Workbench
Once connected, you can perform SQL queries to create and manage your database. Here's how to perform CRUD operations:
1. Create a Database (If not already created)
sql
Copy code
CREATE DATABASE mydb2;
2. Select the Database:
sql
Copy code
USE mydb2;
3. Create a Table:
sql
Copy code
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
4. Insert Data (Create):
sql
Copy code
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com');
5. Retrieve Data (Read):
sql
Copy code
SELECT * FROM users;
6. Update Data (Update):
sql
Copy code
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;
7. Delete Data (Delete):
sql
Copy code
DELETE FROM users
WHERE id = 2;
________________________________________
Step 5: Clean Up Resources
After completing your work, it's a good practice to clean up the resources to avoid unnecessary charges:
1. Delete the RDS Instance:
o Go back to the AWS RDS Console.
o Select your RDS instance and choose Delete.
o Optionally, you can create a final backup before deletion.
2. Remove Security Groups:
o If you've created a new security group, you can delete it from the EC2 section in the AWS Console.
________________________________________
Conclusion
The steps to create an RDS MySQL instance on AWS, connect to it using MySQL Workbench, and perform basic CRUD operations. Now you can build more complex queries or manage your database as needed.

Видео Step-by-Step Guide: Access MySQL Instance using Workbench and Perform CRUD Operations in AWS RDS канала VC Tech Education