배경
서비스를 운영하다 보면 개발 DB에서 운영 DB의 데이터를 필요로 하거나, 또는 특수한 업무 상황에서 데이터 전체 또는 특정 데이터가 보존 되어야 할 때가 있다.
실제로 업무에서 위와 같은 상황이 발생했고 어떤 식으로 문제를 해결할지에 대해서 고민하다가 DB dump를 만들어서 import 하는 방식으로 해결하기로 했다.
그 결과 운영 환경에서만 발생하는 데이터에 대해서 추가적인 관리 없이 깔끔하게 개발 DB로 동기화하여 사용하는 중이다.
mysql부터 shellscript, crontab 까지 무려 3가지 콤비네이션 기술이 합쳐진 ! DB 관리 방법을 알아보자 !
아래의 모든 내용은 필요한 환경 변수를 export 했다는 가정 하에 작성되었습니다.
1. mysqldump
우선 운영 DB의 필요한 데이터를 백업 하기 위해서 mysqldump를 알아야 한다.
mysqldump 명령을 통해 개발자는 대상이 되는 DB에서 동일한 데이터를 저장할 수 있는 SQL파일을 만들 수 있다. 이때의 SQL파일을 dump 파일이라고도 부르며, 전체 DB, 특정 테이블, 특정 row까지 사용자의 입맛에 따라 dump 파일을 만들 수 있다.
DB 전체 dump 파일 만들기
mysqldump -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME > ./dump.sql위의 명령어를 통해 우리는 현재 디렉토리에 dump.sql이라는 dump 파일을 만들 수 있다.
몇몇 옵션에 대해서 살펴 보자.
-
-u : DB 유저명
-
-p : DB 패스워드
- 옵션 뒤에 패스워드를 입력하지 않는다면 명령문 실행 후 CLI에 패스워드를 입력해야 한다.
-
-h : DB 호스트
- 로컬 환경 DB를 사용할 경우는 필요하지 않다.
-
-v : SQL 출력
- 화면에 실행되는 모든 쿼리를 출력한다.
- 처리 과정을 볼 수 있어서 좋으나 처리 속도에 어느정도 영향을 끼침
위의 옵션만으로도 dump 파일을 만들 수는 있다.
하지만 실제로 DB 전체에 대해서 dump를 만들고 import 하는 것은 꽤나 무식한 방법이다.
개발자가 원하는 데이터 dump만 만드는 법을 알아보자.
스키마 제외 하고 dump 파일 만들기
mysqldump는 default로 DB의 스키마 정보가지 dump로 만들어준다.
import할 DB에 해당 테이블이 없다면 도움이 되겠지만 이미 존재하는 테이블이라면 불필요한 작업이 될 확률이 높다.
이 때 --no-create-info 옵션을 사용한다면 스키마를 생성하는 (CREATE) 쿼리를 제외하고 오직 데이터를 저장하는 (INSERT) 쿼리만을 dump 파일에 저장한다.
mysqldump -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME --no-create-info > ./dump.sql
특정 테이블만 dump 파일 만들기
그저 그냥 테이블 이름만 추가해준다면 된다.
mysqldump -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME <table> --no-create-info > ./dump.sql특정 row만 dump 파일 만들기
--where="<조건>" 옵션을 사용하여 조건에 맞는 row만을 추출하여 dump 파일을 만들 수 있다.
아래의 예시코드와 같이 큰 따옴표와 작은 따옴표를 잘 구분하여 사용해야 한다.
mysqldump -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME <table> --where="name = 'jungho'" --no-create-info > ./dump.sql2. Truncate
dump 파일을 사용해 데이터를 저장하기 위해선 저장 될 DB의 상태 또한 점검해야 한다.
만약 특정 조건의 데이터가 누적되어 쌓이는 테이블이라면 TRUNCATE 할 필요 없이 Insert만 되면 되지만 전체 데이터에 대해서 새로이 저장될 필요가 있다면 TRUNCATE 해줄 필요가 있다.
스키마를 포함하는 dump를 만들어서 DROP 테이블을 하는 방법도 있지만 스키마의 변화가 없다면 굳이 그럴 필요가 있을까 싶다.
주의해야 할 점은 순서상 TRUNCATE가 나중에 와야 dump 파일 없이 데이터만 삭제되는 울고 싶은 상황을 방지 할 수 있다.
mysql -u $DB_USER -h $DB_HOST $DB_NAME -p$DEV_DB_PW -e "TRUNCATE TABLE $table"3. Import
이제 dump 파일도 만들었고 데이터를 저장할 공간도 만들었으니 mysql 명령문을 통해 dump sql 파일을 import 해주기만 하면 된다.
mysql -u $DB_USER -p$_DB_PW -h $DB_HOST $DB_NAME < ./dump.sql주의 할 점은 mysqldump가 아니라 mysql 명령문이라는 것이다.
나 같은 바보가 흔하지 않겠지만 나는 mysqldump로 명령문을 실행하여 1시간 넘게 삽질 했었다...
그리고 화살표의 방향에도 주의하자 !
dump를 만들 때는 > dump를 import 할 때는 < 방향이다.
ShellScript Example
위의 순서에 맞춰 간단한 쉘 스크립트를 만들어보자.
실무에서 쓰기엔 많이 아쉬운 코드지만 최대한 간단하게 만들어 보겠다.
#!/bin/bash
# 혹시라도 존재할 이전 dump 파일 삭제
rm ./dump.sql
table=<table>
mysqldump -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME $table --no-create-info > ./dump.sql
# 덤프가 실패할 경우 TRUNCATE 되는 것을 방지 하기 위한 예외처리
if [ $? -eq 0 ];
then
echo "Create dump complete"
else
log_date=$(date +%y-%m-%d_%T)
echo "$log_date Create $table dump fail" >> ./dump.log
exit 9
fi
# 'NO_AUTO_CREATE_USER' 에러를 방지하기 위한 SET 쿼리 삭제
sed -i '/^SET/d' $DUMP_FILE
mysql -u $DB_USER -h $DB_HOST $DB_NAME -p$DB_PW -e "TRUNCATE TABLE $table"
mysql -u $DB_USER -p$DB_PW -h $DB_HOST $DB_NAME < ./dump.sql
