Create and Connect to a MySQL Database with Amazon RDS

#devops #aws #career #database

Overview
Amazon Relational Database Service (Amazon RDS) is a fully managed relational database service provided by Amazon Web Services (AWS). It simplifies the process of setting up, operating, and scaling relational databases in the cloud. With RDS, you can choose from several popular database engines, and AWS takes care of routine database tasks such as backups, patch management, and hardware provisioning, allowing you to focus on your application development.

MySQL is the world's most popular open-source relational database and Amazon RDS makes it easier to set up, operate, and scale MySQL deployments in the cloud. With Amazon RDS, you can deploy scalable MySQL servers in minutes with cost-efficient and resizable hardware capacity.
Amazon RDS for MySQL frees you up to focus on application development by managing time-consuming database administration tasks, including backups, upgrades, software patching, performance improvements, monitoring, scaling, and replication.
Amazon RDS supports MySQL Community Edition versions 5.7 and 8.0 which means that the code, applications, and tools you already use today can be used with Amazon RDS.

Benefits of RDS for MySQL

Easy, managed deployments:
It takes only a few clicks in the AWS Management Console to launch and connect to a production-ready MySQL database in minutes. Amazon RDS for MySQL database instances are pre-configured with parameters and settings for the server type you have selected.
Fast, predictable performance: Amazon RDS provides two SSD-backed storage options for your MySQL database. General Purpose storage provides cost-effective storage for small- or medium-sized workloads. For high-performance OLTP applications, Provisioned IOPS delivers consistent performance of up to 256,000 IOs per second.
Backup and recovery: The automated backup feature of Amazon RDS enables recovery of your MySQL database instance to any point in time within your specified retention period of up to 35 days. In addition, you can perform user-initiated backups of your DB Instance. These full database backups will be stored by Amazon RDS until you explicitly delete them.
High availability and read replicas: Amazon RDS Multi-AZ deployments provide enhanced availability and durability for your MySQL databases, making them a natural fit for production database workloads. Amazon RDS Read Replicas are designed to make it easier to elastically scale out beyond the capacity constraints of a single database instance for read-heavy database workloads.
Isolation and security: As a managed service, Amazon RDS provides a high level of security for your MySQL databases. These include network isolation using Amazon Virtual Private Cloud (VPC), encryption at rest using keys you create and control through AWS Key Management Service (KMS) and encryption of data in transit using SSL.

Prerequisites
An AWS account: This will grant access to the AWS management console.

Step 1: Create a MySQL DB Instance
a. Open the AWS Management Console, in the search Bar type RDS and click on RDS to open the Amazon RDS console.

b. In the Create database section, choose Create database.

c. Choose the MySQL icon, leave the default value of edition and engine version, and select the Free Tier template.

d. Configure your DB instance.

Settings
• DB instance identifier: Type a name for the DB instance that is unique for your account. For this project, we will name it rds-mysql-db
• Master username: Type a username that you will use to log in to your DB instance. We will use admin700
• Master password: Type a password for your master user password.
• Confirm password: Retype your password.

Instance specifications:
• DB instance class: Select db.t2. micro
• Storage type: Select General Purpose (SSD).
• Allocated storage: Select the default of 20 to allocate 20 GB of storage for your database.
• Enable storage autoscaling: If your workload is cyclical or unpredictable, you would enable storage autoscaling to enable Amazon RDS to automatically scale up your storage when needed. This option does not apply to this project.
• Multi-AZ deployment: Using a multi-AZ deployment will automatically provision and maintain a synchronous standby replica in a different Availability Zone. We are not using multi-AZ deployment in this project.

e. In the Connectivity section we will provide information that Amazon RDS needs to launch your MySQL DB instance.

Connectivity
• Compute resource: Choose Don’t connect to an EC2 compute resource.
• Virtual Private Cloud (VPC): Select Default VPC.
Additional connectivity configurations
• Subnet group: Choose the default subnet group.
• Public accessibility: Choose Yes. This will allocate an IP address for your database instance so that you can directly connect to the database from your own device.
• VPC security groups: Select Create new VPC security group. This will create a security group that will allow connection from the IP address of the device that you are currently using to the database created.
• Availability Zone: Choose No preference.
• RDS Proxy: By using Amazon RDS Proxy, you can allow your applications to pool and share database connections to improve their ability to scale. Leave the RDS Proxy unchecked.
• Port: Leave the default value of 3306.

f. Database Authentication: Choose Password authentication from the list of options.

Monitoring
• Enhanced monitoring: Leave Enable enhanced monitoring unchecked to stay within the Free Tier. Enabling enhanced monitoring will give you metrics in real time for the operating system (OS) that your DB instance runs on.

in the Additional configurations section:
Database options
• Database name: Enter a database name. We will use springforth_db for this project.
• DB parameter group: Leave the default value.
• Option group: Leave the default value.
Backup
• Backup retention period: You can choose the number of days to retain the backup you take. For this project, set this value to 1 day.
• Backup window: Use the default of No preference.
Maintenance
• Auto minor version upgrade: Select Enable auto minor version upgrade to receive automatic updates when they become available.
• Maintenance Window: Select No preference.

Deletion protection:
Turn off Enable deletion protection for this project. When this option is enabled, you're prevented from accidentally deleting the database.
Choose Create Database.

Step 2: Download SQL client.
Once the database instance creation is complete and the status changes to available, you can connect to a database on the DB instance using any standard SQL client. In this step, we will download MySQL Workbench, which is a popular SQL client.

a. Go to the Download MySQL Workbench page to download and install MySQL Workbench
Note: Remember to run MySQL Workbench from the same device from which you created the DB instance. The security group your database is placed in is configured to allow connection only from the device from which you created the DB instance.

b. You will be prompted to log in, sign up, or begin your download. You can choose No thanks, just start my download for a quick download.

Step 3: Connect to the MYSQL Database
In this step, we will connect to the database you created using MySQL Workbench.
a. Launch the MySQL Workbench application and go to Database > Connect to Database

b. A dialog box appears. Enter the following:
• Hostname: You can find your hostname on the Amazon RDS console as shown in the screenshot.
• Port: The default value should be 3306.
• Username: Type in the username you created for the Amazon RDS database. For this project, it is 'admin700'
• Password: Choose Store in Vault and enter the password that you used when creating the Amazon RDS database.
Choose OK.

c. You are now connected to the database! On the MySQL Workbench, you will see various schema objects available in the database. Now you can create tables, insert data, and run queries.

Step 4: Delete the DB instance
You can easily delete the MySQL DB instance from the Amazon RDS console. It is a best practice to delete instances that you are no longer using so that you don’t keep getting charged for them.
a. Go back to the Amazon RDS console. Select Databases, choose the instance that you want to delete, and then select Delete from the Actions dropdown menu.

b. You are asked to create a final snapshot and to confirm the deletion. For our example, do not create a final snapshot, acknowledge that you want to delete the instance, and then choose Delete.