I've inherited responsibility for a 400GB MySQL database that is hosted in Amazon RDS.
The DB serves an application that relies intensively on DB operations, so version releases frequently involve running ALTER statements on very large tables. These updates take over 24 hours to run.
Typically, we make a snapshot of the production RDS instance, restore it to a temporary instance, run the migration on that and then rename it so that the apps start connecting to it as the Production DB.
The obvious difficulty in this is that during the 24+ hour upgrade, we have to keep both DBs in sync, which means data has to be loaded to the application twice, which is a major headache.
I'm convinced there is a better way to do this (albeit I'm aware of the limitations of RDS), but I can't see find a solution.
The DB can't be live while it is being altered, but the alteration takes forever.
Anyone have similarly experience, or should I just learn to live with it?
