RDS is a powerful relational database running on the AWS platform. RDS makes it easy to scale and manage database engines whilst maintaining high availability. Using the AWS console, it’s possible to complete complex administration tasks including automatic backup and automatic failover.

RDS fully supports MySQL databases and it’s therefore not surprising that Winchester Innovation have been asked to provide database migration services from existing MySQL platforms to RDS.

Migrating data to a database is straightforward enough. However, migrating a very large database (~500GB+) with minimal/no downtime is a different story. Our customers require:

  • No data loss
  • Minimal/no downtime
  • Minimal effort

Of course, if your users are comfortable with a generous maintenance period then the migration process is well documented. Equally if the data-set is small then the process can be completed quickly.

But how do you migrate a large data-set with only a small period of maintenance? The following steps relate to a specific architecture and is shown purely as an example.

Migrating a database

RDS-migration-step1

There’s no escaping step 1, at some point we need to take a copy of our database and although tools exist to snapshot a live database most experts would agree that the only safe way to do this is with the database in a read only mode.  This is going to be our longest maintenance window (depending on the size of your data-set) in our example we have provided enough local storage to snapshot the database locally on the server.

Importantly during this phase, we make sure that our MySQL database is using binary transaction logging and is configured for replication to be a master (hopefully it already should be). If not we’ll set that up and place our database into read lock mode ‘FLUSH TABLES WITH READ LOCK;’ and make a note of our snapshot position in the binary logs ‘SHOW MASTER STATUS;’.

We’re now ready to snapshot the database to local storage. As soon as we’re done we remove the database read lock and remove our maintenance window.

RDS-migration-step2

We’re now ready get to start work on migrating our database. The Amazon AWS console guides you through the process of creating and importing data to an RDS instance. Using RDS reduces the workload on our development team and simplifies the solution for our clients.

RDS-migration-step3

Of course, during the period that we’ve setup our new database and ingested our snapshot the live database has fallen out of sync. Before we complete our migration process we’ll need to re-sync the two databases. As the original database is a replication master, we can setup our new RDS database to be its slave. We’ll need to wait for the two databases to resynchronise.

 

RDS-migration-step4

The final step involves a second period of maintenance. This window should be much shorter than the last as we just need to reconfigure our clients to point to the new RDS instance. With the final maintenance windows closed we can remove our original database.

Conclusions

There are a number of factors that influence the decision to migrate data services to RDS. Given that RDS can be more expensive than EC2 hosted MySQL servers it’s not necessarily always the best solution. However, for organisations requiring high availability and minimal overhead for database maintenance RDS can be a good fit.

Migrating an existing large and live database into RDS with minimal down-time is a complex undertaking requiring careful planning and preparation, but can reap big rewards in return.