Another MySQL DBA: February 2024

Here is a simple example showing a couple options for MariaDB Encryption.

You have to consider what you want to encrypt . The data communication (data in transit) or the data on the instance (data at rest).

This post is going to focus on the data at rest option using a AWS free tier node running on Amazon Linux. I will be using the world database on 2 different instances to show updating current tables with encryption as well as new loading tables to be auto-encrypted.

1st we will start with installs.. quick and simple just for this demo.

# yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=100

# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+

We will load the world db into server_id 100 instance.

# mysql < world.sql
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now we can see that currently, both instances are not using encryption.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

Now across both systems, I am going to set up Random Keys and encrypt them.

# mkdir /etc/mysql/

# mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc

ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc

Now we can set up the cnf file to enable the plugin as well as options for encryption.

# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

Restart MariaDB so we can check on the current status.

systemctl restart mariadb.service

mysql

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

| 100 |

+-------------+

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| world/city | 1 | 1 |

| world/country | 1 | 1 |

| world/countrylanguage | 1 | 1 |

+----------------------------+-------------------+----------------+

8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

| 200 |

+-------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

+----------------------------+-------------------+----------------+

5 rows in set (0.000 sec)

Load up the world data into the server_id 200 instance as well.

# mysql < world.sql
# mysql
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system | 1 | 1 |
| mysql/innodb_index_stats | 1 | 1 |
| mysql/gtid_slave_pos | 1 | 1 |
| mysql/innodb_table_stats | 1 | 1 |
| mysql/transaction_registry | 1 | 1 |
| world/city | 1 | 1 |
| world/country | 1 | 1 |
| world/countrylanguage | 1 | 1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

According to the information_schema.INNODB_TABLESPACES_ENCRYPTION we are encrypted now. However, they do not show that at the schema level. While they say it is encrypted if showing up in the INNODB_TABLESPACES_ENCRYPTION table, I would rather be sure and have it seen in the table and on the schema.

MariaDB [(none)]> show create table world.city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

Up to this point, you can see that both instances have been accounted for in the INNODB_TABLESPACES_ENCRYPTION schema after the restart or loading of the schema and data.

So... a few table alters will help here...

MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1

1 row in set (0.000 sec)

This is simple and etc so far.. now we need to enable binlogs and double check more.

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

Checking via a look at the binlogs....

mariadb-binlog--base64-output=DECODE-ROWS --verbose demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Good to see that it says it is being encrypted now.

MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
Table: city2
Create Table: CREATE TABLE `city2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)

I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...

mariadb-binlog --base64-output=DECODE-ROWS --verbose --read-from-remote-server demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225 0:06:06 server id 100 end_log_pos 296 CRC32 0x0c89f3bb Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225 0:06:06 server id 100 end_log_pos 325 CRC32 0x535776a2 Gtid list []
# at 325
#240225 0:06:06 server id 100 end_log_pos 363 CRC32 0x2ac4a61b Binlog checkpoint demo.000001
# at 363
#240225 0:09:40 server id 100 end_log_pos 405 CRC32 0x93e10dc4 GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225 0:09:40 server id 100 end_log_pos 501 CRC32 0x39269040 Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225 0:09:49 server id 100 end_log_pos 543 CRC32 0xde82b753 GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225 0:09:49 server id 100 end_log_pos 602 CRC32 0x05bbb9e6 Annotate_rows:
#Q> insert into city2 select * from city
#240225 0:09:49 server id 100 end_log_pos 661 CRC32 0x9e0b4e0d Table_map: `world`.`city2` mapped to number 21
# at 661

Hopefully, this can at least help get you started ....

Resources:

https://mariadb.com/kb/en/securing-mariadb-encryption/

Another MySQL DBA

Pages

Saturday, February 24, 2024

MariaDB Encryption ( data at rest )

Resources: