9.5.2 Point-in-Time Recovery Using Event Positions

9.5.2 Point-in-Time Recovery Using Event Positions

이전 섹션인 Section 9.5.1, “Point-in-Time Recovery Using Binary Log”에서는 binary log을 사용하여 point-in-time-recovery를 수행하는 일반적인 개념을 설명합니다. 그 섹션에서는 예제를 통해 동작을 자세히 설명합니다.

예를 들어, 2020년 3월 11일 20:06:00경에 어떤 SQL statement가 실행되어 한 개의 table이 삭제되었다고 가정해 봅니다. table 삭제 직전 시점까지 server를 복구하는 point-in-time recovery를 수행할 수 있습니다. 이를 위해 수행할 수 있는 예시 단계는 다음과 같습니다:

1. 관심 있는 point-in-time 이전에 생성된 마지막 full backup을 복원합니다(이를 tp라고 부르며, 이 예제에서는 2020년 3월 11일 20:06:00입니다). 작업이 끝나면, 나중에 사용하기 위해 server를 어디까지 복원했는지를 나타내는 binary log position을 기록하고, server를 재시작합니다.

Note

마지막으로 복구된 binary log position은 복원 및 server 재시작 이후 InnoDB에 의해 표시되기도 하지만, 그것은 복원의 종료 log position을 얻기 위한 신뢰할만한 수단은 아닙니다. 표시된 position이 반영하는 시점 이후에 발생한 DDL event나 비-InnoDB 변경이 존재할 수 있기 때문입니다. backup 및 restore tool이 recovery를 위한 마지막 binary log position을 제공해야 합니다. 예를 들어, 이 작업에 mysqlbinlog 을 사용하고 있다면, binary log replay의 stop position을 확인하십시오. MySQL Enterprise Backup을 사용하는 경우, 마지막 binary log position은 backup 안에 저장되어 있습니다. Point-in-Time Recovery를 참조하십시오.

2. 복원하려는 시점까지의 database에 해당하는 정확한 binary log event position을 찾습니다. 이 예제에서, table 삭제가 발생한 대략적인 시간 (tp)을 알고 있으므로, 해당 시점 주변의 log 내용을 mysqlbinlog 유틸리티를 사용해서 확인하여 log position을 찾을 수 있습니다. --start-datetime 및 --stop-datetime 옵션을 사용해 tp 주변의 짧은 시간 구간을 지정한 뒤, 출력에서 event를 찾습니다. 예를 들면 다음과 같습니다:

1$> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
2                      --stop-datetime="2020-03-11 20:08:00" --verbose \
3            /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
4
5/*!80014 SET @@session.original_server_version=80019*//*!*/;
6/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
7SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
8# at 232
9#200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0
10SET TIMESTAMP=1583971580/*!*/;
11SET @@session.pseudo_thread_id=16/*!*/;
12SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13SET @@session.sql_mode=1168113696/*!*/;
14SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15/*!\C utf8mb4 *//*!*/;
16SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
17SET @@session.lc_time_names=0/*!*/;
18SET @@session.collation_database=DEFAULT/*!*/;
19/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
20DROP TABLE `pets`.`cats` /* generated by server */
21/*!*/;
22# at 355
23#200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473
24# original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
25# immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
26/*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
27/*!80014 SET @@session.original_server_version=80019*//*!*/;
28/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
29SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
30# at 434
31#200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217
32use `pets`/*!*/;
33SET TIMESTAMP=1583971668/*!*/;
34/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
35CREATE TABLE dogs

mysqlbinlog의 출력에서, ``DROP TABLE pets.cats``` statement는 # at 232와 # at 355두 줄 사이의 binary log segment에서 찾을 수 있습니다. 이는 해당 statement가 log position 232 _이후에_ 발생했으며,DROP TABLE` statement 이후에는 log가 position 355에 있음을 의미합니다.

Note

--start-datetime 및 --stop-datetime 옵션은 관심 있는 실제 event position을 찾는 데에만 사용하십시오. 이 두 옵션으로 적용할 binary log segment의 범위를 지정하는 것은 권장되지 않습니다. 옵션을 사용할 경우 binary log event를 누락할 위험이 더 큽니다. 그 대신 --start-position 및 --stop-position 을 사용하십시오.

3. step 1에서 찾은 log position(여기서는 155라고 가정)부터 step 2에서 찾은, 관심 있는 point-in-time 이전의 position(여기서는 232)까지의 binary log file 내 event를 server에 적용합니다:

1$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
2            | mysql -u root -p

이 명령은 시작 position부터 stop position 직전까지의 모든 transaction을 복구합니다. mysqlbinlog의 출력에는 각 SQL statement가 기록되기 전에 SET TIMESTAMP statement가 포함되어 있으므로, 복구된 data와 관련 MySQL log는 transaction이 실행되었을 당시의 원래 시간을 반영합니다.

이제 database는 관심 있는 point-in-time인 tp 시점, 즉 pets.cats table이 drop되기 직전으로 복원되었습니다.

4. 완료된 point-in-time recovery 이후에, 관심 있는 point-in-time 이후의 모든 statement를 다시 실행하고 싶다면, mysqlbinlog을 다시 사용하여 tp 이후의 모든 event를 server에 적용하십시오. step 2에서, 건너뛰려 했던 statement 이후의 log가 position 355에 있음을 기록해 두었으므로, 이를 --start-position 옵션에 사용할 수 있고, 따라서 해당 position 이후의 모든 statement가 포함됩니다:

1$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
2            | mysql -u root -p

이제 database는 선택적으로 건너뛴 event를 제외하고 binary log file에 기록된 가장 최근 statement까지 복원되었습니다.