1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# InnoDB transparent tablespace data encryption
# This test case will test basic encryption support features.
--disable_query_log
call mtr.add_suppression("Error generating data for Data ID");
--enable_query_log
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
SET GLOBAL innodb_file_per_table = 1;
SELECT @@innodb_file_per_table;
# Create a table with encryption
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(0, "aaaaa");
INSERT INTO t1 VALUES(1, "bbbbb");
INSERT INTO t1 VALUES(2, "ccccc");
INSERT INTO t1 VALUES(3, "ddddd");
INSERT INTO t1 VALUES(4, "eeeee");
INSERT INTO t1 VALUES(5, "fffff");
INSERT INTO t1 VALUES(6, "ggggg");
INSERT INTO t1 VALUES(7, "hhhhh");
INSERT INTO t1 VALUES(8, "iiiii");
INSERT INTO t1 VALUES(9, "jjjjj");
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
SELECT * FROM t1 LIMIT 10;
# Restart to confirm the encryption info can be retrieved properly.
--let $restart_parameters= restart: $PLUGIN_DIR_OPT
--source include/restart_mysqld_no_echo.inc
SELECT * FROM t1 LIMIT 10;
# Key rotation.
ALTER INSTANCE ROTATE INNODB MASTER KEY;
DROP TABLE t1;
# Crash/recovery test.
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
INSERT INTO t1 VALUES(0, "aaaaa");
INSERT INTO t1 VALUES(1, "bbbbb");
INSERT INTO t1 VALUES(2, "ccccc");
INSERT INTO t1 VALUES(3, "ddddd");
INSERT INTO t1 VALUES(4, "eeeee");
INSERT INTO t1 VALUES(5, "fffff");
INSERT INTO t1 VALUES(6, "ggggg");
INSERT INTO t1 VALUES(7, "hhhhh");
INSERT INTO t1 VALUES(8, "iiiii");
INSERT INTO t1 VALUES(9, "jjjjj");
# Restart to confirm the encryption info can be retrieved properly.
let $restart_parameters = restart: $PLUGIN_DIR_OPT;
--source include/restart_mysqld_no_echo.inc
SELECT * FROM t1 LIMIT 10;
DROP TABLE t1;
let $restart_parameters = restart: $PLUGIN_DIR_OPT --general-log --log-output=FILE --general_log_file=$MYSQL_TMP_DIR/keyring_query_log;
--source include/restart_mysqld_no_echo.inc
#
# Check no effect of block_encryption_mode = 'aes-256-cbc' variable on table encryption
SET block_encryption_mode = 'aes-256-cbc';
# Test encryption .
--disable_warnings
DROP DATABASE IF EXISTS tde_db;
CREATE DATABASE tde_db;
CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENCRYPTION = 'Y' ENGINE = InnoDB;
--enable_warnings
#
INSERT INTO tde_db.t1 VALUES(0, 'abc');
INSERT INTO tde_db.t1 VALUES(1, 'xyz');
INSERT INTO tde_db.t1 VALUES(2, null);
INSERT INTO tde_db.t1 VALUES(3, null);
SELECT * FROM tde_db.t1 LIMIT 10;
ALTER INSTANCE ROTATE INNODB MASTER KEY;
SELECT * FROM tde_db.t1 LIMIT 10;
--echo # Mysqldump output
--exec $MYSQL_DUMP --compact --skip-comments --databases tde_db
--echo # Redirecting mysqlpump output to MYSQL_TMP_DIR/mysqlpump_encrypt.sql
--exec $MYSQL_PUMP --default-parallelism=1 --databases tde_db > $MYSQL_TMP_DIR/mysqlpump_encrypt.sql
DROP DATABASE tde_db;
--let SEARCH_FILE=$MYSQL_TMP_DIR/keyring_query_log
let SEARCH_PATTERN= ALTER INSTANCE ROTATE INNODB MASTER KEY;
--source include/search_pattern.inc
--echo # Loading tables from mysqlpump_encrypt.sql
--exec $MYSQL --skip-comments < $MYSQL_TMP_DIR/mysqlpump_encrypt.sql
SELECT * FROM tde_db.t1 LIMIT 10;
INSERT INTO tde_db.t1 VALUES(4, null);
SELECT * FROM tde_db.t1 LIMIT 10;
DROP DATABASE tde_db;
#
--echo #
--echo # Bug #26634507 CREATE_OPTIONS FLD IN INFORMATION_SCHEMA.TABLES NOT
--echo # FILLING PROPERLY.
--echo # The CREATE_OPTIONS field from I_S.TABLES should show the option
--echo # 'ENCRYPTION='.
--echo #
CREATE TABLE not_encrypted1 (col1 INT) ENCRYPTION='n';
CREATE TABLE not_encrypted2 (col1 INT) ENCRYPTION='N';
CREATE TABLE encrypted1 (col1 INT) ENCRYPTION='y';
CREATE TABLE encrypted2 (col1 INT) ENCRYPTION='Y';
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%encrypted%'
ORDER BY TABLE_NAME;
DROP TABLE encrypted1;
DROP TABLE not_encrypted1;
DROP TABLE encrypted2;
DROP TABLE not_encrypted2;
--let $restart_parameters = restart: $PLUGIN_DIR_OPT
--source include/restart_mysqld_no_echo.inc
--remove_file $MYSQL_TMP_DIR/keyring_query_log
--remove_file $MYSQL_TMP_DIR/mysqlpump_encrypt.sql
eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
|
