---
title: "Minimize Downtime for MySQL Upgrades: How to Work Around RDS Limitations"
description: "Techniques for reducing service interruptions during database maintenance and version transitions.	"
---

# Minimize Downtime for MySQL Upgrades: How to Work Around RDS Limitations

Techniques for reducing service interruptions during database maintenance and version transitions.	

If you've worked on a web application backed by a relational database, you will understand how frequently the underlying schema changes. You might add or remove columns, change the type of existing columns, or add new indices as your business evolves.

While modern ORMs make such schema changes fairly simple, the resulting migrations on our production database at Zapier was becoming painful. We were using MySQL 5.5, and `ALTER` statements on 5.5 involve a "lock and copy" of the entire table. With a large database having several hundred GB of data, this meant an interruption to our production traffic lasting for at least 30 minutes. To reduce the impact on our customers, we had to schedule these migrations during a planned maintenance window in the middle of the night every few weeks.

In addition to being a painful process, the scheduled downtime also slowed our engineering team's momentum. Features that required a schema migration took longer to ship as we had to buffer and plan these periodic migrations in advance.

As a company which otherwise shipped code to production multiple times a day, this was simply not good enough. We decided to upgrade to MySQL 5.6, which has online DDL migrations that run `ALTER` statements on-the-fly without locking the tables. Here's how we switched, and what we learned from upgrading to a new MySQL version.

## The Naive Way to Upgrade MySQL

Amazon RDS [recommends](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.ReducedDowntime) the following process for upgrading a MySQL 5.5 instance:

1. Create a new read replica of the MySQL 5.5 database in production
2. Modify the replica's MySQL version to 5.6 to upgrade it
3. Schedule a maintenance window to promote the new 5.6 read-replica to a master
4. Enable multi-AZ on the new 5.6 master, create additional 5.6 replicas
5. Point our systems to the new 5.6 master and replicas

Although RDS makes the process simpler than it would be with a self-hosted MySQL set-up, it's still a very time-consuming process because of the amount of data and replicas involved. Can we do better?

## Create a Better MySQL Upgrade with "Master-Master" Replication

To keep the maintenance window for the upgrade small, we decided to explore if we can get the new MySQL 5.6 master and replicas replicating off the MySQL 5.5 master database and running in parallel before the maintenance window begins.

This way, switching to the 5.6 instances or rolling back would be simple. In addition, we can also canary live traffic to the new databases well in advance to identify any performance bottlenecks that might derail the migration.

Here's what we wanted to achieve:

Note how the 5.6 master is actually replication off the 5.5 master. This sounds great, but unfortunately RDS console does not support a `master-master` replication directly. So, we had to find another way to do it.

In our exploration, we came across a [`mysql.rds_set_external_master`](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html) command that would configure a RDS instance to replicate from an instance of MySQL running external to Amazon RDS.

That sounded promising—if we can replicate from an external MySQL instance, we can do the same from an RDS instance right?

It seemed like it should, but we ran into a small problem. The `mysql.rds_set_external_master` command required a user with replication slave permissions to be present on the MySQL 5.5 master instance. When we created a new `mysql56_repl` user and tried to give it the required permission, we encountered an error:

The master user on RDS did not have the privileges to assign this permission on MySQL 5.5. Thankfully, when we contacted AWS support, they were able to grant the replication slave permission to the `mysql56_repl` user from their end.

With that out of the way, we were able to create a completely parallel set-up of MySQL 5.6 master and replicas, replicating off the 5.5 MySQL DB on production. If you are looking to do just that, here's what you should do, step-by-step:

1. Launch a new read-replica of the MySQL 5.5 DB instance.
2. When the read replica is available, upgrade the replica to MySQL 5.6.
3. Pause replication on this 5.6 replica.
4. Login to the 5.6 replica and run`SHOW SLAVE STATUS\G`.
5. Note the `Master_Log_File `and `Read_Master_Log_Pos `values. We need this for running the `mysql.rds_set_external_master `configuration later.
6. Create a snapshot of the MySQL 5.6 replica. You don't need this 5.6 replica anymore and can terminate it.
7. Create a new RDS master MySQL 5.6 instance from the snapshot you just created.
8. Login to the old 5.5 RDS master instance and create replication user and grant replication slave permission as we described earlier.
9. Make sure that the security group of your 5.5 RDS master instance allows egress.
10. Login to the new RDS master and configure it to replicate off the 5.5 master database this way

11. Use the `mysql.rds_start_replication `to start the replication on the new 5.6 master instance. If all goes well, here's what you will see:

Your new 5.6 master is now a slave as well, by replicating asynchronously off the 5.5 master. You can create read replicas of this 5.6 master if you want to mirror your production set-up.

At this point, you can start canarying production read traffic to the MySQL master and replica to verify that everything is fine. Switching over to the new instances would now be as simple as updating your configuration to point to the new hosts, and will no longer involve a lengthy maintenance window anymore.