i've currently set up a Windows EC2 instance with MySQL installed on the root/C: drive, with the database files, logs, etc on another volume.

Is it safe to use snapshots to back up the instance? My thinking is that it would be impossible to take a snapshot of each volume at the exact same time, so you don't have a consistent snapshot of the entire instance.

If there is such an issue, what other backup options are there? Back up using AMIs instead?



Okay, first off you're correct that creating a snapshot of both volumes isn't atomic. You'll have to send the snapshot create requests sequentially. However, this may not be a problem. Given that your data files are located on the second volume, this is the only volume you'll need to snapshot for a data backup. If you snapshot the EBS volume containing your operating system and the MySQL binaries every time you make a significant change, and snapshot your data volume independently periodically as a backup this should be enough.

Making an AMI is a good idea, but I would suggest that you bundle the instance specifically excluding the data volume. Should you need to recover at some future time you will then spin up a new instance from your AMI, create a new data volume from the most recent data volume snapshot, attach this to the new instance, and start up MySQL.

One additional thing you should note is that taking a snapshot of the data volume while the database is running will give you a 'crash consistent' copy of the database files. This means that MySQL will run consistency checks on startup and may need to repair tables which can be time-consuming. To minimise risk, I'd recommend either stopping the database cleanly, or at least flushing the filesystem buffers with the 'sync' command, before taking the snapshot.

  • Good advice about syncing the MySQL tables to disk. This can be accomplished using the FLUSH TABLES command. Make sure you remember to unlock the tables after you've finished taking the snapshot. Also, the flush command works differently for MyISAM and InnoDB, so make sure you read the documentation and choose the form that applies to your database. – Paccc Sep 27 '13 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.