[NBP] Cloud for mysdql Binlog 실시간 저장
🛡️ Naver Cloud MySQL 장애 대비: 실시간 Binlog 저장 및 복구 시나리오 (Feat. 권한 문제) 🛡️
Naver Cloud의 Cloud for MySQL 버전을 사용하면서 발생할 수 있는 데이터 손실 위험에 대비하기 위해, Binary Log를 실시간으로 저장하고 이를 활용하여 복구하는 시나리오를 공유합니다. 백업 파일을 주기적으로 받더라도, 동기화가 해제된 직후 Failover가 발생하면 데이터 유실 가능성이 존재합니다. 이를 해결하기 위한 고민과 실제 테스트 과정을 담았습니다.
😥 데이터 손실 위험과 한계
Naver Cloud for MySQL 환경에서 백업은 중요한 안전장치이지만, 실시간으로 발생하는 변경 사항까지 모두 커버하기는 어렵습니다. 특히, Master-Slave 동기화가 끊어진 상태에서 예기치 않은 Failover가 발생한다면, 마지막 백업 시점 이후의 데이터는 고스란히 손실될 수 있습니다.
콘솔에서 제공하는 Replication DB 생성은 또 다른 대안이지만, 이는 Slave DB로만 생성이 가능하며, 근본적으로 Binary Log 없이는 완벽한 장애 대처가 불가능하다는 결론에 도달했습니다. 따라서 Binary Log를 별도로 백업하는 방안을 모색하게 되었습니다.
🛠️ 실시간 Binary Log 저장 시나리오
1. MySQL 8.0.25 버전 설치 가정
본 시나리오는 MySQL 8.0.25 버전을 사용하는 환경을 기준으로 합니다.
2. Binary Log 스트리밍 스크립트 작성 및 설정
MySQL DB에 접근 가능한 서버 (본 테스트에서는 Bastion 서버)에 아래 스크립트 파일을 생성하고, 실제 DB 정보에 맞게 수정합니다. 동일한 버전의 MySQL 클라이언트가 해당 서버에 설치되어 있어야 합니다.
[root@ksh-pub-bastion binlog]# cat mysql_binlog_stream.sh
#!/bin/bash
# MySQL 설정
MYSQL_HOST="db-3000an-kr1.vpc-cdb.gov-ntruss.com"
MYSQL_USER="ksh"
MYSQL_PASSWORD="password1!"
MYSQL_PORT="3306"
# 저장할 디렉토리
LOG_DIR="/var/log/binlog"
mkdir -p $LOG_DIR
# MySQL 바이너리 로그 파일 이름
BINLOG_FILE="mysql-bin.000001"
# 로그 파일 크기 제한 (100MB)
MAX_SIZE=104857600 # 100MB in bytes
# 로그 스트리밍 시작
CURRENT_FILE="$LOG_DIR/$(date +'%Y%m%d').log"
# 로그 파일을 1주일 이상 된 파일들을 삭제하는 함수
cleanup_old_logs() {
# 7일(1주일) 이상된 로그 파일 삭제
find $LOG_DIR -type f -name "*.log" -mtime +7 -exec rm -f {} \;
echo "Old log files older than 7 days have been deleted."
}
# 바이너리 로그 스트리밍 및 크기/날짜/삭제 관리
mysqlbinlog --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASSWORD --port=$MYSQL_PORT --raw --read-from-remote-server --stop-never --start-position=4 $BINLOG_FILE | \
while IFS= read -r line; do
# 날짜가 바뀌면 새로운 파일로 저장
NEW_FILE="$LOG_DIR/$(date +'%Y%m%d').log"
if [ "$CURRENT_FILE" != "$NEW_FILE" ]; then
CURRENT_FILE="$NEW_FILE"
echo "New log file created: $CURRENT_FILE"
fi
# 로그 추가
echo "$line" >> "$CURRENT_FILE"
# 파일 크기 체크 (100MB 넘으면 새 파일로 저장)
FILE_SIZE=$(stat -c %s "$CURRENT_FILE")
if [ $FILE_SIZE -ge $MAX_SIZE ]; then
CURRENT_FILE="$LOG_DIR/$(date +'%Y%m%d_%H%M%S').log"
echo "New log file created due to size limit: $CURRENT_FILE"
fi
# 60분마다 1주일 이상된 로그 파일 삭제
if (( $(date +%M) % 60 == 0 )); then
cleanup_old_logs
fi
done
3. 클라이언트 Binary 로그 확인
DB 서버에서 Binary Log가 활성화되어 있는지 확인합니다.
[root@ksh-pub-bastion binlog]# mysql -h db-3000an-kr1.vpc-cdb.gov-ntruss.com -uksh -p -e "SHOW BINARY LOGS"
Enter password:
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 6225 | No |
| mysql-bin.000003 | 1326 | No |
+------------------+-----------+-----------+
4. 스크립트 실행 및 Binary Log 파일 확인
작성한 스크립트를 백그라운드로 실행하고, 로컬에 Binary Log 파일이 생성되는지 확인합니다.
[root@ksh-pub-bastion binlog]# ./mysql_binlog_stream.sh &
[root@ksh-pub-bastion binlog]# ls -alrt
합계 28
drwxr-xr-x. 11 root root 4096 12월 26 15:01 ..
-rwxr--r-- 1 root root 1712 12월 26 15:14 mysql_binlog_stream.sh
-rw-r----- 1 root root 6225 12월 26 15:16 mysql-bin.000002
-rw-r----- 1 root root 179 12월 26 15:16 mysql-bin.000001
-rw-r----- 1 root root 1326 12월 26 15:21 mysql-bin.000003
drwxr-xr-x 2 root root 124 12월 26 15:24 .
5. Binary Log를 이용한 데이터 복구 시도
5-1. Binary Log 파일 → SQL 파일 추출
Binary Log 파일은 직접 읽을 수 없으므로, mysqlbinlog 명령어를 사용하여 SQL 파일로 변환해야 합니다.
[root@ksh-pub-bastion binlog]# mysqlbinlog mysql-bin.000003 > test.sql
추출된 SQL 파일을 이용하여 데이터베이스 복구를 시도했지만, 권한 부족 오류가 발생했습니다.
[root@ksh-pub-bastion binlog]# mysql -h db-3000an-kr1.vpc-cdb.gov-ntruss.com -uksh -p
Enter password:
mysql> show tables;
+---------------+
| Tables_in_ksh |
+---------------+
| users |
+---------------+
1 row in set (0.00 sec)
mysql> drop table users;
Query OK, 0 rows affected (0.01 sec)
mysql> source /var/log/binlog/test.sql
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
... (중략) ...
-> BINLOG_ADMIN or REPLICATION_APPLIER 권한 필요
5-2. 특정 시점 기반 Binary Log → SQL 추출
mysqlbinlog 명령어는 --start-datetime, --stop-datetime, --start-position, --stop-position, --database 등의 옵션을 통해 특정 시점 또는 특정 데이터베이스의 로그만 추출할 수 있습니다.
[root@ksh-pub-bastion binlog]# mysqlbinlog --start-datetime="2024-12-26 15:40:00" --stop-datetime="2024-12-26 15:46:00" /var/log/binlog/mysql-bin.* > /var/log/binlog/time.sql
특정 시점 이후 데이터를 삽입하고, 테이블을 삭제한 후 추출된 time.sql 파일을 이용하여 복구를 시도했지만, 동일하게 권한 부족 오류가 발생했습니다.
[root@ksh-pub-bastion binlog]# mysql