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

Restore encrypted database in another server, when preparing the backup set, returns error

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None
    • None

    Description

      Hello Team,

      We have Production environment which is enable Data at rest with encrypted Database .
      We are trying to restore the physical backup from source to Target server . While preparing command in target server , we are getting the below errors for all tables .

      MariaDB Database Source Version = 10.3.12
      MariaDB Database Target Version = 10.3.12

      We have copied all Encrypted files from Source to target.

      Backup command in source = mariabackup --defaults-file=/etc/my.cnf --backup --port=3306 --socket=/tmp/mysql.sock --slave-info --target-dir=/mariadb/backups/01072022_vasu_enc --encrypt=AES256 --encrypt-key-file=/mariadb/encryption
      --user=mariadb_bkp --password=mariadb_pass

      Error - 1
      Prepare command in Target =
      mariabackup --prepare --target-dir=/mysql/backup/01072022_vasu_enc/
      mariabackup based on MariaDB server 10.3.12-MariaDB Linux (x86_64)
      mariabackup: cd to /mysql/backup/01072022_vasu_enc/
      Loading encryption plugin from file_key_management=file_key_management
      Loading encryption plugin
      Encryption plugin parameter : '--plugin_load=file_key_management=file_key_management'
      Encryption plugin parameter : '--file_key_management_encryption_algorithm=aes_ctr'
      Encryption plugin parameter : '--file_key_management_filekey=FILE:/mysql/encryption/keyfile.key'
      Encryption plugin parameter : '--file_key_management_filename=/mysql/encryption/keyfile.enc'
      Encryption plugin parameter : '--prepare'
      Encryption plugin parameter : '--target-dir=/mysql/backup/01072022_vasu_enc/'
      mariabackup: Error while setting value 'aes_ctr' to 'file-key-management-encryption-algorithm'
      2022-07-01 10:00:29 0 [ERROR] Parsing options for plugin 'file_key_management' failed.

      Error - 2

      mysql@ont-dc1-mariadb-test01 0 10:05:41 ~ $ mariabackup --prepare --target-dir=/mysql/backup/01072022_vasu_enc/
      mariabackup based on MariaDB server 10.3.12-MariaDB Linux (x86_64)
      mariabackup: cd to /mysql/backup/01072022_vasu_enc/
      Loading encryption plugin from file_key_management=file_key_management
      Loading encryption plugin
      Encryption plugin parameter : '--plugin_load=file_key_management=file_key_management'
      Encryption plugin parameter : '--file_key_management_filekey=FILE:/mysql/encryption/keyfile.key'
      Encryption plugin parameter : '--file_key_management_filename=/mysql/encryption/keyfile.enc'
      Encryption plugin parameter : '--prepare'
      Encryption plugin parameter : '--target-dir=/mysql/backup/01072022_vasu_enc/'
      mariabackup: This target seems to be not prepared yet.
      mariabackup: using the following InnoDB configuration for recovery:
      mariabackup: innodb_data_home_dir = .
      mariabackup: innodb_data_file_path = ibdata1:12M:autoextend
      mariabackup: innodb_log_group_home_dir = .
      2022-07-01 10:05:56 0x7f225102e740 InnoDB: Using Linux native AIO
      mariabackup: Starting InnoDB instance for recovery.
      mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
      2022-07-01 10:05:56 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2022-07-01 10:05:56 0 [Note] InnoDB: Uses event mutexes
      2022-07-01 10:05:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2022-07-01 10:05:56 0 [Note] InnoDB: Number of pools: 1
      2022-07-01 10:05:56 0 [Note] InnoDB: Using SSE2 crc32 instructions
      2022-07-01 10:05:56 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
      2022-07-01 10:05:56 0 [Note] InnoDB: Completed initialization of buffer pool
      2022-07-01 10:05:56 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
      2022-07-01 10:05:56 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=19430549880801
      2022-07-01 10:05:59 0 [Note] InnoDB: Starting a batch to recover 2941 pages from redo log.
      2022-07-01 10:05:59 0 [ERROR] InnoDB: The page [page id: space=278, page number=119634] in file 'csam_orchestrator/csam_video_fingerprints.ibd' cannot be decrypted.
      2022-07-01 10:05:59 0 [Note] InnoDB: However key management plugin or used key_version 1 is not found or used encryption algorithm or method does not match.
      2022-07-01 10:05:59 0 [Note] InnoDB: Marking tablespace as missing. You may drop this table or install correct key management plugin and key file.
      2022-07-01 10:05:59 0 [ERROR] InnoDB: Failed to read file 'csam_orchestrator/csam_video_fingerprints.ibd' at offset 119634: Table is encrypted but decrypt failed.

      Could you please share proper commands for Backup , prepare , copy-back commands for encrypted database .

      Here is the my.cnf file from target :-

      cat /etc/my.cnf
      [mysqld]
      datadir = /mysql/data
      socket = /tmp/mysql.sock
      user = mysql
      bind-address = 0.0.0.0
      log-error = /mysql/data/mysqld.log
      pid-file = /mysql/data/mysqld.pid
      log-bin = /mysql/log/mysql-bin

      max_connections = 8000
      innodb_buffer_pool_size = 148G
      innodb_flush_method = O_DIRECT
      default_storage_engine = innodb
      innodb_log_file_size = 512M
      tmp_table_size = 2048000
      sort_buffer_size = 2048000
      query_cache_type = 0
      query_cache_size = 0
      table_open_cache = 2000
      lower_case_table_names = 1

      1. Added these two lines on 05-08-2021
        interactive_timeout = 7200
        wait_timeout = 7200

      thread_handling = pool-of-threads

      log_slave_updates = 1
      server_id = 1
      innodb_file_per_table = 1
      expire_logs_days = 7
      binlog_format = MIXED
      max_prepared_stmt_count = 1000000
      key_buffer_size = 1024000
      innodb_buffer_pool_instances = 16
      innodb_flush_log_at_trx_commit = 1
      sync_binlog = 1
      innodb_autoinc_lock_mode = 2
      log_slave_updates = 1
      relay_log_recovery = 1

      1. slave configs
        slave_compressed_protocol = 1
        slave_ddl_exec_mode = IDEMPOTENT
        slave_sql_verify_checksum = ON
        slave_net_timeout = 30
        slave_parallel_threads = 8
        slave_parallel_max_queued = 536870912
        slave_parallel_mode = "conservative"
        gtid_strict_mode = 1
              1. Encryption Plugin ######
      1. load plugin
        plugin-load=file_key_management=file_key_management.so
        file_key_management_filename = /mysql/encryption/keyfile.enc
        file_key_management_filekey = FILE:/mysql/encryption/keyfile.key
        file_key_management_encryption_algorithm = AES_CTR
      1. InnoDB encryption
        innodb_encrypt_tables = ON

      Encrypted files location in target ==>
      mysql@mariadb-test01 0 09:23:58 /mysql $ cd encryption/

      mysql@mariadb-test01 0 09:24:00 /mysql/encryption $ pwd
      /mysql/encryption

      mysql@mariadb-test01 0 09:24:01 /mysql/encryption $ ls -ltr
      drwxr-xr-x. 2 root root 59 Jun 29 21:43 dc3_entryption
      rw-rw-r-. 1 mysql mysql 70573 Jun 29 21:45 keyfile
      rw-rw-r-. 1 mysql mysql 70592 Jun 29 21:45 keyfile.enc
      rw-rw-r-. 1 mysql mysql 257 Jun 29 21:45 keyfile.key

      Attachments

        Activity

          People

            Unassigned Unassigned
            vasu VASUDEVARAO
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.