Importing MySQL DB to RDS instance AWS

» First and foremost, let us use phpMyAdmin to connect to your MySQL database and carry out of exporting your data as an .sql file.

» Once you have completed this step, we can proceed to AWS RDS (Relational Database Service) to set up a MySQL instance.

There are two methods for creating a database in RDS:

  • Standard create
  • Easy create.

Let us choose Easy create with a Free tier database instance size to optimize costs.

Note: When you choose for Easy create, you will see the Master password. On the other hand, with Standard create, it's advised to use Secret Manager for added security when working with your RDS instance.

Note: In Amazon RDS, specifically in the free tier, there are constraints on renaming or changing attributes of a DB instance or other resources. Keep in mind that free tier services may have limitations for resource optimization and cost control.

» If you are using MySQL for your RDS instance, you can manage your databases using tools like MySQL Workbench, DBeaver, or any other program that works with MySQL.

» Go to Database > Connect to Database, then adjust the configuration from AWS RDS MySQL to establish a direct connection on your local machine using MySQL Workbench.

» Take advantage of the Connectivity & Security tab by clicking on 'flagtick-dev-free-tier-instance,' which we created earlier. This provides essential information, including:

  • Endpoint: flagtick-dev-free-tier-instance.xxxxxxxxxxxx.ap-southeast-1.rds.amazonaws.com
  • Port: 3306
  • Amazon Resource Name (ARN): arn:aws:rds:ap-southeast-1:xxxxxxxxxxxxxxx:db:flagtick-dev-free-tier-instance.

Use this information to configure MySQL Workbench, and don't forget to grant permission for the IAM role to access the resource.

» Additionally, you need to select Publicly accessible in the Public access section of the Additional Configuration tab when modifying the RDS instance.

» You can enhance security by using Secure Socket Layer (SSL) or Transport Layer Security (TLS) from your MySQL Workbench application to encrypt the connection to a managed database in Amazon RDS running MySQL. Each DB engine has its own process for implementing SSL/TLS. To obtain a certificate bundle containing both intermediate and root certificates, download it from here.

» After successfully converting the file rds-combined-ca-bundle.p7b, proceed to install it on Windows and choose Trusted Root Certification Authorities during the installation process.

» Let us try connecting again with MySQL Workbench after installing SSL. Here are our results:

» After establishing a new connection named MySQL Engine AWS, navigate to Database > Connect to Database. Here, wait for all configurations to load.

» In MySQL Workbench, click on the Open SQL Script icon, as shown here. Take a moment to review all data and schema in the .sql file before proceeding with the next action.

Note: If you encounter issues related to the database not existing or if the query to create/update the database is not found, set up two questions before executing or running all data from the .sql file.

CREATE DATABASE admin_flagtick;
USE admin_flagtick;

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
-- Host: localhost:3306
-- Generation Time: Dec 23, 2023 at 08:29 AM
-- Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1
-- PHP Version: 8.2.12

SET time_zone = "+00:00";

» Click on Schemas in MySQL Workbench, navigate to the database named admin_flagtick. Here, you can get an overview of all the imported tables and query data using SELECT statements.

» You need to run the commands to define and modify the structure of the table, specifically focusing on the id column as the primary key with auto-increment functionality as below:

alter table <your table> modify column id int;
alter table <your table> add primary key (id);
alter table <your table> modify column id int auto_increment;

» Furthermore, you need to go to Edit --> Preferences and proceed to permanently disable safe update mode in MySQL Workbench. This action will help you avoid the error 'You are using safe update mode' in the Workbench tool.

Note: Ensure that 'Safe Updates' (which rejects UPDATEs and DELETEs with no restrictions) is unchecked.

create AWS Postgres SQL RDS Configure nodejs lambda connect amazon RDS postgres
You need to login to do this manipulation!