It is possible that the data corruption happens at the database contents level rather than being due to errors at a lower level (i.e, disk, filesystem, etc.). (If you think that only your Master-DB is corrupted, see the Master-DB is corrupted scenario.) Data corruption of all your database instances is commonly due to errors or bugs in the application, which might cause things like inserting incorrect records into the database, deleting valuable records, or disconnecting table relationships. Since these changes are valid from an SQL point of view, they will be propagated to the slave instances immediately. Therefore, both master and slave databases likely have the same data corruption.
The best way to recover from this situation is to restore the data from the most recent backup of snapshots for your EBS stripe that was taken before the data corruption took place. In this situation, the recovery is the same as when there are no running DB instances! (master or slave) with the exception that you need to be more careful when choosing which snapshot you use to restore the database, because the snapshot could have been taken after the database was already corrupted.
By default, the ServerTemplate is designed to take periodic snapshots of both the master and slave databases. Snapshot management includes daily, weekly, monthly, and yearly archives, which allows you to always have a clean set of snapshots from which to recover.
Overall Strategy: Existing database instances should be discarded and a new master-slave cluster should be started from scratch. Basically, you need to disable the current MySQL instances and starting from scratch.
© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.