I’m trying to setup xtrabackup automation, but I’m having an issue with preparing the backups.
I run prepare on the base backup with –apply-log-only and it’s fine.
I run prepare on the first incremental backup (incr1) also with –apply-log-only and it runs fine.
At that point, I try to run it on incr2 directory and it fails saying xtrabackup_checkpoints file is missing.
I can verify this is occurring after the first incremental is prepared. Below is a log of the entire process including verifying all three xtrabackup_checkpoints files exist between executing is prepare statement.
After preparing the incr1, all three xtrabackup_checkpoints files are gone which prevents me from preparing the second incremental backup. (incr2)
Well, I can’t get passed this and from what I can tell. I’m not sure it would be safe to depend on this as even restoring single databases seems to be rather convoluted and the best I can tell. If you do a full backup, you can’t use that to restore a single database / table?
Am I right? You have to backup individually to restore individually and a standard backup does not provide this ability? (the documentation lacks any details around this that I can find)
I will return to my old way of backing up with mysql dump. Maybe take full backups and incrementals, but I would have to restore to a different database then export from there to restore to a production database.
No thats incorrect, we can do restores for single tables as well as single database from the full backup.
You should look here,
mysqldump is a good way to backup when you just need to restore single table or a smaller databases. However, logical backups can take a lot of time to restore. That is where the xtrabackup helps.
If you want to backup bigger database, xtrabackup works faster.
Also if you are comfortable and good using logical backups, you can explore mydumper which is multithreaded mysqldump which helps to do faster logical backups.
You are storing incremental backups within a fullbackup directory. This will make all the backups invalid. Please store incremental backups separately (out of the fullbackup dir).
