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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
SET @start_encr_threads = @@global.innodb_encryption_threads;
SET @start_encrypt_tables = @@global.innodb_encrypt_tables;
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;
begin;
set statement unique_checks=0, foreign_key_checks=0 for
call innodb_insert_proc(100);
commit;
DROP PROCEDURE innodb_insert_proc;
# Wait max 10 min for key encryption threads to encrypt all spaces
FLUSH TABLES t1, t2, t3 FOR EXPORT;
# 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
db.opt
t1.cfg
t1.frm
t1.ibd
t2.cfg
t2.frm
t2.ibd
t3.cfg
t3.frm
t3.ibd
backup: t1
backup: t2
backup: t3
db.opt
t1.cfg
t1.frm
t1.ibd
t2.cfg
t2.frm
t2.ibd
t3.cfg
t3.frm
t3.ibd
UNLOCK TABLES;
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t3 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
restore: t2 .ibd and .cfg files
restore: t3 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT COUNT(*) FROM t1;
COUNT(*)
100
ALTER TABLE t2 IMPORT TABLESPACE;
SELECT COUNT(*) FROM t2;
COUNT(*)
100
ALTER TABLE t3 IMPORT TABLESPACE;
SELECT COUNT(*) FROM t3;
COUNT(*)
100
FLUSH TABLES t1, t2, t3 FOR EXPORT;
# 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
UNLOCK TABLES;
ALTER TABLE t1 FORCE;
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `encrypted`=yes
ALTER TABLE t2 FORCE;
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
ALTER TABLE t3 FORCE;
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED `encrypted`=yes
# Verify that tables are still usable
CHECK TABLE t1, t2, t3 EXTENDED;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t2 check status OK
test.t3 check status OK
FLUSH TABLES t1, t2, t3 FOR EXPORT;
# 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
UNLOCK TABLES;
DROP TABLE t1, t2, t3;
#
# MDEV-34670 IMPORT TABLESPACE unnecessary traverses
# tablespace list
#
SET GLOBAL innodb_encrypt_tables= OFF;
SET GLOBAL innodb_encryption_threads= 0;
CREATE TABLE t1(f1 int,f2 text)ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, "InnoDB");
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
ALTER TABLE t2 IMPORT TABLESPACE;
SET GLOBAL innodb_encryption_threads=2;
SET GLOBAL innodb_encrypt_tables = ON;
# Wait max 10 min for key encryption threads to encrypt all spaces
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0
AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry';
NAME
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0
AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry';
NAME
innodb_system
test/t1
test/t2
SET GLOBAL innodb_encrypt_tables = OFF;
# Wait max 10 min for key encryption threads to decrypt all spaces
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0
AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry';
NAME
innodb_system
test/t1
test/t2
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0
AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry';
NAME
DROP TABLE t1, t2;
SET GLOBAL innodb_encryption_threads=@start_encr_threads;
SET GLOBAL innodb_encrypt_tables=@start_encrypt_tables;
|
