1SET GLOBAL innodb_file_per_table = ON; 2create table t1 (a varchar(255)) engine=innodb encrypted=yes; 3create table t2 (a varchar(255)) engine=innodb; 4show warnings; 5Level Code Message 6create table t3 (a varchar(255)) engine=innodb encrypted=no; 7insert t1 values (repeat('foobarsecret', 12)); 8insert t2 values (repeat('tempsecret', 12)); 9insert t3 values (repeat('dummysecret', 12)); 10# Wait max 10 min for key encryption threads to encrypt all spaces 11SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; 12NAME 13test/t3 14SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; 15NAME 16innodb_system 17mysql/innodb_index_stats 18mysql/innodb_table_stats 19mysql/transaction_registry 20test/t1 21test/t2 22FLUSH TABLES t1,t2,t3 FOR EXPORT; 23# t1 yes on expecting NOT FOUND 24NOT FOUND /foobarsecret/ in t1.ibd 25# t2 ... on expecting NOT FOUND 26NOT FOUND /tempsecret/ in t2.ibd 27# t3 no on expecting FOUND 28FOUND 12 /dummysecret/ in t3.ibd 29# ibdata1 expecting NOT FOUND 30NOT FOUND /foobarsecret/ in ibdata1 31UNLOCK TABLES; 32# Now turn off encryption and wait for threads to decrypt everything 33SET GLOBAL innodb_encrypt_tables = off; 34# Wait max 10 min for key encryption threads to decrypt all spaces 35SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; 36NAME 37innodb_system 38mysql/innodb_index_stats 39mysql/innodb_table_stats 40mysql/transaction_registry 41test/t2 42test/t3 43SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; 44NAME 45test/t1 46FLUSH TABLES t1,t2,t3 FOR EXPORT; 47# t1 yes on expecting NOT FOUND 48NOT FOUND /foobarsecret/ in t1.ibd 49# t2 ... default expecting FOUND 50FOUND 12 /tempsecret/ in t2.ibd 51# t3 no on expecting FOUND 52FOUND 12 /dummysecret/ in t3.ibd 53# ibdata1 expecting NOT FOUND 54NOT FOUND /foobarsecret/ in ibdata1 55UNLOCK TABLES; 56# Now turn on encryption and wait for threads to encrypt all spaces 57SET GLOBAL innodb_encrypt_tables = on; 58# Wait max 10 min for key encryption threads to encrypt all spaces 59SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; 60NAME 61test/t3 62SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; 63NAME 64innodb_system 65mysql/innodb_index_stats 66mysql/innodb_table_stats 67mysql/transaction_registry 68test/t1 69test/t2 70FLUSH TABLES t1,t2,t3 FOR EXPORT; 71# t1 yes on expecting NOT FOUND 72NOT FOUND /foobarsecret/ in t1.ibd 73# t2 ... on expecting NOT FOUND 74NOT FOUND /tempsecret/ in t2.ibd 75# t3 no on expecting FOUND 76FOUND 12 /dummysecret/ in t3.ibd 77# ibdata1 expecting NOT FOUND 78NOT FOUND /foobarsecret/ in ibdata1 79UNLOCK TABLES; 80drop table t1, t2, t3; 81