테이블 생성시 데이터 타입 char(n), nchar(n) 으로 생성한 컬럼의 경우 입력한 데이터의 길이거 n보다 짧은 경우 뒤에 공백이 들어간다.

예) char(10) 에 'A' 를 넣으면 'A          ' 가 들어간다.

문제는.. char(10) 에 'A' 를 넣어야되는 상황이 발생했다.

set ansi_padding off 를 설정하면 char(n) 데이터 타입에도 공백이 들어가지 않는다. 

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/set-ansi-padding-transact-sql 

-- 테스트 테이블 생성 
create table tb_test (
   colA char(10),
   colB varchar(10),
   colC nchar(10),
   colD nvarchar(10)
);

-- 테스트 데이터 입력 
insert into tb_test (colA, colB, colC, colD) values ('A','B','C','D');

-- 데이터 확인 
select 
	colA + '*',
	colB + '*',
	colC + '*',
	colD + '*'
from tb_test;

char(10), nchar(10) 은 공백이 들어간다. 

set ansi_padding off 테스트 

-- 테스트 테이블 삭제 
drop table tb_test;

set ansi_padding off;
-- 테스트 테이블 생성 
create table tb_test (
   colA char(10),
   colB varchar(10),
   colC nchar(10),
   colD nvarchar(10)
);
-- 테스트 데이터 입력
insert into tb_test (colA, colB, colC, colD) values ('A','B','C','D');
-- 데이터 확인 
select 
	colA + '*',
	colB + '*',
	colC + '*',
	colD + '*'
from tb_test;

char(10) 은 해결되었으나 nchar(10) 은 공백이 들어간다. 

BOL 을 보면.. nchar 는 안되는구나 ㅠㅠ

MongoDB 에서 insert 는 insertOne, insertMany 를 사용하는데 insertMany 테스트 

insertMany는 트랜잭션을 보장하지 않는다. insert 하다 에러가 발생하면 중단되고 그전에 insert 된것은 입력된 상태.

updateMany, deleteMany 도 동일할듯 

# 테스트용 test 컬렉션 생성 
test> db.test.insertOne({"name":"user1", "age":20})
{
  acknowledged: true,
  insertedId: ObjectId("61f52e50260badd9f57f3a1f")
}
# name 컬럼에 유니크 인덱스 생성 
test> db.test.createIndex(
...     {"name":1},
...     {unique: true}
... );
name_1

# insertMany 로 name: user2, user1 을 넣을 경우
test> to_insert = [
...      {"name":"user2", "age":19},
...      {"name":"user1", "age":20}
... ]
[ { name: 'user2', age: 19 }, { name: 'user1', age: 20 } ]

# 오류 발생 
test> db.test.insertMany(to_insert)
Uncaught:
MongoBulkWriteError: E11000 duplicate key error collection: test.test index: name_1 dup key: { name: "user1" }
Result: BulkWriteResult {
  result: {
    ok: 1,
    writeErrors: [
      WriteError {
        err: {
          index: 1,
          code: 11000,
          errmsg: 'E11000 duplicate key error collection: test.test index: name_1 dup key: { name: "user1" }',
          errInfo: undefined,
          op: {
            name: 'user1',
            age: 20,
            _id: ObjectId("61f52f4b260badd9f57f3a23")
          }
        }
      }
    ],
    writeConcernErrors: [],
    insertedIds: [
      { index: 0, _id: ObjectId("61f52f4b260badd9f57f3a22") },
      { index: 1, _id: ObjectId("61f52f4b260badd9f57f3a23") }
    ],
    nInserted: 1,
    nUpserted: 0,
    nMatched: 0,
    nModified: 0,
    nRemoved: 0,
    upserted: []
  }
}

# 먼저 입력된 name: user2 는 입력되고 name: user1 은 유니크 인덱스 오류로 입력 안됨 
test> db.test.find()
[
  { _id: ObjectId("61f52e50260badd9f57f3a1f"), name: 'user1', age: 20 },
  { _id: ObjectId("61f52f4b260badd9f57f3a22"), name: 'user2', age: 19 }
]

# name: user1, user2 순서로 입력하면 어떻게 될지 궁금해서 name:user2 를 삭제 
test> db.test.deleteMany({"name":"user2"})
{ acknowledged: true, deletedCount: 1 }
test> db.test.find()
[
  { _id: ObjectId("61f52e50260badd9f57f3a1f"), name: 'user1', age: 20 }
]

# name: user1, user2 순서로 입력
test> to_insert = [
...     {"name":"user1", "age":20}, 
...     {"name":"user2", "age":19}
... ]
[ { name: 'user1', age: 20 }, { name: 'user2', age: 19 } ]

# 유니크 인덱스 중복으로 오류 발생 
test> db.test.insertMany(to_insert)
Uncaught:
MongoBulkWriteError: E11000 duplicate key error collection: test.test index: name_1 dup key: { name: "user1" }
Result: BulkWriteResult {
  result: {
    ok: 1,
    writeErrors: [
      WriteError {
        err: {
          index: 0,
          code: 11000,
          errmsg: 'E11000 duplicate key error collection: test.test index: name_1 dup key: { name: "user1" }',
          errInfo: undefined,
          op: {
            name: 'user1',
            age: 20,
            _id: ObjectId("61f52f02260badd9f57f3a20")
          }
        }
      }
    ],
    writeConcernErrors: [],
    insertedIds: [
      { index: 0, _id: ObjectId("61f52f02260badd9f57f3a20") },
      { index: 1, _id: ObjectId("61f52f02260badd9f57f3a21") }
    ],
    nInserted: 0,
    nUpserted: 0,
    nMatched: 0,
    nModified: 0,
    nRemoved: 0,
    upserted: []
  }
}

# 입력 처리가 되지 않았다. 
test> db.test.find()
[
  { _id: ObjectId("61f52e50260badd9f57f3a1f"), name: 'user1', age: 20 }
]

출처 : https://www.youtube.com/watch?v=dF8qmCOSE7g 

MySQL 8.0 
- Redo log 디자인을 변경해서 성능향상
- system mutex 를 최대한 분리해서 IO Capacity 를 충분히 이용하도록 변경 
- Information Schema, Performance Schmea 를 조회하는 성능 향상

Memory 
- 작업 데이터셋이 innodb buffer pool 에 적합한지 확인
  사용 가능한 메모리의 70~80%를 innodb buffer pool 에 할당 할 수 있음 
- MySQL 은 Connection 당 메모리 소모, Connection 당 할당되는 메모리는 크게 할당하는것을 권장하지 않는다. 
  max_allowed_packet, thread_stack, net_buffer_length, max_digest_length
- 디스크 접속을 완하하기 위한 Caching 및 Buffer 
  Read log/Join/Sorting Buffer 와 Table Cache
  Replication 관련 max_binlog_cache_size 
- 기타
  - OS, FS cache
  - temporary tables tmp_table_size 
  - Performance Schema
- 모니터링 방법: top 명령어로 확인, 전체 메모리의 80% 이하 

Monitoring MySQL Memory Usage 
- 대부분 메모리 지표는 비활성화 됨 
  select * from performance_schema_setup_instruments where name like '%memory%';
- performance_schema 
  memory_summary_global_by_event_name 
- sys 스키마 
  memory_global_by_current_bytes 
- 상태값 확인
  show global status like 'table_open%';
  show global status like 'binlog%';
  show global status like 'created_tmp%';
  
  select 
    substring_index(event_name, '/', 2) as core_area, 
    format_bytes(sum(current_alloc)) as current_alloc
  from sys.x$memory_global_by_current_bytes
  group by substring_index(event_name, '/', 2)
  order by sum(current_alloc) desc;

Disk 
- SSD, NVMe 권장 
 innodb datadir, tmp 파일, undo 로그 모두 랜덤 IO, 빠른 SSD 권장
 inndb_page_size = 4K (디폴트는 16K, 디스크 page 사이즈와 매칭)
 innodb_flush_neighbors = 0 
- Spinning 디스크는 log (순서 IO)에 여전히 사용할 수 있음 
- RAID 디스크를 사용한다면 성능을 위해 RAID 1+0 권장 
- FBWC/BBWC (Flash/Battery-Backed Write Cache) 권장 
  write 성능 향상 및 crash-safe
- 디스크 유형(rpm 지표) 및 부하에 따라 innodb_io_capactiy 설정 
  디폴트값은 200, 느린 ssd 혹은 일반 하드 디스크에 적합 
  디스크 성능이 좋다면 200보다 더 큰값을 설정

OS 
- 성능만 고려하면 Linux 권장 
- MySQL 8.0 인 경우 최신 커널 권장하지
- ext4, xfs 파일 시스템 추천
- ulimit을 사용하여 file/process limits 확장 
- MySQL 전용 서버인 경우, innodb_numa-interleave = 1 권장 
- Swappiness, 권장 옵션은 (1-6): sysctl - vn.swappinness=1
- InnoDB 데이터 파일은 FS 캐시를 거치지 않음, innodb_flush_method=O_DIRECT 

MySQL Configuration 
- 8.0 설정 파일 : my.cnf, mysqld-auto.cnf (SET PERSIST 변수로 지정한 값을 mysqld 재기동 후에도 유지)
- 설정값 확인 테이블 추가 
  - performance_schema.global_variables
  - performance_schema.session_variables
  - performance_schema.variables_by_thread
  
- 클라이언트 커넥션 관련 설정 
  - max_connections 를 필요 이상으로 크게 설정하지 말아야 할 것 
   - OS limit on file descriptors 
     - Linux/Solaris : UP to 10,000
     - Windows: Up to 16,384
   - 응답 시간의 기대치 및 워크로드 감안
   - 많은 연결 수가 예상되면 버퍼에 따른 메모리 용량을 확복 
   - wait_timeout으로 연결을 닫음 
  - 상태값 확인
    - Connections
    - Max used connections
    - Connection errors max connections 
  - 커넥션별로 할당되는 버퍼 값은 작게 시작 
    - sort_buffer_size
    - binlog_cache_size
    - net_buffer_length ~ max_allowed_packet 
    - read_buffer_size 
    - read_rnd_buffer_size 
    
Reusing Threads
- thread_cache_size : 서버가 스레드를 재사용할 수 있도록 저장한 스레드 개수 
  - 디폴트 값은 자동 설정 : 8 + (max_connections / 100)
  - Thread cache hit rate % = 100 - ((Threads_created / Connections) * 100)
- 연관된 상태 값 확인 
  - Connections
  - Threads_connected
  - Connections / Uptime 
  - Threads_created / Uptime 
  - Uptime_since_flush_status 
  - Threads_cached 
  - Threads_running 
- thread_pool_size 스레드 풀 성능 튜닝 (엔터프라이즈만 사용가능)
  - https://dev.mysql.com/doc/refman/8.0/en/thread-pool-tuning.html

InnoDB Buffer pool 
- innodb_buffer_pool_size 
  - 모든 데이터를 innodb_buffer_pool 에 넣어서 사용하면 이상적이나(이상일 뿐)
  - 테이블 및 인덱스 데이터를 캐시한 InnoDB의 메모리 영역, LRU 알고리즘에 의해 관리 
  - 전용 데이터베이스 서버에서 시스템의 실제 메모리 크기의 70%~80%정도로 설정 할 수 있음 
  - innodb_buffer_pool_size  = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 
- 확인 방법 : SHOW ENGINE INNODB STATUS;
  - Total memory allocated
  - Buffer pool size 
  - Free buffers 
  - Database pages, Old database pages 
  - Modified db pages 
  - Pending reads, Pending writes 
  - Buffer pool hit rate 

Innodb redo log (트랜잭션 로그)
- innodb data file에 발생하는 랜덤 io 를 red log file을 사용해서 순차 io 로 변경해서 처리 해줘서 write 성능 향상 
- innodb_log_file_size 
  - write 성능에 유리하지만 복구 시간에 영향 (크래시 리커버는 redo log 기반)
  - 총 사이즈(~512G) : innodb_log_file_size * innodb_log_files_in_group (기본값 2)
  - 운영 환경에서 최소 512M으로 권장하고, 가능한 충분히 크게 설정할 것 권장 
- redo 사용율 
  - SHOW ENGINE INNODB STATUS 에서 Log sequence number, Last checkpoint at 값을 확인하여 계산 
  - SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME IN ('log_lsn_current', 'log_lsn_last_checkpoint')
  - Used % = (Used log / Total log) * 100 
    = (log_lsn_current - log_lsn_last_checkpoint / (innodb_log_file_size * innodb_log_files_in_group)) * 100
    = (47605855 / 100663296) * 100 = 47.29% 
  - Used % 가 50% 정도는 괜찮지만 80%정도일 경우 위험함 
    redo log 가 부족하게 되면 innodb buffer pool flushing 이 발생하는데 이때 대량의 io가 발생하여 성능 저하가 발생한다. 
- innodb_log_buffer_size = 16MB
  - Redo 로그 I/O를 완화하기 위한 버퍼 영역 
  - 일반적으로 16MB정도면 충분하지만 트랜잭션 사이즈가 크면 commit 전에 disk IO 가 발생되기에, 따라서 크게 조절할 필요가 있음 
 - innodb_flush_log_at_trx_commit = 1
  - InnoDB가 commit한 트랜젝션에 대해서 언제 flush 할 것인지 결정
  - 디폴트값인 1로 권장하지만 다음 상황에서 제외:
    - InnoDB가 commit한 트랜젝션에 대해서 언제 flush 할 것인지 결정
    - Bulk 데이타 로딩할 경우 2로 설정하거나 8.0 버전에서 redo로그를 비활성화
    - 예측하지 못한 peak 부하로 인해 디스크 I/O부하가 심할 경우
    - 데이터 로스가 발생하여도 괜찮을 경우

바이너리 로그 성능만- 
-  binlog_cache_size에 비해 트랜젝션이 너무 크면, 임시 파일에 저장하기에 성능 영향
- 관련된 상태 변수 확인:
  - Binlog_cache_use
  - Binlog_cache_disk_use
  - 캐시 효율성% = (100 - ( Cache Disk Use/Cache Use) * 100)
- 트랜젝션 사이즈를 제어함으로 바이너리 로그 사이즈 제어, 복제 효율성 향상
  - DELETE 대신에 DROP TABLE/PARTITION
  - DELETE FROM … WHERE … LIMIT 1000;
  - binlog_row_image = minimal
- 복제 지연 방지 설정
  - binlog_transaction_dependency_tracking = WRITESET
  - replica_parallel_type = LOGICAL_CLOCK
  - replica-parallel-workers = 16
  - binlog_order_commits=OFF
  - replica_preserve_commit_order = 1(optional)
https://dev.mysql.com/blog-archive/improving-the-parallel-applier-with-writeset-based-dependency-tracking/

OPS Manager를 쓸수 있으면 좋겠지만.. 라이센스 이슈로 사용하지 못할때 사용할만한 모니터링 솔루션!

Percona Monitoring and Management (PMM)

구성 계획

- OS : CentOS 8

- PMM Server : 192.168.137.100

- PMM Client : 192.168.137.21, 호스트명 mongodb 

PMM Server 설치 

참고 : https://www.percona.com/doc/percona-monitoring-and-management/2.x/index.html

# yum-config-manager 사용하기 위해 yum-utils설치 
sudo yum -y install yum-utils

# 레포지토리 설치
sudo --add-repo https://download.docker.com/linux/centos/docker-ce.repo
 
# Docker 설치 및 구동 
sudo yum -y install docker-ce
sudo usermod -aG docker $USER
sudo systemctl enable docker
sudo systemctl start docker

# 이미지 받아오기
docker pull percona/pmm-server:2

# 영구 데이터 컨테이너 생성 
docker create --volume /srv \
--name pmm-data \
percona/pmm-server:2 /bin/true

# PMM 서버 시작
docker run --detach --restart always \
--publish 443:443 \
--volumes-from pmm-data \
--name pmm-server \
percona/pmm-server:2

- 브라우저로  admin/admin 으로 로그인 
https://192.168.137.100/graph/login

PMM Client 설치 

참고 : https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/index.html

# PMM Client 설치 
sudo yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum -y install pmm2-client

# PMM Server 에 Client 등록 
sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.137.100:443 192.168.137.21 generic mongodb

# MongoDB에 DB 모니터링 계정 생성 
db.getSiblingDB("admin").createRole({
    role: "explainRole",
    privileges: [{
        resource: {
            db: "",
            collection: ""
            },
        actions: [
            "listIndexes",
            "listCollections",
            "dbStats",
            "dbHash",
            "collStats",
            "find"
            ]
        }],
    roles:[]
})

db.getSiblingDB("admin").createUser({
   user: "pmm_mongodb",
   pwd: "password",
   roles: [
      { role: "explainRole", db: "admin" },
      { role: "clusterMonitor", db: "admin" },
      { role: "read", db: "local" }
   ]
})

# PMM Server 에 MongoDB 등록 
sudo pmm-admin add mongodb --username=pmm_mongodb --password=password --service-name=mongodb --host=127.0.0.1 --port=27017

EMail 알람 설정

참고 : https://www.percona.com/blog/2020/07/07/enable-email-sending-in-percona-monitoring-and-management-2/

# pmm-server 컨테이너 접속 
sudo docker exec -it pmm-server bash
# pmm-server 컨테이너의 /etc/grafana/grafana.ini 파일에 email 서버 설정 
vi /etc/grafana/grafana.ini
[smtp]
enabled = true
host = smtp.gmail.com:587
user = <gmail username>
password = <gmail password>
;cert_file =
;key_file =
skip_verify = false
from_address = <gmail email address>
;from_name = Grafana
;EHLO identity in SMTP dialog (defaults to instance_name)
;ehlo_identity = dashboard.example.com
[emails]
;welcome_email_on_sign_up = false
# pmm-server 컨테이너에서 빠져나온다.
exit

# pmm-server 컨테이너 재기동
sudo docker restart pmm-server

# 컨테이너 생성시에 email 서버를 지정하는 경우 
sudo docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data --name pmm-server \
--restart always \
-e GF_SMTP_ENABLED=true \
-e GF_SMTP_HOST=smtp.gmail.com:587 \
-e GF_SMTP_USER=<gmail username> \
-e GF_SMTP_PASSWORD=<gmail password> \
-e GF_SMTP_SKIP_VERIFY=false \
-e GF_SMTP_FROM_ADDRESS=<gmail email address> \
-e GF_SMTP_FROM_NAME=Grafana \
percona/pmm-server:2

- google 의 smtp 를 사용하려면 Google 계정 관리 -> 보안 -> 보안 수준이 낮은 앱의 액세스를 허용으로 변경해야한다. 

- grafana 의 Alterting -> Notification channels 설정 후 "Test" 버튼을 클릭하면 테스트 메일이 발송된다.

CentOS 에서 yum 으로 xtrabackup 설치

# Percona yum repository 설치 
yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# Enable the repository:
percona-release enable-only tools
# xtrabackup 설치 
yum -y install percona-xtrabackup-80
# 압축 백업을 사용하기 위해 qpress 설치 
yum -y install qpress

CentOS 6 에서 xtrabackup 을 설치하기 위해서는 libev 패키지가 설치되어 있어야한다. 

rpm 설치 

# 필요 패키지 설치 
yum install -y libev perl-DBD-MySQL perl-Time-HiRes
# xtrabackup 설치 rpm 다운로드 
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
# xtrabackup 설치 
yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

서버에 연결 

xtrabackup --default-file=/etc/my.cnf --user=MySQL계정(백업 실행 권한 필요) --password=패스워드 --backup \
--host=접속할 MySQL서버 ip --port=접속할 MySQL서버 포트 --socket=접속할 MySQL 서버 소켓파일 \
--target-dir=백업할 디렉토리

xtrabackup 기본 백업 디렉토리 설정 

my.cnf 파일에 아래와 같이 지정할 수 있다. 

[xtrabackup]
target_dir=/data/backups/mysql/

필요 권한 

- BACKUP_ADMIN : performance_schema.log_status 테이블 쿼리,  LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, LOCK TABLES FOR BACKUP 실행 권한 

- PROCESS : SHOW ENGINE INNODB STATUS 실행과, 서버에서 실행중인 모든 스레드를 보기위한 권한 

- RELOAD, LOCK TABLES : 파일 복사를 시작하기전에 FLUSH TABLES WITH READ LOCK, FLUSH ENGINE LOGS를 실행하기 위한 권한 (백업 잠금이 사용될때 필요한 권한)

- REPLICATION CLIENT : binary log position 확인을 위한 권한 

- CREATE TABLESPACE : import tables 를 위한 권한 (개발 테이블 복원시)

- SUPER : 복제구성에서 복제 스레드 시작/중지를 위한 권한, XtraDB에서 증분백업을 위한 변경 페이지 추적을 위한FLUSH TABLES WITH READ LOCK를 사용하기 위한 권한

- CREATE : PERCONA_SCHEMA.xtrabackup_history 데이터베이스, 테이블을 생성하기 위한 권한

- INSERT : PERCONA_SCHEMA.xtrabackup_history 테이블에 히스토리 레코드를 추가하기 위한 INSERT 권한

- SELECT : PERCONA_SCHEMA.xtrabackup_history 테이블에서 innodb_to_lsn 값 조회, --incremental-history-name 또는 --incremental-history-uuid 을 사용하기 위한 권한 

전체 백업에 필요한 최소 권한을 가진 데이터베이스 사용자 생성 예제

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'localhost'
mysql> FLUSH PRIVILEGES;

전체백업

- xtrabackup 실행시 --backup, --target-dir 을 지정하여 실행

- --target-dir 로 지정된 디렉토리가 비어있는 경우에만 백업이 진행됨

  지정 디렉토리에 파일이 있는경우 에러가 발생한다.

xtrabackup --backup --target-dir=/data/backups/

전체백업 복원 

준비 단계 

- xtrabackup 실행시 --prepare, --target-dir 을 지정하여 실행

  백업파일 무결성, 일관성 검증실행 

- --prepare 옵션을 주고 xtrabackup 프로세스 실행중에 중지하지 않는것이 좋다. 

  데이터파일이 손상되고 백업을 사용할수 없게 될수도 있다. 

xtrabackup --prepare --target-dir=/data/backups/

복원 

- xtrabackup 실행시 --copy-back 또는 --move-back 옵션을 사용

  기존의 data 디렉토리에 자동으로 복원됨 

  data 디렉토리는 비어있어야 하며, mysql 서버도 종료된 상태여야함 

- --copy-back, --move-back 대신 rsync를 사용할수도 있다. 

 rsync -avrP /data/backup/ /var/lib/mysql/

- mysql 을 구동하기 전에 data 디렉토리의 소유권을 변경한다. 

chown -R mysql:mysql /var/lib/mysql

증분 백업

- xtrabackup 은 증분 백업을 지원한다. 

  마지막 백업 이후 변경된 데이터만 복사할 수 있다. 

- 증분 백업은 각 InnoDB 페이지에 로그 시퀀스번호 또는 LSN이 포함되어 있기 때문에 작동한다. 

  LSN은 전체 데이터베이스의 시스템 버전 번호.

- 증분 백업은 LSN이 이전 증분, 전체 백업의 LSN보다 최신인 각 페이지를 복사한다. 

- 복사할 페이지 세트를 찾는데 사용되는 알고리즘 

  - 모든 데이터 페이지를 읽어 페이지 LSN을 직접 확인 

  - 변경된 페이지 추적 기능 사용 (Percona Server for MySQL 에서만 사용가능)

- 증분 백업은 실제로 데이터 파일을 이전 백업 파일과 비교하지 않는다.  

  부분 백업후 증분 백업을 실행하면 데이터가 일치하지 않을 수도 있다. 

- 증분 백업으로 증분 변경사항을 복구하라면 전체 백업이 필요하다. 

- LSN 을 알고 있는 경우 --incremental-lsn 옵션을 사용하여 이전 백업 없이도 증분 백업 수행 가능 

증분 백업 생성

- 중분 백업을 수행하려면 전체 백업본이 필요하다. 

xtrabackup --prepare --target-dir=/data/backups/base

- xtrabackup 백업 대상 디렉토리에 xtrabackup_checkpoints 라는 백업 기록 파일이 있다. 

  이 파일에는 백업 시작,  종료 lsn 정보(from_lsn, to_lsn) 가 있다. 

backup_type = full-backuped
from_lsn = 0
to_lsn = 1626007
last_lsn = 1626007
compact = 0
recover_binlog_info = 1

- 전체 백업을 기반으로 증분 백업 진행 

xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base

- /data/backups/inc1 디렉토리의 xtrabackup_checkpoints 파일에는 아래와 같이 백업 시작,  종료 lsn 정보(from_lsn, to_lsn) 가 있다. 

backup_type = incremental
from_lsn = 1626007
to_lsn = 4124244
last_lsn = 4124244
compact = 0
recover_binlog_info = 1

- /data/backups/inc1 디렉토리를 다른 증분 백업의 기반으로 사용할 수 있다.

 xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1

- /data/backups/inc2 디렉토리의 xtrabackup_checkpoints 파일에는 아래와 같이 백업 시작,  종료 lsn 정보(from_lsn, to_lsn) 가 있다. 

backup_type = incremental
from_lsn = 4124244
to_lsn = 6938371
last_lsn = 7110572
compact = 0
recover_binlog_info = 1

증분 백업 복원

준비 단계 

- 증분 백업을 복원하기 위한 준비 단계는 전체 백업과 동일하지 않다. 

- 전체 백업에서는 데이터베이스 일관성을 유지하기 위해 두기지 유형의 작업이 수행된다. 

  커밋된 트랜잭션은 데이터파일에 대해 로그파일에서 재생되고 커밋 되지 않은 트랜잭션은 롤백된다. 

  백업시 커밋되지 않은 트랜잭션이 진행중 일수 있고 다음 증분 백업에서 키밋 될 가능성이 있으므로 

  증분 백업을 준비할 때 커밋되지 않은 트랜잭션의 롤백을 건너 뛰어야 한다. 

  롤백 단계를 방지하기 위해서 --apply-log-only 옵션을 사용해야 한다. 

- --apply-log-only 옵션을 사용하지 않으면 증분백업이 소용 없다. 

  트랜잭션이 롤백된 이후에는 추가 증분 백업을 적용할 수 없다. 

- 롤 백 단계를 건너 뛰고 복원해도 안전하다.

  복원하고 MySQL을 시작하면 InnoDB는 롤백 단계가 수행되지 않았음을 감지하고 일반적으로 시작시 충돌 복구에 

  대해 수행하는 것처럼 백그라운드에서 복구를 수행한다. 

예제 ) 다음과 같은 백업이 있을 때 복원 

/data/backups/base
/data/backups/inc1
/data/backups/inc2

- 기본 백업을 준비하려면 --perpare 옵션에  --apply-log-only 옵션을 사용하여 롤백 단계를 방지한다. 

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

- 첫번째 증분 백업을 전체 백업에 적용

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

- 두번째 증분 백업을 전체 백업에 적용

※ --apply-log-only 옵션은 마지막 증분 백업을 적용할때는 사용하지 않는다.

xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2

복원은 전체 백업과 동일하게 xtrabackup 실행시 --copy-back 또는 --move-back 옵션을 사용한다. 

압축 백업

- 압축 백업을 만들려면 --compress 옵션을 사용한다. 

 xtrabackup --backup --compress --target-dir=/data/compressed/

- 압축 백업을 사용하려면 qpress 패키지가 필요하다.

- --compress-threads 옵션으로 압축 속도를 높일 수 있다.

# 4개의 스레드를 사용한 압축 백업
trabackup --backup --compress --compress-threads=4 \
--target-dir=/data/compressed/

압축 백업본으로 복원 준비

- --decompress 옵션으로 압축을 해제한다. 

 xtrabackup --decompress --target-dir=/data/compressed/

- --parallel 옵션을 사용해서 동시에 여러파일의 압축을 해제할 수 있다.

- 압축이 해제되도 압축된 백업파일은 삭제되지 않는다. 

  --copy-back, --move-back 옵션 사용시에 압축된 백업 파일은 복사되지 않는다. 

  --remove-original 옵션을 사용할 경우 압축된 백업파일이 삭제된다. 

- 백업 파일의 압축이 해제되면 --prepare 옵션을 사용하여 복원을 준비할 수 있다.

xtrabackup --prepare --target-dir=/data/compressed/

백업 복원 

- xtrabackup 실행시 --copy-back 또는 --move-back 옵션을 사용하여 기존의 data 디렉토리에 복원한다.

xtrabackup --copy-back --target-dir=/data/backups/

참고

https://www.percona.com/doc/percona-xtrabackup/8.0/index.html

xtrabackup 사용시 이점

- 빠르고 안정적으로 완료되는 백업

- 백업 중 중단없는 트랜잭션 처리

- 디스크 공간 및 네트워크 대역폭 절약

- 자동 백업 확인

- 더 빠른 복원시간으로 더 높은 가동시간 

지원버전 

- xtrabackup 8.0.x 버전은 MySQL 8.0.x 버전 호환

- xtrabackup 2.4 버전은 MySQL 5.6, 5.7 버전 호환

지원되는 스토리지 엔진

- InnoDB, XtraDB, MyRocks 스토레지 엔진의 완전 비차단 백업 지원

  - MyRocks 스토리지 엔진은 8.0.6 버전부터 지원 

- MyISAM, Merge, Archive, Patition Table, Trigger, Database Option 의 경우 백업이 끝날때 쓰기를 일시 중지하여 백업 가능 

- xtrabackup 8.0 은 TukuDB 스토리지 엔진 지원 안함 

xtrabackup 기능 

- 데이터베이스를 중지하지 않고 InnoDB 백업 생성

- MySQL 증분 백업

- 압축된 MySQL 백업을 다른 서버로 스트리밍

- 온라인으로 MySQL 서버간의 테이블 이동

- 새로운 MySQL 복제 복제본을 쉽게 생성

- 서버에 부하를 추가하지 않고 MySQL 백업 

xtrabackup 동작 방식

- xtrabackup 은 InnoDB의 crash-recovery 기능을 기반으로한다.

- InnoDB 데이터 파일을 복사하므로 내부적으로 일관성이 없는 데이터 생성된다. 

  그런 다음 파일에 대해 응급 복구를 수행하여 일관되고 사용 가능한 데이터베이스를 다시 만든다. 

- 이 작업은 InnoDB가 트랜잭션 로그라고도하는 Redo Log를 유지하기 때문에 작동한다. 

- Redo Log에는 InnoDB 데이터에 대한 모든 변경 기록이 포함된다.

  InnoDB가 시작되면 데이터 파일과 트랜잭션 로그를 검사하는 단계를 수행한다.

  커밋된 트랜잭션 로그 항목을 데이터 파일에 적용하고 데이터를 수정했지만

  커밋하지 않은 모든 트랜잭션에 대해 실행 취소 작업을 수행한다. 

- xtrabackup 은 시작할때 로그 시퀀스 번호(LSN)을 기억한 당므 데이터 파일을 복사하는 방식으로 작동한다. 

  이를 수행하는데 약간의 시간이 걸리므로 파일이 변경되면 다른 시점의 데이터베이스 상태를 반영한다. 

- xtrabackup 은 트랜잭션 로그파일을 감시하는 백그라운드 프로세스를 실행해서 변경 사항을 복사한다. 

  트랜잭션 로그가 라운드 로빈 방식으로 작성되고 재사용 가능성이 있기 때문에 이를 지속적으로 수행한다. 

- xtrabackup 실행후 데이터 파일이 변경될 때마다 트랜잭션 로그 레코드가 필요하다. 

- xtrabackup은 FLUSH TABLES WITH READ LOCK의 경량 대안으로 사용 가능한 백업 작업을 사용한다. 

  이 기능은 MySQL 5.6 이후부터 사용가능하며, MySQL 8.0에서는 LOCK INSTANCE FOR BACKUP 문을 통해

  인스턴스 수준 백업 잠금을 획득할 수 있다. 

- 잠금은 xtrabackup 이 모든 InnoDB/XtraDB 데이터 및 로그 백업을 완료한 후에

  MyISAM 및 non-InnoDB 테이블에 대해서만 수행된다.

- LOCK INSTANCE FOR BACKUP 또는 LOCK TABLES FOR BACKUP을 효과적으로 사용하려면 

  performance_schema.log_status를 쿼리하기 위해 BACKUP_ADMIN 권한이 필요하다.

- xtrabackup은 인스턴스에 InnoDB 테이블만 포함된 경우 백업 작금 및 FLUSH TABLES WITH READ LOCK을 방지한다. 

  이 경우 xtrabackup 은 perfomance_schema.log_status 에서 binary log position를 가져온다. 

  --slave-info 로 xtrabackup을 시작할때 MySQL 8.0에서 FLUSH TABLES WITH READ LOCK가 여전히 필요하다. 

-  Percona Server for MySQL 8.0 의 log_status 테이블은 relay log 좌표를 포함하도록 확장되어

   --slave-info 옵션을 사용해도 잠금이 필요하지 않다. 

- 서버에서 backup lock을 지원하면 xtrabackup은 InnoDB 데이터를 복사하고 LOCK TABLES FOR BACKUP을 실행한

  다음 MyISAM 테이블을 복사한다. 이 작업이 완료되면 파일 백업이 시작된다.

  .frm, .MRG, .MYD, .MYI, .ARM, .ARZ, .CSM, .CSV, .sdi 및 .par 파일을 백업한다. 

- 그 후 xtrabackup은 LOCK BINLOG FOR BACKUP 을 사용하여 SHOW MASTER / SLAVE STATUS 에서 확인한 

  Binary log position, Exec_Master_Log_Pos, Exec_Gtid_Set 을 변경할 수 있는 모든 작업을 차단한다. 

- xtrabackup 은 Redo Log 파일 복사를 완료하고 Binary log position을 가져온다. 

  이것이 완료되면 xtrabackup은 Binary log와 테이블의 잠금을 해제한다. 

- 마지막으로 Binary log position은 STDERR에 인쇄되고 xtrabackup 은 모든 것이 정상이면 0을 반환하고 종료된다. 

  xtrabackup 의 STDERR은 파일에 기록되지 않는다. 

  파일로 리디렉션 해야한다. (예 : xtrabackup OPTIONS 2>backupout.log)

- 백업 디렉토리에 다음 파일을 생성한다. 

  - backup-my.cnf

    my.cnf 파일의 백업이 아니며, --prepare 중에 InnoDB 의 미니 인스턴스를 시작하기 위한 정보가 포함되어 있다.

  - xtrabackup_checkpoints

    백업 유형(전체 또는 증분), 상태(ex, prepared) 및 포함된 LSN 범위가 저장되어 있으며

    이 정보는 증분 백업에 사용된다. 

  - xtrabackup_binlog_info

    SHOW MASTER STATUS의 확인된 Binary Log 파일 및 Position 

  - xtrabackup_binlog_pos_innodb

    Binary Log 파일 및 InnoDB 또는 XtraDB 테이블의 현재 위치 

  - xtrabackup_binary

    프로세스에 사용되는 xtrabackup 바이너리 

  - xtrabackup_logfile

    --prepare 를 실행하는데 필요한 데이터를 포함한다.

    이 파일이 클수록 --prepare 프로세스에 걸리는 시간이 늘어난다. 

  - <table_name>.delta.meta

    증분 백업을 수행할때 생성되는 파일로 페이지 크기, 압축된 페이지 크기 및 공간 ID와 같은

    테이블별 delta metadata 가 포함된다. 

  - xtrabackup_slave_info

    --slave-info 옵션을 사용하는 경우 생성되며 복제본 설정에 필요한 CHANGE MASTER 문이 기록된다. 

  - xtrabackup_galera_info

    --galera-info 옵션을 사용하는 경우 생성되며 Galera 및 Percona XtraDB 클러스터 정보가(wsrep_local_state_uuid 및 wsrep_last_committed 상태 변수의 값) 기록된다. 

- perpare 단계 동안 xtrabackup은 복사된 트랜잭션 로그 파일을 사용하여 복사된 데이터 파일에 대해 응급복구를 수행한다.  이 작업이 완료되면 데이터베이스를 복원하고 사용할 수 있다.

- perpare(recovery) 프로세스후에 InnoDB의 데이터는 백업이 완료된 시점으로 롤백되지 않고 백업이 완료된 시점으로 롤 포위드 된다. 이 시점은 FLUSH TABLES WITH READ LOCK이 취해진 위치와 일치하므로 MySQL 데이터와 준비된 InnoDB 데이터가 동기회된다. 

백업 복원 

- xtrabackup 으로 백업으로 복원하려면 --copy-back 또는 --move-back 옵션을 사용한다. 

- xtrabackup은 my.cnf에서 datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir 변수를

  읽고 디렉토리가 있는지 확인한다.

- 아래의 순서로 복사한다.

  1) MyISAM 테이블, 인덱스 등 (.MRG, .MYD, .MYI, .ARM, .ARZ, .CSM, .CSV, .sdi 및 par 파일)

  2) InnoDB 테이블과 인덱스

  3) 로그

- 파일을 복사 할 때 파일의 속성을 보존한다.

  - 데이터베이스 서버를 시작하기 전에 복원된 데이터 파일의 소유권 확인, 변경 한다.

- --move-back 옵션을 사용하여 백업을 복원할 수 있다. 

   --copy-back 옵션과 비슷하지만 파일을 복사하는 대신 대상위치로 이동하는다는 점만 다르다. 

    대상위치로 이동하기 때문에 속도는 빠르지만 백업 파일이 이동되어 사라진다. 

참고:

https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

MySQL 8.0.18 부터 Hash Join 을 지원한다. 

Hash Join 은 Hash Table을 사용하여 두 입력간에 일치하는 행을 찾는 Join  방법이다. 

입력 중 하나가 메모리에 들어갈 수 있는 경우 Nested Loop Join 보다 효율적이다. 

빌드 단계

일반적으로 Hash Join을 빌드 단계와 프로브 두단계로 나눈다.

빌드 단계에서 서버는 조인 속성을 해시 테이블 키로 사용하여 입력 중 하나의 행이 저장되는 인메모리 해시 테이블을 빌드한다. 

이 입력을 빌드 입력이라고도하며 'countries'가 빌드 입력으로 지정되었다고 가정했을때, 이상적으로 서버는 두 입력중 더 작은것을 빌드 입력으로 선택한다. (행수가 아닌 바이트로 측정) 'countries.country_id'는 빌드 입력에 속하는 조인 조건이므로 해시 테이블에서 키로 사용된다. 모든 행이 해시 테이블에 저장되면 빌드 단계가 완료된다. 

프로브 단계

프로브 단계 동안 서버는 프로브 입력(이 예에서는 'persons')에서 행을 읽기 시작한다. 

각 행에 대해 서버는 'persons.country_id'의 값을 조회 키로 사용하여 행과 일치하는 해시 테이블을 조사한다. 각 일치에 대해 조인된 행이 클라이언트로 전송된다. 결국 서버는 두 입력간에 일치하는 행을 찾기 위해 일정한 시간 조회를 사용하여 각 입력을 한번 만 스캔했다. 

이것은 서버가 전체 빌드 입력을 메모리에 저장할 수 있다는 점을 감안할때 매우 잘 작동한다. 사용 가능한 메모리양은 시스템변수 join_buffer_size에 의해 제이되며 운영중에 조정할 수 있다. 그러나 빌드 입력이 사용 가능한 메모리보다 크면 디스크를 사용한다. 

디스크로 유출

빌드 단계에서 메모리가 가득차면 서버는 빌드 입력의 나머지 부분을 디스크의 여러 청크 파일에 쓴다. 서버는 가장 큰 청크가 메모리에 정확히 맞도록 청크 수를 설정하려고 하지만 MySQL은 입력당 최대 128개의 청크파일제한한다. 행이 기록되는 청크 파일은 조인 속성의 해시를 계산하여 결정된다. 그림에는 메모리 내 빌드 단계에서 사용된것과 다른 해시 함수가 사용된다. 그 이유는 나중에 살펴본다. 

프로브 단계 동안 서버는 모든 것이 메모리에 맞을때와 마찬가지로 해시 테이블에서 일치하는 행을 조사한다. 그러나 행은 디스크에 기록된 빌드 입력의 행중 하나와 일치할 수도 있다. 따라서 프로브 입력의 각 행도 청크 파일 세트에 기록된다. 행이 기록되는 청크 파일은 빌드 입력이 디스크에 기록될때 사용되는 동일한 해시 함수 및 공식을 사용하여 결정된다. 이렇게 하면 두 입력사이에 일치하는 행이 동일한 청크파일쌍에 위치하게 된다. 

프로브 단계가 완료되면 디스크에서 청크 파일을 읽기 시작한다. 일반적으로 서버는 첫번째 청크 파일 세트를 빌드 및 프로브 입력으로 사용하며 빌드 및 프로브 단계를 수행한다. 빌드 입력의 첫번째 청크 파일을 메모리 내 해시 테이블로 로드한다. 이것은 우리가 메모리에 정확히 맞는 가장 큰 청크를 원하는 이유를 설명한다. 청크 파일이 너무 크면 더 작은 청크로 분할해야한다. 빌드 청크가 로드된후 프로브 입력에서 해당 청크 파일을 읽고 모든 것이 메모리에 맞을때와 마찬가지로 해시 테이블에서 일치항목을 검색한다. 청크 파일의 첫번째 쌍을 처리하면 다음 쌍의 청크 파일로 이동하고 모든 청크파일이 처리 될때까지 계속한다. 

이제 행을 청크 파일로 분할하고 해시 테이블에 행을 쓸때 두개의 다른 해시함수를 사용해야하는 이유를 짐작했을 것이다. 두작업 모두에 동일한 해시 함수를 사용하면 동일한 청크 파일의 모든 행이 동일한 값으로 해시되므로 빌드 청크 파일을 해시 테이블에 로드 할때 매우 나쁜 해시 테이블을 얻게 된다. 

Hash Join 사용방법

해시 조인은 기본적으로 활성화되어 있으므로 해시 조인을 사용하기 위해 필요한 작업이 없다. 한가지 주목할만한 점은 해시 조인이 새로운 반복자 실행기를 기반으로 구축된다는 것이다. 즉 EXPLAIN FORMAT=tree를 사용하여 해시 조인이 사용되는지 여부를 확인할 수 있다. 

mysql> EXPLAIN FORMAT=tree
    -> SELECT
    ->   given_name, country_name
    -> FROM
    ->   persons JOIN countries ON persons.country_id = countries.country_id;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (countries.country_id = persons.country_id)  (cost=0.70 rows=1)
|   -> Table scan on countries  (cost=0.35 rows=1)
|   -> Hash
|      -> Table scan on persons  (cost=0.35 rows=1)
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

일반적으로 하나 이상의 동등 조인 조건을 사용하여 테이블을 조인하고 조인 조건에 사용할 수 있는 인덱스가 없는 경우 해시 조인이 사용된다. 인덱스를 사용할 수 있는 경우 MySQL은 인덱스 조회가 있는 중첩 루프(Nested Loop)를 선호하는 경향이 있다. 

모든 쿼리에 대해 해시조인을 비활성화 할수 있는 새로운 optimizer switch를 도입했다. 

mysql> SET optimizer_switch="hash_join=off";
Query OK, 0 rows affected (0.00 sec)
 
mysql> EXPLAIN FORMAT=tree
    -> SELECT
    ->   given_name, country_name
    -> FROM
    ->   persons JOIN countries ON persons.country_id = countries.country_id;
+----------------------------------------+
| EXPLAIN                                |
+----------------------------------------+
|                                        |
+----------------------------------------+
1 row in set (0.00 sec)

해시 조인이 꺼지면 MySQL은 블록 중첩 루프로 돌아가고 따라서 이전 실행 프로그램이 된다. (블록 중첨 루프는 반복기 실행 프로그램에서 지원되지 않는다.) 이 스위치를 사용하면 해서 조인과 블록 중첩 루프의 성능을 쉽게 비교할 수 있다. 

빌드 입력이 너무 커서 메모리에 맞지 않아 해시조인이 디스크를 사용하는 경우 조인 버퍼 크기를 늘릴 수 있다. 블록 중첩 로프와 달리 해시 조인은 메모리를 점진적으로 할당하므로 필요한것보다 더 많은 메모리를 사용하지 않는다. 이러한 이유로 해시 조인을 사용할 때 더 큰 조인 버퍼 크기를 사용하는것이 더 안전하다. 

해시 조인 제한사항

MySQL은 내부 해시 조인만 지원한다. 

anit, semi, outer join 은 블록 중첩 루프를 사용하여 실행된다. 

optimizer/planner 는 블록 중첩 루프를 사용하여 조인을 실행하는것에 우선을 둔다. 

참고

https://mysqlserverteam.com/hash-join-in-mysql-8/

- Redo Log는 불완전한 트랜잭션에 의해 작성된 데이터를 수정하기 위해 응급 복구 중에 사용되는 디스크 기반 데이터 구조이다. 정상 작업중에는 Redo Log는 SQL문 또는 하위 수준 API 호출로 인한 테이블 데이터 변경 요청을 인코딩한다. 예기치 않은 종료 전에 데이터 파일 업데이트를 완료하지 않은 수정 사항은 초기화 중, 연결이 승인되기전에 자동으로 재생된다.

- 기본적으로 Redo Log는 ib_logfile0, ib_logfile1 이라는 두개의 파일로 디스크에 물리적으로 저장된다. MySQL은 순환방식으로 Redo Log 파일에 기록한다. Redo Log의 데이터는 영향을 받는 레코드 측면에서 인코딩된다. 이 데이터 집합을 redo 라고 한다. Redo Log를 통한 데이터 전달은 계쏙 증가하는 LSN 값으로 표시된다. 

Redo Log 파일수 또는 크기 변경

- MySQL 서버를 중지하고 오류 없이 종료되었는지 확인한다. 

- my.cnf 를 편집하여 로그 파일 구성을 변경한다. 

  로그 파일 크기를 변경하려면 innodb_log_file_size 를 수정한다. 

  로그 파일 수를 늘릴려면 innodb_log_file_in_group을 수정한다.

- MySQL 서버를 다시 시작한다. 

- InnoDB는 innodb_log_file_size가 Redo Log 파일 크기와 다른것을 감지하면 로그 체크 포인트를 작성하고 이전 로그파일을 닫고 제거하며 요청된 크기로 새 로그 파일을 생성하고 새 로그 파일을 연다. 

Redo Log 플러싱을 위한 그룹 커밋

- 다른 ACID 호환 데이터베이스 엔진과 마찬가지로 InnoDB는 트랜잭션이 커밋되기 전에 다시 실행 로그를 플러시한다.  InnoDB는 그룹 커밋 기능을 사요하여 여러 플러시 요청을 함께 그룹화하여 각 커밋에 대해 하나의 플러시를 방지한다. 그룹 커밋을 사용하면 InnoDB는 로그 파일에 단일 쓰기를 실해앟여 거의 동시에 커밋하는 여러 사용자 트랜잭션에 대해 커밋 작업을 수행하여 처리량을 크게 향상시킨다. 

Redo Log Archiving

- Redo Log 레코드를 복사하는 백업 유틸리티는 때때로 백업 작업이 진행되는 동안 Redo Log 생성 속도를 유지하지 못하여 해당 레코드를 덮어 쓰므로 Redo Log 레코드가 손실 될 수 있다. 이 문제는 백업 작업중 상당한 MySQL 서버 활동이 있고 Redo Log 파일 저장소 미디어가 백업 저장소 미디어보다 빠른 속도로 작동할때 가장 자주 발생한다. MySQL 8.0.17에 도입된 Redo Log Archiving 기능은 Redo Log 파일 외에도 Redo Log 레코드를 아카이브 파일에 순차적으로 기록하여 이 문제를 해결한다. 백업 유틸리티는 필요에 따라 아카이브 파일에서 Redo Log 레코드를 복사하여 잠재적인 데이터 손실을 방지할 수 있다. 

- Redo Log Archiving이 서버에 구성되어 있는 경우 MySQL Enterprise Edition을 사용할 수 있는 MySQL Enterprise Backup 은 MySQL 서버를 백업할때 Redo Log Archiving 기능을 사용한다. 

- Redo Log Archiving을 활성화 하려면 innodb_redo_log_archive_dirs 시스템 변수의 값을 설정한다. 

  값은 레이블이 저장된 Redo Log 아카이브 디렉토리의 세미콜론으로 구분된 목록으로 지정된다. lable:directory 쌍은 콜론(:)으로 구분된다. 

  예)

mysql> SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';

- lable은 아카이브 디렉토리에 대한 임의의 식별자이다. 허용되지 않는 콜론(:)을 제외한 모든 문자열을 사용할 수 있다. 빈 lable도 허용되지만 이 경우에도 콜론(:)이 필요하다. 

  directory_path를 지정해야한다. Redo Log 아카이브 파일에 대해 선택한 디렉토리는 Redo Log 아키이브가 활성화되거나 오류가 반활 될 때 존재해야한다. 경로에는 콜론(:)이 포함될 수 있지만 세미콜론(;)은 허용되지 않는다. 

- Redo Log 아카이브를 활성화하기 전에 innodb_redo_log_archive_dirs 변수를 설정해야한다. 기본값은 NULL 이며 Redo Log 아카이브 활성화를 허용하지 않는다. 

- Redo Log 아키이브를 지원하는 백업 유틸리티가 백업 유틸리티가 백업을 시작하면 백업 유틸리티는 innodb_redo_log_archive_start() 함수를 호출하여 Redo Log 아카이브를 활성화한다. 

- Redo Log 아카이브를 지원하지 백업 유틸리티를 사용하지 않는 경우 다음과 같이 Redo Log 아카이브를 수동으로 활성화 할 수도 있다. 

mysql> SELECT innodb_redo_log_archive_start('label', 'subdir');
+------------------------------------------+
| innodb_redo_log_archive_start('label') |
+------------------------------------------+
| 0                                        |
+------------------------------------------+

또는 
mysql> DO innodb_redo_log_archive_start('label', 'subdir');
Query OK, 0 rows affected (0.09 sec)

- Redo Log 아카이브를 활성화하는 MySQL 세션은 (innodb_redo_log_archive_start() 사용)은 archiving 기간동안 열려 있어야한다. 반드시 동일 세션에서 Redo Log 아카이브를 비활성화(innodb_redo_log_archive_stop() 사용) 해야한다.  Redo Log 아카이브가 명시적으로 비활성화되기 전에 세션이 종료되면 서버는 Redo Log 아키이브를 암시적으로 비활성화하고 Redo Log 아카이브 파일을 제거한다. 

- innodb_redo_log_archive_start 에서 lable은 innodb_redo_log_archive_dirs에 정의된 레이블이다. subdir은 아카이브 파일을 저장하기 위해 lable로 식별되는 디렉토리 하위 디렉토리를 지정하기 위한 선택적인 입력값이다. 단순한 디렉토리 이름이어야한다. (슬레시(/), 백슬레시(\) 또는 콜론(:)은 허용되지 않는다.(). subdir은 비어 있거나 NULL이거나 생략 할 수 있다. 

- INNODB_REDO_LOG_ARCHIVE 권한이있는 사용자만 innodb_redo_log_archive_start()를 호출하여 Redo Log 아카이브를 활성화하거나 innodb_redo_log_archive_stop()을 사용하여 비활성화할 수 있다. 백업 유틸리티를 실행하는 MySQL 사용자 또는 수동으로 Redo Log 아카이브를 활성화 및 비활성화하는 MySQL 사용자는 이 권한이 있어야한다. 

- Redo Log 아카이브 파일 경로는 directory_identified_by_label/[subdir/]archive.serverUUID.000001.log 입니다. 여기서 directory_identified_by_label은 innodb_redo_log_archvie_start()의 레이블 인수로 식별되는 아카이브 디렉토리이다. subdir은 innodb_redo_log_archive_start()에 사용되는 선택적인 입력값이다. 

  예를 들어 Redo Log 아카이브 파일의 전체 경로 및 이름은 다음과 유사하게 나타난다. 

/directory_path/subdirectory/archive.e71a47dc-61f8-11e9-a3cb-080027154b4d.000001.log

- 백업 유틸리티가 InnoDB 데이터 파일 복사를 마치면 innodb_redo_log_archive_stop() 함수를 호출하여 Redo Log 보관(archiving)을 비활성화 한다. 

- Redo Log 아카이브를 지원하는 백업 유틸리티를 사용하지 않는 경우 다음과 같이 Redo Log 아카이브를 수동으로 비활성화 할 수 있다. 

mysql> SELECT innodb_redo_log_archive_stop();
+--------------------------------+
| innodb_redo_log_archive_stop() |
+--------------------------------+
| 0                              |
+--------------------------------+

또는
mysql> DO innodb_redo_log_archive_stop();
Query OK, 0 rows affected (0.01 sec)

- stop function 이 성공적으로 완료되면 백업 유틸리티는 아카이브 파일에서 Redo Log 데이터의 관련 섹션을 찾아 백업에 복사한다. 

- 백업 유틸리티가 Redo Log 데이터 복사를 완료하고 더 이상 Redo Log 아카이브 파일이 필요하지 않으면 아카이브 파일을 삭제한다. 

- 아카이브 파일의 제거는 정상적인 상황에서 백업 유틸리티가 제거한다. 그러나 Innodb_redo_log_archive_stop()이 호출되기 전에 Redo Log 아카이브 작업이 예기치 않게 종료되면 MySQL 서버가 아카이브 파일을 제거한다. 

성능 고려 사항 

- Redo Log 아카이브를 활성화하면 일반적으로 추가 쓰기 작업으로 인해 약간의 성능 비용이 발생한다.

- Unix 및 Uinx 유사 운영 체제에서 지속적으로 높은 업데이트 비율이 없다고 가정하면 성능에 미치는 영향은 일반적으로 경미하다. Windows 에서 성능 영향은 일반적으로 조금 더 높으며, 동일하게 가정한다. 

- 업데이트 비율이 지속적으로 높고 Redo Log 아카이브 파일이 Redo Log 파일보다 느린 저장매체에 있는 경우 복합적인 쓰기 활동으로 인해 성능 영향이 더 클 수 있다.

- 업데이트 비율이 지속적으로 높고 Redo Log 아카이브 파일이 Redo Log 파일보다 느린  저장매체에 있는 경우 성능이 임의로 영향을 받는다.

- Redo Log 아카이브 파일에 쓰기는 Redo Log 아카이브 파일 저장매체가 Redo Log 파일 저장매체보다 휠씬 느린속도로 작동하는 경우를 제외하고는 정상적인 트랜잭션 로깅을 방해하지 않는다. 

- 이 경우 트랜잭션 로깅 속도는 Redo Log 아카이브 파일이 있는 느린 저장매체에서 관리 할 수 있는 수준으로 감소한다. 

Redo Logging 비활성화 

- MySQL 8.0.21 부터 ALTER INSTANCE DISABLE INNODB REDO_LOG 문을 사용하여 Redo Logging 을 비활성화 할 수 있다.  이기능은 데이터를 새로운 MySQL 인스턴스로 로드하기 위한것이다. Redo Logging을 비활성화하면 Redo Log 쓰기 및 doublewrite 버퍼링을 방지하여 데이터 로드 속도를 높일 수 있다. 

- Redo Logging 활성화 비활성화를 하려면 INNODB_REDO_LOG_ENABLE 권한이 필요하다.

- innodb_redo_log_enabled 상태 변수로 Redo Logging 상태 모니터링 할 수 있다.

- Redo Logging 이 비활성화된 동안 복제 작업 및 Redo Log 아카이브가 허용되지 않으며 그 반대의 경우도 마찬가지다.

- ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG  작업에는 다른 ALTER INSTANCE 작업이 동시에 실행되는것을 방지하는 베타적 백업 백업 메타 잠금(exclusive backup metadata lock)이 필요하다. 다른 ALTER INSTANCE 작업은 잠금이 해제 될때까지 기다려야 한다. 

예제) 새로운 MySQL 인스턴스로 데이터를 로드할때 Redo Logging을 비활성화하는 방법

1. INNODB_REDO_LOG_ENABLE 권한 부여 

mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';

2. data_log_admin 계정으로 Redo Logging을 비활성화 

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

3. innodb_redo_log_enabled 상태 변수를 확인하여 Redo Logging 이 비활성화 되었는지 확인 

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

4. 데이터 로드 작업 실행

5. 데이터 로드 작업 완료후 data_load_admin 계정으로 Redo Logging 활성화 

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

6. innodb_redo_log_enabled 상태 변수를 확인하여 Redo Logging 이 활성화 되었는지 확인

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

참고

https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html