Using Amazon's EBS for MySQL hot backup

Question

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?

Answer 1

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.