Upgrading Amazon RDS instance from MySQL 5.5 to MySQL 5.6

Question

With a large data set and a real time system, halting that applications using RDS for a mysqldump from a 5.5 instance and loading that data with the mysql command is impractical.

In the Amazon blog http://aws.typepad.com/aws/amazon-rds/page/2/ on July 1, 2013 Jeff Barr posts "Upgrading an existing database instance from MySQL 5.5 to MySQL 5.6 is not currently supported. However, we intend to provide this functionality in the near future."

7 months later, I don't see any answer from them. I'm looking for options to have a no outage or few minute outage to have an upgrade of MySQL 5.5 to MySQL 5.6 in the RDS environment.

Normally for an upgrade like this I make a MySQL 5.6 replica from a MySQL 5.5 master and then shutdown the applications, change the application MySQL connect to the 5.6 server, and restart the applications. Replicating an RDS MySQL 5.5 instance to a different version RDS replica or to an external replica is not an option in RDS.

What options have worked for people?

Answer 1

I've used a spot instance of a very large instance type in the same availability zone. It took an 8 core instance that had an SSD drive, and made the export. It did take some work as the data set was large, and the spot instance terminated. However I was able to succeed by upping the price of the spot instance and making a RAID0 of the available SSD space.

There are multiple ways to do this; however I did this. - launched an m3.2xlarge spot instance ubuntu 12.04LTS 64 bit instance - logged in then became user root - unmounted /mnt - used apt-get to install mdadm (accepted defaults) - used sudo mdadm --create -l0 -n2 /dev/md0 /dev/xvdb /dev/xvdc - used sudo mkfs.ext4 /dev/md0 - mounted the partition to /mnt with sudo mount -t auto /dev/md0 /mnt - used apt-get to install MySQL client - updated the security on the old RDS to allow the new instance access - stopped the application from updating the old RDS - performed the mysql dump to /mnt - launched the new RDS with the parameters that I wanted in the same security groups - used mysql client to load the databases - then updated the application to use the new database

It will depend on your DB size and the conditions that day to see how long this takes. The spot instance did cost money and I also had to run two RDS instances at a time. However this was a feasible path and I was able to get things moving.