Details
-
Bug
-
Status: Done
-
High
-
Resolution: Fixed
-
8.0.16-7
-
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
- causes
-
PXB-1930 PXB crashes during full backup after restore of encrypted tablespace (innodb-redo-log-encrypt=ON)
-
- Done
-