Always-On service is very important for Today businesses. MySQL High Availability using MySQL InnoDB Cluster provides Easy-to-setup/configure/use to allow Data to be ALWAYS available.
Data Protection is also one of the Key elements for Today.
With MySQL InnoDB Cluster, the article is written to provide a Tour how to ENABLE MySQL Transparent Data Encryption (TDE) on a running 3-Nodes MySQL InnoDB Cluster.
The full setup/demo Video is posted on YOUTUBE
Video : MySQL InnoDB Cluster with TDE + Encrypted File KeyRing Plugin
Link : https://youtu.be/_Mrnl7fE-KA
Demo Landscape
MySQL Version : 8.0.15 Enterprise Edition
Operating System : Oracle Linux 7.6
MySQL Shell : 8.0.15
MySQL TDE keyring Plugin : keyring-encrypted-file-plugin
https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/keyring-encrypted-file-plugin.html
3 Instances of MySQL Running on the same VM (hostname : node1)
Configuration Files (my1.cnf, my2.cnf and my3.cnf) are attached in this blog.
| Instance A | Instance B | Instance C | |
| Port | 3310 | 3320 | 3330 |
| DataDir | data/3310 | data/3320 | data/3330 |
| keyring_encrypted_file_data | password | password | password |
The diagram to show a 3-nodes MySQL InnoDB Cluster Status from 'MySQL Shell'
MySQL Router is setup to connect to the MySQL InnoDB Cluster where :
Port 6446 is for R/W Primary Node(s) access
A while-loop script is running at the TERMINAL to continuously access port 6446 for a Primary Node access :
# while [ 1 ]; do sleep 1; mysql -ugradmin -pgrpass -h127.0.0.1 -P6446 -e "select @@hostname, @@port;"; done
It shows the CONNECTION always connecting to the PRIMARY node [port :3310].
Port 6447 is for R/O Secondary Node(s) access
A while-loop script is running at the TERMINAL to continuously access port 6447 for a Primary Node access :
# while [ 1 ]; do sleep 1; mysql -ugradmin -pgrpass -h127.0.0.1 -P6447 -e "select @@hostname, @@port;"; done
It shows the CONNECTION always connecting to the SECONDARY nodes [port :3320 and 3330].
Enabling Transparent Data Encryption
The concept is to RESTART the nodes with "keyring_encypted_file.so" plugin from MySQL Enterprise Edition. This is done by having the ROLLING RESTART from the R/O nodes first and finally the PRIMARY node.
Steps
1. Shutdown the R/O node - 3330
2. Modify the my3.cnf to add the following in [mysqld] section
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3330/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
3. Start up the R/O node - 3330 [The node should rejoin the InnoDB Cluster AUTOMATICALLY ]
4. Repeat the Step [2] and Step [3] for Node - 3320
my2.cnf changes :
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3320/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
5. Repeat the Step [2] and Setp [3] for Node - 3310
my1.cnf changes :
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3320/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
At this point, the PRIMARY Node should be switched to another node because the Node1:3310 was 'shutdown'.
**** The 3 Nodes MySQL InnoDB Cluster with TDE enabled *****
Switching the PRIMARY node to node1:3310
mysqlsh>\connect gradmin:grpass@node1:3320
mysqlsh>var x = dba.getCluster()
mysqlsh> x.setPrimaryInstance('node1:3310')
Creating Table for TESTING 'TDE'
- Create ONE TABLE mydb.mytable - No TDE
- Create ONE TABLE mydb.mytable_enc - TDE enabled.
On PRIMARY Node (RW) :
mysql> create database if not exists mydb;
mysql> create table mydb.mytable (f1 int not null primary key, f2 varchar(200));
mysql> create table mydb.mytable_enc (f1 int not null primary key, f2 varchar(200)) encryption='Y';
INSERTING few rows of data to both table.
mysql> insert into mydb.mytable values (1, 'hello world'), (2, 'hello world'), (3, 'hello world');
mysql> insert into mydb.mytable values (1, 'hello world'), (2, 'hello world'), (3, 'hello world');
Checking the InnoDB Data File on mytable.ibd and mytable_enc.ibd
# strings <datafolder>/mydb/mytable_enc.ibd|grep hello
We can easily see data is actually exposed as plain text.
With MySQL Transparent Data Encryption (TDE) enabled for table : mytable_enc
A full screen shot with command
# strings <datafolder>/mydb/mytable_enc.ibd
What about the KEYS in the encrypted key store for 3 nodes
The HEXDUMP for 3310 encrypted key file as shown :
The HEXDUMP for 3320 encrypted key file as shown :
The HEXDUMP for 3330 encrypted key file as shown :
ALL keys are different. It means that those Data Files across different nodes will be physically different but LOGICALLY - they have the same data content.
Example Configuration files
Configuration [my1.cnf]
[mysqld]
datadir=/home/mysql/data/3310
basedir=/usr/local/mysql
log-error=/home/mysql/data/3310/my.error
port=3310
socket=/home/mysql/data/3310/my.sock
mysqlx-port=33100
mysqlx-socket=/home/mysql/data/3310/myx.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=101
# enable gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64
secure-file-priv=NULL
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3310/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
Configuration [my2.cnf]
[mysqld]
datadir=/home/mysql/data/3320
basedir=/usr/local/mysql
log-error=/home/mysql/data/3320/my.error
port=3320
socket=/home/mysql/data/3320/my.sock
mysqlx-port=33200
mysqlx-socket=/home/mysql/data/3320/myx.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=102
# enable gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64
secure-file-priv=NULL
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3320/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
Configuration [my3.cnf]
[mysqld]
datadir=/home/mysql/data/3330
basedir=/usr/local/mysql
log-error=/home/mysql/data/3330/my.error
port=3330
socket=/home/mysql/data/3330/my.sock
mysqlx-port=33300
mysqlx-socket=/home/mysql/data/3330/myx.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=103
# enable gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64
secure-file-priv=NULL
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/home/mysql/data/3330/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password
