Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5979

PS does not encrypt redo logs after restore

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Fixed
    • 8.0.16-7
    • 8.0.17-8
    • None
    • None

    Description

      Initialize and start PS8.0.16-7 with encryption options

      ./all_no_cl --log-bin=binlog --early-plugin-load=keyring_vault=keyring_vault.so --keyring_vault_config=/home/mchawla/test_mode/vault/keyring_vault.cnf --innodb-undo-log-encrypt --innodb-redo-log-encrypt --default-table-encryption=ON --innodb_encrypt_online_alter_logs=ON --innodb_temp_tablespace_encrypt=ON --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --master_verify_checksum=ON --binlog_checksum=CRC32 --encrypt-tmp-files --innodb_sys_tablespace_encrypt --innodb_parallel_dblwr_encrypt --binlog-rotate-encryption-master-key-at-startup --table-encryption-privilege-check=ON --innodb-default-encryption-key-id=4294967295 --innodb-encryption-threads=10

      Create encrypted data

      #!/bin/bash
      num_tables=10
      for ((i=1; i<=${num_tables}; i++)); do
       echo "Creating the table sbtest$i..."
       ${PWD}/bin/mysql -uroot -S${PWD}/socket.sock -e "CREATE TABLE test.sbtest$i (id int(11) NOT NULL AUTO_INCREMENT, k int(11) NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y';"
      done
      echo "Adding data in tables..."
      sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=${PWD}/socket.sock --time=30 run >/dev/null 2>&1

      Run a small load using sysbench during backup

      sysbench /usr/share/sysbench/oltp_insert.lua --tables=10 --mysql-db=test --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${PWD}/socket.sock --time=50 --report-interval=1 run

      Take full backup

      ./xtrabackup --user=root --password='' --backup --target-dir=$HOME/dbbackup_PS8/full -S $HOME/PS110919_8_0_16_7_debug/socket.sock --datadir=$HOME/PS110919_8_0_16_7_debug/data --keyring-vault-config=$HOME/test_mode/vault/keyring_vault.cnf --xtrabackup-plugin-dir=$HOME/pxb_8_0_8_debug/lib/plugin 2>&1 | tee full_backup_$(date +"%d_%m_%Y")_log

      Add data using sysbench for short duration

      sysbench /usr/share/sysbench/oltp_insert.lua --tables=10 --mysql-db=test --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${PWD}/socket.sock --time=10 run

      Take incremental backup

      ./xtrabackup --user=root --password='' --backup --target-dir=$HOME/dbbackup_PS8/inc --incremental-basedir=$HOME/dbbackup_PS8/full -S $HOME/PS110919_8_0_16_7_debug/socket.sock --datadir=$HOME/PS110919_8_0_16_7_debug/data --keyring-vault-config=$HOME/test_mode/vault/keyring_vault.cnf --xtrabackup-plugin-dir=$HOME/pxb_8_0_8_debug/lib/plugin 2>&1 | tee inc_backup_$(date +"%d_%m_%Y")_log

      Prepare full backup

      ./xtrabackup --prepare --apply-log-only --target_dir=$HOME/dbbackup_PS8/full --keyring-vault-config=$HOME/test_mode/vault/keyring_vault.cnf --xtrabackup-plugin-dir=$HOME/pxb_8_0_8_debug/lib/plugin 2>&1 | tee prepare_full_backup_$(date +"%d_%m_%Y")_log

      Prepare incremental backup

      ./xtrabackup --prepare --target_dir=$HOME/dbbackup_PS8/full --incremental-dir=$HOME/dbbackup_PS8/inc --keyring-vault-config=$HOME/test_mode/vault/keyring_vault.cnf --xtrabackup-plugin-dir=$HOME/pxb_8_0_8_debug/lib/plugin 2>&1 | tee prepare_inc_backup_$(date +"%d_%m_%Y")_log

      Stop PS and move the data directory to another location
      Restore the backup

      ./xtrabackup --copy-back --target-dir=$HOME/dbbackup_PS8/full --datadir=$HOME/PS110919_8_0_16_7_debug/data --keyring-vault-config=$HOME/test_mode/vault/keyring_vault.cnf --xtrabackup-plugin-dir=$HOME/pxb_8_0_8_debug/lib/plugin 2>&1 | tee restore_full_backup_$(date +"%d_%m_%Y")_log

      Start PS as:

      ./start --log-bin=binlog --early-plugin-load=keyring_vault=keyring_vault.so --keyring_vault_config=/home/mchawla/test_mode/vault/keyring_vault.cnf --innodb-undo-log-encrypt --innodb-redo-log-encrypt --default-table-encryption=ON --innodb_encrypt_online_alter_logs=ON --innodb_temp_tablespace_encrypt=ON --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --master_verify_checksum=ON --binlog_checksum=CRC32 --encrypt-tmp-files --innodb_sys_tablespace_encrypt --innodb_parallel_dblwr_encrypt --binlog-rotate-encryption-master-key-at-startup --table-encryption-privilege-check=ON --innodb-default-encryption-key-id=4294967295 --innodb-encryption-threads=10

      Issue: After startup, the redo logs are not encrypted and can be viewed as plaintext

      MySQL 8.0.17
      Dzus
      lAQ&
      33401176041-8114085!
      2676-78261727885-12831108849-01673507709-12469951426-52842838633-87519025409-39588030580-47449479462 11971755348-96527271555-9674882265
      4-20086494108-42961640762 
      90495438660-42329480999-16886240780-96019322648-16790576668-84319328074-11409595958-96385517087-50078795948-50561410740 31776194311-071
      18742223-29944126538-52730956230-55479841832 
      [79156080613-76105053699-40310707210-29279668002-92022349620-94464282973-52552808111-25251190553-64557083227-48378703332 26184320780-54
      757204926-16410154369-96740722234-29532612210 
      MySQLXidd
      56694228283-77973641370-07317763033-34165640791-56813364346-40840818350-47844650255-66282728448-04128011657-31339158844 29764101583-027
      56868417-63236445534-91488482057-21441502091 
      19813096954-51384471264-72766866565-42447259246-
      92441214292-10352300261-68390957594-79842962268-42580874575-50828029976 70853182108-09951654697-66210608226-61514496978-09823080428 
      MySQLXidd
      MySQLXidd

      Due to this issue, it is not possible to take backup again.

      Another scenario tested and found by sergei.glushchenko

      1. start PS with innodb-redo-log-encrypt=OFF
      2. start sysbench
      3. while sysbench is running killall -9 mysqld
      4. start PS with innodb-redo-log-encrypt=ON
      PS will start, but redo log is still unencrypted. Backup will fail. If you do the same with MySQL 8.0.17, backup will run just fine because MySQL encrypts the redo log.

      Note: The issue is not observed in MySQL 8.0.17.

      Attachments

        Issue Links

          Activity

            People

              zsolt.parragi Zsolt Parragi
              manish.chawla Manish Chawla
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 day
                  1d

                  Smart Checklist