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.
MySQL has a reputation for recovering poorly from inconsistent disk states, XFS essentially pauses IO to the file system whilst the snapshot takes place. Normally a database does a flush() once a full transaction log entry has been created which indicates essentially a checkpoint to the file system. In the case of a journalling file system, this is important, and for the most part, the file system will recover to the last valid journal entry once mounted, this isn't 100%, but it's better than nothing. Most database systems use the transaction log file to 'roll-forward' on recovery if the database files are behind the transaction logs, and the database engine will only roll forward as much as it can given the contents of the transaction logs. It won't try to roll forward through a partially written transaction. The problem here is that MySQL is not the best at achieving that, so it can absolutely be a problem. I haven't found a solid solution for this, I would imagine running a mirror, pausing MySQL whilst you do the snapshot and then resume synchronization might work, but I don't know if MySQL mirror can cope with a mirror being partially unavailable for awhile and then be able to catch up without a full re-mirror, in which case, you might as well just do a mysqldump of all the databases as it would have about the same effect on the database as running a full mirror. That's the other whilst-running option I can think of -- to run a mysqldump of all databases to a backup partition and snapshot that. Doesn't give you running backups, so you can't do it often, and if you're 24/7, mysqldump puts a heavy load on the database whilst it's running, so far from optimal.
Other database engine are much better at this. PostgreSQL is very good at recovering from an incosistent disk state to the point that they don't recommend running it on a journalled filesystem at all. You also have the option of archiving transaction logs so you can roll forward from the last good full backup up to any point in time where the archived logs exist. Much easier to make consistent backups with this. Oracle will allow you to have multiple sets of transaction logs that switch between physical disks/EBS partitions giving you frequent windows to take a snapshot that is consistent and the ability to indicate to the database engine that you wish to do this, and not to flip back until you say so.
Along the journalling line of thinking, LVM has the ability to snapshot the entire file system in typically under a second. I don't know if the EBS snapshot functionality will take advantage of this, though you could do so manually. LVM is a bit more fiddly than XFS, but I've had issues with XFS in the past crapping out on large numbers of files in a single directory where ext3 was fine. LVM has a bunch of other benefits too, and is definitely worth looking into either way.
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.
I'd suggest using LVM as the abstract layer for the DB filesystem. as This will get the benefits of local snapshots which is like a hot backup. LVM snapshots has the benefit of acheivng similar results to EBS with the benefit of using it on any machine (not just amazon based).
Another plus, is that LVM can use hot resize, specially nice when you need minimum down time and need to expand your disk space on the fly (not recommended, but possible in specific scenarios)
You might consider using Amazon RDS to manage your database(s) -- it works just like a standard MySQL server and then you could blame Amazon if it crashes (it won't). Also, they back up and patch the server for you regularly. I moved my Wordpress and vBulletin installs over and it took maybe an hour.
Just my 2¢!
© 2022 - 2024 — McMap. All rights reserved.
