Details
Description
When encryption at rest is enabled, actively updated InnoDB tables are not stored encrypted when creating backups with mariabackup --backup as evidenced by the following failure when trying to restore a single database and running `ALTER TABLE <table> IMPORT TABLESPACE;`:
ERROR 1296 (HY000) at line 1: Got error 192 'Table encrypted but decryption failed. This could be because correct encryption management plugin is not loaded, used encryption key is not available or encryption method does not match.' from InnoDB
We have not modified /etc/my.cnf.d/enable_encryption.cnf and /etc/my.cnf has the following in the [mariadb] section:
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
Backup command:
/usr/bin/stdbuf -o 10MB /bin/mariabackup --backup --slave-info --parallel=8 --user=root --target-dir=/backup/mysql/${DATE}/base/
Rsync command:
rsync -ogrvt ${BACKUP_DIR}/ ${RESTORE_DIR}/
Prepare command:
/usr/bin/mariabackup --prepare --export --target-dir=${RESTORE_DIR} --user=[user] --password=[pass]
Restore is done using the instructions found here:
https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#importing-transportable-tablespaces-for-non-partitioned-tables
Basically we are running `ALTER TABLE [table] DISCARD TABLESPACE;` for each table, copying files into the appropriate directory in the data dir and then running `ALTER TABLE [table] IMPORT TABLESPACE;` for each table.
The `ALTER TABLE [table] IMPORT TABLESPACE;` fails if a table was actively being updated when the backup was created. We have verified that the .idb file contains a mix of encrypted and unencypted data by using the Linux strings command.
Background on how it was reproduced:
I have 3 VMs: dbase01-dev, dbase02-dev, and dbase03-dev. All have encryption at rest enabled. dbase01-dev is the master and dbase02-dev and dbase03-dev are slaves. I'll call the database production instead of its actual name. Not sure if it's relevant but on the slave where backups are being created the /etc/my.cnf file has replicate_do_db=production and binlog_do_db=production. On dbase01-dev, I put 10 insert statements into a SQL file and created a bash script that imports the sql file via `mariadb production < import.sql`. Said bash script was then run via `watch import.sh` and left running for a while. `SHOW SLAVE STATUS` showed that it was keeping up with the master. On dbase03-dev, I ran the backup and prepare commands. At this point, strings ${RESTORE_DIR}/base/production/active_table.ibd shows a mix of encrypted and unencrypted data.
Attachments
Issue Links
- duplicates
-
MDEV-33438 mariadb-backup fails to preserve innodb_encrypt_tables=1
- Closed
- relates to
-
MDEV-33438 mariadb-backup fails to preserve innodb_encrypt_tables=1
- Closed