Restoring nodes without keyring_file after shutdown after encrypted INSERT

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi everyone.
I have a 3-node cluster set up on Ubuntu 18.04 LTS (Bionic Beaver) with PXC 5.7 and bundled MySQL 5.7: version: 5.7.27-30-57-log
version_comment: Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
It worked just fine until I wanted to test automatic keyring_file donation. The features I had enabled beforehand are:
[LIST]
[]PXC automatic SSL communication channel (pxc_encrypted_cluster_traffic=1) on every node,
[]basic xtrabackup SST, with identical sst user on each node,
[*]my nodes have non-conflicting names, etc.
[/LIST] I shut down all of the nodes, checked /var/lib.mysql/grastate.dat to find the node that was safe to bootstrap and on this one node, I added the below configuration to /etc/mysql/my.cnf under:


[mysqld]
pxc_encrypted_cluster_traffic=1
early-plugin-load=/usr/lib/mysql/plugin/keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

I bootstrapped it and logged into MySQL as root on all nodes. The keyring_plugin plugin on was loaded on the bootstrapped node only, keyring_datafile variable was correct there and I could create a table with enabled encryption with the query:


CREATE TABLE demo.enctab (id INT PRIMARY KEY, nick VARCHAR(20) NOT NULL) ENCRYPTION='Y';

The table was then succesfully created. I checked if it was visible on the other 2 nodes - it wasn’t. Neither was there any keyring_file configuration enabled on them, despite what the user guide states: “If user has bootstrapped node with keyring enabled, then upcoming nodes of the cluster (…) will generate it (starting from Percona XtraDB Cluster 5.7.22).”
I had also read that “Actual keyring is generated only when node starts using encrypted tablespace.” So I tried to insert a row into said table on the first node:
INSERT INTO demo.enctab(nick) VALUES (‘ABC’);
MySQL refused to accept the transaction. The other 2 nodes shut down and the bootstrapped one transitioned into non-primary state, so I tried to recover the cluster in MySQL as PXC User Guide suggests:


SET GLOBAL wsrep_provider_options='pc.bootstrap=true';

It worked for the first node but the other 2 still couldn’t connect to the cluster, so I checked for .pids and .sock.locks in /var/run/mysqld/ - there was a sock lock present on both crashed nodes - I removed it. That didn’t help. I added


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

to their my.cnf files - didn’t help. Now I don’t know what to do. Any help would be appreciateed.

2

Hi zmusiato

Could you share the my.cnf files of each node, and also any error files and logs that are being written (database and relevant system logs).

What’s your situation? Do you have a production system issue here or is it a development/test environment?
I can’t guarantee a rapid turnaround on Forum questions, so if you have an urgent need you may need to talk to our team about professional services. I can introduce you if that’s needed.

Meanwhile, though, get the files across and I will bring your question to the attention of the team.

3

OK, great thanks, if there’s no urgency I can see if I can find community help for you.
:slight_smile:

4

So, I found the solution: my.cnf lacked the keyring_file_data option under the [xtrabackup] section. So, the keyring was never transmitted in SST by xtrabackup hence the crash. I set up a new cluster using the correct configuration and it works now. The solution was written about on the PXC blog but the needed information is absent in both the PDF User Guide and the on-line documentation. I suggest corrections be made so other newcomers to Percona software aren’t as clueless as myself. :slight_smile:

5

Ah well done, and thank you sincerely for the update. It will, as you say, help other users going forward.