Enabling InnoDB Tablespace Encryption on Percona XtraDB Cluster 5.7

InnoDB Tablespace Encryption

InnoDB Tablespace EncryptionSecurity 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.

This article will not cover the basics of setting up a cluster nor will it cover how to create SSL certs and keys since both of these topics have been well explained here and here.

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:

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

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.

[xtrabackup]
keyring-file-data=/var/lib/mysql-keyring/keyring

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.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569