Migrating MySQL to Amazon RDS and Google Cloud SQL: Real-World Experience from Large Projects – ITFROMZERO

Table of Contents

Background: When Should You Say Goodbye to Self-Managed MySQL?

Building your own MySQL on a VPS might sound cost-effective, but once your database hits the 50GB+ mark, things start to get “suffocating.” I once stayed up all night because a MySQL cluster on EC2 hit 98% Disk I/O saturation. The web app was nearly paralyzed while I struggled with manual replication scripts. Self-management isn’t just time-consuming; it carries a high risk of data loss if hardware fails.

Managed Database services like Amazon RDS or Google Cloud SQL solve these issues completely. By moving to the Cloud, you get:

Hands-free Operations: Automated backups and periodic OS patching.
High Availability (HA): Multi-AZ setups enable self-recovery in less than 60 seconds if a zone fails.
Flexible Scaling: Upgrade from 2 vCPUs to 16 vCPUs in minutes, without reinstalling the entire server.

However, moving hundreds of gigabytes of data without service interruption is a difficult challenge. Here is the hard-earned experience I’ve gathered from several real-world migration projects.

Preparing the Environment: Don’t Skip the Network Check

The most common mistake is running the migration command directly from a local machine over Wi-Fi. If the connection flickers for just one second, your three-hour data dump will be ruined. Always use a Jump Server (EC2 or Compute Engine) located in the same Region as the target database to coordinate the process.

1. Install Necessary Tools

Ensure the mysqldump version on the intermediary server is equal to or newer than the current database version. On Ubuntu, perform a quick installation:

sudo apt update && sudo apt install mysql-client -y

2. Establish Connectivity (Networking)

“Connection Timeout” errors account for 80% of failed migrations on the first attempt.

AWS RDS: Add an Inbound Rule to the Security Group, allowing port 3306 from the source server’s IP.
Google Cloud SQL: Add the source server’s IP to “Authorized Networks.” For better security, use the Cloud SQL Auth Proxy to create an encrypted tunnel.

3. Optimize Configuration for Speed

Before starting, increase the max_allowed_packet value to 128MB or 256MB on both ends. This helps handle large data rows (Longtext, Blob) more smoothly. Additionally, use the --single-transaction flag to dump data without locking active tables.

Detailed Migration Process for Each Platform

Method 1: Migrating to Amazon RDS using the Pipe Method

For small databases (under 20GB), you can push data directly from the source to the destination without creating an intermediate file. This saves disk space on the Jump Server.

mysqldump -u [source_user] -p[source_password] --databases [db_name] \
--single-transaction --compress --order-by-primary --set-gtid-purged=OFF \
| mysql -u [rds_user] -p[rds_password] -h [rds_endpoint] [db_name]

Note: The --set-gtid-purged=OFF flag is extremely important when migrating to RDS to avoid Superuser permission errors.

Method 2: Migrating to Google Cloud SQL via Cloud Storage

For large databases (over 100GB), pushing directly over the internet is risky. Break the process down using Google Cloud Storage (GCS) to ensure stability.

Export the data:

mysqldump -u root -p --databases my_db --single-transaction --routines --triggers > backup.sql

Upload to GCS:
Use gsutil to leverage Google’s internal network:
```
gsutil cp backup.sql gs://your-bucket-name/
```
Import into Cloud SQL:
Go to the Console, select Import, and point to the file in the bucket. Don’t forget to grant the Storage Object Viewer role to the Cloud SQL Service Account before running this command.

Post-Migration Checks: Costly “Silly” Mistakes

Don’t rush to point your Domain to the new server as soon as the import command reports success. Spend at least 30 minutes performing the following steps:

1. Data Integrity Audit

Don’t just count the total number of rows. Check the current AUTO_INCREMENT values of important tables to ensure no records were skipped or lost.

2. The Timezone Lesson

This is a mistake I once made that caused all revenue reports to be off by 7 hours. RDS usually defaults to UTC. If your application defaults to UTC+7, remember to update the time_zone in the Cloud provider’s Parameter Group.

3. Monitoring Real-world Performance

Enable Performance Insights (AWS) or Query Insights (GCP). After migration, default Cloud configurations may differ from your old server. Check the innodb_buffer_pool_size parameter; if it’s too low, your application will run significantly slower even if the CPU is idle.

A successful database migration isn’t just about typing the right commands. It’s about thorough preparation and the ability to control the smallest details. Good luck with your data “move”!