[MySQL][Backup n Recovery] Binary Log 를 이용하여 특정 시점 복구하기

특정 시점 복구는 특정 시점 이후의 데이터 변경 사항까지 적용한 복구를 의미합니다. 일반적으로 이 유형의 복구는 전체 백업을 복원 한 후 백업 시점의 서버 상태로 복원 한 후에 수행됩니다. (7.2 절.“데이터베이스 백업 방법”에 나열된 것과 같은 여러 가지 방법으로 전체 백업을 수행 할 수 있습니다.) 특정 시점 복구는 전체 풀 백업 시간에서 가능한 한 최신 시점까지 시간을 적용하여 최근의 데이터베이스와 비슷하게 만드는 것입니다. 또는 특정 용도에 따라 원하는 시간까지만 복구할 수도 있습니다. 예를 들어 풀백업 이후 몇월 몇일 몇시까지의 특정 데이터베이스가 필요한경우 이 방법을 이용해서 특정 시간으로 복구를 할 수 있습니다.

 

■ 사전 지식사항

여기에있는 몇몇 예제는 mysqlbinlog에 의해 생성 된 바이너리 로그 출력을 mysql 클라이언트를 사용하여데이터베이스에 적용합니다. 바이너리 로그에 \0 (널) 문자가 포함되어 있으면 --binary-mode 옵션으로 호출하지 않으면 mysql에서 해당 출력을 구문을 분석 할 수 없습니다.

 

▶ 특정 시점 복구는 다음 원칙을 기반으로합니다 : 

+ 특정 시점 복구를 위한 정보 소스는 전체 백업 조작 후에 생성 된 바이너리 로그 파일로 표시되는 증분 백업 세트입니다. 따라서 바이너리 로깅을 활성화하려면 서버를 --log-bin 옵션으로 시작해야합니다.

 

- 바이너리 로그에서 데이터를 복원하려면 현재 바이너리 로그 파일의 이름과 위치를 알아야합니다. 기본적으로 서버는 데이터 디렉토리에 바이너리 로그 파일을 만들지만 --log-bin 옵션으로 경로 이름을 지정하여 파일을 다른 위치에 배치 할 수 있습니다.

 

- 모든 이진 로그 파일 목록을 보려면 다음 명령문을 사용하십시오.

mysql> SHOW BINARY LOGS;

 

현재 바이너리 로그 파일의 이름을 확인하려면 다음 명령문을 실행합니다.

mysql> SHOW MASTER STATUS;

 

+ mysqlbinlog 유틸리티는 바이너리 로그 파일의 이벤트를 MySQL 서버에서만 읽을 수 있는 바이너리 형식에서 사람이 읽을 수 있는 텍스트로 변환하여 실행하거나 내용이 무엇인지 확인해 볼 수 있도록 합니다. mysqlbinlog에는 이벤트시간 또는 로그 내 이벤트 위치에 따라 바이너리 로그 섹션을 선택하는 옵션이 있습니다. 4.6.7 절.“mysqlbinlog — 이진 로그 파일 처리를위한 유틸리티”를 참조하십시오.

 

+ 바이너리 로그에서 이벤트를 실행하면 이들이 나타내는 데이터 수정이 다시 수행됩니다. 이를 통해 일정 시간 동안 데이터 변경을 복구 할 수 있습니다. 바이너리 로그에서 이벤트를 실행하려면 mysql 클라이언트를 사용하여 mysqlbinlog에 의해 출력된 SQL문을 실행합니다.

shell> mysqlbinlog binlog_files | mysql -u root -p

 

+ 로그 내용을 보는 것은 이벤트를 실행하기 전에 일정부분 로그 내용을 선택하기 위해 이벤트 시간 또는 위치를 결정해야 할 때 유용 할 수 있습니다. 로그에서 이벤트를 보려면 mysqlbinlog프로그램을 통해 화면에 출력해야 합니다. 그러나 내용이 워낙 많기 때문에 페이징 출력 프로그램(shell 프로그램의 more 명령어)을 통해 확인해 봅니다.

shell> mysqlbinlog binlog_files | more

 

또는 화면에 나오는 출력을 파일로 저장하고 텍스트 편집기에서 파일을 확인해 봅니다. 이 방법이 추천드리는 방법입니다.

shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...

 

+ 출력된 내용을 파일에 저장하면 위험한 명령어인 DROP DATABASE와 같은 특정 이벤트가 제거 된 로그 내용을 실행하기위한 예비 수단으로 유용합니다. 내용을 실행하기 전에 실행하지 않아야하는 명령문을 파일에서 삭제할 수 있습니다. 파일을 편집 한 후 다음과 같이 내용을 실행하시면 위험부담을 줄일 수 있습니다.

shell> mysql -u root -p < tmpfile

 

MySQL 서버에서 실행할 바이너리 로그가 둘 이상인 경우 안전한 방법은 서버에 대한 단일 연결을 사용하여 모두 처리하는 것입니다. 안전하지 않은 것을 보여주는 예는 다음과 같습니다.

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

 

첫 번째 로그 파일에 CREATE TEMPORARY TABLE 문이 있고 두 번째 로그에 임시 테이블을 사용하는 명령문이 포함 된 경우 서버에 다른 연결을 사용하여 이 방법으로 바이너리 로그를 처리하면 문제가 발생합니다. 첫 번째 mysql 프로세스가 종료되면 서버는 임시 테이블을 삭제합니다. 두 번째 mysql 프로세스가 테이블을 사용하려고하면 서버는 "알 수없는 테이블"을 보고합니다. 첫번째 mysql 프로세스가 앞에서 언급했던 것처럼 삭제했기 때문입니다.

따라서 이와 같은 문제를 피하려면 단일 연결을 사용하여 처리하려는 모든 바이너리 로그의 내용을 실행해야 합니다. 그렇게하는 방법은 다음과 같습니다.

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

 

다른 방법은 모든 로그를 단일 파일에 쓴 다음 파일을 처리하는 것입니다.

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

 

GTID가 포함 된 바이너리 로그에서 다시 읽는 동안 덤프 파일에 쓸 때 mysqlbinlog와 함께 --skip-gtids 옵션을 다음과 같이 사용하세요.

shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

 

 

 

■ 이벤트 타임을 이용한 시점 복원

복구 시작 및 종료 시간을 표시하려면 mysqlbinlog에 대한 --start-datetime 및 --stop-datetime 옵션을 DATETIME 형식으로 지정합니다. 예를 들어, 2005년 4월 2일 오전 10시에 정확히 큰 테이블을 삭제하는 SQL 문이 실행되었다고 가정하십시오. 테이블과 데이터를 복원하기 위해 전날 밤의 백업을 복원 한 후 다음 명령을 실행할 수 있습니다.

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \
         /var/log/mysql/bin.123456 | mysql -u root -p

 

이 명령은 --stop-datetime 옵션으로 지정된 날짜와 시간까지 모든 데이터를 복구합니다. 몇 시간 후까지 입력한 잘못된 SQL 문을 발견하지 못한 경우 나중에 발생한 활동을 복구 할 수도 있습니다. 이를 기반으로 다음과 같이 시작 날짜 및 시간으로 mysqlbinlog를 다시 실행할 수 있습니다.

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

 

이 명령에서 오전 10시 1 분에 로깅된 SQL 문이 다시 실행됩니다. 전날 밤의 덤프 파일 복원과 두 개의 mysqlbinlog 명령의 조합은 오전 10시 이전 1초 전까지, 오전 10시 01분 전까지 모든 것을 복원합니다.

 

이 특정 시점 복구 방법을 사용하려면 명령에 지정할 정확한 시간을 확인하기 위해 로그를 검사해야합니다. 로그 파일 내용을 실행하지 않고 표시하려면 다음과 같이 합니다.

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

 

그런 다음 텍스트 편집기로 /tmp/mysql_restore.sql 파일을 열어서 확인해 봅니다. 제외할 명령문과 여러 명령문을 동시에 실행하는 경우 mysqlbinlog 시간을 지정하여 특정 변경 사항을 제외하면 제대로 작동하지 않습니다.

 

 

 

■ 이벤트 포지션을 이용한 시점 복원

특정 날짜 및 시간을 지정하는 대신 mysqlbinlog에 대한 --start-position 및 --stop-position 옵션을 사용하여 로그 위치를 지정할 수 있습니다. 날짜가 아닌 로그 위치 번호를 지정한다는 점을 제외하고 시작 및 중지 날짜 옵션과 동일하게 작동합니다. 위치를 사용하면 특히 손상된 SQL 문과 같은 시간에 많은 트랜잭션이 발생한 경우 복구 할 로그 부분을 보다 정확하게 파악할 수 있습니다. 위치 번호를 확인하려면 원하지 않는 트랜잭션이 실행 된 시간 근처에서 mysqlbinlog를 일정 시간 동안 실행하되 결과를 텍스트 파일로 리디렉션하여 파일로 저장한다음 확인해 봅니다.

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
         --stop-datetime="2005-04-20 10:05:00" \
         /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

 

이 명령은 원치 않는 SQL 문이 실행된 시간 동안 SQL 문이 포함 된 작은 텍스트 파일을 / tmp 디렉토리에 리다이렉션 한 파일 이름으로 새성됩니다. 텍스트 편집기로 이 파일을 열고 수행하지 않으려는 명령문을 모두 찾습니다. 바이너리 로그에서 복구 중지 및 다시 시작할 위치를 판별하고 기록해 둡니다. 위치는 log_pos로 표시되고 그 뒤에 숫자가 붙습니다. 이전 백업 파일을 복원 한 후 위치 번호를 사용하여 바이너리 로그 파일을 실행합니다.

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
         | mysql -u root -p

shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
         | mysql -u root -p

 

첫번째 명령은 지정된 중지 위치까지 모든 트랜잭션을 복구합니다. 두 번째 명령은 바이너리 로그의 끝까지 주어진 시작 위치에서 모든 트랜잭션을 복구합니다. mysqlbinlog의 출력에는 각 SQL 문이 기록되기 전에 SET TIMESTAMP 문이 포함되므로 복구 된 데이터 및 관련 MySQL 로그는 트랜잭션이 실행 된 원래 시간을 반영합니다.