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
|
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB encrypted=yes;
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB row_format=compressed encrypted=yes;
create procedure innodb_insert_proc (repeat_count int)
begin
declare current_num int;
set current_num = 0;
while current_num < repeat_count do
insert into t1 values (current_num,repeat('foobar',42));
insert into t2 values (current_num,repeat('temp', 42));
insert into t3 values (current_num,repeat('barfoo',42));
set current_num = current_num + 1;
end while;
end//
commit;
set autocommit=0;
call innodb_insert_proc(10000);
commit;
set autocommit=1;
# Wait max 10 min for key encryption threads to encrypt all spaces
# tablespaces should be now encrypted
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
FLUSH TABLE t1, t2, t3 FOR EXPORT;
# List before copying files
t1.cfg
t1.frm
t1.ibd
t2.cfg
t2.frm
t2.ibd
t3.cfg
t3.frm
t3.ibd
UNLOCK TABLES;
# Restarting server
# Done restarting server
# List before t1 DISCARD
t1.frm
t1.ibd
t2.frm
t2.ibd
t3.frm
t3.ibd
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t3 DISCARD TABLESPACE;
# Discarded tablespaces should be encrypted
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
# List after t1 DISCARD
t1.frm
t2.frm
t3.frm
# Tablespaces should be still encrypted
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
ALTER TABLE t1 IMPORT TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
ALTER TABLE t3 IMPORT TABLESPACE;
# tablespaces should remain encrypted after import
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
ALTER TABLE t1 ENGINE InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes
ALTER TABLE t2 ENGINE InnoDB;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
ALTER TABLE t3 ENGINE InnoDB;
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED `encrypted`=yes
# Wait max 10 min for key encryption threads to encrypt all spaces
# Tablespaces should be encrypted after alter table
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
# Restarting server
# Done restarting server
# Verify that tables are still usable
SELECT COUNT(1) FROM t1;
COUNT(1)
10000
SELECT COUNT(1) FROM t2;
COUNT(1)
10000
SELECT COUNT(1) FROM t3;
COUNT(1)
10000
# Tablespaces should be encrypted after restart
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1, t2, t3;
|
