Security is one of the hottest topics lately, and in this blog post, I will walk you through what needs to be configured to have a working three-node Percona XtraDB Cluster running with InnoDB Tablespace Encryption enabled.
Just to give you a brief history, InnoDB tablespace encryption was introduced in MySQL 5.7.11, and starting from Percona XtraDB Cluster 5.7.16 this feature was fully supported if coupled with SSL-based encryption of SST traffic. However, for this blog post I recommend using the latest Percona XtraDB Cluster 5.7.20-19 release. It has the recent fix that affects incremental state transfer when keyring-file-data is set.
What do you need to enable InnoDB tablespace encryption? If you are an avid reader of this blog, then you might have read this awesome article from Manjot Singh and Matthew Boehm about MySQL Encryption at rest – Part 2. The two important configuration options are:
This alone lets you use the keyring_file.so plugin to encrypt InnoDB tablespaces, given that InnoDB file per table is enabled. But to get state transfer (SST/IST) to work between cluster nodes you should also configure the SSL-related configuration in the [mysqld] and [sst] sections of your configuration file.
Doing It the Easy Way
To make life easier and less complicated, we’ve added an option to take care of the job for you through the automatic configuration of SSL encryption with one variable: pxc-encrypt-cluster-traffic=ON. This is the recommended option. Once set, it will look for the SSL keys and certificate files in the ssl-ca, ssl-cert and ssl-key options under [mysqld]. If you don’t set these, it then looks for the necessary SSL keys and certificate files in the data directory.
The next step is to create the SSL certs and keys by following the instructions in the manual. Note that for some distributions, like RPM packages, the SSL keys and certificate file are automatically created upon data directory initialization by invoking mysql_ssl_rsa_setup.
You only need to securely transfer the SSL files from one node to another. This doesn’t include the keyring file, which the wsrep_sst_xtrabackup-v2 script handles.
We recommend using wsrep_sst_method=xtrabackup-v2, so we need to declare the keyring-file-data option under the [xtrabackup] section of the configuration file.
Taking everything into consideration, we should have something like this as a working configuration file:
[mysqld] socket = /var/lib/mysql/mysql.sock datadir = /var/lib/mysql user = mysql log-error = /var/log/mysqld.err wsrep_cluster_name = my_pxc_cluster wsrep_provider = /usr/lib64/libgalera_smm.so wsrep_auto_increment_control = ON wsrep_sst_method = xtrabackup-v2 wsrep_sst_auth = sstuser:passw0rd wsrep_cluster_address = gcomm://10.0.3.251,10.0.3.226,10.0.3.240 wsrep_node_address = 10.0.3.251 wsrep_node_name = pxc_node1 innodb_autoinc_lock_mode = 2 innodb_file_per_table = 1 server_id = 100 log_bin = mysql-bin pxc-encrypt-cluster-traffic = ON early-plugin-load = keyring_file.so keyring-file-data = /var/lib/mysql-keyring/keyring [sst] streamfmt = xbstream [xtrabackup] keyring-file-data = /var/lib/mysql-keyring/keyring
Doing It The Hard Way
If you prefer to keep your SSL keys and certificate files in a separate directory outside of the data directory, then you should declare the SSL-related variables under the [mysqld] section like this:
[mysqld] ssl-ca = /etc/mysql/ca.pem ssl-cert = /etc/mysql/server-cert.pem ssl-key = /etc/mysql/server-key.pem pxc-encrypt-cluster-traffic = ON early-plugin-load = keyring_file.so keyring-file-data = /var/lib/mysql-keyring/keyring
Lastly, if you prefer not to use the pxc-encrypt-cluster-traffic variable, you will need to declare the same SSL-related variables under the [sst] section like this:
[sst] streamfmt = xbstream encrypt = 4 ssl-ca = /etc/mysql/ca.pem ssl-cert = /etc/mysql/server-cert.pem ssl-key = /etc/mysql/server-key.pem
And here is sample content from the log in the JOINER node
2018-02-01T15:19:14.099468Z 0 [Note] WSREP: Member 1.0 (pxc_enc_10.0.3.9) requested state transfer from '*any*'. Selected 0.0 (pxc_enc_10.0.3.152)(SYNCED) as donor. 2018-02-01T15:19:14.099562Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 70230) 2018-02-01T15:19:14.099753Z 2 [Note] WSREP: Requesting state transfer: success, donor: 0 2018-02-01T15:19:14.099873Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 39679102-c8e3-11e7-bdf7-77fd128ab7b1:70230 2018-02-01T15:19:16.526266Z 0 [Note] WSREP: (424bffbb, 'ssl://0.0.0.0:4567') connection to peer 424bffbb with addr ssl://10.0.3.9:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout) 2018-02-01T15:19:16.526845Z 0 [Note] WSREP: (424bffbb, 'ssl://0.0.0.0:4567') turning message relay requesting off 2018-02-01T15:19:18.807781Z WSREP_SST: [INFO] donor keyring received at: '/var/lib/mysql-keyring/donor-keyring' 2018-02-01T15:19:18.826503Z WSREP_SST: [INFO] Proceeding with SST......... 2018-02-01T15:19:18.994007Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete! 2018-02-01T15:19:34.571567Z 0 [Note] WSREP: 0.0 (pxc_enc_10.0.3.152): State transfer to 1.0 (pxc_enc_10.0.3.9) complete. 2018-02-01T15:19:34.572022Z 0 [Note] WSREP: Member 0.0 (pxc_enc_10.0.3.152) synced with group. 2018-02-01T15:19:34.684483Z WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst 2018-02-01T15:19:41.733319Z WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ 2018-02-01T15:19:41.853808Z WSREP_SST: [INFO] Moving sst keyring into place: moving /var/lib/mysql-keyring/donor-keyring to /var/lib/mysql-keyring/keyring 2018-02-01T15:19:41.866181Z WSREP_SST: [INFO] Galera co-ords from recovery: 39679102-c8e3-11e7-bdf7-77fd128ab7b1:70230 2018-02-01T15:19:41.877406Z 0 [Note] WSREP: SST complete, seqno: 70230
And from the DONOR node, we will see this from the log file:
2018-02-01T15:19:14.099510Z 0 [Note] WSREP: Member 1.0 (pxc_enc_10.0.3.9) requested state transfer from '*any*'. Selected 0.0 (pxc_enc_10.0.3.152)(SYNCED) as donor. 2018-02-01T15:19:14.099615Z 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 70230) 2018-02-01T15:19:14.099877Z 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2018-02-01T15:19:14.100194Z 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '10.0.3.9:4444/xtrabackup_sst//1' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --binlog 'pxc_enc_1-bin' --gtid '39679102-c8e3-11e7-bdf7-77fd128ab7b1:70230') 2018-02-01T15:19:14.100986Z 1 [Note] WSREP: DONOR thread signaled with 0 2018-02-01T15:19:16.071875Z 0 [Note] WSREP: (133946dd, 'ssl://0.0.0.0:4567') turning message relay requesting off 2018-02-01T15:19:17.679926Z WSREP_SST: [INFO] Streaming donor-keyring file before SST 2018-02-01T15:19:28.810417Z WSREP_SST: [INFO] Streaming the backup to joiner at 10.0.3.9 4444 2018-02-01T15:19:34.570569Z 0 [Note] WSREP: 0.0 (pxc_enc_10.0.3.152): State transfer to 1.0 (pxc_enc_10.0.3.9) complete. 2018-02-01T15:19:34.570628Z 0 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 70230) 2018-02-01T15:19:34.571971Z 0 [Note] WSREP: Member 0.0 (pxc_enc_10.0.3.152) synced with group. 2018-02-01T15:19:34.572061Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 70230)
It’s easy setting up Percona XtraDB Cluster with InnoDB tablespace encryption. We just need to make sure to declare the above configuration on all nodes for state transfer to work.