Warning: I’m a noob - I know next to nothing about MySQL, and trying to learn as I deploy an enormously important project for my company.
Can someone help me understand the tablespace encryption setup? I’m confused about exactly what I’ve enabled, and what I should be enabling.
I first found this: [url]https://www.percona.com/blog/2018/03/06/enabling-innodb-tablespace-encryption-percona-xtradb-cluster-5-7/[/url]
I followed the above and have a cluster running with ‘pxc-encrypt-cluster-traffic=ON’ set. But does this only do cluster traffic encryption like the variable says? Or does this actually do tablespace encryption?
I searched a bit more and found this: [url]https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html#installation[/url]
That seems a lot more involved - was all of that abstracted by the cluster configuration? I definitely haven’t setup a vault server or anything. Can anyone tell me if I’ve done what’s required?
+++++++
Bonus questions:
-
How do I restore an XtraBackup backup on an XtraDB cluster? I can’t find any documentation about this. Do I restore on one node at a time and wait for sync or something? If I’ve enabled tablespace encryption on the cluster, does the database restore encrypt the tables in the restored databases?
-
(I’ll ask this over in the XtraBackup forum too) If I have an XtraBackup backup that contains multiple databases, and I need to have different databases go to different clusters, do I get a choice in where the restores take place? It looks like I just copy the DB files into the MySQL data directory (/var/lib/mysql for me) - is each database its own set of files, and can I just put them on another machine as I wish?
I really appreciate any help - like I said I’m a noob and really trying hard to learn.
Hi,
I first found this: [URL=“Enabling InnoDB Tablespace Encryption on Percona XtraDB Cluster 5.7 - Percona Database Performance Blog”]https://www.percona.com/blog/2018/03...b-cluster-5-7/[/URL]
I followed the above and have a cluster running with ‘pxc-encrypt-cluster-traffic=ON’ set. But does this only do cluster traffic encryption like the variable says? Or does this actually do tablespace encryption?
Correct, pxc-encrypt-cluster-traffic only pertains to cluster traffic encryption. I assume you enabled keyring_file plugin:
early-plugin-load=keyring_file.so
keyring-file-data=/var/lib/mysql-keyring/keyring
Once that is in place, to encrypt a table you then execute:
ALTER TABLE tblname ENCRYPTION="Y";
-
How do I restore an XtraBackup backup on an XtraDB cluster? I can’t find any documentation about this. Do I restore on one node at a time and wait for sync or something? If I’ve enabled tablespace encryption on the cluster, does the database restore encrypt the tables in the restored databases?
To restore a backup, the entire cluster should be shut down. Restore the backup to one node, bootstrap that node and start one node at a time forcing an SST. The tables will not be encrypted unless you run ALTER TABLE on each table you want to encrypt.
-
(I’ll ask this over in the XtraBackup forum too) If I have an XtraBackup backup that contains multiple databases, and I need to have different databases go to different clusters, do I get a choice in where the restores take place? It looks like I just copy the DB files into the MySQL data directory (/var/lib/mysql for me) - is each database its own set of files, and can I just put them on another machine as I wish?
You can restore the backup to different clusters, and then drop the databases that you don’t want to store in each cluster. I would like to stress that each cluster (eg 3-nodes), all 3 nodes should have the same exact data so you can’t have cluster1_node1 to have db1 while the cluster1_node2 doesn’t have db1.
Sorry for the late reply, I forgot the forum doesn’t automatically subscribe for notifications.
Thanks very much for the responses! This is exactly what I needed.
If I may, could I as one more noob question?
I don’t know how the MySQL keyring works. I have enabled the plugin, but I can’t determine how I need to use it. Are the keys automatically populated or do I need to insert/store/create the keys somehow? The keyring_file plugin seems to me to mean that I need to supply some kind of file with keys in it that I’ve created, but I can’t find documentation on how to do so. Also, I’m assuming that same keyring must be present on each node in the cluster.
Assuming I can create the keyring, there’s a best practice question: if I store the keyring file locally on the machine, tablespace encryption is meaningless, isn’t it? Anyone with access to the machine normally can be assumed to control whatever, anyhow, but if there’s a keyring file sitting around it just makes things easier for an attacker, no? I’m running in AWS - can I pass the keys to the database on startup via the init script (I can make it call KMS or something) and avoid leaving the keys in the filesystem? Honestly I don’t know how the keyring works, maybe it’s secured in a way I can’t think of.
Also, because AWS, before you ask yes I would much rather be looking at Aurora/RDS, but our vendor said no so I’m stuck with MySQL on EC2 instances. Life sucks.
The keys are created as needed (so there is nothing that you need to do, other than specify the keyring file location).
PXC will copy the keyring file as part of an SST, so there is no need to copy it yourself. This will then require the use of an encrypted transport for PXC internal communications (usually SSL).
The keyring_file plugin will store the keys locally, so the keyring file must be secured properly. More info can be found here:
[url]MySQL :: MySQL 5.7 Reference Manual :: 6.4.4.12 Keyring System Variables