ERROR: please confirm the keyfile is match and keyring plugin is loaded

Question

I want to create a copy of my Production DB to a new instance.

There is a innobackupex running on the prod every night looks like this:

innobackupex --user=username --password=password --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path

I copied the mybackup-path contents to my new instance and then prepared it by as following:

xtrabackup --prepare --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path-in-new-server

then copy over files --copy-back and chowned the data folder to mysql & everything is working

BUT I get "please confirm the keyfile is match and keyring plugin is loaded".

I have added updated my.cnf to early-plugin-load but still nothing

early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

When I start mysql, and run SHOW PLUGINS, Keyring is ACTIVE,

My both instances are the same and both have ubuntu and Percona Server 5.7

I'm sure I'm missing something, and I believe it is related to keyring, can someone help?

Paul White · Accepted Answer · 2019-11-21 17:04:37Z

I have solved my issue:

Make sure that the backup taken by xtrabackup and the keyring file in /var/lib/mysql-keyring/keyring are from the same server.

Also after copying the keyring file to the destination server, make sure to chown mysql:mysql to it.

The Keyring file should be copied as is not the contents of the file.

To verify if things are working, you can create a new DB and some new tables, also insert some data, that are encrypted then run the following

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

You need to get the result tables. Now try to select from those tables, you should see the data inserted.

1 Answer 1