SET @saved_file_per_table = @@global.innodb_file_per_table;
SET @saved_encrypt_tables = @@global.innodb_encrypt_tables;
SET @saved_encryption_threads = @@global.innodb_encryption_threads;
SET @saved_encryption_key_id = @@global.innodb_default_encryption_key_id;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_encrypt_tables = ON;
SET GLOBAL innodb_encryption_threads = 4;
SET GLOBAL innodb_default_encryption_key_id=4;
create table tce(a serial, b blob, index(b(10))) engine=innodb
ROW_FORMAT=COMPRESSED encrypted=yes;
create table tc(a serial, b blob, index(b(10))) engine=innodb
ROW_FORMAT=COMPRESSED encrypted=no;
Warnings:
Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
create table te(a serial, b blob, index(b(10))) engine=innodb
encrypted=yes;
create table t(a serial, b blob, index(b(10))) engine=innodb
encrypted=no;
Warnings:
Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
create table tpe(a serial, b blob, index(b(10))) engine=innodb
page_compressed=yes encrypted=yes;
create table tp(a serial, b blob, index(b(10))) engine=innodb
page_compressed=yes encrypted=no;
Warnings:
Warning	140	InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1
begin;
insert into tce(b) values (repeat('secret',20));
insert into tc(b) values (repeat('secret',20));
insert into te(b) values (repeat('secret',20));
insert into t(b) values (repeat('secret',20));
insert into tpe(b) values (repeat('secret',20));
insert into tp(b) values (repeat('secret',20));
commit;
FLUSH TABLES tce, tc, te, t, tpe, tp FOR EXPORT;
backup: tce
backup: tc
backup: te
backup: t
backup: tpe
backup: tp
db.opt
t.cfg
t.frm
t.ibd
tc.cfg
tc.frm
tc.ibd
tce.cfg
tce.frm
tce.ibd
te.cfg
te.frm
te.ibd
tp.cfg
tp.frm
tp.ibd
tpe.cfg
tpe.frm
tpe.ibd
UNLOCK TABLES;
ALTER TABLE tce DISCARD TABLESPACE;
ALTER TABLE tc DISCARD TABLESPACE;
ALTER TABLE te DISCARD TABLESPACE;
ALTER TABLE t DISCARD TABLESPACE;
ALTER TABLE tpe DISCARD TABLESPACE;
ALTER TABLE tp DISCARD TABLESPACE;
restore: tce .ibd and .cfg files
restore: tc .ibd and .cfg files
restore: te .ibd and .cfg files
restore: t .ibd and .cfg files
restore: tpe .ibd and .cfg files
restore: tp .ibd and .cfg files
ALTER TABLE tce IMPORT TABLESPACE;
update tce set b=substr(b,1);
ALTER TABLE tc IMPORT TABLESPACE;
update tc set b=substr(b,1);
ALTER TABLE te IMPORT TABLESPACE;
update te set b=substr(b,1);
ALTER TABLE t IMPORT TABLESPACE;
update t set b=substr(b,1);
ALTER TABLE tpe IMPORT TABLESPACE;
update tpe set b=substr(b,1);
ALTER TABLE tp IMPORT TABLESPACE;
update tp set b=substr(b,1);
CHECK TABLE tce, tc, te, t, tpe, tp;
Table	Op	Msg_type	Msg_text
test.tce	check	status	OK
test.tc	check	status	OK
test.te	check	status	OK
test.t	check	status	OK
test.tpe	check	status	OK
test.tp	check	status	OK
DROP TABLE tce, tc, te, t, tpe, tp;
SET GLOBAL innodb_file_per_table = @saved_file_per_table;
SET GLOBAL innodb_encrypt_tables = @saved_encrypt_tables;
SET GLOBAL innodb_encryption_threads = @saved_encryption_threads;
SET GLOBAL innodb_default_encryption_key_id = @saved_encryption_key_id;