Fulfilled Tablespace Encryption (TDE) in Percona Cluster

May 10, 2018

Mins to Read

All

Percona XtraDB Cluster (PXC) offers robust high availability for your MySQL databases. But is your data secure? This comprehensive guide walks you through encrypting data at rest and in transit, fortifying your PXC against unauthorized access.

Encryption is a very important form of security and It’s becoming a standard from a compliance perspective to ensure PCI, PII and HIPPA compliances. Encryption needs to be performed for Data at Rest, Data over Wire.

Data At Rest:

Until 5.6, To encrypt the MySQL data files, Only disk level encryption possible (Gazzang, Luks)
From 5.7.11 MySQL added a feature for tablespace encryption. Which encrypts only the InnoDB tablespaces, Not the logs files.
Percona Server 5.7.20-19 added a feature to encrypt binary logs and relay logs.
DBaaS like RDS supports TDE (Transparent Data Encryption), It’s a disk level encryption through Amazon KMS.
On EC2, Google Cloud servers it’s possible to create the disk as encrypted during the time of creation and MySQL data can be stored on that disk.

Data Over Wire:

Encryption over the wire (network) can be setup configuring MySQL with SSL.

Now coming to the topic,

We are about to explain how to make your PXC installation encrypted properly to ensure compliance is met.

Encrypting Client Traffic / Cluster Communication Using SSL
Encrypting Table space With TableSpace Encryption
Encrypting SST
Encrypting Binary/Relay logs With Percona Server Binlog Encryption

Below is the test environment used for this test case,

Operating System: Centos7.4

PXC Version : 5.7.21-20-57-log Percona XtraDB Cluster (GPL),

In this blog, we will not explain the basic installation and configuration of PXC,

Step1: Generating SSL certificates

By Default during MySQL installation, we will be having self-signed certificates created under “/var/lib/mysql”, it’s always recommended to have the below certificates in a separate directory common in all cluster nodes.

ca-key.pem (CA key file)
Ca.pem (CA certification file)
Server-key.pem (server key file)
Server-cert.pem (server certification file)

You can copy these file generated during installation to a separate location or use “mysql_ssl_rsa_setup” to generate these file to the specified directory as below, in this demo I have used “/etc/certs”

 
# mysql_ssl_rsa_setup -d /etc/certs

Generating a 2048 bit RSA private key
.................+++.....+++
writing new private key to 'ca-key.pem'
Generating a 2048 bit RSA private key....................................................................................................................................+++
................+++
writing new private key to 'server-key.pem'
Generating a 2048 bit RSA private key
.............................+++
.........................................................+++
writing new private key to 'client-key.pem'

Step 2: Enabling variables for encryption

Encrypting Tablespace:

Tablespace encryption is done using keyring plugin, make sure to have individual table space for each table(“innodb_files_table=1“), you can refer here for TDE with details explanation

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

Encrypting Replication Traffic :

Replication traffic in PXC majorly involves the following

Write set replication, which is the major one
IST (Incremental state transfer) copies only the missing transaction from DONOR to JOINER
Service messages ensure all the nodes are synchronised

We can have this variable “pxc-encrypt-cluster-traffic = ON” to enable the cluster traffic to be encrypted under mysqld section of cnf

Encrypting SST traffic:

As you were aware SST refers to State Snapshot transfer, It’s always recommended to use xtrabackup-v2 as the SST method for safer and faster way for transfer, While doing the SST, keyfile has to be sent along with file for decryption, hence provide keyfile location under [xtrabackup] and also provide the same SSL files under [sst].

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

[sst]
streamfmt = xbstream
encrypt = 4
tmpdir = /tmp
ssl-key=/etc/certs/server-key.pem
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem

Encrypting Binlog/Relay log:

As you are aware, binlog contains the change data(writes), Replication uses binlogs to copy data between master and slave, Anyone having access to binlog basically means, he has access to your DATA, this is a potential risk, hence it makes trivial to enable binlog Encryption. Binlog encryption is supported from Percona Server for MySQL version 5.7.20-19, Below are the trivial variables to enable binlog/Relaylog encryption along with the basic variable for binlog, Binlog encryption also needs the keyring plugin to be enabled,

 
[mysqld]
encrypt-binlog=1
master_verify_checksum
binlog_checksum

Its mandatory to have the same setting on the slave server, which makes the relay log to be encrypted.

Consolidating and considering all the configs, A working config of PXC will look as below

 
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
user = mysql
log-error = /var/log/mysqld.err
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address = gcomm://192.168.33.11,192.168.33.12,192.168.33.13
wsrep_node_address = 192.168.33.11
wsrep_node_name = v11
wsrep_sst_method = xtrabackup-v2
wsrep_cluster_name = xtradbcluster
wsrep_auto_increment_control = ON
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:s3cretPass
innodb_autoinc_lock_mode = 2
server_id = 311
#binlog encrption
log_bin = mysql-bin
encrypt-binlog=1
master_verify_checksum
binlog_checksum=crc32
log_slave_updates

#Cluster encryption
pxc-encrypt-cluster-traffic = ON
early-plugin-load = keyring_file.so
keyring-file-data = /var/lib/mysql-keyring/keyring
ssl-key=/etc/certs/server-key.pem
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem

[sst]
streamfmt = xbstream
encrypt = 4
tmpdir = /tmp
ssl-key=/etc/certs/server-key.pem
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem

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

Now lets proceed to start the nodes, First node will be bootstrapped, make sure to have the cluster address as “wsrep_cluster_address=gcom://”

Below is the log from first node, which is limited to the SSL setting

 
2018-04-24T17:59:48.485704Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.33.11; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = yes; gcache.size = 8G; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 800; gcs.fc_master_slave= YES; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; socket.ssl_ca = /etc/certs/ca.pem; socket.ssl_cert = /etc/certs/server-cert.pem; socket.ssl_cipher = AES128-SHA; socket.ssl_compression = YES; socket.ssl_key = /etc/certs/server-key.pem; 

2018-04-24T17:59:48.619657Z 0 [Note] WSREP: Assign initial position for certification: 3, protocol version: -1
2018-04-24T17:59:48.619875Z 0 [Note] WSREP: Preparing to initiate SST/IST
2018-04-24T17:59:48.619910Z 0 [Note] WSREP: Starting replication
2018-04-24T17:59:48.619946Z 0 [Note] WSREP: Setting initial position to 300efff9-431e-11e8-98e0-e27f04800ef7:3
2018-04-24T17:59:48.620379Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2018-04-24T17:59:48.620489Z 0 [Note] WSREP: initializing ssl context
2018-04-24T17:59:48.621385Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2018-04-24T17:59:48.621800Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting forfirst time or re-booting after a graceful shutdown
2018-04-24T17:59:48.621810Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2018-04-24T17:59:48.622330Z 0 [Note] WSREP: GMCast version 0

2018-04-24T17:59:48.622647Z 0 [Note] WSREP: (476f28e5, 'ssl://0.0.0.0:4567') listening at ssl://0.0.0.0:4567
2018-04-24T17:59:48.622657Z 0 [Note] WSREP: (476f28e5, 'ssl://0.0.0.0:4567') multicast: , ttl: 1

2018-04-24T17:59:48.623454Z 0 [Note] WSREP: EVS version 0
2018-04-24T17:59:48.623800Z 0 [Note] WSREP: gcomm: connecting to group 'xtradbcluster', peer ''
2018-04-24T17:59:48.623858Z 0 [Note] WSREP: start_prim is enabled, turn off pc_recovery
2018-04-24T17:59:48.626063Z 0 [Note] WSREP: Node 476f28e5 state primary
2018-04-24T17:59:48.626176Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(PRIM,476f28e5,1)
memb {
476f28e5,0
}

Now let’s proceed to join the next node, Please find the logs as below DONOR→ JOINER

 
2018-04-24T18:09:50.293384Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') listening at ssl://0.0.0.0:4567
2018-04-24T18:09:50.293395Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') multicast: , ttl: 1
2018-04-24T18:09:50.294414Z 0 [Note] WSREP: EVS version 0
2018-04-24T18:09:50.294860Z 0 [Note] WSREP: gcomm: connecting to group 'xtradbcluster', peer '192.168.33.11:,192.168.33.12:'
2018-04-24T18:09:50.312625Z 0 [Note] WSREP: SSL handshake successful, remote endpoint ssl://192.168.33.12:41398 local endpoint ssl://192.168.33.12:4567 cipher: AES128-SHA compression: none
2018-04-24T18:09:50.313114Z 0 [Note] WSREP: SSL handshake successful,remote endpoint ssl://192.168.33.12:4567 local endpoint ssl://192.168.33.12:41398 cipher: AES128-SHA compression: none
2018-04-24T18:09:50.313615Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') connection established to ae0ecc92 ssl://192.168.33.12:4567
2018-04-24T18:09:50.313636Z 0 [Warning] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') address 'ssl://192.168.33.12:4567' points to own listenin address, blacklisting
2018-04-24T18:09:50.314606Z 0 [Note] WSREP: SSL handshake successful, remote endpoint ssl://192.168.33.11:4567 local endpoint ssl://192.168.33.12:35336 cipher: AES128-SHA compression: none
2018-04-24T18:09:51.305207Z 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 3)
2018-04-24T18:09:51.306246Z 2 [Note] WSREP: State transfer required:
Group state: 300efff9-431e-11e8-98e0-e27f04800ef7:3
Local state: 00000000-0000-0000-0000-000000000000:-1
2018-04-24T18:09:51.306312Z 2 [Note] WSREP: New cluster view: global state: 300efff9-431e-11e8-98e0-e27f04800ef7:3, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
2018-04-24T18:09:51.306344Z 2 [Note] WSREP: Setting wsrep_ready to true
2018-04-24T18:09:51.306429Z 2 [Warning] WSREP: Gap in state sequence.Need state transfer.
2018-04-24T18:09:51.306433Z 2 [Note] WSREP: Setting wsrep_ready to false
2018-04-24T18:09:51.307298Z 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.33.12' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5788' '' )
2018-04-24T18:09:53.810961Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') turning message relay requesting off
2018-04-24T18:09:57.217241Z 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.33.12:4444/xtrabackup_sst//1
2018-04-24T18:09:57.217414Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2018-04-24T18:09:57.217611Z 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
2018-04-24T18:09:57.217739Z 2 [Note] WSREP: Assign initial position for certification: 3, protocol version: 3
2018-04-24T18:09:57.217901Z 0 [Note] WSREP: Service thread queue flushed.
2018-04-24T18:09:57.217974Z 2 [Note] WSREP: Check if state gap can be serviced using IST
2018-04-24T18:09:57.218020Z 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: 300efff9-431e-11e8-98e0-e27f04800ef7
2018-04-24T18:09:57.218477Z 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
2018-04-24T18:09:57.218549Z 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (300efff9-431e-11e8-98e0-e27f04800ef7): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
2018-04-24T18:09:57.220866Z 0 [Note] WSREP: Member 1.0 (v12) requested state transfer from '*any*'. Selected 0.0 (v11)(SYNCED) as donor.
2018-04-24T18:09:57.221004Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 3)
2018-04-24T18:09:57.221495Z 2 [Note] WSREP: Requesting state transfer: success, donor: 0
2018-04-24T18:09:57.221534Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 300efff9-431e-11e8-98e0-e27f04800ef7:3
2018-04-24T18:10:06.909709Z WSREP_SST: [INFO] donor keyring received at: '/var/lib/mysql-keyring/donor-keyring'
2018-04-24T18:10:07.061101Z WSREP_SST: [INFO] Proceeding with SST.........
2018-04-24T18:10:07.350117Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!
2018-04-24T18:10:24.368719Z 0 [Note] WSREP: 0.0 (v11): State transfer to 1.0 (v12) complete.
2018-04-24T18:10:24.370118Z 0 [Note] WSREP: Member 0.0 (v11) synced with group.
2018-04-24T18:10:24.474538Z WSREP_SST: [INFO] Preparing the backup at /tmp/pxc_sst_VRBd/sst_Teu6
2018-04-24T18:10:33.692709Z WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/
2018-04-24T18:10:33.891894Z WSREP_SST: [INFO] Moving sst keyring into place: moving /var/lib/mysql-keyring/donor-keyring to /var/lib/mysql-keyring/keyring
2018-04-24T18:10:33.939633Z WSREP_SST: [INFO] Galera co-ords from recovery: 300efff9-431e-11e8-98e0-e27f04800ef7:3
2018-04-24T18:10:34.021154Z 0 [Note] WSREP: SST complete, seqno: 3

From the above logs, it has undergone SST successfully with encryption and synced with the cluster.

In the future version of PXC (MySQL 8.0) we may get the undo logs and redo logs encryption . It will make all data files (most) at rest to be encrypted.

Need expert help securing your MySQL databases? Mydbops, our open-source database management service, offers comprehensive MySQL support, including PXC configuration and optimization. Let our team of database specialists ensure your data's safety and performance. Contact Mydbops today!

No items found.