MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
MySQL Cluster 8.0.31 Transparent Data Encryption

With MySQL Cluster you can now from version 8.0.31 use transparent data encryption (TDE). Together with the encrypted backup feature it encrypts all data persisted on disk for tables that uses the Ndb storage engine in MySQL. This complements other already existing features that encrypts data at rest in MySQL.

TDE encryption uses XTS-AES and is transparent to applications accessing the data via MySQL or directly using the C++ NdbAPI. The same application can run unmodified whether TDE is on or off. TDE is turned on by the administrator of the Ndb cluster and can not be turned off (or on) via MySQL, ClusterJ, or NdbAPI.

Data in memory is not encrypted by TDE, make sure your operating system or virtualization environment does not copy memory to disk, like swapping, core dumps, and, suspend to disk.

Ndb data nodes should be configured to lock their memory to RAM to avoid swapping, recommended also if TDE is not used. And measures are taken to avoid core dumps if a node stops unexpectedly.

The above encryption options are specific for encrypting data stored by Ndb data nodes, but your system may also have some MySQL servers that store table content on disk. MySQL Server has additional functionality to encrypt this data:

  • transparent data encryption for InnoDB storage engine
  • encrypted binary log files and relay log files
  • encrypted audit log files
  • encrypted backups for InnoDB storage engine

See MySQL Secure Deployment Guide, Appendix A for more details.

Using Transparent Data Encryption

Configure Transparent Data Encryption

If you are about to create a new cluster and want to use TDE, make sure to set EncryptedFileSystem in your cluster configuration file (config.ini).

# config.ini
[DB default]
EncryptedFileSystem=1
...

Then also supply the password in the script that starts each data node (ndbmtd). There are a few variants to do that.

Supplying a Password to a Data Node

Each data node encrypts its data independently of other data nodes and each data node can use different password.

Simplest way to supply the password may be by using option defaults file. That file provides the defaults to the command line argument for mysql and ndb programs, but mind that the password is stored in plain text. Using this one do not need to change the start script for the data nodes.

# /etc/mysql/my.cnf
[ndbd]
filesystem-password="1234"
...

If you do not like to reuse the global option file /etc/mysql/my.cnf you could create your own ndbd.cnf and refer to that when starting the data node:

$ ndbmtd --defaults-file=/path/to/ndbd.cnf ...

Note --defaults-file option must be the first argument on command line.

And, do not allow others then the user running ndbmtd to access the file.

Passwords can contain printable ASCII characters except !, ', ", `, $, %, \, and ^. If your password is binary or may contain other characters you could hexify it, the resulting password can be up to 512 characters long.

If you have a program (called pwdtool in examples) that can fetch the password in a more secure way and print it, that can be used in the start script piping it to the data node using the --filesystem-password-from-stdin option:

# ndbd start script
ENCRYPTED_FS=1 # Match with EncryptedFileSystem setting in config.ini
ARGS=...
case "$1" in
start)
  if [ "${ENCRYPTED_FS}" -eq 1 ]; then
    pwdtool | ndbmtd ${ARGS} --filesystem-password-from-stdin
  else
    ndbmtd ${ARGS}
  fi
  ;;
...

Or if your script uses bash or ksh you can use Here Strings (<<<word):

# ndbd start script
ENCRYPTED_FS=1 # Match with EncryptedFileSystem setting in config.ini
ARGS=...
case "$1" in
start)
  if [ "${ENCRYPTED_FS}" -eq 1 ]; then
    ndbmtd ${ARGS} --filesystem-password-from-stdin <<<$(pwdtool)
  else
    ndbmtd ${ARGS}
  fi
  ;;
...

Make sure that the tool includes a line break after printing the password, that serves as protection against truncated passwords.

The above examples all avoid having the password on command line or in environment variables which is considered unsafe (test “ps -oargs 1”).

Transparent Data Encryption and MySQL Cluster Manager

MySQL Cluster Manager (MCM) 8.0.31 supports TDE for Ndb. To enable it follow the steps below.

Create a private file that contains only the password and a line break:

$ echo "t0ps3cr3t" > /path/to/my_secret
$ chmod 600 /path/to/my_secret

Then enable TDE in MCM, see Setting Up Encryption section in MCM User Manual:

mcm> set filesystem-password-file:ndbmtd=/path/to/my_secret mycluster;
mcm> set EncryptedFileSystem:ndbmtd=1 mycluster;

Encrypted Backups

When using TDE you probably also want the backup files to be encrypted. For that also add RequireEncryptedBackup to your cluster configuration, that way data nodes will reject any request for backup without any backup password provided.

# config.ini
[DB default]
RequireEncryptedBackup=1
...

Note, the backup password is provided by the client requesting the backup, for example using START BACKUP command from the management client (ndb_mgm).

$ ndb_mgm
> START BACKUP ENCRYPT PASSWORD="t0ps3cr3t"
...

With the above changes all data nodes of the cluster will encrypt all table content stored on disk.

Switching Transparent Data Encryption On and Off

To turn TDE on and off in a running cluster, it is necessary to perform a node initial rolling restart. Each data node clears it’s own disk state and then rebuilds it, with or without encryption, using data fetched from the running data nodes.

# ndbd start script
ENCRYPTED_FS=1 # Match with EncryptedFileSystem setting in config.ini
ARGS=...
case "$1" in
...
initial-start)
  if [ "${ENCRYPTED_FS}" -eq 1 ]; then
    pwdtool | ndbmtd ${ARGS} --initial --filesystem-password-from-stdin
  else
    ndbmtd ${ARGS} --initial
  fi
  ;;
...

Note, for those of you that are accustomed to do node initial restarts from the management client with “RESTART -i” command, that will not be enough. The data node “angel” must also be restarted to correctly handle the change of file system password as the password is re-supplied only when the top level data node process is started.

I/O Threads Cpu Usage

Encryption and compression are offloaded from the main transaction processing path, and handled by a pool of I/O threads. Activating encryption or compression increases the cpu usage of these threads in alignment with the volume of data read or written. I/O threads can be locked to particular cpus to isolate transaction processing from increased cpu usage.

Key Management

When an encrypted data file is created it will get a set of randomly generated data encryption keys (DEK). For larger files more keys will be generated to avoid encrypting different parts of the file using same keys.

The DEKs are stored in the header of the file they encrypt, and are encrypted using a key encryption key (KEK).

The KEK in turn is randomly generated at initial node start and stored in a special secrets file (D1/NDBCNTR/S0.sysfile).

The secrets file is also encrypted, using a key derived with PBKDF2 from the file system password and randomly generated salts. The salts are stored in the header of the secrets file.

Rotate File System Password and All Keys

To change the file system password for a single data node:

  1. update password source
  2. node initial restart of data nodes as described in Switching Transparent Data Encryption On and Off above.

Combined with node initial rolling restart one can rotate the passwords and keys on all data nodes in cluster while cluster is still online.

Is Transparent Data Encryption Enabled?

There are a few ways to see if TDE is enabled for a data node.

Check Active Configuration

One can check the active configuration using the ndbinfo schema from a mysql client:

mysql> SELECT node_id 'NodeId', config_value 'EncryptedFileSystem'
  FROM ndbinfo.config_values cv
  JOIN ndbinfo.config_params cp
  ON cv.config_param = cp.param_number
  WHERE param_name = 'EncryptedFileSystem';
+--------+---------------------+
| NodeId | EncryptedFileSystem |
+--------+---------------------+
|      1 | 1                   |
|      2 | 0                   |
+--------+---------------------+
2 rows in set (0,05 sec)

Here node #1 uses TDE but node #2 does not.

Check Secrets File

If you have access to the data nodes file system (the directory named ndb_<nodeid>_fs) you can check for the secrets file D1/NDBCNTR/S0.sysfile. If the data node managed to start and the secrets file exists TDE is enabled on that node. And if data node managed to start without secrets file TDE is disabled on that node.

Check For Encrypted Files

One can also check if specific data files are encrypted by using the ndbxfrm tool, some examples (exact file names may vary):

# secrets file
$ ndbxfrm -i D1/NDBCNTR/S0.sysfile
File=D1/NDBCNTR/S0.sysfile, compression=no, encryption=yes
# in-memory checkpoint files
$ ndbxfrm -i LCP/0/T2F0.Data
File=LCP/0/T2F0.Data, compression=no, encryption=yes
# transaction redo log
$ ndbxfrm -i D8/DBLQH/S1.FragLog
File=D8/DBLQH/S1.FragLog, compression=no, encryption=yes
# on-disk column data tablespace
$ ndbxfrm -i TS/datafile.dat
File=TS/datafile.dat, compression=no, encryption=yes
# on-disk column data undo log
$ ndbxfrm -i LG/undofile.dat
File=LG/undofile.dat, compression=no, encryption=yes

References

Changes in MySQL NDB Cluster 8.0.31
File System Encryption for NDB Cluster
MySQL Secure Deployment Guide