Friday, April 12, 2019

MySQL InnoDB Cluster - Enabling MySQL TDE with Rolling Restart

MySQL InnoDB Cluster + Enabling Transparent Data Encryption

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