.::: Find ENCRYPTION_KEY_ID & CURRENT_KEY_ID MariaDB from OS & Database MariaDB :::.

 

correlation https://teguhth.blogspot.com/2023/06/enable-encryption-table-database-for.html
https://teguhth.blogspot.com/2023/05/how-to-remove-encryptionkeyid-from-file.html


A. from OS

1. query 1 
grep -o 'ENCRYPTION_KEY_ID`=[0-9]*' teguhth_enc.sql | sort | uniq

2. query 2

grep -o 'ENCRYPTION_KEY_ID`=[0-9]*' teguhth_enc.sql | cut -d= -f2 | sort | uniq

3. query 3

grep -i  ENCRYPTION_KEY_ID teguhth_enc.sql
 

 

B. from MariaDB

1. query general

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION where name LIKE '%teguhth%' ;
 

2. query 1 

SELECT DISTINCT
  e.CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e
JOIN information_schema.INNODB_SYS_TABLESPACES ts
     ON e.SPACE = ts.SPACE
WHERE ts.NAME LIKE 'teguhth_enc%'
ORDER BY e.CURRENT_KEY_ID;

SELECT DISTINCT e.CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e JOIN information_schema.INNODB_SYS_TABLESPACES ts ON e.SPACE = ts.SPACE WHERE ts.NAME LIKE 'teguhth_enc%' ORDER BY e.CURRENT_KEY_ID;

3. query 2 specific key_id 

SELECT
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  e.CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e
JOIN information_schema.INNODB_SYS_TABLESPACES ts
     ON e.SPACE = ts.SPACE
JOIN information_schema.TABLES t
     ON ts.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
WHERE e.CURRENT_KEY_ID = 111
  AND t.TABLE_SCHEMA = 'teguhth_enc';
  
SELECT t.TABLE_SCHEMA,t.TABLE_NAME,e.CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e JOIN information_schema.INNODB_SYS_TABLESPACES ts ON e.SPACE = ts.SPACE JOIN information_schema.TABLES t ON ts.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME) WHERE e.CURRENT_KEY_ID = 111 AND t.TABLE_SCHEMA = 'teguhth_enc';
  
4. query 3 specific 3
 
SELECT
  ts.NAME AS tablespace_name,
  e.CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e
JOIN information_schema.INNODB_SYS_TABLESPACES ts
     ON e.SPACE = ts.SPACE
WHERE e.CURRENT_KEY_ID = 111
  AND ts.NAME LIKE 'teguhth_enc/%';
  
SELECT ts.NAME AS tablespace_name,e.CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION e JOIN information_schema.INNODB_SYS_TABLESPACES ts ON e.SPACE = ts.SPACE WHERE e.CURRENT_KEY_ID = 111 AND ts.NAME LIKE 'teguhth_enc/%';