MySQL binlog 기반 데이터 복구

수동으로 데이터를 보정하거나 배치 작업을 하는 과정 중에서 테이블의 데이터를 오염시키는 일이 발생할 수 있다.

 

상황마다 복구하는 시나리오는 다르겠지만, 이번 글에서는 MySQL binlog를 통해 오염된 데이터를 되돌리는 기본적인 방법에 대해 정리해보려고 한다.

 

실제 운영 환경에서는 매우 많은 트랜잭션과 실시간으로 계속 들어오는 트래픽으로 인해 binlog만 가지고 100% 데이터를 복구하는 것은 어렵다.

 

오히려 수동으로 복구하려는 시도가 데이터를 더 꼬이게 할 수도 있다는 것을 주의해야 하고 필요하다면 반드시 복구 정책(ex. 데이터 오염 이후 발생한 변경은 그대로 둔다, 특정 시간 기준 스냅샷으로 복구한다 등)을 먼저 정해두고 복구 이후 영향도에 대한 고려를 한 뒤 해야 한다.

 

 

실습

실습 환경은 MySQL 8.4.7 최신 Innovation Release 버전을 사용했고 binlog_format은 ROW로 설정되어 있다.

참고로 MySQL 8.4 버전에서 binlog 쓰기는 기본적으로 활성화되며 binlog 포맷도 ROW를 default로 채택하고 있다.

https://dev.mysql.com/doc/refman/8.4/en/binary-log-formats.html

 

 

데이터 생성

회원 테이블을 만들고 회원번호와 이름을 저장한다.

mysql> CREATE TABLE member (
    ->     mbr_no BIGINT PRIMARY KEY,
    ->     mbr_nm VARCHAR(300)
    -> );
Query OK, 0 rows affected (0.008 sec)

mysql> INSERT INTO member (mbr_no, mbr_nm) VALUES
    -> (1, 'Alice'),
    -> (2, 'Bob'),
    -> (3, 'Charlie');
Query OK, 3 rows affected (0.002 sec)

mysql> SELECT * FROM member;
+--------+---------+
| mbr_no | mbr_nm  |
+--------+---------+
|      1 | Alice   |
|      2 | Bob     |
|      3 | Charlie |
+--------+---------+
3 rows in set (0.000 sec)

 

 

잘못된 UPDATE

회원의 이름을 실수로 모두 'DELETED'로 업데이트 해버렸다고 하자...

(추후에 binlog 확인을 위해 사고 발생 시점인 쿼리 등을 정리해두는 게 좋다)

mysql> UPDATE member
    -> SET mbr_nm = 'DELETED';
Query OK, 3 rows affected (0.004 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 

 

잘못된 UPDATE 이후 바로 해야하는 것

실제 운영 환경에서는 잘못된 업데이트 이후에 다른 변경 사항이 계속 들어온다. 

 

데이터 복구는 시간이 꽤 걸리는 작업이기 때문에 업데이트로 영향받은 데이터를 빠르게 정리해둬야 한다.

그리고 업데이트로 인한 서비스 영향을 조사하고 필요하다면 사용자 공지 및 핫픽스 배포가 반드시 필요하다. 사실 이게 핵심이다.

 

 

Binlog 확인

먼저 binlog 활성화 여부, binlog가 어디에 위치하고 있는지 확인한다.

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.005 sec)


mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.002 sec)


mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| log_bin_basename | /opt/homebrew/var/mysql/binlog |
+------------------+--------------------------------+
1 row in set (0.002 sec)

개발 환경마다 binlog 이름이나 위치 등은 다를 수 있다.

나같은 경우에 로컬 PC에서 실습하고 있어서 /opt/homebrew/... 경로에 binlog가 존재한다. 

 

binlog 경로에 가보면 아래와 같이 postfix로 00001 ... 숫자가 붙을텐데 숫자가 클수록 최신본이다.

$ ls -lh /opt/homebrew/var/mysql/binlog.*                                                        %

-rw-r-----@ 1 shjo  admin   1.6K 12 23 13:14 /opt/homebrew/var/mysql/binlog.000001
-rw-r-----@ 1 shjo  admin   1.1K 12 23 13:15 /opt/homebrew/var/mysql/binlog.000002
-rw-r-----@ 1 shjo  admin    32B 12 23 13:14 /opt/homebrew/var/mysql/binlog.index

 

 

binlog 파일 내용 확인 

mysqlbinlog 명령어를 통해 binlog 파일 내용을 확인할 수 있다. 

$ mysqlbinlog \                                                                            28s | %
  --base64-output=DECODE-ROWS \
  -vv \
  /opt/homebrew/var/mysql/binlog.000002

대충 사고가 발생한 시각을 안다면 시간 범위로 binlog 를 추출하는 것도 좋다.

 

binlog 파일을 확인해보면 아래와 같이 'DELETED'로 덮어쓴 UPDATE 커밋 내역이 있다.

binlog 파일 확인

로그를 보면 포지션(# at pos) 값이 존재하는데 BEGIN 명령어의 시작 포지션은 870이고 end_log_pos가 1100이다.

 

 

binlog 파일 덤프(선택)

스크립트를 이용하여 복구 SQL을 작성해야 하는 경우 필요한 부분을 덤프할 수 있다.

$ mysqlbinlog --base64-output=DECODE-ROWS -vv \                                                           %
  --start-position=870 --stop-position=1100 \
  /opt/homebrew/var/mysql/binlog.000002 > binlog_dump.txt

pos 값을 기반으로 잘못된 커밋 부분만을 잘라서 dump 했다.

 

단, 지금처럼 적은 데이터의 트랜잭션 경계는 명확하지만 트랜잭션 경계가 정확하지 않은 경우에 단순 pos 값으로 덤프뜨는 것은 위험할 수도 있다.

 

$ cat binlog_dump.txt      
%
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 198
#251223 13:14:13 server id 1  end_log_pos 127 CRC32 0x98596706 	Start: binlog v 4, server v 9.5.0 created 251223 13:14:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 870
#251223 13:15:35 server id 1  end_log_pos 931 CRC32 0xb9288881 	Table_map: `study`.`member` mapped to number 92
# at 931
#251223 13:15:35 server id 1  end_log_pos 1069 CRC32 0x5c6f233e 	Update_rows: table id 92 flags: STMT_END_F
### UPDATE `study`.`member`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='DELETED' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
### UPDATE `study`.`member`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='Bob' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='DELETED' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
### UPDATE `study`.`member`
### WHERE
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='Charlie' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
### SET
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='DELETED' /* VARSTRING(1200) meta=1200 nullable=1 is_null=0 */
# at 1069
#251223 13:15:35 server id 1  end_log_pos 1100 CRC32 0xfd749d92 	Xid = 17
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

이렇게 하면 문제가 된 부분만을 떼어서 스크립트 등으로 수정할 수 있다. 많은 데이터가 수정된 경우 복구 SQL을 일일이 짜기 어렵다.

 

예를 들면 아래와 같은 식이다.

BEGIN{
  print "START TRANSACTION;";
  mode="";
  id=""; old=""; new="";
}
# row 시작마다 초기화
/^### UPDATE `study`\.`member`/{
  mode="";
  id=""; old=""; new="";
}
# WHERE/SET 모드 전환
/^### WHERE/{ mode="W"; next }
/^### SET/{ mode="S"; next }

# WHERE에서 @1(id), @2(old name) 잡기
mode=="W" && $1=="###" && $2 ~ /^@1=/{
  sub(/^@1=/,"",$2); id=$2; next
}
mode=="W" && $1=="###" && $2 ~ /^@2=/{
  sub(/^@2=/,"",$2); old=$2; next
}

# SET에서 @2(new name) 잡기
mode=="S" && $1=="###" && $2 ~ /^@2=/{
  sub(/^@2=/,"",$2); new=$2;
  # new를 얻는 시점에 undo SQL 한 줄 출력
  if (id!="" && old!="" && new!="") {
    printf "UPDATE study.member SET name=%s WHERE id=%s AND name=%s;\n", old, id, new;
  }
  next
}

END{
  print "COMMIT;";
}
' binlog_dump.txt > undo.sql

 

데이터 복구

위에서 만든 undo.sql 파일은 아래와 같다. 해당 명령을 사용하여 데이터를 복구할 수 있다.

START TRANSACTION;

UPDATE study.member SET name='Alice'   WHERE id=1 AND name='DELETED';
UPDATE study.member SET name='Bob'     WHERE id=2 AND name='DELETED';
UPDATE study.member SET name='Charlie' WHERE id=3 AND name='DELETED';

COMMIT;

name = 'DELETED' 조건을 넣는 것으로 그 다음에 변경이 일어난 것에 대해서는 무효 처리를 한다.

 

mysql> select * from member;
+--------+---------+
| mbr_no | mbr_nm  |
+--------+---------+
|      1 | Alice   |
|      2 | Bob     |
|      3 | Charlie |
+--------+---------+
3 rows in set (0.001 sec)