Shulou(Shulou.com)06/01 Report--

MariaDB how to use encryption features, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

I. Encryption Features of MariaDB

innodb table space encryption

innodb log encryption

binlog encryption

Aria table encryption

Temporary file encryption

Some limitations of encryption features:

The metadata file (.frm) is not currently encrypted;

Currently only MariaDB server can decrypt, mysqlbinlog tool does not support parsing encrypted binlog files

The xtrabackup tool currently cannot backup/restore MariaDB instances that use encryption features.

Slow query logs and error logs are not encrypted and may contain raw data.

II. Encrypted with MariaDB

In order to protect encrypted data, the key is generally stored in a different location than the data file. MariaDB's key management method allows you to develop key management plug-ins according to different privacy requirements. By default, you can use the file_key_management plug-in, which stores keys as files.

file_key_management plug-in:

Related parameters:

file_key_management_filename: key file location, such as/etc/my.cnf.d/file_key.txt

file_key_management_filekey: decryption password of the key file, if the key file is encrypted, it must be provided

file_key_management_encryption_algorithm: encryption algorithm, AES_CBC/AES_CTR

Key file format:

# MariaDB encryption file key1;561A4A02DA569D12EE4A4682369574322;561A4A02DA569D12EE4A4682369574443;87A6C96D487659137E316A467BEA646787A6C96D487659137E316A467BEA6467

Each row of keys consists of two parts, the first part is the key id, followed by the hexadecimal key.

Each table can be individually assigned a key id(1-255). However, innodb system tablespaces and log files are encrypted with a fixed key id of 1, so there must be a key id of 1 in the key file. If a key with key id 2 exists, it is used to encrypt temporary tables and temporary files.

Here, the key file itself can also be encrypted to prevent leakage of the key file. For example, the plaintext file_key.txt file is encrypted into file_key_enc.txt, and the password is file_key_encrypt_key: openssl enc -aes-256-cbc -md sha1 -k file_key_encrypt_key -in file_key.txt -out file_key_enc.txt

innodb encryption options:

innodb-encrypt-tables: on/off/force, whether to encrypt all innodb tables, force means to force encryption of all innodb tables

innodb-encrypt-log: on/off, whether to encrypt innodb log files

innodb-encryption-rotate-key-age: seconds, how often to update the page encryption if a new key is obtained

innodb-encryption-rotation-iop: IOPS, the maximum allowed IOPS for page encryption updates

innodb-encryption-threads: number of threads, how many background threads are used to do page encryption updates

Binlog encryption options:

encrypt-binlog: on/off, whether to encrypt binlog

Other encryption options:

encrypt_tmp_files: on/off, whether to encrypt temporary files

encrypt_tmp_disk_tables: on/off, whether to encrypt aria temporary tables

aria_encrypt_tables: on/off, encrypt aria table (ROW_FORMAT=PAGE only)

Encryption Test:

Add plugin-load-add=file_key_management.sofile_key_management_encryption_algorithm=aes_cbcfile_key_management_filename = /etc/my.cnf.d/file_key_enc.txtfile_key_management_filekey = file_key_encrypt_keyinnodb-encrypt-tablesinnodb-encrypt-loginnodb-encryption-threads=4encrypt-binlogencrypt_tmp_filesencrypt_tmp_disk_tablesaria_encrypt_tables Restart MariaDB after adding.

To create an unencrypted table:

create table unencrypt_t(id int, name varchar(32)) ENCRYPTED=NO;

Create an encrypted table, specifying key id 3:

create table encrypt_t(id int, name varchar(32)) ENCRYPTED=YES ENCRYPTION_KEY_ID=3;

Modify unencrypted tables to encrypted:

alter table unencrypt_t ENCRYPTED=YES ENCRYPTION_KEY_ID=3;

Modify encrypted tables to unencrypted:

alter table unencrypt_t ENCRYPTED=NO;

Insert data:

insert into unencrypt_t(id, name) values(1, 'one');insert into encrypt_t(id, name) values(1, 'one');

Encrypted versus unencrypted data files:

Binlog encryption:

Active and standby synchronization after encryption is enabled:

After encryption is enabled, binlog transmissions between the host and the backup are unencrypted, and the backup encrypts when writing relaylog/binlog/data files. Therefore, the keys between the active and standby machines can be different, but the id information must be consistent, otherwise the table creation statement cannot be executed successfully on the standby machine, which will cause the slave SQL thread to abort.

Encryption and compression:

Data encryption and data compression can be used at the same time, MariaDB first do data compression and then do data encryption, can save a lot of storage space.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.