Verifying MariaDB 10.1 encryption

Question

I have set up table-level InnoDB database encryption on MariaDB.

I'd like to know if there is any way to confirm that the data is truly encrypted. I've tried searching /var/lib/mysql/ibdata1 for sample data in the tables, but I don't know if that's a reliable test or not.

Answer 1

I posted this question on mariadb.com, and the suggestion there was to perfom a grep for some known data.

A DBA at Rackspace suggested using the strings command instead, to better handle the binary data, for example:

strings /var/lib/mysql/sample_table/user.ibd | grep "knownuser"

This approach returns no results on an encrypted table and does return results on an unencrypted table (assuming both have "knownuser" loaded into them).

Answer 2

You can query information_schema.innodb_tablespaces_encryption. When innodb tablespace is encrypted it is present in the table.

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION 
WHERE NAME LIKE 'db_encrypt%';

source