另一個MySQL DBA: 2024

下面是一个简单的示例，显示了 MariaDB 加密的几个选项。

您必须考虑要加密的内容。数据（传输中的数据）或实例中的数据（静态数据）的通信。

本文将重点介绍使用在 Amazon Linux 上运行的 AWS 免费套餐节点的静态数据选项。我将在 2 个不同的实例上使用全局数据库来显示通过加密更新当前表以及将自动加密的新加载表。

首先，我们将从安装开始......对于这个演示来说快速而简单。

# 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 |
+-------------+

我们将在实例 server_id 100 上加载全局数据库。

# 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.

现在我们可以看到两个实例当前都没有使用加密。

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)

现在，我将在两个系统上设置随机密钥并对其进行加密。

# 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

现在我们可以设置 cnf 文件来启用插件以及加密选项.

# 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

重新启动 MariaDB,以便我们可以检查当前状态.

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)

同时将世界数据上传到实例server_id 200。

# 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)

根据 information_schema.INNODB_TABLESPACES_ENCRYPTION 我们现在已加密。但是，它们不会在架构级别显示它。虽然他们说如果它出现在 INNODB_TABLESPACES_ENCRYPTION 表中，它就会被加密，但我更愿意确定并在表和模式中看到它。

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)

到目前为止，您可以看到在重新启动或加载架构和数据后，两个实例都已计入 INNODB_TABLESPACES_ENCRYPTION 架构中。

所以...对表格进行一些修改将会有所帮助...

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)

这很简单等等。到目前为止...现在我们需要启用二进制日志并检查更多信息。

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)

通过查看二进制日志来检查......

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*/;

很高兴看到它说它现在正在加密。

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

希望这至少可以让你开始......

资源：

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

另一個MySQL DBA

Pages

2024年2月25日星期日

MariaDB 加密（静态数据）

资源：