[NBP] Cloud for mysdql Binlog 실시간 저장

카테고리 없음

[NBP] Cloud for mysdql Binlog 실시간 저장

세브웁스 2025. 2. 9. 01:00

Naver Cloud 상품 중 Cloud for mysql 버전에서 binlog 를 실시간으로 저장하고, 해당 bin 로그로 복구하는 시나리오

백업파일을 매일 받는 상황이더라도, 동기화가 해제된 직후 Failover가 발생하여 데이터가 날아갈 확률 존재

이를 해결하기 위해서 콘솔창에서 할 수 있는것은

1. 또 다른 Replication DB를 생성하거나

2. Failover가 발생하기 전 빠르게 복구하는 방법 뿐

→ 또 다른 Replication DB를 생성할 때, 비용 문제는 둘째더라도, 해당 DB는 Slave DB로만 생성 가능

→ 결국 Binary log가 없다면 100% 장애대처가 불가능하므로, Binary log를 백업하는 방안 모색

1. MYSQL 8.0.25 버전 설치 가정

2. 해당 DB로 접속 가능한 서버에서 아래 스크립트 파일 추가 및 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 로그 확인

[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. 스크립트 실행 후 바이너리 파일 확인 (동일하게 파일 구성된 점 확인 가능)

[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-1. 바이너리 파일 SQL 파일로 추출

# bin log sql 파일로 변경하는 명령어
# bin log 파일은 읽을 수 없으므로 파일 변환이 필요함

[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
Query OK, 0 rows affected (0.00 sec)
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
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
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
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
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, BINLOG_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
Query OK, 0 rows affected (0.00 sec)

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
Query OK, 0 rows affected (0.00 sec)

-> BINLOG_ADMIN or REPLICATION_APPLIER 권한 필요

5-2. 특정 시점 기반으로 바이너리 파일 → SQL 추출 명령어

mysqlbinlog 명령어는 다음과 같은 옵션을 제공하여 로그를 필터링할 수 있습니다:

-start-datetime 및 -stop-datetime: 특정 날짜 범위만 선택.
-start-position 및 -stop-position: 특정 로그 파일 내의 위치만 선택.
-database: 특정 데이터베이스에 대한 이벤트만 선택.

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

특정 시점 기반으로 데이터 복구# 2

# 데이터 삽입
# 12월 26일 15:44분 실행
mysql> INSERT INTO ksh.users (name, email) VALUES ('Alice2', 'alice@example.com'), ('Bob2', 'bob@example.com'), ('Charlie2', 'charlie@example.com');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM ksh.users;
+----+----------+---------------------+
| id | name     | email               |
+----+----------+---------------------+
|  1 | Alice2   | alice@example.com   |
|  2 | Bob2     | bob@example.com     |
|  3 | Charlie2 | charlie@example.com |
+----+----------+---------------------+

# 데이터 강제 손실
# 12월 26일 15:45 실행
mysql> drop table users;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM ksh.users;
ERROR 1146 (42S02): Table 'ksh.users' doesn't exist

# binlog 업데이트 된 것 확인
[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--r--   1 root root 4062 12월 26 15:20 test.sql
drwxr-xr-x   2 root root  124 12월 26 15:24 .
-rw-r-----   1 root root 1907 12월 26 15:47 mysql-bin.000003


# 시점기반으로 binlog -> sql 추출
[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

[root@ksh-pub-bastion binlog]# ls -arlt
합계 32
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--r--   1 root root 4062 12월 26 15:20 test.sql
-rw-r-----   1 root root 1907 12월 26 15:47 mysql-bin.000003
drwxr-xr-x   2 root root  140 12월 26 15:48 .
-rw-r--r--   1 root root 1633 12월 26 15:48 time.sql


[root@ksh-pub-bastion binlog]# mysql -h db-3000an-kr1.vpc-cdb.gov-ntruss.com -uksh -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1673
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /var/log/binlog/time.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
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, BINLOG_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
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
Query OK, 0 rows affected (0.00 sec)

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, BINLOG_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
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
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, BINLOG_ADMIN or REPLICATION_APPLIER privilege(s) for this operation
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
Query OK, 0 rows affected (0.00 sec)

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 권한 필요

결론

Binary log를 이용하여 복구하기 위해서는 BINLOG_ADMIN 권한이 필요하다.

→ 바이너리 로그 파일 가지고 있더라도 권한문제로 네이버 문의하기 통해서 복구해야함

저작자표시 비영리