Massive DB Conversions in AWS MySQL RDS

22 Dec

aws logoWe recently had the need to perform a massive DB conversion to move all tables over from a mishmash of encodings to UTF-8. We had many large alters and character conversions to perform. The entire operation was about 24 hours of work, so we couldn’t afford to take the site down during the whole thing.

Fortunately, we are using MySQL in AWS RDS. We have a single master instance (running multi-AZ) and several replicas we use for read-only queries. To solve our problem, we created a new replica and applied a parameter group with the setting READ_ONLY set to 1. This allowed us to have write queries active against our replica DB.

With the special parameter group in place, we could alter the DB column types as much as we wanted and not cause any problems. As long as the table and column names remained the same, replication would continue as usual and this special DB would keep up with all of the live data coming in from the website.

When the alters were all done, we put the site into maintenance mode and promoted the special DB to be a new master. We gave it a more standard parameter group and then fired up several read replicas. A few DNS changes later, our site was back up and running with a freshly altered DB and minimal downtime.

Note: This approach can also be used in the event you want to make changes to the names of tables. Simply rename your table and create a view that re-maps the old table and columns to the new. If replication halted because a query came through the binlog trying to write data to the missing table, just reboot the RDS instance and replication will resume as usual.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: