1# InnoDB transparent tablespace data encryption 2# This test case will test basic encryption support features. 3 4--source include/no_valgrind_without_big.inc 5--source include/have_innodb.inc 6--source include/not_embedded.inc 7 8CREATE TABLE t1(c1 int) ENGINE=InnoDB ENCRYPTION="Y"; 9 10DROP TABLE t1; 11 12# Restart the server with keyring loaded 13--let restart_parameters="restart:$KEYRING_PARAMS" 14--source include/restart_mysqld_no_echo.inc 15 16let $innodb_file_per_table = `SELECT @@innodb_file_per_table`; 17 18SET GLOBAL innodb_file_per_table = 1; 19SELECT @@innodb_file_per_table; 20 21# Create a table with encryption 22CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; 23 24SHOW CREATE TABLE t1; 25INSERT INTO t1 VALUES(0, "aaaaa"); 26INSERT INTO t1 VALUES(1, "bbbbb"); 27INSERT INTO t1 VALUES(2, "ccccc"); 28INSERT INTO t1 VALUES(3, "ddddd"); 29INSERT INTO t1 VALUES(4, "eeeee"); 30INSERT INTO t1 VALUES(5, "fffff"); 31INSERT INTO t1 VALUES(6, "ggggg"); 32INSERT INTO t1 VALUES(7, "hhhhh"); 33INSERT INTO t1 VALUES(8, "iiiii"); 34INSERT INTO t1 VALUES(9, "jjjjj"); 35INSERT INTO t1 SELECT * FROM t1; 36INSERT INTO t1 SELECT * FROM t1; 37INSERT INTO t1 SELECT * FROM t1; 38INSERT INTO t1 SELECT * FROM t1; 39INSERT INTO t1 SELECT * FROM t1; 40INSERT INTO t1 SELECT * FROM t1; 41INSERT INTO t1 SELECT * FROM t1; 42INSERT INTO t1 SELECT * FROM t1; 43INSERT INTO t1 SELECT * FROM t1; 44 45SELECT * FROM t1 LIMIT 10; 46 47# Restart to confirm the encryption info can be retrieved properly. 48--let restart_parameters="restart:$KEYRING_PARAMS" 49--source include/restart_mysqld_no_echo.inc 50 51SELECT * FROM t1 LIMIT 10; 52 53SELECT rotate_system_key("percona_redo"); 54 55if ($wait_for_key_version) 56{ 57--let $wait_condition = select variable_value = 2 from performance_schema.global_status where variable_name = 'innodb_encryption_redo_key_version' 58--source include/wait_condition.inc 59} 60 61ALTER INSTANCE ROTATE INNODB MASTER KEY; 62 63DROP TABLE t1; 64 65# Crash/recovery test. 66CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; 67 68INSERT INTO t1 VALUES(0, "aaaaa"); 69INSERT INTO t1 VALUES(1, "bbbbb"); 70INSERT INTO t1 VALUES(2, "ccccc"); 71INSERT INTO t1 VALUES(3, "ddddd"); 72INSERT INTO t1 VALUES(4, "eeeee"); 73INSERT INTO t1 VALUES(5, "fffff"); 74INSERT INTO t1 VALUES(6, "ggggg"); 75INSERT INTO t1 VALUES(7, "hhhhh"); 76INSERT INTO t1 VALUES(8, "iiiii"); 77INSERT INTO t1 VALUES(9, "jjjjj"); 78INSERT INTO t1 SELECT * FROM t1; 79INSERT INTO t1 SELECT * FROM t1; 80INSERT INTO t1 SELECT * FROM t1; 81INSERT INTO t1 SELECT * FROM t1; 82INSERT INTO t1 SELECT * FROM t1; 83INSERT INTO t1 SELECT * FROM t1; 84INSERT INTO t1 SELECT * FROM t1; 85INSERT INTO t1 SELECT * FROM t1; 86INSERT INTO t1 SELECT * FROM t1; 87 88# Restart to confirm the encryption info can be retrieved properly. 89--let restart_parameters="restart:$KEYRING_PARAMS" 90--let $restart_hide_args = 1 91--source include/kill_and_restart_mysqld.inc 92 93SELECT * FROM t1 LIMIT 10; 94 95let $restart_parameters = restart: $KEYRING_PARAMS --general-log --log-output=FILE --general_log_file=$MYSQL_TMP_DIR/keyring_query_log; 96--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH 97--replace_regex /\.dll/.so/ 98--source include/restart_mysqld_no_echo.inc 99 100INSERT INTO t1 SELECT * FROM t1; 101DROP TABLE t1; 102 103let $restart_parameters = restart: $KEYRING_PARAMS --general-log --log-output=FILE --general_log_file=$MYSQL_TMP_DIR/keyring_query_log; 104--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH 105--replace_regex /\.dll/.so/ 106--source include/restart_mysqld_no_echo.inc 107 108# Check no effect of block_encryption_mode = 'aes-256-cbc' variable on table encryption 109SET block_encryption_mode = 'aes-256-cbc'; 110# Test encryption . 111CREATE DATABASE tde_db; 112CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENCRYPTION = 'Y' ENGINE = InnoDB; 113 114INSERT INTO tde_db.t1 VALUES(0, 'abc'); 115INSERT INTO tde_db.t1 VALUES(1, 'xyz'); 116INSERT INTO tde_db.t1 VALUES(2, null); 117INSERT INTO tde_db.t1 VALUES(3, null); 118SELECT * FROM tde_db.t1 LIMIT 10; 119ALTER INSTANCE ROTATE INNODB MASTER KEY; 120SELECT * FROM tde_db.t1 LIMIT 10; 121--echo # Mysqldump output 122--exec $MYSQL_DUMP --compact --skip-comments --databases tde_db 123--echo # Redirecting mysqlpump output to MYSQL_TMP_DIR/mysqlpump_encrypt.sql 124--exec $MYSQL_PUMP --default-parallelism=1 --databases tde_db > $MYSQL_TMP_DIR/mysqlpump_encrypt.sql 125DROP DATABASE tde_db; 126 127--let SEARCH_FILE=$MYSQL_TMP_DIR/keyring_query_log 128let SEARCH_PATTERN= ALTER INSTANCE ROTATE INNODB MASTER KEY; 129--source include/search_pattern.inc 130 131--echo # Loading tables from mysqlpump_encrypt.sql 132--exec $MYSQL --skip-comments < $MYSQL_TMP_DIR/mysqlpump_encrypt.sql 133SELECT * FROM tde_db.t1 LIMIT 10; 134INSERT INTO tde_db.t1 VALUES(4, null); 135SELECT * FROM tde_db.t1 LIMIT 10; 136DROP DATABASE tde_db; 137# 138 139# Cleanup 140--remove_file $MYSQL_TMP_DIR/keyring_query_log 141--remove_file $MYSQL_TMP_DIR/mysecret_keyring2 142--remove_file $MYSQL_TMP_DIR/mysqlpump_encrypt.sql 143eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table; 144