Percona XtraBackup (PXB) version 8.0.28 supports taking backups for the encrypted tables in your MySQL database using the AWS Key Management Service. For setting up data-at-rest encryption using AWS key management service, please see Configuring Keyring for Encryption Using AWS Key Management Service in Percona Server for MySQL.
In this blog post, we will discuss how the backup and restore would work if you have an encrypted table using this keyring component.
Do we need to take any additional care with Percona XtraBackup?
It is mentioned in the Percona XtraBackup documentation that the backup does not need any additional configuration for backing up the encrypted data using the component_keyring_kms component, as PXB reads the necessary keys from MySQL.
I have enabled the component keyring KMS on my Percona Server for MySQL and also encrypted one of the tables (test.t1).
Let’s try to take a backup and see the logs. I am using a very basic command to PXB to keep it simple; however, you might have various other parameters as per the requirement,
1 | root@default ~]# xtrabackup --backup --target-dir=./backup |
The very important note in the logs is to make sure the PXB has initialized the component, and the backup was completed.
1 2 3 4 5 6 | 2024-03-06T09:11:28.862220-00:00 0 [Note] [MY-013713] [Server] Component component_keyring_kms reported: 'Keyring component initialized successfully.' 2024-03-06T09:11:28.862314-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded 2024-03-06T09:11:32.366735-00:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (21812215) to (21812251) was copied. 2024-03-06T09:11:32.574732-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK! |
For preparing the backup
Here is the list of files in PXB that was taken along with the component keyring KMS installed in MySQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [root@default ~]# ls -l backup/ total 70708 -rw-r-----. 1 root root 456 Mar 6 09:11 backup-my.cnf -rw-r-----. 1 root root 237 Mar 6 09:11 default-bin.000027 -rw-r-----. 1 root root 21 Mar 6 09:11 default-bin.index -rw-r-----. 1 root root 3424 Mar 6 09:11 ib_buffer_pool -rw-r-----. 1 root root 12582912 Mar 6 09:11 ibdata1 drwxr-x---. 2 root root 4096 Mar 6 09:11 mysql -rw-r-----. 1 root root 26214400 Mar 6 09:11 mysql.ibd drwxr-x---. 2 root root 4096 Mar 6 09:11 performance_schema drwxr-x---. 2 root root 4096 Mar 6 09:11 sys drwxr-x---. 2 root root 4096 Mar 6 09:11 test -rw-r-----. 1 root root 16777216 Mar 6 09:11 undo_001 -rw-r-----. 1 root root 16777216 Mar 6 09:11 undo_002 -rw-r-----. 1 root root 106 Mar 6 09:11 xtrabackup_binlog_info -rw-r-----. 1 root root 134 Mar 6 09:11 xtrabackup_checkpoints -rw-r-----. 1 root root 565 Mar 6 09:11 xtrabackup_info -rw-r-----. 1 root root 3072 Mar 6 09:11 xtrabackup_logfile -rw-r-----. 1 root root 39 Mar 6 09:11 xtrabackup_tablespaces |
Notice that there is no information about the component keyring in the backup files.
Let’s try to prepare the backup as it is and with a simple command,
1 | [root@default ~]# xtrabackup --prepare --target-dir=/root/backup |
I see the below errors in the prepare logs,
1 2 3 | 2024-03-06T09:24:42.766855-00:00 0 [Warning] [MY-013712] [Server] No suitable 'keyring_reader_with_status' service implementation found to fulfill the request. 2024-03-06T09:24:42.766886-00:00 0 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded. 2024-03-06T09:24:42.766906-00:00 0 [Note] [MY-011825] [InnoDB] Encryption information in datafile: ./test/t1.ibd can't be decrypted, please confirm that keyring is loaded. |
When you start preparing a backup, it should load the keyring first. However, since the keyring did not load, it did not log whether the keying was loaded. That is what the error says while PXB reads table t1 to prepare and make sure the keyring is loaded.
The proper way to prepare the backup
You need two things:
- Local keyring file that is created when the component loads.
- Component keyring kms config file that you used for the encryption.
Copy the component_keyring_kms.cnf file in the backup directory. This file should be either in the local config or global config location as you configured. Take a look here at how the configuration is done for this component.
Here is what the config file looks like. I have hidden my access keys to AWS.
1 2 3 4 5 6 7 8 9 10 | [root@default ~]# cat /root/backup/component_keyring_kms.cnf { "read_local_config": true, "path": "/usr/local/mysql/keyring-mysql/aws-keyring-data", "read_only": false, "region": "us-east-1", "kms_key": "arn:aws:kms:us-east-1:482433642182:alias/yunustest", "auth_key": "************", "secret_access_key": "****************" } |
My local keyring file, as per the configuration, is at this path /usr/local/mysql/keyring-mysql/aws-keyring-data
I will copy that file at the same path where I am preparing the backup, or if you are preparing it on the same host, you can use the same file as well and just copy the config file to the backup directory.
PXB tries to see if there is any component config file to initialize and then initializes it accordingly before preparing the backup. It needs both keys, the local keyring data file as well as the AWS key from KMS. Both of them are equally important. If you lose either, the backup is non-recoverable.
My backup directory has the config file now:
1 2 | [root@default ~]# ls -l /root/backup/component_keyring_kms.cnf -rw-r--r--. 1 root root 305 Mar 6 09:30 /root/backup/component_keyring_kms.cnf |
Also, the local keyring file at its defined location in the config file:
1 2 | [root@default ~]# ls -l /usr/local/mysql/keyring-mysql/aws-keyring-data -rw-r-----. 1 mysql mysql 1254 Mar 6 09:23 /usr/local/mysql/keyring-mysql/aws-keyring-data |
Let’s try to prepare the backup again and monitor the logs.
The important thing to note in the logs:
1 2 | 2024-03-06T09:43:00.864190-00:00 0 [Note] [MY-013713] [Server] Component component_keyring_kms reported: 'Keyring component initialized successfully.' 2024-03-06T09:43:00.864277-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded |
Once the component is loaded in the prepare stage, it should be able to decrypt any encrypted tables and prepare the backup successfully.
Then you can do a copy-back with PXB and start the MySQL service.
Suppose you are restoring this backup on the new instance. In that case, you need to make sure to initialize the component with the same keyring data file and the same component keyring KMS configuration to be able to read the encrypted tables.
Otherwise, the keyring will not load and you won’t be able to see the data on the new instance.
Even if you are restoring on the same instance, it is essential to configure the instance with the same keyring and config file.
Conclusion
Even though the backup does not need any extra parameters to take backup for encrypted data, the preparation stage needs a few additional steps.
The important thing to note is that the PXB won’t backup the local key and the config file for the component. You need to back it up and keep it safe.
Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.
