MySQL 데이터베이스 복구 실패: 실험에서 배운 교훈과 데이터베이스 관리 전략

 

Workbench status

?

DB 가 죽어버렸다. 껐다 켜면 되겠지라는 생각으로 다음 명령어를 쳤는데

brew services restart mysql

여전히 안된다.

여전히 되지 않는다.

원인 분석

그래서 로그를 확인하기 위해 ‘/usr/local/mysql/data/mysqld.local.err’ 을 까보기로 마음을 먹었다.

근데 파일이 자그마치 12MB 나 되는 파일을 일일이 읽는다는 건 비효율적이 판단했다.

자그마치 16.2MB 의 텍스트양

ERROR 관련 키워드(ex. ‘ERROR’, “Assertion Failure’, ‘crash’) 만 따로 추출해 분석해 보기로 했다.

# 로그 파일에서 특정 키워드를 포함하는 부분을 찾아냅니다.
keywords = ["ERROR", "Assertion failure", "crash"]
matching_lines = []

for line in full_log_content.splitlines():
    if any(keyword in line for keyword in keywords):
        matching_lines.append(line)

# 일치하는 줄들을 출력합니다.
matching_lines[]

[
'LATEST FOREIGN KEY ERROR',
 'LATEST FOREIGN KEY ERROR',
 '2023-12-07T02:50:41.702802Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...',
 '2023-12-07T02:50:41.704774Z 0 [System] [MY-010232] [Server] XA crash recovery finished.',
 '2024-01-12T11:11:58.413275Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...',
 '2024-01-12T11:11:58.416802Z 0 [System] [MY-010232] [Server] XA crash recovery finished.',
 '2024-01-19T06:14:05.264879Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...',
 '2024-01-19T06:14:05.268060Z 0 [System] [MY-010232] [Server] XA crash recovery finished.',
 '2024-01-19T08:26:49.035040Z 26 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:1222:table2 == nullptr thread 0x177b97000',
 'InnoDB: If you get repeated assertion failures or crashes, even'
]

로그를 통해 보이는 가시적인 원인은 다음 3가지로 보인다.

1. XA 크래시 복구
MySQL 서버가 자체적으로 문제를 해결하기 위해 특별한 복구 과정을 거침.
이는 마치 컴퓨터가 시스템 오류 후에 재부팅하는 것과 비슷한 상황이라고 할 수 있음.

2. InnoDB 단언문 실패
'InnoDB'라는 중요한 부분에서 프로그램이 예상과 다른 상황을 마주침.
이는 데이터베이스 내부에 뭔가 일반적이지 않은 문제가 발생했다는 신호로,
이런 문제는 데이터베이스의 오류나 소프트웨어 버그 때문에 발생할 수 있다.

3. 외래 키 오류
'외래 키'라는 데이터베이스의 연결고리 중 하나에서 문제를 발견
이는 데이터베이스 내의 서로 다른 테이블 사이의 관계 설정에 문제가 있음을 의미.

💡 여기서 XA, XA 크래시란?
XA 란 여러 개의 개별 트랜잭션 리소스가 글로벌 트랜잭션에 참여할 수 있도록 허용하는 기능을 지원함.

XA 크래시 복구는 이러한 트랜잭션이 시스템 오류 후에도 안전하게 계속되도록 복구하는 과정입니다.
[출처] : https://dev.mysql.com/doc/refman/8.0/en/xa.html

최근 게시글의 갯수를 200만 개에서 1억 개로 늘렸더니 검색 API의 성능이 너무 낮아져서

이를 위해 post 테이블에 title, content 에 대한 FULL TEXT INDEX를 추가하려고 했다.

 

이때 기존의 인덱스와 충돌?이 나도 별 신경 안 쓰고 실험을 위해 생성했던

인덱스들을 강제로 지우고 나서 컴퓨터를 닫았었다.

돌이킬 수 없다구?

이때 분명 unique 관련 제약 조건에 대해 경고문이 있었는데

무시하고 지우다 데이터베이스가 터진것이다.

즉, 인덱스를 잘못 건드리다가 InnoDB의 무결성 제약 조건을 내가 깨버린 것.

하 그냥 갈아엎을까?

솔직한 마음으로 이런 일이 일어나자마자 그냥 MySQL 새로 깔고 엎자! 다…라고 생각했다.

개인 플젝이고, 생성한 데이터들도 그냥 마구잡이로 넣었던 데이터들이고,

이게 시간상 가장 효율적이니까!

 

하지만 실서비스라면 목숨을 걸고 데이터 한올(?)이라도 건져야 하는 법.

그래서 죽어버린 내 MySQL에서 데이터라도 건져보자는 시도를 하게 되었다.

하나라도 소중하단 말이죠?

시도해 본 해결책 #1 : InnoDB 복구 모드

MySQL 8.0의 공식 문서에 보면 InnoDB에 대한 복구 모드로 MySQL을 재부팅하면

손상된 InnoDB를 복구할 수 있다고 나와있어 적용해 봤다.

# my.cnf
[mysqld]
innodb_force_recovery = 1 # 1~6 까지 가능. 높을수록 복구가 잘 되지만, 데이터의 유실 위험도 UP

#[출처] : https://dev.mysql.com/doc/refman/8.0/en/innodb-recovery.html

이렇게 하고 나서 기도하는 마음으로 컴퓨터 자체를 재부팅해봤는데 결과는 여전했다.

여전히 먹통이다

시도해 본 해결책 #2: data 폴더 갈아 끼기

$ mysql -u root -p

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)

몇몇의 스택오버플로우 글들은 문제가 되는 저 파일을 지우라고 하는데

나의 경우 저 mysql.sock 파일은 존재도 하지 않는다.

그러다 MySQL의 폴더구조를 보다 보니 data라는 폴더가 보여 각각의 폴더를 찾아봤다.

.
├── LICENSE                  # 라이선스 파일
├── README                   # 읽어보기 파일, MySQL에 대한 기본 정보 제공
├── bin                      # 실행 파일들이 포함된 디렉토리
├── data                     # **MySQL 데이터 디렉토리, 로그 파일과 데이터베이스/테이블 정보 저장**
│   └── mysql                # MySQL 데이터베이스 디렉토리, 관리 데이터베이스 포함
├── docs                     # 문서 파일들이 포함된 디렉토리
├── include                  # 헤더 파일들이 포함된 디렉토리
├── keyring                  # 키링 관련 파일이 포함된 디렉토리
├── lib                      # 라이브러리 파일들이 포함된 디렉토리
├── man                      # 매뉴얼 페이지가 포함된 디렉토리
├── share                    # 다양한 지원 파일들이 포함된 디렉토리
│   ├── charset              # 문자 집합 지원 파일들 포함
│   ├── english              # 영어 언어 오류 메시지 파일들 포함
│   └── [other languages]    # 다른 언어 지원 파일들 포함
└── support-files            # MySQL 지원 파일들이 포함된 디렉토리

[참고] : https://www.ibm.com/docs/en/ztpf/2020?topic=concepts-mysql-directory-structure

별표시를 해둔 data 폴더를 보고 이런 발칙한 생각을 해봤다.

새로 MySQL를 설치하고, data 폴더를 그대로 Override 하면 괜찮지 않을까?

 

혹시나 여기까지만 보고 달려가는 분들 멈춰!!!!

멈춰ㅓㅓㅓㅓㅓ!!

(진짜로,,,)

위에서 발칙한 생각이라고 한 이유가 있다.

1. 데이터 호환성 문제: 다른 DBMS의 파일 형식이 MySQL과 맞지 않아 데이터가 손상될 수 있음.

2. 구성 및 버전 불일치: 백업 환경과 MySQL 환경이 다를 경우 오류나 문제 발생 가능.

3. 보안 문제: 외부 데이터로 인한 보안 취약점 발생 가능성.

4. 잠재적 데이터 손상: MySQL 서버 운영 중 데이터 디렉터리 변경 시 데이터 손상 위험. 수리 및 복구 복잡성: MySQL 복구 도구가 다른 DBMS 데이터에는 효과적이지 않을 수 있음.

[출처] : https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch04s04.html

 

한마디로 정리하면, 그대로 복사했다간 호환성, 보안등등의 이유로 뭔 일이 터질지 모른다는 뜻,,,

그래서 이를 위해서는 보통 mysqldump를 사용해 데이터베이스에서 필요한 정보들을 백업해 둔다.

 

What Is mysqldump? | Pure Storage

What Is mysqldump? | Pure Storage

하지만 mysqldump 도 애초에 작동도 덩달아 되지 않았고,

이것저것 시도하다가 이미 mysql 도 지워버렸다,,,

마지막으로 쓴 해결책 

그냥 새로 갈아엎었다. 다 지우고 새롭게 설치하기,,,

하,, 이거 고치려고 하루를 썼는데,,, 이 방법은 원래 할 줄 알았는데,,,

ㅎㅎ

정리

- 로그 분석을 통한 문제 파악: 로그 파일에서 'ERROR', 'Assertion failure', 'crash' 같은 키워드를 추출하여 문제의 원인을 분석

- 주요 문제 발견: XA 크래시 복구, InnoDB 단언문 실패, 외래 키 오류 등의 문제가 로그 분석을 통해 암

- 실험적 변경의 부작용: 인덱스 변경 시 발생한 경고를 무시하고 진행했던 것이 문제를 야기

- 복구 시도의 실패: InnoDB 복구 모드 설정과 데이터 폴더 교체 등 다양한 복구 시도에도 불구하고 실패

- 최종적인 재설치 결정: 복잡한 문제 해결 시도 끝에 데이터베이스를 새로 설치하기로 결정

 

배운 게 있다면

어째서 RDS를 비싼 돈 주고 쓰는지 배웠다! 가 아니라

데이터베이스가 단일 시점이기에, 다중화 및 백업 시스템을 구축해야겠다는 것이다.

다음글은 이에 대한 글로!