xtrabackup_checkpoints file encrypted during backup

[Nickolay Ihalainen]

innobackupex --encrypt=AES256 --encrypt-key="4D27E0ED263A8F2BAF337DED0A5FFFBE" .

The xtrabackup_checkpoints is mentioned in xtrabackup 2.2.10 documentation for innobackupex incremental backups, but its encrypted to xtrabackup_checkpoints.xbcrypt

It's still possible to get incremental LSN from innobackupex output or xtrabackup_info file, but it could be better to have a method to get encrypted incremental backup in the same way as described in documentation.

[Nilnandan Joshi (nilnandan-joshi)]

Verified with XB 2.2.10. It should not encrypt xtrabackup_checkpoints file.

root@desktop:/home/nilnandan/backup/2015-04-27_12-14-24# xtrabackup --version
xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
root@desktop:/home/nilnandan/backup/2015-04-27_12-14-24#
root@desktop:/home/nilnandan/backup/2015-04-27_12-14-24# ll
total 350668
drwxr-xr-x 7 root root 4096 Apr 27 12:14 ./
drwxrwxr-x 4 nilnandan nilnandan 4096 Apr 27 12:14 ../
-rw-r--r-- 1 root root 395 Apr 27 12:14 backup-my.cnf
-rw-r----- 1 root root 52476800 Apr 27 12:14 ibdata1.xbcrypt
-rw-r----- 1 root root 253987712 Apr 27 12:14 ibdata2.xbcrypt
-rw-r----- 1 root root 52476800 Apr 27 12:14 ibdata3.xbcrypt
drwx------ 2 root root 20480 Apr 27 12:14 mysql/
drwx------ 2 root root 4096 Apr 27 12:14 nil/
drwx------ 2 root root 4096 Apr 27 12:14 nil@002dtest/
drwxr-xr-x 2 root root 12288 Apr 27 12:14 performance_schema/
drwx------ 2 root root 4096 Apr 27 12:14 test/
-rw-r--r-- 1 root root 21 Apr 27 12:14 xtrabackup_binlog_info
-rw-r----- 1 root root 149 Apr 27 12:14 xtrabackup_checkpoints.xbcrypt
-rw-r--r-- 1 root root 606 Apr 27 12:14 xtrabackup_info
-rw-r----- 1 root root 2680 Apr 27 12:14 xtrabackup_logfile.xbcrypt
root@desktop:/home/nilnandan/backup/2015-04-27_12-14-24#

[Shahriyar Rzayev (rzayev-sehriyar)]

With PXB 2.4.5 xtrabackup_info file also encrypted.

[root@tokubackup full]# ls -l 2017-01-20_12-33-20/
total 12356
-rw-r----- 1 root root 517 Jan 20 12:33 backup-my.cnf.xbcrypt
drwxr-x--- 2 root root 27 Jan 20 12:33 ccc
drwxr-x--- 2 root root 111 Jan 20 12:33 dbtest
drwxr-x--- 2 root root 69 Jan 20 12:33 fff
-rw-r----- 1 root root 411 Jan 20 12:33 ib_buffer_pool.xbcrypt
-rw-r----- 1 root root 12600576 Jan 20 12:33 ibdata1.xbcrypt
drwxr-x--- 2 root root 4096 Jan 20 12:33 mysql
drwxr-x--- 2 root root 8192 Jan 20 12:33 performance_schema
drwxr-x--- 2 root root 71 Jan 20 12:33 qptest
drwxr-x--- 2 root root 8192 Jan 20 12:33 sys
-rw-r----- 1 root root 205 Jan 20 12:33 xtrabackup_checkpoints.xbcrypt
-rw-r----- 1 root root 681 Jan 20 12:33 xtrabackup_info.xbcrypt
-rw-r----- 1 root root 2744 Jan 20 12:33 xtrabackup_logfile.xbcrypt

So both xtrabackup_checkpoints and xtrabackup_info files are encrypted it is making troublesome to take incremental backups.

[Shahriyar Rzayev (rzayev-sehriyar)]

The backup command:

/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=root --password='Baku12345#' --target-dir=/home/backup_dir/full/2017-01-20_12-33-20 --backup --host=127.0.0.1 --port=3306 --encrypt=AES256 --encrypt-key='VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' --encrypt-threads=4

[Shahriyar Rzayev (rzayev-sehriyar)]

The possible workaround is to decrypt xtrabackup_checkpoints.xbcrypt prior taking backup as:

# xbcrypt -d -k 'VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' -a AES256 -i xtrabackup_checkpoints.xbcrypt
backup_type = full-backuped
from_lsn = 0
to_lsn = 2541071
last_lsn = 2541080
compact = 0
recover_binlog_info = 1

Or to store decrypted xtrabackup_checkpoints.xbcrypt:
# xbcrypt -d -k 'VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' -a AES256 -i xtrabackup_checkpoints.xbcrypt -o xtrabackup_checkpoints

[root@tokubackup 2017-01-20_13-12-43]# ls
backup-my.cnf.qp.xbcrypt dbtest ib_buffer_pool.qp.xbcrypt mysql qptest xtrabackup_checkpoints xtrabackup_info.qp.xbcrypt
ccc fff ibdata1.qp.xbcrypt performance_schema sys xtrabackup_checkpoints.xbcrypt xtrabackup_logfile.qp.xbcrypt

Then to run incremental backup:

/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=root --password='Baku12345#' --target-dir=/home/backup_dir/inc/2017-01-20_13-30-15 --incremental-basedir=/home/backup_dir/full/2017-01-20_13-12-43 --backup --host=127.0.0.1 --port=3306 --compress=quicklz --compress-chunk-size=65536 --compress-threads=4 --encrypt=AES256 --encrypt-key='VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' --encrypt-threads=4 --encrypt-chunk-size=65536

0120 13:30:20 completed OK!

Thanks @Sveta for great advice.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-934