[MySQL] DB 관리를 위한 바이너리 로그(binlog) 활용하기

작성일: 2023-02-24 (금)

오늘의 주제는 “mysqlbinlog” 입니다.

mysqlbinlog는 MySQL에서 binary log 를 통해 데이터를 추적하거나, 복구할 수 있도록
제공하는 유틸리티 도구입니다.

앞서 MySQL 바이너리 로그에 대해서 다뤄 보았는데요,
오늘은 바이너리 로그를 활용해 데이터를 복구하는 작업을 다뤄보려고 합니다.

MySQL 바이너리 로그 파일을 이용한 데이터 복구

• 바이너리 로그는 DDL & DML 명령문을 통해 데이터의 변화가 발생할 경우
  해당 이벤트를 기록하는 로그 파일
• 바이너리 로그의 내용을 기반으로 데이터 복제(Replication) 또는 데이터 복원(Recovery) 작업 가능
• 바이너리 형태로 기록되기 때문에 일반 편집기를 통해서는 내용 확인하기 어려우며,
  mysqlbinlog 유틸리티를 통해 확인 가능

모든 바이너리 로그 파일을 mysqlbinlog 유틸리티 도구를 통해 추출할 수 있으며, 특정 바이너리 로그 파일만 추출할 수 있습니다.

mysqlbinlog 유틸리티 사용법

• 모든 바이너리 로그 파일 추출

  # mysqlbinlog /var/lib/mysql/binlog.0* > /home/test/all_data.sql

• 특정 바이너리 로그 파일 추출

  # mysqlbinlog /var/lib/mysql/binlog.000001 > /home/test/bin_000005.sql

• 주요 옵션

  | —database=[DB_NAME] | -d | **특정 데이터베이스의 내용만을 출력** |
  | --- | --- | --- |
  | —short-form | -s | 일반 쿼리만 출력, 모든 정보 출력을 원하지 않을 때 사용 |
  | —start-datetime=”YYYY-MM-DD hh:mm:ss” |  | 지정한 시간부터 binlog 추출 |
  | —stop-datetime=”YYYY-MM-DD hh:mm:ss” |  | 지정한 시간까지 binlog 추출 |
  1. 모든 DB 복구
      
      ```bash
      # mysqlbinlog /var/lib/mysql/binlog.0* > /home/test/all_data.sql
      # mysql -u root -p < /home/test/all_data.sql
      ```
      
  2. 특정 DB 복구
      
      ```bash
      # mysqlbinlog /var/lib/mysql/binlog.0* -d mydb > /home/test/mydb.sql
      # mysql -u root -p < /home/test/mydb.sql
      ```
      
  3. 특정 시점 복구
      
      ```bash
      # mysqlbinlog /var/lib/mysql/binlog.0* \
        —-start-datetime="2023-02-01 00:00:00" \
        --stop-datetime="2023-03-01 00:00:00" \
       > /home/test/2023-02.sql
      # mysql -u root -p < /home/test/2023-02.sql
      ```

  💡 작업 실수로 인해 특정 데이터가 변경되거나 지워질 경우, mysqlbinlog 유틸리티 도구를 통해서 해당 데이터를 추적하고, 복구하여 데이터를 원복할 수 있기 때문에 mysqlbinlog 유틸리티는 꼭 알아두면 좋은 소프트웨어입니다.