Details
Critical 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
-
-
- Closed
-
Activity
Scp the attached tarball to /tmp on the test system and then SSH into said system.
cd /tmp |
tar -zxvf mdev-33334-testing.tgz |
cd mdev-33334-testing |
./start_testing.sh |
After running the strings command, you'll be prompted to press any key to continue on to the restore.
Tail end of the output from my last test run:
Iran into a problem with restoring databecause it was a mix of encrypted and decrypted data%
|
Icreated a ticket with MariaDBso they would be aware of the problem'"
|
somebodysaid I should create a test scriptso they can try to recreate the problem'! |
Icreated a test database and tableso they can try to recreate the problem') |
Icreated a sql file to do multiple insertsso they can try to recreate the problem'3 |
Icreated a bash script that loads the sql repeatedlyso they can try to recreate the problem'; |
Icreated a bash script that creates a backup and prepares itso they can try to recreate the problem'0 |
Icreated a bash script checks the prepared backupso they can try to recreate the problem |
G&s
|
Press any key to continue on to restore |
Continue? Discard tablespace: mariadb mdev_33334_testing -e 'ALTER TABLE test DISCARD TABLESPACE' |
Copy files: rsync -ogrvt /tmp//backup/restore-prep/mdev_33334_testing/test.* /var/lib/mysql/mdev_33334_testing/ |
sending incremental file list
|
test.cfg
|
test.frm
|
test.ibd
|
|
|
sent 17,832,263 bytes received 73 bytes 35,664,672.00 bytes/sec |
total size is 17,827,685 speedup is 1.00 |
Correct ownership: chown mysql.mysql -R /var/lib/mysql/mdev_33334_testing/
|
Import tablespace: mariadb mdev_33334_testing -e 'ALTER TABLE test 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 |
[root@dbase01-dev mdev-33334-testing]# |
Thank you, thiru! So, the server parameter innodb_encrypt_tables=ON is not being correctly handled by mariadb-backup.
I had forgotten that I had analyzed the root cause of MDEV-33438 (which we reproduced internally) some time earlier. This seems to be the same bug.
Based on this description, I would first guess that the encryption was not properly enabled when mariadb-backup was run. I believe that thanks to some format changes related to
MDEV-14425andMDEV-19534, mariadb-backup --backup should be able to succeed even when no encryption has been configured and the data and log files are encrypted. In theMDEV-14425ib_logfile0 format, page numbers or file names are never encrypted; only the log record payload is. In the innodb_checksum_algorithm=full_crc32 format, page checksums are calculated after encryption.When it comes to mariadb-backup --prepare, the log record payload as well as the contents of the pages would be interpreted as garbage if the encryption parameters are incorrect. Applying the log might appear to succeed, but I have some doubts. Especially the application of the
MDEV-21724INSERT operations includes rather extensive consistency checks, and therefore I would expect some errors to be reported.Could you please write a script that reproduces this problem and shows the exact steps that are needed for reproducing this? I checked our regression test mariabackup.incremental_encrypted, and it is invoking mariadb-backup in a similar way, with no additional parameters related to encryption. It is also using the file_key_management encryption plugin. What it is missing is an INSERT running concurrently with backup, and any use of ALTER TABLE…IMPORT TABLESPACE.