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:
Data Over Wire:
We are about to explain how to make your PXC installation encrypted properly to ensure compliance.
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,
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.
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'
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
Replication traffic in PXC majorly involves the following
We can have this variable “ pxc-encrypt-cluster-traffic = ON” to enable the cluster traffic to be encrypted under mysqld section of cnf
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
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=1master_verify_checksumbinlog_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 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://192.168.33.11,192.168.33.12,192.168.33.13wsrep_node_address = 192.168.33.11wsrep_node_name = 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 = 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; 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://0.0.0.0:4567') listening at ssl://0.0.0.0:4567</b><b>2018-04-24T17:59:48.622657Z 0 [Note] WSREP: (476f28e5, 'ssl://0.0.0.0:4567') 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://0.0.0.0:4567') listening at ssl://0.0.0.0:45672018-04-24T18:09:50.293395Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') 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 '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: none2018-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: none2018-04-24T18:09:50.313615Z 0 [Note] WSREP: (ae0ecc92, 'ssl://0.0.0.0:4567') connection established to ae0ecc92 ssl://192.168.33.12:45672018-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<b>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 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 '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 off2018-04-24T18:09:57.217241Z 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.33.12:4444/xtrabackup_sst//12018-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: 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.