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!