What are your experiences using Amazons EBS snapshot features for MySql hot backups.
I have a database running a batch processing job in ec2. I backup with EBS snapshot. So far the backups looks consistent. But I am afraid they "will stop being consistent as soon as I stop checking" (Uncertainty principle).
What are your experiences with backuping relational databases (and mysql in particular) with ebs snapshot?
I've been using EBS snapshots to back up my MySQL data dir for more than a year. It's been working perfectly. I've never had a problem using these snapshots as the basis for a replacement (or cloned) MySQL setup.
Best practice is to format the EBS volume with a filesystem that allows freezing, such as XFS. This allows you to get a consistent snapshot: flush MySQL's memory to disk, freeze the filesystem, snapshot, and then unfreeze. The whole process takes less than 10 seconds (but can take longer when the DB is in heavy use).
See this article by Eric Hammond for a script that does all this for you.
The primary concern I have with relying on EBS snapshots for database backups in the level at which they operate. The snapshots operate at a very low level on the disk and take an image regardless of the state of applications writing to it. Theoretically your backup image could be in the middle of a transaction or something, which would make it somewhat awkward to restore should that time ever come.