📢 개요
서비스 중인 DB에서 데이터가 유실되면 전체 서비스에 심각한 문제가 발생할 수 있으므로, 신속한 데이터 복구 절차가 필요하다.
데이터 복구에는 여러 가지 방법이 있지만, 이번 글에서는 Binary log 파일을 활용하여 데이터를 복구하는 과정을 다룰 예정이다.
이 방법은 Binary log 기록이 활성화되어 있다는 전제하에 수행할 수 있다.
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
🎬 데이터 복구 시나리오
-
현재 사용 중인 DB는 매일 새벽 2시에 전체 백업을 진행하고 있다.
-
sakila데이터베이스에 만든 테이블tmp_order_m은 오늘(2025-07-11) 오후에 생성했으며, 다양한 작업에 사용 중이라 가정한다.
(단순 조회뿐만 아니라INSERT,UPDATE,DELETE등의 작업이 이루어지고 있다) -
직원의 실수로
sakila데이터베이스에film,film_actor,film_category,film_text,tmp_order_m테이블을 DROP했다. 해당 테이블은 서비스 운영에 중요한 테이블이라 가정한다. -
DROP된
film,film_actor,film_category,film_text테이블은 오늘 새벽 2시에 백업한 데이터로 복구할 수 있지만, 새벽 2시 이후에 작업이 이루어진tmp_order_m테이블 데이터는 복구할 수는 없다. -
새벽 2시 이후에 작업이 이루어진 데이터를 Binary log와
mysqlbinlog,mysqldump를 사용하여 복구해 볼 예정이다.
🔎 데이터 확인
[테이블 확인]
[작업 전 테이블] [작업 후 테이블] [삭제 후 테이블] mysql> show tables; mysql> show tables; mysql> show tables; +----------------------------+ +----------------------------+ +----------------------------+ | Tables_in_sakila | | Tables_in_sakila | | Tables_in_sakila | +----------------------------+ +----------------------------+ +----------------------------+ | actor | | actor | | actor | | actor_info | | actor_info | | actor_info | | address | | address | | address | | category | | category | | category | | city | | city | | city | | country | | country | | country | | customer | | customer | | customer | | customer_list | | customer_list | | customer_list | | film | | film | | film_list | | film_actor | | film_actor | | inventory | | film_category | | film_category | | language | | film_list | | film_list | | nicer_but_slower_film_list | | film_text | | film_text | | payment | | inventory | | inventory | | rental | | language | | language | | sales_by_film_category | | nicer_but_slower_film_list | | nicer_but_slower_film_list | | sales_by_store | | payment | | payment | | staff | | rental | | rental | | staff_list | | sales_by_film_category | | sales_by_film_category | | store | | sales_by_store | | sales_by_store | +----------------------------+ | staff | | staff | 19 rows in set (0.00 sec) | staff_list | | staff_list | | store | | store | +----------------------------+ | tmp_order_m | 23 rows in set (0.00 sec) +----------------------------+ 24 rows in set (0.00 sec)
[tmp_order_m 테이블 데이터 확인]
mysql> select * from tmp_order_m; +----+----------+-----------+---------------------+ | id | ord_nm | ord_cd | ord_dt | +----+----------+-----------+---------------------+ | 1 | test_999 | test1_001 | 2025-07-11 19:09:41 | | 2 | test2 | test1_002 | 2025-07-11 19:10:09 | | 3 | test3 | test1_003 | 2025-07-11 19:10:35 | | 5 | test5 | test1_005 | 2025-07-11 19:10:38 | | 6 | test6 | test1_006 | 2025-07-11 19:10:39 | | 7 | test7 | test1_007 | 2025-07-11 19:10:40 | +----+----------+-----------+---------------------+ 6 rows in set (0.00 sec)
⏰ 작업 시간 확인
Binary log를 활용해 DROP된 데이터를 복구하려면, 마지막으로 DROP 명령어가 수행된 정확한 시점을 반드시 확인해야 한다.
아래 타임라인과 같이, 전체 백업 이후 데이터 생성·수정·삭제 시점을 기준으로 복구 범위를 결정하게 된다.
▶︎ Binary log 파일 확인
일반적으로 Binary log 파일은 /var/lib/mysql 경로에 있다.
정확한 위치를 알고 싶으면 mysql 서버에 접속하여 확인할 수 있다.
log_bin_basename을 참고하여 경로와 파일명을 확인 할 수 있다.mysql> show variables like 'log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-----------------------------+ 5 rows in set (0.00 sec)
Binary log 파일은 일반적인 방법으로 로그 내용을 확인할 수 없다. 이름 그대로 바이너리(2진법) 형식으로 저장되기 때문에 별도의 유틸리티 명령어인 mysqlbinlog를 사용해야 파일 내 기록을 조회할 수 있다.
일반적인 파일 확인 명령어(
cat,vi등)로 바이너리 파일을 확인할 수 없다.
mysql 바이너리 로그파일 확인 명령어인
mysqlbinlog를 사용하면 파일을 확인할 수 있다.
▶︎ DROP 시간 확인
mysqlbinlog와 grep명령어를 사용하여 로그 파일 내 특정 명령어가 언제 실행되었는지 확인할 수 있다.
[root@localhost mysql]# mysqlbinlog binlog.000025 | grep -i -B 3 'drop' #250711 19:33:05 server id 1 end_log_pos 3766 CRC32 0x05654fdb Query thread_id=11 exec_time=0 error_code=0 Xid = 215 SET TIMESTAMP=1752229985/*!*/; SET @@session.foreign_key_checks=0/*!*/; DROP TABLE `film`,`film_actor`,`film_category`,`film_text`,`tmp_order_m` /* generated by server */
여기서 실행 시간은 아래라인을 확인하면 된다.
#250711 19:33:05 server id 1 end_log_pos 3766 CRC32 0x05654fdb Query thread_id=11 exec_time=0 error_code=0 Xid = 215
해당 행 앞 부분 250711 19:33:05이(가) 바로 DROP이 실행된 시간이다.
▶︎ 다른 쿼리문 작업 시간 확인
mysqlbinlog명령어로 다른 작업을 확인할 때 명확한 조회가 안 되는 경우가 있다. 이러한 이유로 아래 옵션과 설정을 통해 상세한 작업 시간을 확인할 수 있다.
--base64-output=DECODE-ROWS: ROW 기반 로그를 사람이 읽을 수 있도록 디코딩
-vv: 상세하게 표시mysqlbinlog --base64-output=DECODE-ROWS -vv binlog.000025 | grep -i -B 3 'insert'
🔁 삭제된 데이터 복구
▶︎ 데이터 복구 파일 준비
전체 백업을 수행한 시점과 테이블이 삭제된 시점 사이에 진행된 모든 작업을 하나의 SQL 파일로 모아두고, 이 파일을 다시 실행하면 삭제된 데이터를 복구할 수 있다.
복구에 필요한 파일은 2025-07-11 02:00 ~ 19:33 사이 데이터다.
아래 바이너리 파일을 기준으로 보면
binlog.000014~binlog.000025파일이 복구에 필요한 대상임을 확인할 수 있다.# ls -al | grep 'binlog.0' -rw-r-----. 1 mysql mysql 157 Jul 6 17:36 binlog.000011 -rw-r-----. 1 mysql mysql 157 Jul 9 22:44 binlog.000012 -rw-r-----. 1 mysql mysql 157 Jul 11 01:02 binlog.000013 -rw-r-----. 1 mysql mysql 157 Jul 11 16:29 binlog.000014 -rw-r-----. 1 mysql mysql 157 Jul 11 16:31 binlog.000015 -rw-r-----. 1 mysql mysql 157 Jul 11 16:37 binlog.000016 -rw-r-----. 1 mysql mysql 157 Jul 11 17:58 binlog.000017 -rw-r-----. 1 mysql mysql 157 Jul 11 18:06 binlog.000018 -rw-r-----. 1 mysql mysql 180 Jul 11 18:33 binlog.000019 -rw-r-----. 1 mysql mysql 180 Jul 11 18:41 binlog.000020 -rw-r-----. 1 mysql mysql 157 Jul 11 18:48 binlog.000021 -rw-r-----. 1 mysql mysql 180 Jul 11 18:49 binlog.000022 -rw-r-----. 1 mysql mysql 180 Jul 11 18:54 binlog.000023 -rw-r-----. 1 mysql mysql 180 Jul 11 18:58 binlog.000024 -rw-r-----. 1 mysql mysql 3766 Jul 11 19:33 binlog.000025
아래처럼 binlog.000014 파일부터 binlog.000025 파일을 하나로 묶어 하나의 복구 파일로 만든다. 여기서 binlog.000025 파일에는 주의가 필요하다.
binlog.000025 파일에는 복구하려는 테이블을 삭제한 DROP 쿼리문이 포함되어 있기 때문에 해당 쿼리문 이전까지만 복구 파일에 포함되어야 한다.
DROP이 이루어진 시점이
250711 19:33:05이기 때문에250711 19:33:04이전 기록만 복구 파일에 포함한다.실제 기록에는 --stop-datetime="2025-07-11 19:33:06"에 DROP 관련 기록이 있다. 이는 DROP 이벤트 시점과 이벤트가 플러시(쓰기)된 시점이 다르기 때문이다. 이러한 이유로 DROP된 시점의 시간을 확실하게 확인하는 게 중요하다.
(사실 이번 복구에서 시간 설정을--stop-datetime="2025-07-11 19:33:05"으로 설정해도 복구가 된다.)# mysqlbinlog binlog.000014 > /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000015 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000016 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000017 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000018 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000019 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000020 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000021 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000022 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000023 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000024 >> /backup/restore/restore_20250711.sql # mysqlbinlog binlog.000025 \ --start-datetime="2025-07-11 02:00:00" \ --stop-datetime="2025-07-11 19:33:04" \ > /backup/restore/restore_20250711.sql
▶︎ 데이터 복구
가장 먼저 전체 백업 데이터를 기준으로 film, film_actor, film_category, film_text 테이블을 복구한다.
[전체 백업 복구]
# mysql -u admin -p < /backup/mysqldump_20250711/database_full.sql
이후 전체 백업으로 복구 불가능한 데이터를 복구한다.
[Binary log 기반 복구]
# mysql -u admin -p sakila < /backup/restore/restore_20250711.sql
