Migrating a MySQL database to Amazon RDS without service disruption

Shifting a live MySQL workload into Amazon RDS is one of those jobs that looks straightforward until you realise how much application state lives in those connection pools. For teams running ecommerce, booking platforms or SaaS products out of Sydney or Melbourne, even a few minutes of downtime can blow through SLAs and frustrate customers across multiple time zones. Getting the move right means treating it as a project with discrete phases rather than a single weekend script.

Australian operators also need to weigh data sovereignty and the Australian Privacy Principles before anything leaves the on-premises datacentre. Choosing the Sydney region (ap-southeast-2) keeps data residency local and delivers sub-50-millisecond latency from most CBD offices. Once the legal and architectural boxes are ticked, the real work begins with planning, replication and a carefully staged cutover.

Pre-migration assessment and planning

The first task is a proper inventory of what is actually running on the source MySQL server. That means not just the schema but the storage engine mix, average row size, index depth, and the read and write throughput during peak business hours. Tools like pt-mysql-summary or MySQL's own performance schema give you the numbers you need to size the new RDS instance correctly. Australians running 24/7 platforms often see peak traffic around 6pm to 10pm AEST, so sample during that window rather than at 9am on a Tuesday when everyone is still on their first coffee.

It is also worth mapping out every application that touches the database. Microservices talk to databases in surprising ways, and a forgotten reporting job that fires off a 50GB query at 2am can ruin a carefully planned migration. During this stage, Karl's consulting notes are a useful reference for the kind of dependency mapping that prevents surprises on cutover night.

Choosing the right RDS configuration

RDS offers a dizzying menu of instance classes, storage types and high-availability options. For most Australian mid-sized workloads, a db.m6g or db.m7g instance running MySQL 8 with General Purpose gp3 volumes hits a sensible balance between price and IOPS. If your workload is write-heavy, provisioned IOPS (io1 or io2) is worth the premium, particularly for OLTP systems handling payment processing or inventory updates.

Multi-AZ deployment is essentially mandatory for anything you cannot afford to lose, even though it roughly doubles your storage cost. The standby in the second Availability Zone protects against datacentre failures, which is relevant given the occasional regional incidents that have hit ap-southeast-2 in recent years. Parameter groups should be tuned carefully, especially for innodb_buffer_pool_size, max_connections and character_set_server, to match the on-premises baseline as closely as possible.

Preparing the source MySQL instance

Before any replication begins, the source database needs to be in a clean state. Run OPTIMIZE TABLE on fragmented tables, ensure all tables use InnoDB by converting any lingering MyISAM tables, and verify that row-based binary logging is enabled. A typical pitfall is discovering during cutover that a single MEMORY-engine table is being used for session caching and cannot be replicated cleanly.

Schema compatibility is the next checkpoint. RDS MySQL supports most extensions but blocks certain administrative features like SET GLOBAL and the loading of user-defined functions from arbitrary paths. Running mysqlcheck --check-upgrade against a staging snapshot flags any incompatibilities early. It is also a good moment to remove or archive historical data that does not need to come across to the new instance, shrinking the migration window and reducing storage costs.

Establishing replication and initial sync

There are two common paths to get data into RDS: AWS Database Migration Service or native MySQL replication. DMS is the more polished option, handling continuous change data capture, schema conversion and built-in validation. For straightforward migrations where source and target MySQL versions are compatible, native replication using mysqldump for the initial baseline followed by binary log shipping is reliable and gives you more control.

The initial sync of a multi-hundred-gigabyte database can take many hours, so it should run during a quiet period. A typical Sydney business might start the dump on a Sunday evening, when retail traffic has died down and before Monday morning orders start hitting the system. Throughout the sync, monitor replication lag with SHOW REPLICA STATUS and keep an eye on binlog retention on the source. If the source runs out of binlog space, replication breaks and you start over.

Cutover window and DNS routing

Cutover is the moment the team holds its breath. The play is straightforward in principle: stop writes on the source, confirm replication has caught up, promote the RDS replica, redirect application connections, and verify reads and writes work end-to-end. In practice, the trick is in the details. Applications need to honour connection retry logic, and any DNS caching at the application tier must respect the new TTL.

Route 53 weighted records or CNAME aliases pointing at the RDS endpoint let you shift traffic in seconds rather than waiting for client-side cache expiry. For extra safety, some Australian teams run the cutover in the early hours AEDT, around 4am, when local traffic is at its lowest and the team is fresh from a Sunday rest. The whole exercise typically takes 15 to 45 minutes for a well-rehearsed run, which is far more palatable than a full weekend outage.

Post-migration validation and monitoring

Once traffic is flowing into RDS, the validation phase begins. Row-level checksums across critical tables confirm that no rows were dropped or duplicated during the transition. Performance baselines taken before the migration are compared against the post-migration numbers; minor regressions in latency are common and usually tunable via parameter groups.

CloudWatch metrics give you the basic health view, but pairing them with a tool like Percona Monitoring or Datadog gives deeper visibility into query-level performance. If you are documenting the runbook for future team members, the SharePoint documentation library pattern is a clean way to keep runbooks, parameter group settings and rollback procedures versioned and searchable from the company intranet.

Rollback planning and contingency

Even with the best preparation, things sometimes go wrong on cutover. The safety valve is a tested rollback path back to the source MySQL instance, kept warm until you are confident the new RDS deployment is stable. Automated RDS snapshots taken just before cutover let you revert the target if needed, but reverting application traffic is what actually matters.

Keep the source database online and writable in a paused state for at least 48 hours after migration. If a critical bug appears, you can pause replication on RDS, redirect writes back to the source and resume from there. The cost of keeping that source instance up for a few extra days is a small price compared with the alternative of a multi-day outage during peak retail season.

Cutover day checklist

• Stop scheduled batch jobs and crons on the source
• Confirm SHOW REPLICA STATUS shows zero lag on RDS
• Snapshot the source instance and the RDS target immediately before promotion
• Update Route 53 records and lower TTL to 60 seconds
• Restart application services in a staggered manner
• Run smoke tests against the new RDS endpoint

Post-migration hardening tasks

• Review CloudWatch alarms and adjust thresholds for the new baseline
• Enable Performance Insights and configure digest storage
• Rotate database credentials used during migration
• Update connection string management and secret storage
• Schedule the first automated RDS snapshot
• Decommission the source instance after a seven-day observation period

For teams navigating a similar move into AWS, talking through the architecture and cutover plan with someone who has done it before saves a lot of late-night debugging. Reach out to discuss infrastructure planning, RDS sizing or a broader cloud migration engagement tailored to the realities of operating from Australia.