Fulfilled Tablespace Encryption (TDE) in Percona Cluster

  • 时间: 2018-05-11 01:13:18

Encryptionis a very important form of security and It’s becoming a standard from a compliance perspective to ensure PCI, PIIand HIPPAcompliances. 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 theInnoDB tablespaces, Not the logs files.
  • Percona Server 5.7.20-19added a feature to encrypt binary logs and relay logs.
  • DBaaS like RDSsupports 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.

  • 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 to 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/certsGenerating 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, you can refer this

[mysqld]early-plugin-load = keyring_file.sokeyring-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 and also provide the same SSL files under [sst].

[xtrabackup]keyring-file-data=/var/lib/mysql-keyring/keyring[sst]streamfmt = xbstreamencrypt = 4tmpdir = /tmpssl-key=/etc/certs/server-key.pemssl-ca=/etc/certs/ca.pemssl-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,


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 with look as below

[mysqld]port = 3306socket = /var/lib/mysql/mysql.sockdatadir = /var/lib/mysqluser = mysqllog-error = /var/log/mysqld.errwsrep_provider = /usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address = gcomm://,, = = v11wsrep_sst_method = xtrabackup-v2wsrep_cluster_name = xtradbclusterwsrep_auto_increment_control = ONwsrep_sst_method = xtrabackup-v2wsrep_sst_auth = sstuser:s3cretPassinnodb_autoinc_lock_mode = 2server_id = 311<strong>#binlog encrption</strong>log_bin = mysql-binencrypt-binlog=1master_verify_checksumbinlog_checksum=crc32log_slave_updates<strong>#Cluster encryption</strong>pxc-encrypt-cluster-traffic = ONearly-plugin-load = keyring_file.sokeyring-file-data = /var/lib/mysql-keyring/keyringssl-key=/etc/certs/server-key.pemssl-ca=/etc/certs/ca.pemssl-cert=/etc/certs/server-cert.pem[sst]streamfmt = xbstreamencrypt = 4tmpdir = /tmpssl-key=/etc/certs/server-key.pemssl-ca=/etc/certs/ca.pemssl-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 =; 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; s<b>ocket.ssl_ca = /etc/certs/ca.pem; socket.ssl_cert = /etc/</b><b>certs/server-cert.pem; socket.ssl_cipher = AES128-SHA; socket.ssl_compression = YES; socket.ssl_key = /etc/certs/server-key.pem; </b>2018-04-24T17:59:48.619657Z 0 [Note] WSREP: Assign initial position for certification: 3, protocol version: -12018-04-24T17:59:48.619875Z 0 [Note] WSREP: Preparing to initiate SST/IST2018-04-24T17:59:48.619910Z 0 [Note] WSREP: Starting replication2018-04-24T17:59:48.619946Z 0 [Note] WSREP: Setting initial position to 300efff9-431e-11e8-98e0-e27f04800ef7:32018-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 context2018-04-24T17:59:48.621385Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:02018-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 shutdown2018-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 shutdown2018-04-24T17:59:48.622330Z 0 [Note] WSREP: GMCast version 0<b>2018-04-24T17:59:48.622647Z 0 [Note] WSREP: (476f28e5, 'ssl://') listening at ssl://</b><b>2018-04-24T17:59:48.622657Z 0 [Note] WSREP: (476f28e5, 'ssl://') multicast: , ttl: 1</b>2018-04-24T17:59:48.623454Z 0 [Note] WSREP: EVS version 02018-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_recovery2018-04-24T17:59:48.626063Z 0 [Note] WSREP: Node 476f28e5 state primary2018-04-24T17:59:48.626176Z 0 [Note] WSREP: Current view of cluster as seen by this nodeview (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://') listening at ssl:// 0 [Note] WSREP: (ae0ecc92, 'ssl://') multicast: , ttl: 12018-04-24T18:09:50.294414Z 0 [Note] WSREP: EVS version 02018-04-24T18:09:50.294860Z 0 [Note] WSREP: gcomm: connecting to group 'xtradbcluster', peer ','2018-04-24T18:09:50.312625Z 0 [Note] WSREP: SSL handshake successful, remote endpoint ssl:// local endpoint ssl:// cipher: AES128-SHA compression: none2018-04-24T18:09:50.313114Z 0 [Note] WSREP: SSL handshake successful,remote endpoint ssl:// local endpoint ssl:// cipher: AES128-SHA compression: none2018-04-24T18:09:50.313615Z 0 [Note] WSREP: (ae0ecc92, 'ssl://') connection established to ae0ecc92 ssl:// 0 [Warning] WSREP: (ae0ecc92, 'ssl://') address 'ssl://' points to own listenin address, blacklisting<b>2018-04-24T18:09:50.314606Z 0 [Note] WSREP: SSL handshake successful, remote endpoint ssl:// local endpoint ssl:// cipher: AES128-SHA comp</b><b>ression: none</b>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:3Local state: 00000000-0000-0000-0000-000000000000:-12018-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 32018-04-24T18:09:51.306344Z 2 [Note] WSREP: Setting wsrep_ready to true2018-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 false2018-04-24T18:09:51.307298Z 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '' --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://') turning message relay requesting off2018-04-24T18:09:57.217241Z 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2| 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: 32018-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 IST2018-04-24T18:09:57.218020Z 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: 300efff9-431e-11e8-98e0-e27f04800ef72018-04-24T18:09:57.218477Z 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST2018-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: 02018-04-24T18:09:57.221534Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 300efff9-431e-11e8-98e0-e27f04800ef7:3<b>2018-04-24T18:10:06.909709Z WSREP_SST: [INFO] donor keyring received at: '/var/lib/mysql-keyring/donor-keyring'</b><b>2018-04-24T18:10:07.061101Z WSREP_SST: [INFO] Proceeding with SST.........</b><b>2018-04-24T18:10:07.350117Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!</b>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_Teu62018-04-24T18:10:33.692709Z WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/<b>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</b>2018-04-24T18:10:33.939633Z WSREP_SST: [INFO] Galera co-ords from recovery: 300efff9-431e-11e8-98e0-e27f04800ef7:32018-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 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.