Migrating Data from AWS RDS to LightSail Relational Database: A Step-by-Step Guide

Starting the process of moving data from the Relational Database Service (RDS) of Amazon Web Services (AWS) to LightSail entails a number of meticulously carried out stages. We’ll lead you through the manual migration process in this tutorial, making sure it’s user-friendly and seamless.

Step 1: Getting to the RDS-Connected EC2 Instance
Log in to the EC2 instance that is linked to your RDS database to start the migration process. For an easier experience, use any FTP program, such as WinSCP. Once WinSCP has been installed, launch the program and connect using the hostname, port number, username, and password that are needed.

Step 2: Configuring MySQL Bind Host
You must make changes to the mysql.cnf file in /etc/mysql/conf.d in order to enable effective data transmission. Use these commands to open the file:

# Edit the mysql.cnf file
sudo nano /etc/mysql/conf.d/mysql.cnf

Update thebind value to match the host endpoint. In AWS RDS, the endpoint typically ends with ".rds.amazonaws.com." Save the changes and return to the command line.

Step 3: Exporting Data from RDS
Open a directory such as /home/ubuntu that has write rights. Execute the subsequent command:

# Export data from RDS to a self-contained MySQL dump file
sudo mysqldump -u dbuser -p -h host.com dbname > exportfile_name.sql

This creates a self-contained MySQL dump file, which can also be imported via Workbench. Use WinSCP to download the dump file to your local PC.

Step 4: Using the CLI to Create a LightSail Relational Database
To establish a relational database, use the LightSail CLI. Run the following command in the Lightsail Cloud Shell:

# Example command for creating a LightSail relational database
aws lightsail create-relational-database `
  --relational-database-name mydb `
  --relational-database-blueprint-id <blueprint-id> `
  --relational-database-bundle-id <bundle-id> `
  --master-database-name <master-db-name> `
  --master-username <master-username> `
  --master-user-password <master-password> `

Step 5: Retrieving Database Information
Use the following command to retrieve database information via the CLI:

# Example command for getting database information
aws lightsail get-relational-database --relational-database-name mydbname

Gather metadata such as host endpoint, port, database name, and username.

Step 6: Accessing AWS MySQL Server From Remote PC
You can either add the path to PowerShell or open it in the MySQL path. Execute the following command:

# Example command for accessing MySQL CLI for LightSail
mysql -h host.rds.amazonaws.com -u db_user_name -p

Enter the password when prompted. Once inside the MySQL CLI, execute commands likeshow databases to view available databases and use db to select an active database.

SHOW DATABASES;
USE DB_NAME;

Step 7: Data Import and Upload
Use the following command to upload the exported SQL file:

# Example command for uploading and importing data
source /path/to/contained/sql/file.sql

Monitor the import process and wait for completion. Ussually you get an output like this below

Step 8: Using Workbench to Verify
To verify the migration’s success, launch Microsoft MySQL Workbench. Connect using your password and LightSail database endpoint. Check to see if the data and tables were correctly imported.

A mindful and straightforward to operate data migration from AWS RDS to LightSail Relational Database is guaranteed by this comprehensive manual. These instructions will help you move your data to your new database environment with ease. Make sure to use the example commands that are provided.