Last Updated on 3월 18, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 의 암호화 기능에 대해서 확인 해보려고 합니다.
해당 내용은 Real MySQL 8.0 책에 대해서 정리한 내용 입니다.
Contents
암호화 기능 지원
암호화 기능 은 MySQL 5.7 버전부터 지원되기 시작하였습니다.
MySQL 5.7 버전에서는 데이터 파일(테이블 스페이스)에 대해서만 암호화 기능이 제공 되었습니다
그러다가 MySQL 8.0 으로 업그레이드 되면서 데이터 파일뿐만 아니라 리두 로그 나 언두 로그, 복제를 위한 바이너리 로그 등도 모두 암호화 기능을 지원하기 시작 하였습니다.
또한 MySQL 8.0.16부터 스키마 및 일반 테이블스페이스에 대한 암호화 기본값 설정(시스템 변수)도 지원 되며, DB 관리자는 해당 스키마 및 테이블스페이스에서 생성된 테이블의 암호화 여부를 제어할 수 있게 되었습니다.
• 관련 시스템 변수 : default_table_encryption
| Command-Line Format | --default-table-encryption[={OFF|ON}] |
|---|---|
| Introduced | 8.0.16 |
| System Variable | |
| Scope | Global, Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
Yes |
| Type | Boolean |
| Default Value | OFF |
MySQL 서버의 데이터 암호화
암호화 기능 은 데이터베이스 서버와 디스크 사이의 데이터를 읽고 쓰기 지점에서 암호화 또는 복호화가 수행 됩니다.
그래서 MySQL 서버에서 디스크 입출력 이외의 부분에서는 암호화 처리가 전혀 필요치 않으며 MySQL 서버(InnoDB 스토리지 엔진) 의 I/O 레이어에서 데이터 암호화 및 복호화가 실행되게 됩니다.
사용자의 쿼리가 처리하는 과정에서 테이블의 암호화 여부를 식별할 필요는 없으며, 사용자 입장에서도 아무런 차이가 없으며, 암호화된 테이블과 그렇지 않은 테이블이 동일한 처리 과정을 거치게 됩니다.
이러한 암호화 방식을 TDE(Transparent Data Encryption) 이라고 하며 또는 Data at Rest Encryption 이라고도 합니다.
여기서 Data at Rest 는 메모리(In-Process) 나 네트워크 전송(In-Transit) 단계가 아닌 디스크에 저장(At Rest)된 단계에서만 암호화된다는 의미로 사용 되는 표현 입니다.
Note
Oracle Database 에도 구현 방식이 동일(유사)하며 이름도 동일한 TDE(Transparent Data Encryption) 기술을 통해 DB 데이터 암호화를 사용하고 있습니다.
Oracle 은 10gR2 부터 TDE 기능을 지원하며 10gR2 는 2005년에 출시 되었습니다
2단계 키 관리
MySQL 서버의 TDE의 암호화키는 키링(Keyring) 플러그인 에 의해 관리됩니다.
MySQL 8.0 버전에서 지원되는 키링 플러그인은 아래와 같으며 다만 MySQL Community Edition 버전에서는 keyring_file 플러그인만 사용가능 하며 그외 플러그인은 MySQL Enterprise Edition 에서 사용 할 수 있습니다.
사용가능한 키링
• keyring_file FILE-Based 플러그인
• keyring_encrypted_file keyring 플러그인
• keyring_okv KMIP 플러그인
• keyring_aws Amazon Web Service keyring 플러그인
다양한 플러그인이 제공되지만 키를 관리 하는 방법이 다를 뿐, MySQL 에서 동작하는 방식은 모두 동일 합니다.
MySQL 서버의 키링 플러그인은 2단계(2-Tier) 키 관리 방식을 사용하게 됩니다.
마스터 키(master key) 와 테이블스페이스 키(tablespace key) 라는 두가지 종류의 키를 가지고 있으며, 테이블스페이스 키는 프라이빗 (private key) 라고도 합니다.
외부 키 관리 솔루션(KMS) 또는 디스크의 파일(keyring_file 또는 keyring_encrypted_file 플러그인 사용)에서 마스터 키를 가져오고, 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급하게 됩니다.
서버는 마스터 키를 이용하여 테이블 스페이스 키를 암호화해서 각 테이블의 데이터파일 헤더에 저장하게 됩니다.
테이블이 삭제 되지 않는 이상 테이블 스페이스 키는 변경되지 않으며 서버 외부로 노출이 되지 않기 때문에 테이블 스페이스 키를 주기적으로 변경하지 않아도 보안상 취약점은 되지 않습니다.
하지만! 마스터 키는 외부의 파일을 이용하기 때문에 노출이 될 가능성이 있으며, 그에 따라서 마스터 키는 주기적으로 변경하는 부분이 고려가 되게 되며, 키 관리에 신경을 써야 합니다.
키는 아래와 같은 명령어로 변경할 수 있습니다.
mysql> alter instance rotate innodb master key;
마스터 키를 변경하면 기존의 마스터 키를 이용해 테이블의 테이블 스페이스 키를 복호화 한 다음 새로운 마스터 키로 다시 암호화 하게 됩니다.
마스터 키가 변경되는 과정에서도 테이블 스페이스 키 자체와 데이터 파일의 데이터는 변경이 전혀 없으며 이렇게 2단계 암호화 방식을 사용하는 이유는 암호화 키 변경으로 인한 과도한 시스템 부하를 방지하기 위해서 입니다.
MySQL 서버의 TDE에서 지원되는 암호화 알고리즘은 AES 256 비트이며, 이외의 알고리즘은 지원되지 않고 있습니다.
테이블스페이스 키는 AES-256 ECB(Electronic CodeBook) 알고리즘을 이용해 암호화 되고, 실제 데이터 파일은 AES-256 CBC(Cipher Block Chaining) 알고리즘을 이용해 암호화 됩니다.
암호화의 성능
MySQL 서버의 암호화는 TDE(Transparent Data Encryption) 방식이기 때문에 디스크로부터 한 번 읽은 데이터 페이지는 복호화되어 InnoDB의 버퍼 풀에 적재가 되게 됩니다.
그래서 데이터 페이지가 한 번 메모리 적재되면 암호화 되지 않은 테이블과 동일한 성능을 보이게 됩니다.
다만 수행하는 쿼리의 버퍼가 InnoDB 버퍼풀에 존재 하지 않는 데이터 페이지를 읽어야 하는 경우에 복호화 과정을 거치기 때문에 복호화 시간동안 쿼리가 일부 지연은 될 수 있습니다.
그리고 암호화된 테이블이 변경되면 다시 디스크로 동기화될 때 암호화 되어야 하기 때문에 디스크에 저장할 때도 추가로 시간이 소요 될 수 있습니다 하지만 데이터 페이지 저장은 서버의 백그라운드 스레드가 수행하기 때문에 실제 사용자 쿼리가 지연되는 것은 아닙니다.
읽는 과정에서는 SELECT 이외에 DML 명령 또한 변경하고자 하는 레코드를 InnoDB 버퍼 풀로 읽어와야 하기 때문에 새롭게 디스크에서 읽어야 하는 데이터 페이지의 개수에 따라서 그 만큼 복호화 지연이 발생하게 됩니다.
AES(Advanced Encryption Standard) 암호화 알고리즘은 암호화 하고자 하는 평문의 길이가 짧은 경우 암호화 키의 크기에 따라서 암호화 결과 용량이 더 커질 수도 있지만 MySQL의 데이터 페이지는 암호화 키보다 훨씬 크기 때문에 용량은 평문의 결과와 동일한 크기로 반환하게 되며, TDE 를 적용한다고 해도 데이터파일의 크기는 동일하게 됩니다.
테이블에 암호화와 압축이 동시에 적용되면 MySQL 서버는 압축을 먼저 실행하고 암호화를 적용하게 됩니다.
암호화의 복제
MySQL 서버의 복제에서 Replica 서버는 소스 서버의 모든 사용자 데이터를 동기화하기 때문에 실제 데이터 파일도 동일할 것이라고 생각할 수도 있습니다 하지만 TDE를 이용한 암호화 사용시 마스터키와 테이블스페이스 키는 그렇지 않습니다.
서버에서 기본적으로 모든 노드는 각자의 마스터 키를 할당하게 됩니다.
서버의 마스터키를 로컬 디렉토리에 관리하게 된다면 각각 다른 마스터 키를 가지게 되고, 원격으로 키를 관리하는 키 관리 솔루션을 사용하는 경우에도 마찬가지로 소스 서버와 레플리카 서버는 서로 다른 마스터 키를 갖도록 설정 해야 합니다.
마스터키 자체가 레플리카로 복제되지 않기 때문에 테이블스페이스 키도 레플리카로 복제 되지는 않습니다.
그러므로 실제 데이터 값(복호화된 값)은 모두 동일하지만 실제 암호화된 데이터가 저장된 데이터파일의 내용은 완전히 달라지게 되는 것입니다(서로 다른 키를 이용함으로)
마스터 인스턴스에서 마스터 키를 변경하기 위해서 명령어를 수행하게 되면 명령어 자체는 Replica 인스턴스로 복제가 되지만 Master 서버의 마스터 키 자체가 Replica 인스턴스에 전달되는 것 은 아닙니다
그래서 명령어 수행시 Master 인스턴스와 Replica 인스턴스 가 각각 서로 다운 마스터 키를 새로 발급받게 됩니다.
백업 과 복구
MySQL 서버 의 TDE 기능에서 사용 되는 키링(Key Ring) 파일을 백업 하지 않는 경우가 있을수도 있으나, 이 경우 키링 파일을 찾지 못하면 데이터를 복구 할 수 없게 됩니다.
그래서 키링 파일을 데이터파일과 별도로 백업을 한다면 마스터 키 로테이션 명령으로 TDE의 마스터 키가 언제 변경되었는지를 기록 또는 기억해두고 있어야 합니다.
보안을 위해서는 키링 과 데이터 파일의 분리 보관이 권장되지만 복구를 감안하고 키 관리 등으로 백업 방식을 어떻게 할지를 고민하고 결정해야 합니다.
이전에 언급한 내용과 같이 명령어에 의해서 마스터키는 변경 될 수 있기 때문에 변경 시 마다 백업을 하거나, 백업시 마다 키링을 같이 백업하는 방법등이 고려가 되어야 할 것 입니다.
keyring_file 플러그인
MySQL 서버의 데이터 암호화 기능인 TDE 의 암호화 키 관리는 플러그인 방식으로 제공됩니다.
MySQL Enterprise Edition 의 경우 다양한 플러그인을 사용할 수 있지만 Community Edition 에서는 keyring_file 플러그인만 사용이 가능 합니다.
keyring_file 플러그인은 테이블스페이스 키를 암호화하기 위한 마스터 키를 디스크의 파일로 관리하는데, 이때 마스터 키는 평문으로 디스크에 저장됩니다.
$ strings tde_master.key
Keyring file version:2.0
INNODBKey-65c2870d-8576-11ec-87cc-08002761d7ed-1AES}
RC{C
EOF*
그래서 마스터 키가 저장된 파일이 외부로 노출이 된다면 데이터 암호화는 사실상 무용지물이 될 수 도 있습니다.
MySQL 서버를 시작 하고 나면 키를 메모리에 캐시 하기 때문에 MySQL 서버가 시작되고 나서는 다음 재시작 전까지는 키가 없어도 상관 없게 됩니다.
그래서 MySQL 서버가 시작 될 때만 키링 파일을 다른 서버에서 다운로드 해서 MySQL 서버를 시작하는 방법도 고려 해볼 수 있습니다.
MySQL 서버의 다른 플러그인과 달리 TDE 플러그인은 서버 시작 단계에서도 가장 빨리 초기화 되어야 합니다. 그래서 다음과 같이 my.cnf 파일에 early-plugin-load 시스템 변수에 keyring_file 플러그인을 위한 라이브러리("keyring_file.so") 를 명시해야 합니다.
마스터 키를 저장할 경로를 keyring_file_data 시스템 변수에 설정을 하면 됩니다.
early-plugin-load = keyring_file.so keyring_file_data = /var/.../.../tde_master.key
MySQL 서버의 설정 파일이 준비되면 MySQL 서버를 재시작하면 자동으로 keyring_file 플러그인이 초기화 되며, 초기화 여부는 show plugins 명령으로 확인 가능 합니다.
mysql> show plugins; +---------------+----------+-------------+-----------------+-----------+ | Name | Status | Type | Library | License | +---------------+-----------+------------+-----------------+-----------+ | keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL | <--!!! < .. 중략 .. >
keyring_file 플러그인이 초기화가 되면 플러그인의 초기화와 동시에 keyring_file_data 시스템 변수의 경로에 빈 파일을 생성하게 됩니다.
플러그인만 초기화 상태 일 뿐 아직 마스터 키를 사용한적이 없기 때문에 실제 키링 파일의 내용은 비어있게 되며, 데이터 암호화 기능을 사용하는 테이블을 생성하거 마스터 로테이션을 실행하면 키링 파일의 마스터 키가 초기화가 됩니다.
[root]# ls -al
합계 4
drwxr-xr-x. 2 mysql mysql 28 03:28 .
drwx------. 19 mysql mysql 4096 03:28 ..
-rw-r-----. 1 mysql mysql 0 03:28 tde_master.key
<--!! 사이즈 0
마스터 키 로테이션을 실행하면 키가 초기화 됩니다.
mysql> alter instance rotate innodb master key; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root]# ls -al 합계 8 drwxr-xr-x. 2 mysql mysql 28 2월 1 03:31 . drwx------. 19 mysql mysql 4096 2월 1 03:28 .. -rw-r-----. 1 mysql mysql 187 2월 1 03:31 tde_master.key
테이블 암호화
키링 플러그인은 마스터 키를 생성하고 관리하는 부분까지만 담당하고 있으며, 어떠한 키링 플러그인을 사용하더라도 관계 없이 암호화된 테이블을 생성하고 활용하는 방법은 동일하게 사용할 수 있습니다.
포스팅에서는 Community Edition 을 사용하고 있으며, 버전은 8.0.23 을 사용하고 있습니다.
테이블 생성
TDE 를 이용하여 암호화 테이블은 다음과 같이 생성할 수 있습니다.
mysql> create table tb_enc
(
id int,
col1 varchar(100),
primary key(id)
) ENCRYPTION = 'Y';
mysql> show create table tb_enc;
+--------+---------------------------------------------------------|
| Table | Create Table |
+--------+---------------------------------------------------------|
| tb_enc | CREATE TABLE `tb_enc` (
`id` int NOT NULL,
`col1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
ENCRYPTION='Y'
<----!!!!!! |
+--------+---------------------------------------------------------|
테이블을 생성 후 생성 구문을 확인하면 위와 같이 ENCRYPTION 구문을 확인 할 수 있습니다.
또한 information_schema 에서도 확인 할 수 있습니다.
mysql> select table_schema,table_name,create_options from information_schema.tables where table_name='tb_enc'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | tdb | tb_enc | ENCRYPTION='Y' | +--------------+------------+----------------+
생성된 테이블에 대해서 암호화는 alter 명령어를 통해서 수행할 수 있습니다.
mysql> alter table 테이블명 encryption='Y';
암호화된 테이블을 복호화 하기 위해서는 다음과 같이 명령어를 수행 합니다.
mysql> alter table 테이블명 encryption='N';
실제로 다시 암호화하여 저장하는 과정이 수행됨에 따라 테이블의 레코드 수에 따라서 수행시간이 달라지며, 많은 레코드를 가진 테이블의 경우 별도의 업무 다운 타임에 진행하거나 복제 테이블을 생성 및 Insert into & rename 이나 PT-OSC 와 같은 Online DDL 툴 등을 고려 할 수 있습니다.
mysql> show full processlist; +----+------+---------+-------+-------------------+------------------------------------+ | Id | User | Command | Time | State | Info | +----+------+---------+-------+-------------------+------------------------------------+ | 42 | root | Query | 352 | copy to tmp table | alter table xxxxxxx encryption='Y' | | 44 | root | Query | 0 | init | show full processlist | +----+------+---------+-------+-------------------+------------------------------------+ -- 기존 테이블을 Encryption 적용시 복사 진행됨 (copy to tmp table)
데이터를 입력하고 조회를 하면 디스크에 기록 될 때는 자동으로 암호화 되며, 다시 디스크에서 메모리로 읽어 올 때 복호화 되게 됩니다.
mysql> insert into tb_enc values(1,'Enc Test'); mysql> select * from tb_enc; +----+----------+ | id | col1 | +----+----------+ | 1 | Enc Test | +----+----------+
테이블을 생성시 위와 같이 ENCRYPTION 옵션을 사용해야 하며 또는 8.0.16 버전 부터 추가된 default_table_encryption 시스템 변수를 ON 으로 설정하여 테이블 생성시 자동으로 암호화가 설정될 수 있도록 할 수도 있습니다.
default_table_encryption 시스템 변수의 기본값은 OFF 입니다.
default_table_encryption 을 ON 으로 설정 후에 데이터베이스를 생성하면 ENCRYPTION 속성이 Y 가 되게 됩니다.
mysql> show create database test; CREATE DATABASE `test` ... /*!80016 DEFAULT ENCRYPTION='Y' */
데이터베이스(스키마)에 지정된 암호화 속성을 제거 하기 위해서는 아래와 같이 명령어를 수행 합니다.
mysql> ALTER DATABASE 데이터베이스명 ENCRYPTION='N';
암호화 권한 제어
MySQL 8.0.16 버전 부터 암호화 테이블 생성에 관한 권한 체크 기능이 추가 되었으며 table_encryption_privilege_check 시스템 변수에 의해서 결정 됩니다.
table_encryption_privilege_check=ON 으로 설정 시에 테이블을 생성 하거나 변경(alter) 할 때 TABLE_ENCRYPTION_ADMIN 권한이 있는지를 체크 하게 됩니다.
해당 권한이 없다면 테이블 생성(또는 변경) 은 아래의 메세지와 함께 에러가 발생되게 됩니다
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.
table_encryption_privilege_check 시스템 변수는 8.0.16 부터 사용 가능하며 기본값은 OFF 입니다.
그래서 해당 시스템 변수를 활성화(ON) 를 하지 않으면 테이블 생성/변경 권한(create 와 alter) 이 있다면 암호화 속성의 테이블 생성과 변경이 가능 합니다.
암호화 방식 차이
데이터베이스 암호화 방식은 여러가지 방식이 있으며, 플러그인,API 방식의 컬럼 암호화가 있으며, 블록 암호화인 TDE 나 파일시스템 암호화 가 있으며 그 외 하이브리드 나 어플라이언스 방식 등이 있습니다.
그 중에서 외부 암호화 방식인 컬럼 암호화의 API 의 경우 응용 프로그램에서 직접 암호화를 수행하며, 그렇기 때문에 DB 상에서의 부하가 적으며 암/복호화가 빠릅니다.
DB 서버 자체에서는 암호화된 값이 저장이 되며 MySQL 서버는 인지 하지 못하게 됩니다. 그래서 응용 프로그램(API 방식)에서 암호화된 컬럼은 인덱스를 생성 하더라도 100% 활용하지 못할 수 있습니다.
TDE 기능은 인덱스 관련된 작업을 모두 처리 후 최종 디스크에 데이터 페이지를 저장할 때만 암호화하기 때문에 이 같은 제약이 없습니다.
사용 편의성이나 애플리케이션의 변경 유무 등을 고려한다면 MySQL TDE 기능의 사용이 권장되나 암호화 방식의 경우 목적과 용도가 다르게 됩니다.
TDE 는 로그인한 사용자에게 적절한 조회 권한이 있다면 평문 으로 조회가 가능하지만, 응용프로그램에서 암호화나 플러그인 방식의 경우 DB 에 로그인하여 조회를 하더라도 암호화된 값으로 보이게 됩니다.
그래서 업무 적으로 그리고 회사에서 하는 사업의 종류에 따라서 암호화 방식 선택을 고려해야 합니다.
테이블스페이스의 이동
MySQL 에서 테이블을 다른 서버로 복사해야 하는 경우 또는 특정 테이블의 데이터 파일만 백업 했다가 복구하는 경우라면 테이블스페이스 이동(Export & Import) 기능이 레코드를 덤프 했다가 복구 하는 방식보다 빠르고 효율이 좋습니다.
(다만 작업중에는 잠금이 발생 됨으로 이부분을 고려 해야 함)
그런데 TDE가 적용된 암호화된 테이블의 경우 원본 MySQL 서버와 목적지 MySQL 서버의 암호화 키(마스터 키)가 다르기 때문에 신경써야 할 부분이 있으며 아래와 같이 flush tables 명령으로 테이블스페이스를 익스포트(Export) 할 수 있습니다.
mysql> flush tables 테이블명 FOR EXPORT;
명령어가 실행되면 아래와 같은 내용이 진행되게 됩니다.
1. 지정한 테이블에 대한 공유 메타 데이터 잠금을 획득합니다. 다른 세션에 해당 테이블을 수정하거나 테이블 Lock을 보유 해야 하는 작업 트랜잭션 일 경우 명령문은 차단되어 대기하게 됩니다
Lock이 확보되면 테이블 데이터를 변경(DML) 하려 시도하는 트랜잭션을 락으로 대기하게 되며 읽기 작업은 계속 할 수 있습니다.
-- 테이블 변경시 mysql> show full processlist; +----+-------+---------+-------+---------------------------------+--------------------------------------------+ | Id | User | Command | Time | State | Info | +----+-------+---------+-------+---------------------------------+--------------------------------------------+ | 9 | root | Query | 0 | init | show full processlist | | 25 | test | Query | 4 | Waiting for table metadata lock | alter table tb_enc add index ix_col1(col1) | +----+-------+---------+-------+---------------------------------+--------------------------------------------+ -- 데이터 변경시 mysql> show full processlist; +----+-------+---------+-------+---------------------------------+-----------------------------------------------+ | Id | User | Command | Time | State | Info | +----+-------+---------+-------+---------------------------------+-----------------------------------------------+ | 9 | root | Query | 0 | init | show full processlist | | 19 | test | Query | 16 | Waiting for table metadata lock | insert into tb_enc values(2,'Insert Test') | +----+-------+---------+-------+---------------------------------+-----------------------------------------------+
2. 테이블이 사용하는 스토리지 엔진이 FOR EXPORT 를 지원하는지 확인합니다.
그렇지 않으면 ER_ILLEGAL_HA 오류가 발생하고 명령문이 실패합니다.
3. 스토리지 엔진은 보류중인 변경 사항이 디스크에 기록되도록 해야 합니다. 명령어에 지정한 테이블에 저장되지 않은 변경 사항을 모두 디스크로 내려 쓰게 됩니다.
(계속 락은 유지)
4. InnoDB는 지정한 테이블과 같은 디렉토리에 테이블명.cfg 파일명의 테이블의 구조 정보(메타 데이터) 를 파일을 생성 하게 됩니다.
5. FOR EXPORT 명령문이 완료 처리 될때 까지 획득한 메타 데이터 잠금이 유지 되도록 합니다
이제 테이블명.idb 와 테이블명.cfg 파일을 목적지 서버(target server) 로 복사를 하고 복사가 완료 되면 UNLOCK TABLES 명령어를 수행하여 잠금을 해제하면 테이블의 테이블스페이스를 복사 하는 과정이 완료 되게 됩니다.
UNLOCK TABLES는 현재 LOCK TABLES로 잠긴 테이블이있는 경우에만 모든 활성 트랜잭션을 암시 적으로 커밋을 하게 됩니다.
TDE 를 사용하는 환경이라면 확장자가 idb , cfg 파일 이외 cfp 확장자 파일이 더 생성되게 됩니다.
해당 파일은 FOR EXPORT 명령어 실행시 임시로 사용할 마스터 키를 발급하여 확장자 cfp 파일에 기록을 하게 됩니다.
그 다음 사용중인 마스터키로 복호화를 한 후에 다시 임시로 발급한(cfp) 마스터 키를 이용하여 다시 암호화해서 데이터 파일 헤더에 저장하게 됩니다.
그래서 TDE 사용환경에서는 확장자 cfp 파일도 같이 복사가 되어야 합니다.
언두 로그 및 리두 로그
테이블의 암호화를 적용하더라도 데이터가 저장된 파일만 암호화가 되어있고, 사용중인 버퍼 메모리는 복호화된 평문으로 관리 됩니다.
이 평문 데이터 변경되어 데이터 파일에 저장되면 암호화가 되지만 데이터 파일 이외 다른 파일에 저장될 경우 평문으로 저장되게 됩니다.
그래서 기본적으로는 언두, 리두 로그 그리고 복제를 위한 바이너리 로그에는 평문으로 저장이 되게 됩니다.
redo 와 undo 를 암호화와 관련된 시스템 변수는 아래와 같으며 기본 값은 OFF 입니다.
| Command-Line Format | --innodb-redo-log-encrypt[={OFF|ON}] |
|---|---|
| System Variable | |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Boolean |
| Default Value | OFF |
| Command-Line Format | --innodb-undo-log-encrypt[={OFF|ON}] |
|---|---|
| System Variable | |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Boolean |
| Default Value | OFF |
Redo 와 Undo 에 대한 암호화는 테이블 의 데이터와 달리 변경한 시점 부터 생성된 데이터에 대해서 리두/언두 에 암호화 저장을 하게 되고, 반대로 Redo/Undo 에 대한 암호화 해제시 그 이후 부터 생성된 레코드 부터 평문으로 저장됩니다.
비활성화 한다고 암호화키가 불필요한 것은 아닙니다. 상황에 따라서 며칠 또는 그 이상 필요할 수 있습니다
REDO/UNDO 암호화 키
리두/언두 로그 모두 각각의 테이블스페이스 키로 암호화 되고, 테이블 스페이스 키는 다시 마스터 키로 암호화 되는 방식 입니다.
즉 테이블 암호화 사용 되는 테이블 스페이스 키와 달리 Redo, Undo 를 위한 별도의 키를 발급 한다는 의미 입니다.
그래서 아래와 같이 새로운 마스터 키를 발급하게 되면 테이블 암호화와 마찬 가지로 새로운 마스터 키에 의해 다시 암호화가 진행 됩니다.
mysql> alter instance rotate innodb master key;
해당 키는 마스터 키로 암호화되어 리두 로그/언두 로그 파일의 헤더에 각각 저장 되어 있습니다.
암호화 여부는 암호화 뒤에 strings 로 ib_logfile.. 을 검색하여 문자열 검색하는 방법으로 확인 할 수 있습니다.
바이너리 로그 암호화
MySQL 8.0.14 버전부터 바이너리 로그의 암호화가 지원되며 바이너리 로그와 릴레이 로그 파일의 데이터는 파일 키(file key) 로 암호화 하게 됩니다.
파일 키(file key) 는 "바이너리 로그 암호화 키" 로 암호화되어 각 바이너리 로그와 릴레이 로그의 파일 헤더에 저장이 됩니다.
즉 바이너리 로그 암호화 키는 테이블 암호화의 마스터 키와 동일한 역할을 하며, 파일 키는 바이너리/릴레이 로그 파일 단위로 자동 생성 되어 해당 로그 파일의 데이터 암호화에만 사용이 되게 됩니다.
바이너리 로그 암호화 활성화 여부는 binlog_encryption 시스템 변수에 의해서 결정되며 기본값은 OFF 입니다.
| Command-Line Format | --binlog-encryption[={OFF|ON}] |
|---|---|
| Introduced | 8.0.14 |
| System Variable | |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Boolean |
| Default Value | OFF |
시스템 변수가 활성화가 되고 binlog 가 암호화가 되고 있다면 아래와 같이 암호화 적용 여부를 확인 할 수 있습니다.
mysql> show binary logs; +-------------------+------------+-----------+ | Log_name | File_size | Encrypted | +-------------------+------------+-----------+ | < ... 중략 ... > | | tdb-binlog.000053 | 4176 | No | | tdb-binlog.000054 | 668 | Yes <-!!! | +-------------------+------------+-----------+
바이너리 로그 암호화 키는 다음과 같이 변경(로테이트) 할 수 있습니다.
mysql> alter instance rotate binlog master key;
mysqlbinlog
바이너리 로그가 암호화 된 환경에서 mysqlbinlog 유틸리티를 이용하는 경우 제약사항이 생기게 됩니다.
바이너리 로그 암호화 키는 생성한 MySQL 서버만 가지고 있기 때문에 서버와 관계 없는 클라이언트 유틸리티인 mysqlbinlog 도구만으로는 복호화하여 내용 확인을 할 수 없습니다.
바이너리 로그 내용을 볼수 있는 방법은 MySQL 서버를 이용해서 가져오는 방법이 유일하며 mysqlbinlog 의 "--read-from-remote-server" 파라미터를 이용 해야 합니다.
그래서 아래와 같이 mysqlbinlog 유틸리티로 직접 조회 하였을 때와 --read-from-remote-server 사용 하였을 때에 차이를 확인할 수 있습니다.
## binlog 파일을 직접 조회 $ mysqlbinlog -vvv tdb-binlog.000055 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ERROR: Reading encrypted log files directly is not supported. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; ## --read-from-remote-server 사용 $ mysqlbinlog --read-from-remote-server -u root -p -vvv tdb-binlog.000055 < ... 중략 ... > BINLOG ' Ktr4YRMBAAAAOQAAAG4BAAAAAGgAAAAAAAEAA3RkYgAGdGJfZW5jAAIDDwKQAQIBAQACAS0YJA+w Ktr4YR4BAAAAOgAAAKgBAAAAAGgAAAAAAAEAAgAC/wAEAAAAEABCaW5nbG9nIGVuYyBUZXN0i6Lz Yw== '/*!*/; ### INSERT INTO `tdb`.`tb_enc` ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='Binglog enc Test' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ # at 424 #220201 15:58:50 server id 1 end_log_pos 455 CRC32 0xb7d9f5e5 Xid = 189 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ;
Conclusion
MySQL 5.7 버전부터 사용할 수 있었던 TDE(Transparent Data Encryption) 에 대해서 확인 해보았으며 Oracle 에서 먼저 TDE 를 알고 있었던 점에서 보았을때 거의 유사하다고 생각 하고 있습니다.
다만 Oracle TDE 의 경우 테이블 스페이스 단위 암호화를 사용할 경우 데이터 변경에 의한 기록(Undo/Redo) 이나 Join 이나 Sort 등에 의한 Temp 사용 등 여러가지 모든 것에 대해서 암호화가 진행되나 MySQL 에서는 redo/undo/binlog 등에 대해서 별도로 암호화 사용 설정이 필요한 부분이(챙겨야 하는 부분이) 있는 차이점이 있다고 생각 합니다.
추가로 InnoDB Temp Tablespace 에 대한 암호화 기능이 포스팅하는 시점에서 출시한 MySQL 8.0.28 까지도 없는 것으로 확인 하고 있습니다.
암호화된 테이블에 대해서 "Using temporary; Using filesort" 이 발생 하도록 group by 와 order by 를 포함한 쿼리를 수행하게 되면 정렬 작업이 temp 파일에 기록되게 됩니다.
해당 파일은 아직 암호화 대상이 아니기 때문에 평문 그대로 보이게 됩니다.
• 예제 테이블에 있는 샘플 데이터
mysql> select * from sbtest1 limit 0,10; +----+---------+---+-------------------------------------------------------------+ | id | k | c | pad | +----+---------+---+-------------------------------------------------------------+ | 1 | 4283284 | A | 67847967377-48000963322-62604785301-91415491898-96926520291 | | 2 | 6968333 | A | 23183251411-36241541236-31706421314-92007079971-60663066966 | | 3 | 9260281 | B | 38615512647-91458489257-90681424432-95014675832-60408598704 | | 4 | 7013182 | A | 63947013338-98809887124-59806726763-79831528812-45582457048 | | 5 | 4812135 | A | 34551750492-67990399350-81179284955-79299808058-21257255869 | | 6 | 5584336 | A | 05161542529-00085727016-35134775864-52531204064-98744439797 | | 7 | 6370837 | A | 91798303270-64988107984-08161247972-12116454627-22996445111 | | 8 | 4937534 | A | 76460662325-41613089656-42706083314-81833284991-17063140920 | | 9 | 1436160 | A | 30508501104-50823269125-88107014550-70202920684-95842308929 | | 10 | 5229147 | A | 62779785163-72948938250-41369758259-80996497836-62655463951 | +----+---------+---+-------------------------------------------------------------+
• Session Temporary Tablespaces 파일을 strings 로 확인 한 결과
77245583671-17072273617-28055106768-47501857941-12383653588 <x
A
06404988187-06076940434-61254645216-21646418777-54723143595 <x
A
24591877848-80165149664-89319675811-34663157305-43355286095 <x
A
79976514192-36352006362-49912182333-25130084316-08480159586 <x
A
91477327303-42762989330-75811709549-72618006485-95264642030 <x
38153970815-17224305523-91903594672-61693488739-22078132428 <x
;A 76096819959-50254114782-71671383714-10862826213-977211
A 13894684467-70763220313-24046410181-36
3-03669452463 <x
ee{A 47069165037-6460674745
99208-19134457418-75645381072 <x
A 943689
155086672-22837535410-22043522484-44867072779 <x
A
95040843500-29797000584-22905116045-71913418360-06938401311 <x
A
69606444645-74585457388-25365612243-81026570593-56140262411 <x
JA
25631092225-42597037198-90560489591-52890619119-83069229225 <x
R7^A
86773443475-44731084558-62592738935-40976453620-44860305911 <x
A
09760870222-23830138080-40736632265-68907836931-90437447294 <x
(mwA
95630943096-22520932621-73775260920-62039247212-40135838566 <x
< ... 중략 ... >
이런 부분도 향후 버전에서는 암호화 기능이 추가되어야 할 것 같다고 생각됩니다.
추가로 MySQL 의 포크(fork,가지치기 버전) 버전인 Percona Server for MySQL에서는 Temporary Tablespace 도 암호화 할 수 있는 기능이 포함되어 있습니다.
- variable
innodb_temp_tablespace_encrypt -
Command Line: innodb-temp-tablespace-encryptDynamic: Yes Scope: Global Variable Type: Boolean Default Value: OFF
When this variable is set to ON, the server encrypts the global temporary tablespace (:file: ibtmp* files) and the session temporary tablespaces (:file: #innodb_temp/temp_*.ibt files).
해당 포스팅은 Real MySQL 8.0 책의 많은 내용 중에서 일부분의 내용만 함축적으로 정리한 것으로 모든 내용 확인 및 이해를 위해서 직접 책을 통해 모든 내용을 확인하시는 것을 권해 드립니다
Reference
Reference Book
• Real MySQL 8.0
Reference Link
• mysql.com/innodb-parameters
• mysql.com/replication-binlog-encryption
• mysql.com/news-8-0-1
• mysql.com/encryption-in-mysql-8-0
• percona.com/encrypting-temporary-files
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
