.::: How to Remove ENCRYPTION_KEY_ID from file backup MariaDB / MySQL , delete & change Key :::.

 A. Remove Key
1. find ENCRYPTION_KEY_ID in backup mariadb example 'teguhth_encrypt.sql'
cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID

 

[root@teguhth-lab02 backup]# cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
[root@teguhth-lab02 backup]#
 2. remove ENCRYPTION_KEY_ID from backup

sed -i 's/`ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234//g' teguhth_encrypt.sql

[root@teguhth-lab02 backup]# sed -i 's/`ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234//g' teguhth_encrypt.sql
[root@teguhth-lab02 backup]#

3. check ENCRYPTION_KEY_ID in backup mariadb after remove
cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID

[root@teguhth-lab02 backup]# cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID
[root@teguhth-lab02 backup]#
 

B. Replace key (1111 to 222)

 

cat teguhthdb.sql | grep ENCRYPTION_KEY_ID

sed -i 's/`ENCRYPTION_KEY_ID`=1111/`ENCRYPTION_KEY_ID`=2222/g' teguhthdb.sql

cat teguhthdb.sql | grep ENCRYPTION_KEY_ID