I will run a replication cluster using MySQL 5.7 and I would like to set up for each database a backup policy that has a full once per day + incremental every N hours with data encrypted (based on the full of the day). The backup will be done on the slave obviously.
I've looked to Percona xtrabackup documentation that cover each subject apart but not together.
For the backup steps I do it this way
Full backup command once a day
xtrabackup --backup --encrypt=AES256
--encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
--compress
--safe-slave-backup # in order to be able to restore on master \
--slave-info \
--databases foo
--extra-lsndir=/data/backups/db_foo/20200201/full # to get clear text binlog info
--target-dir=/data/backups/db_foo/20200201/full
Incremental backup every 2 hours
xtrabackup --backup --encrypt=AES256
--encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
--compress
--safe-slave-backup # in order to be able to restore on master \
--slave-info
--databases foo
--incremental-basedir=/data/backups/db_foo/20200201/full # use full directory of the day
--extra-lsndir=/data/backups/db_foo/20200201/incr_020000
--target-dir=/data/backups/db_foo/20200201/incr_020000
You can see for the backup I pass option --extra-lsndir
to get the LSN / checkpoint info in clear text alongside the encrypted ones so the incremental backup can be done. From my test it seems to work fine.
Layout of the full dump
|/data/backups/db_foo/20200201/full
├── backup-my.cnf.qp.xbcrypt
├── foo
│ ├── db.opt.qp.xbcrypt
│ ├── people.frm.qp.xbcrypt
│ └── people.ibd.qp.xbcrypt
├── ib_buffer_pool.qp.xbcrypt
├── ibdata1.qp.xbcrypt
├── xtrabackup_checkpoints
├── xtrabackup_checkpoints.xbcrypt
├── xtrabackup_info
├── xtrabackup_info.qp.xbcrypt
├── xtrabackup_logfile.qp.xbcrypt
└── xtrabackup_slave_info.qp.xbcrypt
However it's unclear to me the order of the steps necessary to restore the data with incremental and encrypted data together.
so my questions are:
- are the backup commands correct ?
- do you have some information how should I do the restoration? I'm confused about the order to run the decrypt/decompress/prepare, when to use
--apply-log-only
even after reading this resource and also how to restore only one database and not ALL the data.
Best.
Best Answer
After digging a bit and also based on this page I have a solution that seems to work fine.
Step 1: Decompress and decrypt backup files
Step 2: Prepare the data
--export
to generate ready to copy files.--apply-log
to all backup except the last one (from the docs I linked in the question)Step 3: Restore the data
Now we need to replace the table files without deleting the database.