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.
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-onlyeven after reading this resource and also how to restore only one database and not ALL the data.