CREATE DATABASE IF NOT EXISTS db1;

USE db1;

DROP TABLE IF EXISTS default_encrypted_tab1;

DROP TABLE IF EXISTS default_encrypted_tab2;

SET GLOBAL innodb_encrypt_tables=OFF;

SET GLOBAL innodb_file_per_table=OFF;

CREATE TABLE default_encrypted_tab1 ( id int PRIMARY KEY, str varchar(50) );

SET GLOBAL innodb_file_per_table=ON;

CREATE TABLE default_encrypted_tab2 ( id int PRIMARY KEY, str varchar(50) );

SELECT SLEEP(5);

SELECT SPACE, NAME, ENCRYPTION_SCHEME, ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'db1/default_encrypted_tab%'\G

SET GLOBAL innodb_encrypt_tables='FORCE';

SET GLOBAL innodb_encryption_threads=1;

SET GLOBAL innodb_encryption_rotate_key_age=1;

SELECT SLEEP(5);

SELECT SPACE, NAME, ENCRYPTION_SCHEME, ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'db1/default_encrypted_tab%'\G

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS db1;

Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> USE db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> DROP TABLE IF EXISTS default_encrypted_tab1;

Query OK, 0 rows affected (0.010 sec)

MariaDB [db1]> DROP TABLE IF EXISTS default_encrypted_tab2;

Query OK, 0 rows affected (0.005 sec)

MariaDB [db1]> SET GLOBAL innodb_encrypt_tables=OFF;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> SET GLOBAL innodb_file_per_table=OFF;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> CREATE TABLE default_encrypted_tab1 ( id int PRIMARY KEY, str varchar(50) );

Query OK, 0 rows affected (0.006 sec)

MariaDB [db1]> SET GLOBAL innodb_file_per_table=ON;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> CREATE TABLE default_encrypted_tab2 ( id int PRIMARY KEY, str varchar(50) );

Query OK, 0 rows affected (0.007 sec)

MariaDB [db1]> SELECT SLEEP(5);

+----------+

| SLEEP(5) |

+----------+

|        0 |

+----------+

1 row in set (5.000 sec)

MariaDB [db1]> SELECT SPACE, NAME, ENCRYPTION_SCHEME, ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'db1/default_encrypted_tab%'\G

Empty set (0.000 sec)

MariaDB [db1]> SET GLOBAL innodb_encrypt_tables='FORCE';

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> SET GLOBAL innodb_encryption_threads=1;

Query OK, 0 rows affected (0.001 sec)

MariaDB [db1]> SET GLOBAL innodb_encryption_rotate_key_age=1;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> SELECT SLEEP(5);

+----------+

| SLEEP(5) |

+----------+

|        0 |

+----------+

1 row in set (5.000 sec)

MariaDB [db1]> SELECT SPACE, NAME, ENCRYPTION_SCHEME, ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'db1/default_encrypted_tab%'\G

*************************** 1. row ***************************

               SPACE: 415

                NAME: db1/default_encrypted_tab2

   ENCRYPTION_SCHEME: 1

ROTATING_OR_FLUSHING: 0

1 row in set (0.000 sec)