TL DR;

1. MySQL에서 덤프외에 변경기록분을 기록할 수 있음
2. 특정 시점의 백업의 정보를 확인하고, 변경분을 적용하여 시점복원 가능
3. XtraBackup을 사용하여 기준 덤프 생성 및 복원을 빠르게 수행가능

1. Intro

Point-in-Time Recovery (PITR)

시점복구(PITR)은 데이터베이스 백업 및 복원 과정에서, 특정시점을 기준으로 변경사항을 복구하는 것을 의미한다.

일반적인 데이터베이스 백업은 일정 주기 (매일 자정등)를 기반으로 수행된다. 이를통해 장애등의 상황시 최대 24시간 내의 형상으로 복원할 수 있다. 그러나 이는 미리 사전에 수행되어야한다는 문제가 있다.

백업과 복원에 관해서 정책과 다른 다른 시점의 데이터베이스 형상이 필요하다면 어떻게 하여야할까? 가령 예를 들어 개발팀의 휴먼에러로 인해 오늘의 변경분이 담긴 데이터가 손상되어 에러 이전으로 복원해야한다던가 / 데이터팀의 요청에 따라 작년 동일기간의 데이터 형상이 필요하다면 어떻게 해야하는가? 이를 위해 매번 모든 시점의 백업파일을 생성하는것은 불필요한 중복이 될것이다.

매 시점의 데이터 대신, 데이터 변경의 기록을 남겨둠으로써 이를 해결할 수 있다. 특정 복원시점(자정)에서부터 필요시점까지의 변경기록분들 적용함으로써 원하는 시점의 데이터를 복원해 낼 수 있다. 이러한 기능을 Point In Time (시점복구,PITR)이라고 지칭한다.

MySQL에서 PITR을 사용하기 위해서는 변경사항을 기록할 Binary Log가 활성화 되어있어야하며, 백업이 수행 되었던 시점의 binlog 파일과 포지션을 기준으로, 복원시점 (binlog pos or timedate)를 지정하여 복원을 수행할 수 있다

1. mysql 덤프파일 생성 및 binlog 위치 확인
   1. mysqldump –single-transaction > mysql_dump.sql
2. mysqlbinlog 도구를 사용하여 덤프 생성 이후 binlog 추출
   1. mysqlbinlog /var/lib/mysql/mysql-bin.0000* –start-position=”154″ –stop-datetime=”2025-03-16 16:40″ -v > binlog.sql
3. 복원된 mysql 서버에 누락된 binlog 적용
   1. mysql -u”계정” -p < binlog.sql

2. 환경설정

테스트를 위해 AWS EC2 상에서 약 10GB 분량의 dummy database 를 생성한 뒤, 약 10초 간격으로 insert하는 스크립트를 돌릴것이다

10초 간격으로 계속 insert가 들어오는 상황에서, 기준 시점으로 데이터베이스 덤프 및 복원을 수행하고, 원하는 지점까지의 추가 변경분을 binlog 기반으로 추출하여 적용할 것이다

이때 기본 mysqldump 도구는 논리적 백업 및 복원을 수행하기에 그 속도가 느리다, 물리 파일 기반의 xtrabackup을 사용하여 속도 차이를 확인할 것이다

• AWS EC2(t3a.medium, 30GB disk space)
• MySQL 8.0
• XtraBackup

sudo wget https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo dnf install mysql80-community-release-el9-1.noarch.rpm -y
sudo dnf install mysql-community-client mysql-community-server -y --nogpgcheck

# 비밀번호 없는 root 생성
mysqld --initialize-insecure --user=mysql

# /etc/my.cnf 설정
cat <<EOF | sudo tee -a /etc/my.cnf

binlog_format=ROW
log-bin=mysql-bin
server_id=11

secure_file_priv=NULL

EOF

# mysql 시작 및 root pwd 설정
systemctl enable mysqld.service --now

# https://docs.percona.com/percona-xtrabackup/8.0/yum-download-rpm.html
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.32-26/binary/redhat/9/x86_64/percona-xtrabackup-80-8.0.32-26.1.el9.x86_64.rpmsudo yum localinstall percona-xtrabackup-80-8.0.32-26.1.el9.x86_64.rpm

sudo yum localinstall percona-xtrabackup-80-8.0.32-26.1.el9.x86_64.rpm -y

# 작동확인
xtrabackup --version

# 약 3GB dummy 데이터 생성
time seq 1 50000000 | awk -v OFS=',' '{print $1, "User" $1, "user" $1 "@example.com", "2025-03-14 12:00:00"}' > /var/lib/mysql-files/dummy_data.csv

# real	0m46.373s
# user	0m41.349s
# sys	0m6.695s

cat <<EOF > ./init.sql
CREATE DATABASE IF NOT EXISTS dummy;
USE dummy;

DROP TABLE IF EXISTS dummy_data;
CREATE TABLE dummy_data (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP
);

DROP TABLE IF EXISTS insert_10s;
CREATE TABLE insert_10s (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

LOAD DATA INFILE '/var/lib/mysql-files/dummy_data.csv'
INTO TABLE dummy_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email, created_at);
EOF

mysql -uroot < ./init.sql

cat <<EOF > 10s.sh
# nohup ./10s.sh > 10s.log 2>&1 &
#!/bin/sh
DB_HOST="localhost"
DB_USER="root"
DB_PORT="3306"
DB_PASS=""
DB_NAME="dummy"

while true; do
    mysql -h \$DB_HOST -u \$DB_USER \$DB_NAME -e "INSERT INTO \$DB_NAME.insert_10s (created_at) VALUES (NOW());"
    echo "Inserted row at \$(date)"
    sleep 10
done
EOF

chmod +x 10s.sh
nohup ./10s.sh > 10s.log 2>&1 &

3. mysqldump기반 시점 복원

별도의 도구없이 기본적인 mysql 도구로도 binlog가 활성화 되어있다면 PITR 복구를 수행할 수 있다

1. 선택) flush logs > rotate the binlog
   1. 현재까지의 변경을 binlog에 기록하고, 새로운 binlog 파일로 변경
   2. dump 생성 이전까지의 모든 기록을 포함
2. dump 생성
   1. mysqldump -uroot –set-gtid-purged=OFF –single-transaction –routines –triggers –source-data=2 –databases dummy > mysql_dump.sql
3. dump 파일에서의 Master dump 시점의 binlog file/pos 확인
   1. cat mysql_dump.sql | grep MASTER_LOG_FILE
   2. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=15355;
4. binlog를 통해 덤프 생성이후 binlog 추출
   1. mysqlbinlog mysql-bin.000006 –start-position=”15355″ –stop-datetime=”2025-03-16 17:00:00″ -v > binlog.sql
5. 복원
   1. mysql -uroot -p < binlog.sql

4. xtrabackup기반 시점 복원

근본적으로 덤프를 생성하여 복원한뒤, binlog를 통해 변경내역을 조회하여 집어넣는다는 큰 틀은 변화하지 않는다. xtrabackup에서 incremental backup&restore를 지원하나, 생성된 백업 단계의 파일을 적용하는 것이며 특정시점으로의 restore를 지원하지 않는다.

스크립트등을 통해 특정시점에 백업을 수행하도록 (ex. 매일 자정 등) 설정할 수 있지만 백업완료까지 시간이 소요되어 정확한 시점을 파악할 수는 없다

xtrabackup 사용 시 binlog 추출 및 복원 이전 단계의, dump 생성 및 복원단계의 시간 감소를 기대할 수 있다

1. 선택) flush logs > rotate the binlog
   1. 현재까지의 변경을 binlog에 기록하고, 새로운 binlog 파일로 변경
   2. dump 생성 이전까지의 모든 기록을 포함
2. xtrabackup dump 생성
   • xtrabackup –backup –target-dir=/mysql-backup/base
     • –backup > 백업 수행
     • –target-dir > 백업파일을 저장할 경로
3. dump 파일에서의 Master dump 시점의 binlog file/pos 확인
   • cat /mysql-backup/base/xtrabackup_binlog_info
     • mysql-bin.000005 2482
4. binlog를 통해 덤프 생성이후 binlog 추출
   1. mysqlbinlog mysql-bin.000005 –start-position=”2482″ –stop-datetime=”2025-02-20 15:00:00″ -v > /mysql-backup/binlog.sql
5. 복원할 서버로 백업파일 전달
   1. rsync -a /mysql-backup/ root@IP_NODE_SECONDARY:/mysql-backup
6. 복원서버) xtrabackup 복원 준비
   • xtrabackup –prepare –target-dir=/mysql-backup/base
     • –prepare > 백업 적용할 수 있도록 준비
       • dump를 생성하면서 서로다른 시점의 table이 캡쳐됨, 일관된 시점X
       • 덤프생성도중의 변경을 LSN(Log Sequence Nubmer)로 기록
       • 덤프 종료 시점의 LSN으로 적용
       • cat base/xtrabackup_checkpoints (덤프과정중 변경이 없어서? 업데이트필요)
         • # prepare 이전 backup_type = full-backuped from_lsn = 0 to_lsn = 55437859105 last_lsn = 55437859114 compact = 0 recover_binlog_info = 0 flushed_lsn = 55437859114 # prepare 이후 backup_type = full-prepared from_lsn = 0 to_lsn = 55437859105 last_lsn = 55437859114 compact = 0 recover_binlog_info = 0 flushed_lsn = 55437859114
     • –target-dir > 준비할 백업파일의 경로
7. 복원서버) backup파일 가져와서 mysql dump복원
   1. rm -rf /var/lib/mysql/*
   2. rsync -avrP /mysql-backup/base/ /var/lib/mysql/
   3. chown -Rf mysql:mysql /var/lib/mysql
   4. systemctl start mysql
8. 복원서버) 누락된 binlog 적용
   1. mysql -u계정명 -p < binlog.sql

5. Outro

xtrabackup은 우아한 형제들의 장애와 관련된 XtraBackup 적용기를 통해 처음 접하게 되었다.

mysqldump는 논리적 백업&복원방식이기에 그 작동에 있어 CPU/Memory 등 컴퓨팅 리소스의 영향을 많이 받으며 상대적으로 느린 속도를 보인다. 그에 반해 XtraBackup은 물리적 파일기반 백업&복원 방식이기에 Disk Storage I/O에 영향을 받으며 이는 상대적으로 컴퓨팅 리소스보다는 빠른 속도를 갖는다.

실제로 이번글의 약 10GB 테스트에서 mysqldump는 약 15분의 시간이 소요된 반면 XtraBackup은 약 3분의 시간이 소요되었다. 이러한 속도차이는 DB의 용량이 100G, 1T, 10T등 커질수록 더욱 두드러진다

그러나 실제 DB가 구동되는 서버 및 파일경로에 접근이 가능하여야 하므로 AWS RDS와 같은 클라우드 관리형 데이터베이스에서는 사용이 불가능하다. 모든길은 온프렘으로 통한다. 온프렘 짱짱맨