I've read a bunch of articles on taking consistent backups of mySQL, including What's the meaning of consistent dataset? (MySQL), but I still don't think I know enough to answer this question with confidence. I originally posted this question on stackoverflow, but was advised by a couple of high-reputation members that this would be a better place for it. So I'm basically copy-pasting the question here and deleting it from there. Hope that's an acceptable practice - I asked for help on how to go about moving the question, but didn't get any.
Running MySQL 5.5 using only InnoDB tables with file-per-table enabled backing a multi-user website hosted on Amazon EC2 running Amazon Linux.
From what I've been able to find here and other Stack Exchange sites, it sounds like it's okay to copy the whole data tree to make a valid backup after shutting down the server.
What I'm currently doing for backups is:
- stop apache
- stop mysql
- take lvm2 snapshot of the volume containing the data directory
- start mysql
- start apache
- use rsnapshot to copy the lvm2 snapshot to a different volume
- delete the lvm2 snapshot
In his answer to https://stackoverflow.com/questions/9165118/lvm-mysql-backup/9165604#9165604, Bill Karwin noted that lvm snapshots can seriously degrade I/O performance. But I believe that since the lvm snapshot is only active for the short time it takes rsnapshot (which uses rsync) to complete, performance shouldn't be seriously impacted.
So my question is whether I really need to stop the servers while taking the lvm snapshot. I think I read somewhere that although an "instant in time" snapshot could catch InnoDB tables with incomplete transactions, that if that happens mySQL will deal with them consistently when started again. So I'm thinking that stopping the server might be overkill, even though it's only stopped for a very short time while the lvm snapshot is taken. (I first stop apache so requests can't come in without a database running).
I'll certainly check out Bill's advice on http://www.percona.com/software/percona-xtrabackup. And it seems to get cited a lot here. Does it work well with MySQL 5.5, or do I need to install Percona Server? In the meantime, I'd like to know if I can make an incremental improvement to what I'm doing - or if what I'm doing is already broken!
TL;DR : Stopping your services is not necessary with LVM snapshots. +1 for XtraBackup.
It is not required to stop the services. The LVM snapshot is equivalent to pulling the power, which means it is consistent, but the same as a crash. When starting the service from one of your backups, it will have to do crash recovery. The time needed to do this varies depending on the size of your InnoDB log files but can be several minutes. Once the crash recovery has completed, your service will be up and running again normally.
By shutting down services before the snapshot, your recovery will start faster but at the cost of some downtime every time you run your backups. Also worth considering is that every time you stop services, you lose all the warm buffer pool, so you will also incur some performance degradation from having to read data into memory again.
XtraBackup is designed to work with Oracle MySQL as well as Percona Server. It's a good option, though as with all things backup, test, test, and test it again to make sure it works before you find out you need it.