바이너리 로그를 사용한 복구
MySQL에서 바이너리 로그(Binary Log)란 데이터베이스 서버에서 수행된 모든 변경 작업을 기록하는 파일이다.
이 로그는 데이터 손실이 발생했을 때 복구 작업을 수행하거나, 복제하는 데 사용된다.
데이터베이스에 접속한 후, 아래 쿼리를 실행하면 저렇게 binlog.XXXXXX 파일이 있는데, 저 파일이 바이너리 로그이다.
SHOW BINARY LOGS;
하나의 파일을 선택해 조회를 해본 결과 바이너리 로그의 형태는 다음과 같다.
# 바이너리 로그 파일 열람 명령어
mysqlbinlog binlog.000403#250120 18:02:05 server id 1 end_log_pos 2288 CRC32 0x89fd1c5e Query thread_id=10 exec_time=0 error_code=0 Xid = 73
SET TIMESTAMP=1737363725/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
/* ApplicationName=DBeaver 23.1.1 - SQLEditor <Script-17.sql> */ CREATE DATABASE `restoration`
/*!*/;
# at 2288
#250120 18:02:08 server id 1 end_log_pos 2367 CRC32 0xdbdfd7e9 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=no original_committed_timestamp=1737363728837797 immediate_commit_timestamp=1737363728837797 transaction_length=540
# original_commit_timestamp=1737363728837797 (2025-01-20 18:02:08.837797 KST)
# immediate_commit_timestamp=1737363728837797 (2025-01-20 18:02:08.837797 KST)
/*!80001 SET @@session.original_commit_timestamp=1737363728837797*//*!*/;
/*!80014 SET @@session.original_server_version=80300*//*!*/;
/*!80014 SET @@session.immediate_server_version=80300*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2367
#250120 18:02:08 server id 1 end_log_pos 2828 CRC32 0x3dedfbb8 Query thread_id=10 exec_time=0 error_code=0 Xid = 78
use `restoration`/*!*/;
SET TIMESTAMP=1737363728/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 23.1.1 - SQLEditor <Script-17.sql> */ CREATE TABLE test_users (
id INT AUTO_INCREMENT PRIMARY KEY, -- 고유 식별자
username VARCHAR(50) NOT NULL, -- 사용자 이름
email VARCHAR(100) NOT NULL, -- 이메일 주소
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 생성 시간
)
/*!*/;
# at 2828
#250120 18:02:10 server id 1 end_log_pos 2907 CRC32 0xf9b547d4 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=1737363730825765 immediate_commit_timestamp=1737363730825765 transaction_length=405
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1737363730825765 (2025-01-20 18:02:10.825765 KST)
# immediate_commit_timestamp=1737363730825765 (2025-01-20 18:02:10.825765 KST)
/*!80001 SET @@session.original_commit_timestamp=1737363730825765*//*!*/;
/*!80014 SET @@session.original_server_version=80300*//*!*/;
/*!80014 SET @@session.immediate_server_version=80300*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2907
#250120 18:02:10 server id 1 end_log_pos 2989 CRC32 0xed2b6c21 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1737363730/*!*/;
BEGIN
/*!*/;
# at 2989
#250120 18:02:10 server id 1 end_log_pos 3065 CRC32 0x3705ea27 Table_map: `restoration`.`test_users` mapped to number 91
# has_generated_invisible_primary_key=0
# at 3065
#250120 18:02:10 server id 1 end_log_pos 3202 CRC32 0xf1bb9f08 Write_rows: table id 91 flags: STMT_END_F
BINLOG '
EhGOZxMBAAAATAAAAPkLAAAAAFsAAAAAAAEAC3Jlc3RvcmF0aW9uAAp0ZXN0X3VzZXJzAAQDDw8R
BcgAkAEACAEBAAID/P8AJ+oFNw==
EhGOZx4BAAAAiQAAAIIMAAAAAFsAAAAAAAEAAgAE/wABAAAABUFsaWNlEQBhbGljZUBleGFtcGxl
LmNvbWeOERIAAgAAAANCb2IPAGJvYkBleGFtcGxlLmNvbWeOERIAAwAAAAdDaGFybGllEwBjaGFy
bGllQGV4YW1wbGUuY29tZ44REgifu/E=
'/*!*/;
# at 3202
#250120 18:02:10 server id 1 end_log_pos 3233 CRC32 0xc919e982 Xid = 81
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*/;
이 로그는 MySQL 서버에서 실행된 작업과 그와 관련된 설정 및 트랜잭션 정보를 포함하고 있는 것이다.
(참고) mysqlbinlog
일반적으로 대부분의 로그 파일은 터미널 환경에선 vim이나 vi를 사용해 열람한다. 하지만 바이너리 로그는 명칭대로 이진(Binary) 형태로 저장되어 사람이 읽기 어렵다. 그렇기 때문에 바이너리 로그 파일은 mysqlbinlog 도구를 사용해 이진 데이터를 사람이 읽을 수 있는 형태로 변환하여 열람할 수 있도록 해준다.
실행한 쿼리는 다음과 같다.
CREATE DATABASE `restoration`
USE `restoration`
CREATE TABLE test_users (
id INT AUTO_INCREMENT PRIMARY KEY, -- 고유 식별자
username VARCHAR(50) NOT NULL, -- 사용자 이름
email VARCHAR(100) NOT NULL, -- 이메일 주소
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 생성 시간
);
INSERT INTO test_users (username, email)
VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
위와 같이 Alice, Bob, Carlie 정보가 test_users 테이블에 입력된 상태이다.
새로운 바이너리 로그 생성
바이너리 로그를 사용한 복구의 경우, 로그 파일에서 특정 시점으로 롤백하거나 복구할 수 있다.
복구 시점을 정하기 위한 새로운 바이너리 로그 파일을 만들고, 해당 파일을 백업해 두면 더 좋다.
내가 사용중인 로그 파일은 binlog.000403 이다.
내가 진행한 작업을 기록해 둔 로그 파일을 찾았으면, 아래의 쿼리를 실행해 새로운 로그 파일을 생성한다.
FLUSH LOGS;
쿼리를 실행하면 다음과 같이 binlog.000404라는 이름의 새로운 로그 파일이 생성된다.
이후에 생성되는 쿼리 로그는 모두 binlog.000404에 저장된다.
복구
복구 테스트를 위해 아래의 쿼리를 실행한다.
DROP TABLE test_users
쿼리 실행 결과, test_users 테이블 자체가 삭제되었다.
다시 바이너리 로그로 돌아가면, 우리가 이전까지 사용한 로그 파일은 binlog.000403이다.
저기서 우리가 복구해야 할 것은 test_users 테이블에 실행된 DROP 쿼리이다.
복구 작업을 위해 아래 쿼리를 실행해 binlog.000403의 로그들을 restore.sql 파일로 복붙 한다. 참고로 이 SQL 파일은 이후에 데이터베이스에서 복구를 위한 쿼리 실행에 사용될 예정이다.
mysqlbinlog /opt/homebrew/var/mysql/binlog.000403 > restore.sql
복구 작업을 위해 restore.sql 내부로 진입한다.
vi restore.sql
restore.sql 파일 내부엔 binlog.000403와 동일하게 이때까지 실행된 쿼리들이 보이는데, 그중에서 복구하고자 하는 DROP 쿼리를 찾는다.
복구하고자 하는 쿼리를 찾았으면 해당 쿼리를 주석처리한다. 난 저 빨간 네모칸을 주석처리 하였다.
주석 처리한 sql 파일을 저장한 뒤, 아래의 쿼리를 터미널에서 실행한다.
sudo mysql -u root -p -f < restore.sql
실행 결과, 아래와 같이 기존에 삭제했던 test_users 테이블과 데이터가 복구되었다.
DDL과 DML의 차이점
데이터베이스 복구를 연습하면서, DDL(예: CREATE, DROP) 관련 쿼리는 비교적 쉽게 복구할 수 있었다.
하지만, DML(예: INSERT, DELETE) 쿼리를 복구하는 것은 성공하지 못했다.
아마 바이너리 로그 설정이 ROW 모드라서 그런 것 같다.
INSERT나 DELETE 명령 자체는 로그에 나타나지 않고, 삭제된 행들의 변경 내역만 기록된다.
그렇기 때문에 해당 명령들을 독립적으로 복구하긴 어려운 것 같다.
'백엔드' 카테고리의 다른 글
| [백엔드] 코드 커버리지 (0) | 2025.01.09 |
|---|