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

mariabackup does not always write encrypted files to backup location

    XMLWordPrintable

Details

    • Bug
    • Status: Needs Feedback (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.11.6
    • None
    • Backup
    • None
    • Alma Linux 8.9, Alma Linux 9.2

    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

        Activity

          People

            marko Marko Mäkelä
            mmusgrove Matthew Musgrove
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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