Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33334

mariadb-backup fails to preserve innodb_encrypt_tables

    XMLWordPrintable

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

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              mmusgrove Matthew Musgrove
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.