## Identifying Encrypted Tablespaces and Schemas 

The INFORMATION_SCHEMA.INNODB_TABLESPACES  table, introduced in MySQL 8.0.13, includes an ENCRYPTION  column that can be used to identify encrypted tablespaces. 

mysql> SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE ENCRYPTION=’Y’\G *************************** 1. row *************************** SPACE: 4294967294 NAME: mysql SPACE_TYPE: General ENCRYPTION: Y *************************** 2. row *************************** SPACE: 2 NAME: test/t1 SPACE_TYPE: Single ENCRYPTION: Y *************************** 3. row *************************** SPACE: 3 NAME: ts1 SPACE_TYPE: General ENCRYPTION: Y 

When the ENCRYPTION  option is specified in a CREATE TABLE  or ALTER TABLE  statement, it is recorded in the CREATE_OPTIONS  column of INFORMATION_SCHEMA.TABLES . This column can be queried to identify tables that reside in encrypted file-per-table tablespaces. 

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE ’%ENCRYPTION%’; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | test | t1 | ENCRYPTION="Y" | +--------------+------------+----------------+ 

Query INFORMATION_SCHEMA.INNODB_TABLESPACES  to retrieve information about the tablespace associated with a particular schema and table. 

mysql> SELECT SPACE, NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME=’test/t1’; +-------+---------+------------+ | SPACE | NAME | SPACE_TYPE | +-------+---------+------------+ | 3 | test/t1 | Single | +-------+---------+------------+ 

You can identify encryption-enabled schemas by querying the INFORMATION_SCHEMA.SCHEMATA  table. 

mysql> SELECT SCHEMA_NAME, DEFAULT_ENCRYPTION FROM INFORMATION_SCHEMA.SCHEMATA WHERE DEFAULT_ENCRYPTION=’YES’; +-------------+--------------------+ | SCHEMA_NAME | DEFAULT_ENCRYPTION | +-------------+--------------------+ | test | YES | +-------------+--------------------+ 

## Monitoring Encryption Progress 

SHOW CREATE SCHEMA  also shows the DEFAULT ENCRYPTION  clause. 

You can monitor general tablespace and mysql  system tablespace encryption progress using Performance Schema. 

The stage/innodb/alter tablespace (encryption)  stage event instrument reports WORK_ESTIMATED  and WORK_COMPLETED  information for general tablespace encryption operations. 

