책너두 (Real MySQL 8.0 1권) 19일차 (~215p)

현재 책너두 1.5기 모집 링크 입니다 : https://breakbook.notion.site

요약

• 응용 프로그램의 암호화와 테이블 암호와의 차이를 이해하게 됨.
• 테이블스페이스 이동 방법과, 복제시 암호화된 테이블의 테이블스페이스 이동방법시 주의 사항을 알게 됨.
• 언두 로그와 리두 로그에 대한 암호화시점을 알게되었고, 테이블스페이스 키로 암호화한다는 사실을 알게 됨.
• 바이너리 로그 암호화에 대한 내용을 알게 되었고, 해당 키 관리 방법을 알게 됨.(2-Tier)
  • 바이너리 로그 암호화 키의 변경에 대한 프로세스를 이해하게됨.
    • 내부적으로 시퀀스 번호를 이용한다고 하는데 이게 무슨 이점이 있는지는 이해하지 못함..
• mysqlbinlog 도구를 활용하여 mysql 서버에게 바이너리 로그파일을 요청할 수 있음을 알게 됨.
• 인덱스에 대한 내용이 중요하다는 사실과, 그에 앞서 디스크 읽기 방식에 대한 방법을 이해하게 됨.
  • 랜덤 I/O vs 순차 I/O , HDD vs SDD 등..

발췌

• 응용 프로그램의 암호화와 MySQL 서버의 암호화 기능 중 선택해야 하는 상황이라면 고민할 필요 없이 MySQL 서버의 암호화 기능을 선택할 것을 권장한다.
• 아무리 MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자의 역할은 매우 중요하다. 그래서 인덱스에 대한 기본 지식은 지금도 앞으로도 개발자나 관리자에게 매우 중요한 부분이며, 쿼리 튜닝의 기본이 될 것이다.

메모

응용 프로그램 암호화와의 비교

• 응용 프로그램에서 직접 암호화해서 MySQL 서버에 저장하는 경우도 있음.
  • 이 경우, 저장되는 칼럼의 값이 이미 암호화된 것인지 여부를 MySQL 서버는 인지하지 못함.
  • 그래서 응용 프로그램에서 암호화된 칼럼은 인덱스를 생성하더라도 인덱스의 기능을 100% 활용할 수 없음.
• 만약, app_user 라는 테이블에 enc_birth_year 라는 출생연도를 응용 프로그램에서 미리 암호화해서 저장하는 칼럼이라고 하자.
  • 이때, 출생 연도와 동일한 값을 검색하는 쿼리로 검색할 수있다.
  • 하지만 출생 연도 범위의 사용자를 검색하거나 출생 연도를 기준으로 정렬해서 상위 10개만 가져오는 등의 쿼리는 사용할 수 없다.
    • MySQL 서버는 이미 암호화된 값을 기준으로 정렬했기 때문에 암호화되기 전의 값을 기준으로 정렬할 수 없다.
  • 만약, 응용 프로그램에서 직접 암호화하지 않고 MySQL 서버의 암호화 기능(TDE) 를 사용한다면 MySQL 서버는 인덱스 관련 작업을 모두 처리한 후에 최종 디스크에 데이터 페이지를 저장할 때만 암호화 하므로 위와 같은 제약은 없음.
• 응용 프로그램의 암호화와 MySQL 서버의 암호화 기능 중 선택해야 하는 상황이라면 고민할 필요 없이 MySQL 서버의 암호화 기능을 선택할 것을 권장함.
  • 물론, 응용 프로그램의 암호화와 MySQL 서버의 암호화는 목적과 용도가 다름.
    • MySQL 서버의 TDE 기능으로 암호화한다면 실행 중인 MySQL 서버에 로그인하여 모든 데이터를 평문으로 확인 가능
    • 응용 프로그램의 암호화는 MySQL 서버에 로그인 하더라도 평문의 내용을 확인할 수 없음.
      • 응용 프로그램에서의 암호화 기능은 서비스 요건과 성능을 고려하여 선택해야 함.
      • MySQL 서버의 암호화 기능과 혼합해서 사용한다면 더 안전한 서비스 구축할 수 있다.

테이블스페이스 이동

• MySQL 서버의 데이터베이스 관리자라면 테이블스페이스만 이동하는 기능을 자주 사용함.
  • 테이블을 다른 서버로 복사해야 하는 경우, 혹은 특정 테이블의 데이터 파일만 백업했다가 복구하는 경우라면 테이블스페이스 이동(Export & Import) 기능이 레코드를 덤프했다가 복구하는 방식보다 훨씬 효율적이고 빠름.
• TDE가 적용되어 암호화된 테이블의 경우, 원본 MySQL 서버와 목적지 MySQL 서버의 암호화 키(마스터 키)가 다르기 때문에 하나 더 신경써야 한다.
• MySQL 서버에서 다음과 같이 FLUSH TABLES 명령으로 테이블스페이스를 익스포트(Export) 할 수 있다.

mysql> FLUSH TABLES source_table FOR EXPORT;

• 이 명령이 실행되면 MySQL 서버는 source_table 의 저장되지 않은 변경 사항을 모두 디스크로 기록하고 더이상 source_table 에 접근할 수 없게 잠금을 건다.
• 동시에, source_table 의 구조를 source_table.cfg 파일로 기록한다.
  • 그러면 source_table.ibd 파일과 source_table.cfg 파일을 목적지 서버로 복사한다.
  • 복사가 모두 완료되면 UNLOCK TABLES 명령을 실행해서 source_table을 사용할 수 있게 만든다.
    • 이 과정이 암호화되지 않은 테이블의 테이블스페이스 복사 과정임.
• TDE로 암호화된 테이블에 대해 FLUSH TABLES source_table FOR EXPORT 명령을 실행하면 MySQL 서버는 임시로 사용할 마스터 키를 발급해서 source_table.cfp라는 파일로 기록한다.
  • 암호화된 테이블의 테이블스페이스 키를 기존 마스터 키로 복호화한 후, 임시로 발급한 마스터 키를 이용해 다시 암호화해서 데이터 파일의 헤더 부분에 저장한다.
    • 그래서 암호화된 테이블의 경우 테이블스페이스 이동 기능을 사용할 때, 반드시 데이터 파일과 임시 마스터 키가 저장된 *.cfp 파일을 함께 복사해야 함.
      • *.cfg 파일은 단순히 테이블 구조만 가지고 잇기 때문에 파일이 없어져도 경고만 발생하고 테이블스페이스를 복구할 수 있음.
      • *.cfp 파일이 없어지면 복구가 불가능함.

언두 로그 및 리두 로그 암호화

• 테이블 암호화를 적용하더라도 디스크로 저장된 데이터만 암호화 함.
  • MySQL 서버의 메모리에 존재하는 데이터는 복호화된 평문으로 관리됨.
    • 이 평문 데이터가 테이블의 데이터 파일 이외의 디스크 파일로 기록되는 경우에, 여전히 평문으로 저장됨.
    • 그래서 테이블 암호화를 적용해도 리두 로그, 언두 로그, 복제를 위한 바이너리 로그는 평문으로 저장됨.
      • 8.0.16 버전부터 innodb_undo_log_encrypt , innodb_redo_log_encrypt 시스템 변수를 이용해 InnoDB 스토리지 엔진의 리두 로그와 언두 로그를 암호화된 상태로 저장할 수 있게 개선됨.
• 테이블의 암호화는 일단 테이블 하나에 대해 암호화가 적용되면 해당 테이블의 모든 데이터가 암호화돼야 함.
  • 하지만 리두 로그, 언두 로그는 그렇게 적용할 수 없음.
    • 실행중인 MySQL 서버에서 언두, 리두 로그를 활성화 한다고 해도 모든 리두, 언두 로그의 데이터를 해당 시점에 한 번에 암호화해서 다시 저장할 수 없음.
      • MySQL 서버는 리두, 언두 로그를 평문으로 저장하다가 암호화가 활성화되면 그때부터 생성되는 리두, 언두 로그만 암호화해서 저장함.
      • 반대로 리두, 언두 로그가 암호화되는 상태에서 암호화를 비활성화 하면, 그때부터 저장되는 로그만 평문으로 저장됨.
        • 즉, 리두, 언두 로그는 암호화를 활성화했다가 비활성화 한다고 해서 즉시 암호화에 사용된 키가 불필요해지는 건 아님.
          • 특히, 언두 로그의 경우 암호화를 비활성화 한다고 하더라도 새로 생성되는 언두 로그는 평문으로 저장되겠지만, 기존의 언두 로그는 여전히 암호화된 상태로 남아있음.
            • 그래서 상황에 따라 며칠, 또는 몇 달 동안 여전히 암호화 키가 필요할 수 있음.
• 리두 로그와 언두 로그 데이터 모두 각각의 테이블스페이스 키로 암호화됨.
  • 그 테이블스페이스 키는 다시 마스터 키로 암호화 됨.
  • 즉, ALTER INSTANCE ROTATE INNODB MASTER KEY 명령이 실행되면 새로운 마스터 키가 발급되고 테이블 암호화에 사용된 테이블스페이스 키와 동일하게 그 새로운 마스터 키에 의해 다시 암호화됨.
  • 리두 로그와 언두 로그 데이터 암호화에 테이블스페이스 키가 사용된다고 했는데, 이 테이블스페이스 키는 실제 테이블의 암호화에 사용된 테이블스페이스 키가 아니라 리두, 언두 로그 파일을 위한 프라이빗 키를 의미함.
    • 즉, 리두, 언두 로그를 위한 각각의 프라이빗 키가 발급되고, 해당 프라이빗 키는 마스터 키로 암호화되어 리두 로그 파일과 언두 로그 파일의 헤더에 저장됨.
• InnoDB 리두 로그가 암호화됐는지는 SHOW GLOBAL VARIABLES LIKE ‘innodb_redo_log_encrypt’ 로 간단히 확인 가능하다.
• 리두로그 암호화 전, 후에 대한 암호화 문자열에 대한 예시가 p208에 잘 설명되어 있음.

바이너리 로그 암호화

• 테이블 암호화가 적용돼도 바이너리 로그와 릴레이 로그 파일 또한 평문으로 저장됨.
• 일반적으로 언두, 리두 로그는 길지 않은 시간 동안의 데이터만 가지기에 크게 보안에 민감하지 않을 수 있음.
  • 바이너리 로그는 의도적으로 상당히 긴 시간 동안 보관하는 서비스도 있고, 때로 증분 백업(Incremental Backup)을 위해 바이너리 로그를 보관하기도 함.
  • 이런 이유로 바이너리 로그 파일의 암호화는 상황에 따라 중요도가 높아질 수 있음.
• 바이너리 로그와 릴레이 로그 파일 암호화 기능은 디스크에 저장된 로그 파일에 대한 암호화만 담당한다.
  • MySQL 서버의 메모리 내부 또는 소스 서버와 레플리카 서버 간의 네트워크 구간에서 로그 데이터를 암호화하지는 않음.
  • 복제 멤버 간의 네트워크 구간에서도 바이너리 로그를 암호화하고자 한다면 MySQL 복제를 위한 계정이 SSL 을 사용하도록 설정하면 됨.
  • 복제시 네트워크 구간으로 전송되는 데이터의 암호화에 대해서는 3장 ‘사용자 및 권한’을 참조하자.

바이너리 로그 암호화 키 관리

• 바이너리 로그와 릴레이 로그 파일 데이터 암호화도 MySQL 서버에서는 위 그림과 같이 2단계 암호화 키 관리 방식을 사용함.
• 바이너리 로그와 릴레이 로그 파일의 데이터는 파일 키(File Key)로 암호화해서 디스크로 저장한다.
• 파일 키는 “바이너리 로그 암호화" 키로 암호화 해서 각 바이너리 로그와 릴레이 로그 파일의 헤더에 저장된다.
  • 즉, “바이너리 로그 암호화 키”는 테이블 암호화의 마스터 키와 동일한 역할을 함.
  • 파일 키는 바이너리 로그와 릴레이 로그 파일 단위로 자동으로 생성되어 해당 로그 파일의 데이터 암호화에만 사용됨.

바이너리 로그 암호화 키 변경

• 바이너리 로그 암호화 키는 다음과 같이 변경(로테이션)할 수 있다.

mysql> ALTER INSTANCE ROTATE BINLOG MASTER KEY;

• 바이너리 로그 암호화 키가 변경되면 다음의 과정을 거침
  1. 증가된 시퀀스 번호와 함께 바이너리 로그 암호화 키 발급 후 키링 파일에 저장한다.
  2. 바이너리 로그 파일과 릴레이 로그 파일을 스위치 함. (새로운 로그 파일로 로테이션)
  3. 새로 생성된 바이너리 로그와 릴레이 로그 파일의 암호화를 위해 파일 키를 생성하고, 파일 키는 바이너리 로그 파일키(마스터 키)로 암호화해서 각 로그 파일에 저장한다.
  4. 기존 바이너리 로그와 릴레이 로그 파일의 파일 키를 읽어서 새로운 바이너리 로그 파일 키로 암호화해서 다시 저장한다. (암호화되지 않은 로그 파일은 무시한다.)
  5. 모든 바이너리 로그와 릴레이 로그 파일이 새로운 바이너리 로그 암호화 키로 다시 암호화됐다면, 기존 바이너리 로그 암호화 키를 키링 파일에서 제거한다.
• 위 절차에서 4번 과정이 상당한 시간이 걸리는 작업일 수 있음.
• 이를 위해 키링 파일에서 “바이너리 로그 암호화 키”는 내부적으로 버전(시퀀스 번호) 관리가 이루어 짐. (무슨 이점이 있는거지..?)
  • ex) 많은 바이너리 로그와 릴레이 로그를 가진 MySQL 서버에서 ALTER INSTANCE ROTATE BINLOG MASTER KEY 명령을 연속으로 2번 실행한다면 키링 파일에는 순차적인 시퀀스 번호를 가지는 3개의 바이너리 로그 암호화 키가 존재할 것임.
  • 그리고, 바이너리 로그와 릴레이 로그 파일들을 최근 순서대로 파일 키를 다시 암호화해서 저장하는 작업을 수행함.
  • 모든 바이너리 로그와 릴레이 로그 파일의 파일 키가 새로운 바이너리 로그 암호화 키로 암호화되어 저장되면 더이상 기존 바이너리 로그 암호화 키는 필요하지 않으므로 키링 파일에서 제거함.
• MySQL 서버의 바이너리 로그 파일 암호화 여부는 SHOW BINARY LOGS 를 통해 확인할 수 있다.

mysqlbinlog 도구 활용

• MySQL 서버에서 트랜잭션의 내용을 추적하거나 백업 복구를 위해 암호화된 바이너리 로그를 평문으로 복호화할 일이 자주 발생함.
• 하지만 한 번 바이너리 로그 파일이 암호화되면 바이너리 로그 암호화 키 없이 복호화할 수 없음.
  • 그런데 바이너리 로그 암호화 키는 MySQL 서버만 가지고 있어서 복호화가 불가능함.
• mysqlbinlog 도구를 이용하면 암호화된 바이너리 로그 파일의 내용을 SQL 문장으로 접근하면 암호화된 바이너리 로그 파일을 직접 열어 볼 수 없다는 에러 메시지를 출력함.(p211)
• 바이너리 로그 암호화 키는 그 바이너리 로그나 릴레이 로그 파일을 생성한 MySQL 서버만 가지고 있기 때문에 MySQL 서버와 관계없이 mysqlbinlog 도구 만으로는 복호화할 방법이 없음.
  • 그래서 예전처럼 다른 서버로 복사하거나 바이너리 로그 파일을 백업하는 것은 소용없어짐.
• 바이너리 로그 파일의 내용을 볼 수 있는 방법은 MySQL 서버를 통해 가져오는 방법이 유일함.
  • 즉, 현재 MySQL 서버가 mysql-bin.000011 로그 파일을 가지고 있다는 가정하에 mysql-bin.0000111 로그 파일의 내용을 확인하고자 한다면 mysqlbinlog 도구가 MySQL 서버에 접속해서 바이너리 로그를 가져오는 방법밖에 없음.

linux> mysqlbinlog --read-from-remote-server -uroot -p -vvv mysql-bin.0000111

• 위 명령어에서 파라미터로 주어진 mysql-bin.0000111 은 MySQL 서버에게 요청할 바이너리 로그 파일의 이름일 뿐, mysqlbinlog 도구가 직접 mysql-bin.0000111 파일을 읽는 것이 아님.
  • --read-from-remote-server 파라미터와 함께 MySQL 서버 접속 정보를 입력한다.

인덱스

• 인덱스는 데이터베이스 쿼리의 성능을 언급하면 뺴놓을 수 없는 부분임.
• MySQL 쿼리의 개발이나 튜닝 설명하기 전에 MySQL에서 사용가능한 인덱스의 종류 및 특성을 간단히 살펴 보자.
• 각 인덱스의 특성과 차이는 상당히 중요함.
  • 물리 수준의 모델링을 할 때도 중요한 요소가 될 것임.
  • 8.0버전 까지 업그레이드되어 오면서 다른 상용 RDBMS에서 제공하는 많은 기능을 지원하게 됨.
  • 기존의 MyISAM 스토리지 엔진에서만 제공하던 전문 검색이나 위치 기반 검색 기능도 InnoDB 스토리지 엔진에서 사용할 수 있게 개선됨.
    • 하지만, 아무리 MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 하더라도 여전히 관리자의 역할은 매우 중요함.
    • 그래서, 인덱스에 대한 기본 지식은 지금도, 앞으로도 개발자나 관리자에게 매우 중요한 부분이며, 쿼리 튜닝의 기본이 될 것이다.

디스크 읽기 방식

• 자주 언급되는 “랜덤(Random) I/O”, “순차(Sequential) I/O” 와 같은 디스크 읽기 방식을 먼저 간단히 알아보고 인덱스를 살펴보자.
  • 인덱스에만 의존적인 용어는 아님.
• 컴퓨터의 CPU, 메모리처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 매우 빠른 속도로 발전함.
  • 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전함.
    • 비록 최근에는 자기 디스크 원판에 의존하는 하드 디스크보다 SSD 드라이브가 많이 활용되지만, 여전히 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이라는 사실에 변함이 없음.
    • 데이터베이스나 쿼리 튜닝에 어느 정도 지식을 갖춘 사용자가 절감하고 있듯이, 데이터베이스의 성능 튜은 어떻게 디스크 I/O 를 줄이느냐가 관건일 때가 상당히 많음.

하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

• 컴퓨터에서 CPU 나 메모리 같은 주요 장치는 대부분 전자식 장치임
  • 하드 디스크 드라이브는 기계식 장치임
  • 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 됨.
    • 이러한 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 많이 출시되고 잇음.
    • SSD 도 기존 하드 디스크 드라이브와 같은 인터페이스(SATA 나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용할 수 있음.
• SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)를 제거하고 그 대신, 플래시 메모리를 장착하고 있음.
  • 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있음.
  • 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않음.
  • 그리고, 컴퓨터의 메모리(D-Ram) 보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠름.

• 위 그림은 컴퓨터의 주요 부품별 처리 속도를 보여줌 (수치가 클 수록 빠른 장치를 의미한다.)
• Y축의 “Operations / second”란 초당 처리 가능한 연산의 횟수를 의미함.
• 그림에서 보다시피 메모리와 디스크의 처리속도는 10만 배 이상의 차이를 보임
  • 그에 비해, 플래시 메모리를 사용하는 SSD 는 1000배 가량의 차이를 보인다.
  • 시중에 판매되는 SSD는 대부분 기존 하드 디스크 드라이브보다는 용량이 적으며, 가격도 비싼편임.
  • 그렇지만, 예전보다 SSD 가 훨씬 더 대중화된 상태이며, 요즘은 DBMS 용으로 사용할 서버에는 대부분SSD 를 채택하고 있음.
• 디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O 에서는 SSD 가 하드 디스크 드라이브보다 조금 빠르거나 비슷한 성능을 보임.
  • SSD 의 장점은 기존 하드 디스크드라이브보다 랜덤 I/O가 훨씬 빠르다는 것임.
    • 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고, 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분임.
    • 그래서, SSD 의 장점은 RDBMS 용 스토리지에 최적임.

• 위 그림에서는 SSD 와 하드 디스크 드라이브에서 랜덤 I/O의 성능을 벤치마크한 것임. (간단히 준비된 데이터로 테스트한 내용임 → 실제 애플리케이션에서는 어느 정도 성능 차이를 보일지 예측은 어려움)
• SSD 는 초당 436개의 트랜잭션을 처리했지만, 하드 디스크 드라이브는 초당 60개의 트랜잭션밖에 처리 못함.
  • 일반적인 웹 서비스 (OLTP) 환경의 데이터베이스에서는 SSD 가 하드 디스크 드라이브 보다는 훨씬 빠름.