i) To encrypt a database table we run ALTER TABLE <TABLE_NAME> ENCRYPTION=”Y”

ii)  Similarly, we’ll need to encrypt every table in the database. For this, we can generate a master-sql file, which will have the query to alter all the existing DB tables.

iii) Follow the below procedure to create a master-query.

mysql> USE <database_name>;
mysql> TEE encrypt_tables.sql;
mysql> SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` ENCRYPTION=\"Y\" ;") 
         as encryption_query
       FROM 
         INFORMATION_SCHEMA.TABLES
       WHERE 
         TABLE_SCHEMA="<database_name>" AND
         TABLE_TYPE="BASE TABLE";
mysql> NOTEE;
mysql> EXIT;

TEE: This command reads the standard input and writes it to the file as well as standard output.

iv) Remove the table structure and the select query captured in the ‘encrypt_tables.sql’ file using the below command.

grep -i "ALTER" encrypt_tables.sql | sed '1d' | sed 's/|//g' > processed_encryption_tables.sql

v) Again login into MySQL database and run the ‘source processed_encryption_table.sql’.

mysql> USE <database_name>;
mysql> SOURCE processed_encryption_table.sql

vi) Check if all tables are encrypted are not by noting ENCRYPTION="Y" flag under the CREATE_OPTIONS column.

SELECT 
  TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS 
FROM 
  INFORMATION_SCHEMA.TABLES 
WHERE 
  CREATE_OPTIONS LIKE '%ENCRYPTION%';