I'm looking to replace an aging and decidedly suboptimal mysqldump
-based database backup strategy with Percona's Xtrabackup. This all looks pretty straightforward, except that I'm wondering: how many incremental backups should I keep between full backups?
I realize that restoring from a long set of incrementals would be somewhat tedious, but it looks like it'd be pretty easy to script.
I also imagine that if I somehow lose an incremental that's part of a backup set, I would lose everything from that point on. That seems like it'd be unlikely (these guys will be headed to S3), but still a bad day.
Are there rules of thumb about this sort of thing? Would hourly incrementals with a complete backup a week (so, 168 files per backup set) be insane, or normal for some workloads?
FWIW, we're looking at a ~10M row database, growing at ~20k rows a day, very few changed rows (ie, append-mostly). So the incrementals would be pretty small.