Web/DB

MySQL의 비정상 종료와 데이터 복구 방법

Codecheck 2024. 10. 12. 10:49

개요

데이터베이터 시스템은 다양한 이유로 비정상 종료될 수 있으며, 이로 인해 데이터 손실이나 손상이 발생할 수 있습니다. 본 글에서는 MySQL의 비정상 종료 원인과 데이터 복구 방법을 중점적으로 간략하게 다뤄보도록 하겠습니다.

 

명령어 예시

MySQL 비정상 종료의 원인

  • 디스크 손상이나 메모리 오류
  • MySQL 소프트웨어 버그
  • 메모리, CPU, 디스크 자원 부족
  • 운영체제 문제 (커널 패닉)
  • 잘못된 명령어 입력이나 설정 변경

 

MySQL 데이터 복구 방법

1. 백업 복구

가장 일반적인 데이터 복구 방법입니다.

정기적으로 DB의 백업을 수행해두면, 데이터 손실 시 쉽게 복구할 수 있습니다.

MySQL에서 지원하는 백업 도구가 존재하고, 그외의 백업 방법도 있습니다.

 

1) mysqldump

MySQL 커뮤니티 에디션에서 제공하는 기본 백업 도구로, SQL 스크립트 형식으로 데이터베이스를 백업하는 역할을 한다.

 

• SQL 쿼리로 데이터와 스키마를 덤프

• 텍스트 기반의 백업

• 데이터베이스나 테이블을 선택하여 백업할 수 있음

• 데이터를 덤프할 때 잠금을 사용하여 백업 중 데이터 무결성을 보장할 수 있다.

• 상대적으로 느리며 대용량 데이터베이스에 사용하면 비효율적임

• 압축, 암호화 같은 고급기능은 기본적으로 제공되지 않음

 

명령어 예시

mysqldump -u [username] -p [database_name] > backup.sql

 

2) MySQL Enterprise Backup

MySQL 엔터프라이즈 버전에서 제공되는 고급 백업 솔루션으로, 물리적인 데이터베이스 백업을 수행

 

• SQL 스크립트 대신 데이터 파일의 물리적 복사본을 백업

• 압축, 암호화와 같은 기능을 지원

• 증분 백업과 차등 백업을 제공

      ✓ 증분백업? : 가장 최근에 백업된 시점 이후로 변경된 데이터만 백업하는 방식

      ✓ 차등백업? : 마지막 전체 백업 이후 변경된 모든 데이터를 백업하는 방식

• 백업 중 데이터베이스를 읽기/쓰기 가능하도록 유지하는 hot backup을 지원

• 대규모 환경에서 백업 및 복원 속도가 빠르므로 대용량 데이터베이스에 적합

• 물리적 백업이기 때문에 복원 시 mysqldump보다 빠른 속도로 복원이 가능함

 

명령어 예시

mysqlbackup --user=[username] --password --backup-dir=/backup/dir backup

 

3) 파일 시스템 백업

데이터베이스의 데이터 파일을 직접 복사하는 방식으로 수행

• 데이터 무결성을 보장하기 어려움

• 권장하지 않음

 

 

2. InnoDB 복구

MySQL의 기본 스토리지 엔진인 InnoDB의 데이터 복구 과정

→ ACID 트랜잭션을 지원

→ 데이터 무결성을 보장하기 위해 다양한 복구 메커니즘을 제공

 

1) 주요 복구 메커니즘

InnoDB는 크래시 복구를 통해 데이터 무결성을 보장하며, 주요 복구 메커니즘으로는 로그 파일과 데이터 파일을 사용

 

• Redo 로그 : InnoDB는 redo로그를 통해 완료되지 않은 트랜잭션과 이미 커밋된 트랜잭션을 복구할 수 있음

   ✓ 시스템 충돌 시, 이 로그를 사용하여 트랜잭션 로그에 기록된 데이터 변경 사항을 재실행함으로써 데이터를 복구

 

• Undo 로그 : 트랜잭션 중에 발생한 변경 사항을 되돌릴 수 있도록 하는 undo 로그는 시스템 충돌 시 롤백을 통해 데이터 무결성을 유지 → 이 로그를 통해 완료되지 않은 트랜잭션을 원래 상태로 되돌려짐

 

 

2) InnoDB 복구 과정

InnoDB 복구는 보통 자동으로 수행됨

MySQL이 다시 시작되면 InnoDB는 자동으로 복구 작업을 시작하여 데이터 무결성을 보장함

 

InnoDB가 충돌 후 다시 시작될 때 위에서 말한 크래시 복구(Redo 로그 재실행, Undo 로그 롤백)가 이루어짐

 

하지만, InnoDB 데이터 파일이나 로그 파일이 손상되었을 때는 더 복잡한 복구 과정이 필요

 

이 경우에는 innodb_force_recovery 옵션을 사용 

파일의 일부분이라도 덤프한 후, 새로 Mysql을 설정하여 복구하는 방법을 고려할 수 있음

 

innodb_force_recovery

  • MySQL을 시작할 수 없을 때 사용하여 제한된 복구 모드를 활성화할 수 있음
  • 1부터 6까지의 값을 가짐
  • 높은 값일 수록 더 많은 제한을 두고 데이터를 덤프할 수 있지만, 완벽한 복구는 보장되지 않을 수 있음

1: 점검만 수행 (가장 안전)

2: 버퍼 풀 불러오지 않음

3: 롤백 하지 않음

4: Undo 로그 적용하지 않음

5: Insert buffer 병합하지 않음

6: 모든 InnoDB 트랜잭션을 무시하고 읽기 전용으로 시작 (데이터 덤프 후 재구축 가능)

 

이 옵션은 데이터 베이스를 덤프하기 위해 사용하는 것이므로, 복구 후에는 반드시 설정을 제거해야 함

 

 

3. 바이너리 로그 복구

  • MySQL은 바이너리 로그에 MySQL 서버에서 실행된 모든 데이터 변경 쿼리(INSERT, UPDATE, DELETE)를 기록
  • 이를 통해 특정 시점까지 데이터베이스를 복구하거나, 복구 중에 발생한 변경 사항을 다시 적용할 수 있음
  • 바이너리 로그를 기록하도록 설정되어 있어야 가능 (my.cnf에서 log_bin 옵션 활성화)

바이너리 로그를 이용한 복구 과정

• 일반적으로 백업과 바이너리 로그를 조합하여 복구

 

1) 정기적인 전체 백업을 진행한 백업 파일에서 데이터베이스를 복원

2) mysqlbinlog 도구를 사용하여 바이너리 로그 파일의 내용을 실행

 

예시

mysqlbinlog --stop-datetime="2024-10-12 10:00:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

이는 특정 시점까지의 바이너리 로그를 적용하도록 함

mysqlbinlog --stop-position=12345 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

트랜잭션 ID를 통해 특정 트랜잭션까지의 로그를 복구할 수 있음

 

잘못된 쿼리를 포함하지 않고 로그에서 복구하기 위해서는 —skip-gtids 또는 수동으로 로그 파일을 편집하여 건너뛸 수 있음

→ 데이터 일관성에 문제를 발생시킬 수 있으므로 신중하게 사용해야 함

 

3) 잘못된 이전 시점으로 복구 후, 올바른 트랜잭션만 다시 적용

 

 

마무리

이러한 다양한 복구 방식을 이용하여 데이터를 복구한 뒤에는, 검증이 필요합니다.

  • 데이터 무결성 검사를 통해 복구된 데이터가 손상되지 않았는지 확인
  • 애플리케이션이 정상적으로 작동하는지 확인
  • 복구 과정에서 발생한 로그를 분석하여 문제가 왜 발생하였는지 파악을 통해 재발방지

하지만, 심각한 파일 손상이 발생할 경우 복구가 어려울 수 있으므로 정기적인 백업을 통해 최악의 상황을 항상 생각해둘 필요가 있겠습니다.

 

 

참고자료

https://www.issuelink.co.kr/blog/development/abnormal-termination-of-mysql-and-data-recovery-methods

https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html