Relational Data migration to AWS Cloud using MySQLdump

Data migration is a critical step when transitioning between cloud platforms. In this blog post, we’ll explore two powerful techniques to effortlessly move data from to on prem SQL data to Amazon RDS .

Now we know there are two services that we can prefer based on the size of the data to move the data.

Technique 1:Using MySQLdump and AWS CLI:

Migrating data from Cloud SQL to Amazon RDS using the mysqldump and AWS CLI method.

1. Export Data using mysqldump:
Use the mysqldump command to extract data from your Cloud SQL instance. This creates a dump file containing SQL statements.

2. Copy to Local Machine:
Securely transfer the dump file from Cloud SQL to your local machine using secure copy protocols.

3. Upload to Amazon S3:
Utilize the power of AWS CLI to upload the dump file to an Amazon S3 bucket, ensuring its availability for the next steps.

4. Configure Amazon RDS:
Set up your Amazon RDS instance with the desired specifications, including VPC, subnet, security groups, and more.

5. Import Data to Amazon RDS:
Execute the SQL statements from the dump file on your Amazon RDS instance, populating the database with your Cloud SQL data.

If this is a local instance on mac or pc with SQL Workbench

Step 1:Find the target table (small size sample customer table) in Mysql database and export it using the table export wizard and save it as CSV File.

Notice we can filter the columns we need and select offset and count

we can also choose to export it either as csv or json and choose delimiter.

Step 2: If this is on prem instance we can export the data using the following comamnd

1. Open a command prompt or terminal on your local machine.
2. Use the following syntax to export a specific table from your MySQL on-premises database:shCopy code

mysqldump -h hostname -u username -p database_name table_name > output_file.sql

Replace the placeholders with actual values:

• hostname: The hostname or IP address of your MySQL on-premises instance.
• username: Your MySQL username.
• database_name: The name of the database containing the table you want to export.
• table_name: The name of the table you want to export.
• output_file.sql: The name of the file where the exported data will be saved (e.g., mytable_export.sql).

3. After running the command, you will be prompted to enter your MySQL password.

Here’s an example command

mysqldump -h localhost -u myuser -p mydatabase customers > customers_export.sql

This command exports the “customers” table from the “mydatabase” database on the local MySQL instance and saves it to a file named “customers_export.sql” in the same directory.

In our case we used below command

->mysqldump learning_db sample_customer_table | gzip > sample_customer_table.sql.gz

Once the file gets created in local we will configure aws cli in mac using command below.

Before this we need to set access key ID and secret access key using IAM role.

Create a S3 bucket and create a destination path to place the file.

Now we can use the below command to paste the file to destination location

command: aws s3 cp sample_customer_table.sql.gz s3://matrixlearningbucket/MySQL_import/aws_cli/

Hurray!! we have successfully exported our compressed database .sql file to s3 location.

Now copy the file directly to your RDS instance:

Unzip your file first and then copy using below command

• aws s3 cp s3://your-bucket-name/your-file.sql s3://your-rds-import-bucket/

1. Load the Data into Amazon RDS: Once you have the SQL dump file on your Amazon RDS instance, you can use the mysql command-line tool to execute the SQL statements and populate the database.
2. For example, if you’re already connected to your RDS instance using the mysql tool, you can run the following command to load the data from the SQL dump file:

• mysql -h your-rds-endpoint -u your-username -p your-database-name < your-file.sql

Replace the placeholders with actual values:

• your-rds-endpoint: The endpoint of your Amazon RDS instance.
• your-username: Your MySQL username for the RDS instance.
• your-database-name: The name of the database where you want to import the data.
• your-file.sql: The name of the SQL dump file you downloaded or copied to the RDS instance.

3. You will be prompted to enter your MySQL password.

This process will execute the SQL statements in the dump file on your Amazon RDS instance, recreating the table structure and inserting the data into the specified database.

By following these steps, you’ll master concepts like VPC, route tables, IP configuration, and more, ensuring a smooth migration experience._

#CloudMigration #DataTransfer #CloudSQL #AmazonRDS #AWS #GCP #DataMigration