ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • MySQL 테이블 임시 백업 전략
    2022. 7. 25. comments

    임시 백업

    데이터베이스의 테이블 및 데이터 정보를 전체 백업하는 것과 달리 상황에 따라 특정 테이블만 백업하고 싶을 때가 있다. 예를 들어 데이터 마이그레이션을 진행하려고 할 때, postfix로 날짜를 붙여서 별도 테이블로 백업하는 것을 말하는데 전체 백업 및 복구보다 매우 빠르고 쉽게 백업 및 복구를 진행할 수 있기 때문이다. 여기서 백업을 하는 이유는 마이그레이션하는 과정에서 심각한 문제를 발견하게 되었을 때를 고려한 것이다. 백업된 테이블의 내용을 참고하여 잘못된 내용만을 되돌리거나 아예 백업 테이블을 원본 이름으로 변경하여 복구할 수도 있다.

    사용자의 주소 정보가 저장된 가상의 테이블 "users_address"가 있다고 가정해보자.

    가장 쉽게 백업하는 방법은 아래 명령으로 신규 테이블 생성 및 전체 데이터 백업을 진행하는 것이다. 테이블이 자동적으로 생성되며 이어 바로 데이터 백업까지 이어지게 된다.

    create table user_address_20220721 (select * from user_address);

    참고로 PK, FK, Index등은 새롭게 생성된 테이블에 만들어지지는 않는다. 테이블 이름을 user_address_20220721 -> users_address로 다시 되돌리게 될 때 이러한 제약 조건들은 문제가 될 수 있으니 참고하자.

    더 중요한 고려사항이 있는데 해당 "Create Select" 명령이 내려지는 동안 metadata lock이 걸린다는 점이다. 데이터 크기가 크면 클 수록 그 만큼 lock이 오랜 기간 걸릴 수 있기 때문에 자연스럽게 서비스 장애로 이루어질 수 있다.

    그래서 운용 중인 서비스 문제를 최소화 하기 위해 백업 절차는 2가지 단계로 나눠서 진행해야 한다.

    • 백업을 위한 테이블 생성
    • 데이터를 PK 구간별로 복사
    CREATE TABLE user_address_20220721 LIKE user_address;
    INSERT INTO user_address_20220721
    (SELECT * FROM user_address WHERE id >= 1 and id < 10000);

     

    See also

    https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/

     

    SQL - "CREATE TABLE AS SELECT" Statement - Percona Database Performance Blog

    The SQL "create table as select" statement is used to create a normal or temporary table and materialize the result of the select. Here's why you should avoid it.

    www.percona.com

     

    반응형

    댓글

Designed by Tistory.