​In this post, we are going to see how we can backup encrypted tables using Xtrabackup. InnoDB supports data encryption for InnoDB tables stored in file per table tablespaces. For the application to access encrypted tablespace, InnoDB will use master encryption key to decrypt the tablespace key. The master encryption key is stored in a keyring file in the location specified by the key_ring_file_data configuration option. We have already discussed on enabling encrypted tablespace. Here, we will try full and incremental backups of encrypted tablespace.

Percona xtrabackup supports encrypted innodb tablespace backups. While taking backup, we have to add options –keyring-file-data and –server-id. After the completion of the backup, we have to use the same options to prepare the backup. Below is an example of encrypted table,

Backup can be taken without using –keyring-file-data and –server-id and the same can be done for apply log as well. But, after restoring the backup we cant access the encrypted table. It will throw error like below

FULL BACKUP

So, proper encrypted backup should be taken by specifying the path to keyring file and server-id.

 
innobackupex --user=root --password=pass --safe-slave-backup  --keyring-file-data=/var/lib/mysql_keyring/keyring --server-id=176457 /path/to/backup
	

Once the backup is completed, we have prepare the backup by passing the same options. Its needed to specify keyring file data but not server id  as it can be obtained from backup-my.cnf. Xtrabackup will not copy keyring file into the backup directory. In order to prepare the backup, you must make a copy of keyring file yourself.

 
innobackupex --apply-log --use-memory=20G --keyring-file data=/var/lib/mysql_keyring/keyring  /path/to/backup
	

After finishing applying the logs, you should can see the below message,

 
InnoDB: Shutdown completed; log sequence number 21647652
 170928 19:23:15 completed OK!
	

Once the backup is prepared, it can be restored either using –move-back or –copy-back. If the keyring file is rotated during the backup, we have to use the same keyring file which was used to backup and prepare backup. After successful restoration, we can see the data.

If the master key file is deleted or moved, then the encrypted table cant be accessed. So, it must be stored properly.

INCREMENTAL BACKUP

To perform incremental backup, we need a full backup. The Xtrabackup binary writes a file called xtrabackup_checkpoints  in the backup directory.

After the full backup, the incremental backup will be based on that.Even here the xtrabackup will not copy keyring file into the backup directory. In order to be prepare the backup, you must make a copy of keyring file yourself. If the keyring hasn’t been rotated you can use the same as the one you’ve backed-up with the base backup. If the keyring has been rotated you’ll need to back it up otherwise you won’t be able to prepare the backup.

 
innobackupex--user=root --password=pass --incremental --incremental-basedir=/path/to/base/backup/ --keyring-file-data=/var/lib/mysql_keyring/keyring --server-id=176457 /path/to/inc/backup
	

Now the incremental backup directory will have the delta files, which represents the changes.

The apply log phase in incremental backup doesn’t work like full backup. Here, you must use –apply-log-only to rollback phase. Then we have to prepare the base and incremental backup.

 
innobackupex --user=root --password=pass --apply-log-only --use-memory=10G --keyring-file-data=/var/lib/mysql_keyring/keyring/  /path/to/base/backup/
	

After preparing the base backup, the incremental backup should applied from the base backup.

 
 innobackupex--user=root --password=pass --apply-log-only --incremental-dir=/path/to/inc/backup --keyring-file-data=/var/lib/mysql_keyring/keyring/  /path/to/base/backup/
	

Once it is successful, it can be restored. If there is any issue in accessing the encrypted tables, then either the file should have rotated or it might have removed/moved. So, it us necessary to check the encrypted tables after the restoration.