dump data from an encrypted database

Question

Hi I have a mysql server which is using mysql kering plugin to encrypt data. I want to dump a database with mysqlump from first mysql and import it to another mysql server which does not have encryption. when I want to import the data, it says

Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.

what is wrong?

Answer 1

I think the problem may stem from the table's creation options.

When a table is encrypted, the encryption flag is enabled

Example

mysql> show create table thistable \G
*************************** 1. row ***************************
       Table: thistable
Create Table: CREATE TABLE `thistable` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'
1 row in set (0.00 sec)

mysql>

What you need to do is dump the schema and data into separate files

Take the schema only dump and change ENCRYPTION='Y' to ENCRYPTION='N'

Then load the schema dump into the new mysql instance and finally load the data dump.