I’m putting together a backup strategy of a 100% InnoDB database using innobackupex for a large part of the process. I have a very active database that I want to be able to recover up to as close as possible to the time of failure, even if the media is lost.
One question that I can’t get clear in my head is the difference between taking incremental backups using innobackupex and simply snapshotting the binary logs.
To that extent, I’m taking daily full hot backups with innobackupex with the backups sent immediately to both onsite and offsite (online) storage. So, recovering one of those backups will result in losing up to one day’s worth of transactions.
So, in order to recover the remaining transactions, I’m looking at either:
Watching the binary logs and copying each completed log to onsite and offsite storage each time a new log is created, forcing a log creation with FLUSH LOGS each hour if no log is created in the meantime. In the case of media failure, I’d recover the last daily full backup and then reapply all transactions from subsequent logs.
Instead of watching and copying the binary logs each hour, I could do an incremental backup with innobackupex each hour. I would then use innobackupex’s process to merge the incremental backups during restore.
The way I see it, both #1 and #2 would result in the same situation - with a maximum of one hours’ worth of transactions lost. However #2 results in a much larger backup size since and a longer process each hour.
So - the question is what’s the benefit of running innobackupex incremental backups over simply archiving the binary logs?
Please keep in mind that this is just one of the components of the backup strategy. Other components include a weekly mysqldump of the database, disk mirroring and a replication slave, but those are outside the scope of this question.