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

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 8.0.16-7
    • Fix Version/s: 8.0.17-8
    • Component/s: None
    • Labels:
      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.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  zsolt.parragi Zsolt Parragi
                  Reporter:
                  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