How to Create a MySQL Database in AWS RDS: A Step-by-Step Guide

Introduction:

Creating a MySQL database in Amazon Web Services (AWS) using the Relational Database Service (RDS) is straightforward. In this guide, we’ll walk through each step in detail to help you easily set up your MySQL database on AWS.

Step 1: Sign in to the AWS Console

• Log in to your AWS Management Console using your credentials.

Step 2: Navigate to RDS

• Once logged in, navigate to the RDS service by clicking “Services” in the top menu and selecting “RDS” under the Database section.

Step 3: Create a Custom Parameter Group

• In the RDS dashboard, click on “Parameter groups” in the left sidebar.
• Click on the “Create parameter group” button.
• Enter a name for your parameter group (e.g., pg-mysql8-v34).
• Select “MySQL” as the DB engine family and provide a description if needed.
• Click on “Create” to create your custom parameter group.

Step 5: Navigate Back to the RDS Dashboard

• Click on “Databases” in the left sidebar to go back to the RDS dashboard.

Step 6: Click on “Create database”

• In the RDS dashboard, click on the “Create database” button to start creating your MySQL instance.

Step 7: Select Use Case

• Choose the appropriate use case for your MySQL database, such as Production, Dev/Test, or Free tier. This selection will affect the default settings and resources allocated to your instance.

Step 8: Specify DB Details

• Enter the following details:
• DB instance identifier: This is a unique name for your RDS instance.
• Master username: Choose a master username to access your MySQL database.
• Master password: Set a strong password for the master user.
• DB instance class: Choose the instance type based on your performance requirements.
• Storage type: Select your database’s storage type (SSD or HDD).
• Allocated storage: Specify the amount of storage space for your database.
• VPC: Choose the Virtual Private Cloud (VPC) where your RDS instance will be launched.

Step 9: Configure Advanced Settings, Backups & Monitoring

• Customize additional settings as needed:
• Parameter group: Select the custom parameter group you created earlier.
• Database name: Specify the name of your MySQL database.
• Port: Set the port number for database connections (default is 3306).
• Backup retention period: Configure how long automated backups should be retained.
• Maintenance window: Specify a time window for scheduled maintenance activities.
• Security group: Define the security group rules to control inbound and outbound traffic to your database.
• Enable automated backups and specify backup preferences. – optional
• Set up monitoring options using Amazon CloudWatch for performance metrics and alerts. – optional

Step 11: Review and Launch

• Review all the configuration settings on the summary page.
• If everything looks good, click on “Create database” to launch your MySQL instance.

Step 12: Wait for Deployment

• AWS will start deploying your MySQL database instance. Wait for the deployment to complete.

Step 13: Access Your MySQL Database

• Once the instance status changes to “Available,” you can access your MySQL database using the endpoint, port, master username, and password you specified during setup.
• Use a MySQL client or command-line tools to connect to your database and start managing your data.

Step 14: Connect to Your EC2 Instance

• Assuming you already have an EC2 instance running and accessible, use SSH or any remote access method to connect to your EC2 instance where you want to access the MySQL database.

Step 15: Install MySQL Client

• If MySQL client is not already installed on your EC2 instance, you can install it using the package manager specific to your Linux distribution. For example, on Ubuntu, you can install it using the command:

sudo apt-get update
sudo apt-get install mysql-client

Step 16: Connect to MySQL Database

mysql -h <RDS_endpoint> -P <port_number> -u <master_username> -p

Example: mysql -h linuxchamp-masterdb.c508yy6y4o5o.us-east-1.rds.amazonaws.com -P 3306 -ulinuxchamp -p

• Replace <RDS_endpoint>, <port_number>, and <master_username> with the actual values you used during RDS instance creation. You will be prompted to enter the master password.

Step 18: Create a Database & Use the New Database

• After connecting to MySQL, you can create a new database using the following SQL command:

CREATE DATABASE mydatabase;
USE mydatabase;

Replace mydatabase with your preferred database name.

Step 19: Create a Sample Table & Insert Some Data

• Now, let’s create a sample table in the database. Here’s an example of creating a simple table named users with columns for id, name, and email:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

• After creating the table, you can insert sample data into it. Here’s an example:

INSERT INTO users (name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]');

Step 20: Verify Data & Exit from MySQL

• To verify that data has been successfully inserted into the table, you can execute a SELECT query:

SELECT * FROM users;

• Once you have completed your tasks, you can disconnect from MySQL using:

EXIT;

Congratulations! You’ve successfully created a MySQL database in AWS using RDS with a custom parameter group. Fine-tune your database settings for optimal performance and start leveraging the power of cloud-based databases for your applications.

Happy data management!