[MySQL] mysqldump로 DB 마이그레이션하기

이동엽·2024년 5월 25일
2

DB

목록 보기
1/1
post-thumbnail

1. 개요

현재 사내에서 상용 DB는 AWS RDS를 이용하고 있으며, 주기적으로 백업을 진행하고 있다.

다만, 개발 환경의 DB는 온프레미스(구 개발) 서버에서 직접 설치하여 실행 중이며, 백업을 하고 있지 않음.

따라서, 이를 온프레미스(신 개발) 서버로 이전하는 작업을 진행해야 할 필요도 있었습니다.

이 글은 mysqldump를 이용해 데이터를 백업하고, 생성된 백업본으로 새로운 환경에 DB를 구축하는 마이그레이션 과정을 담았습니다.


1-1. 목표

목표는 구 개발서버의 MySQL5.7을 신 개발 서버로 옮기는 것이다
이때, 신 개발 서버에서는 Docker로 MySQL 5.7을 구동한다.

추후 신 개발서버의 MySQL 5.7이 안정화가 되면, MySQL 8.0으로 마이그레이션을 진행할 예정
→ AWS RDS의 백업 정책 부터 알아보자.


2. AWS RDS의 백업 정책

  1. 자동 백업 활성화시, 매일 자동으로 데이터에 대한 스냅샷을 생성 & 트랜잭션 로그를 캡쳐
    1. ex) snapshot-2024-05-10-1538 이라는 이름으로 생성.
  2. 전체 DB 인스턴스 백업
  3. 보존기간 내 언제든지 특정 시점으로 복구(PITR) 수행이 가능
    1. RDS 복원은 기존 RDS 인스턴스의 데이터가 복구되는 것이 아니라,
      복구 시점의 데이터를 바탕으로 새로운 RDS 인스턴스를 생성하는 것임.

3. MySQL(5.7) 백업 방법

3-1. 백업 종류

  • mysqldump 명령어 이용
  • mysqlhotcopy : 5.7버전에서부터는 제거
  • xtrabackup : 별도 설치 필요

→ mysqldump 이용해보자.


3-2. 백업 문법

mysqldump  [--옵션] --all-databases
mysqldump  [--옵션] --databases db_name
mysqldump  [--옵션] --databases db_name --tables table_name

3-3. 백업 옵션

  • -all-database(-A) : mysql와 모든 사용자 데이타베이스
  • -database(-B) : 덤프할 데이타베이스 지정
  • -tables : 데이타베이스에서 특정 테이블만 덤프
  • -flush-logs(-F) : 덤프 실행전 바이너리 로그 switch(새로운 바이너리 로그 파일 생성), --all-databases 옵션과 함께 사용할 경우 각 데이타베이스 백업마다 flush가 일어난다 예로 3개의 데이타베이스가 있고, 현재 바이너리 로그 파일이 bin-log.000004일 경우 이 옵션을 사용하여 백업하면 다음 바이너리 로그 파일은 bin-log.000007이 된다. 예외적으로 --single-transaction 과 함께 사용될 경우는 데이타베이스 수와 상관 없이 한번만 flush가 일어난다.
  • -single-transaction : 데이타베이스 일관성 유지를 위해 mysqldump 실행전 서버에 START_TRANSACTION SQL 문을 보낸다. (원문)This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data

3-4. 예시 코드

# 전체 데이타 베이스 백업
mysqldump -u <username> -p<PASSWORD> -F --single-transaction --all-databases > alldatabase.sql

# 특정 데이타 베이스(testdb) 백업
mysqldump -u <username> -p<PASSWORD> -F --single-transaction --databases testdb > testdb.sql

# 특정 테이블(t1) 백업
mysqldump -u <username> -p<PASSWORD> --databases db_name --tables t1 > t1.sql

# 특정 테이블(t1)의 **구조만** 백업
mysqldump  -u <username> -p<PASSWORD> --no-data <데이터베이스명> > <백업파일명>.sql

# 데이타베이스 복구
mysql -u root -p PASSWORD < testdb.sql

3-5. 바이너리로그를 이용한 PIT(Point to Time) 복구

덤프 백업 후 생성된 바이너리 로그 파일 확인 (ls -l 로 확인) 후에

덤프 후 생성된 바이너리 파일 2개 이상일 경우에 각각 바이너리 로그 파일마다 mysqlbinlog를 해줘야 한다.

# 현재 바이너리로그 파일 확인
mysql> show master status ;

+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| testsvr-bin.000031 |     7443 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 장애 발생시
mysql> drop database testdb ;

# 바이너리 로그 파일 변환
shell> mysqlbinlog testsvr-bin.000031 > binlog.sql

# 데이터베이스 복구
shell> mysql -u root -pPASSWORD < testdb.sql

# 바이너리 로그 파일 복원
shell> mysql -u root -pPASSWORD < binlog.sql

4. 실제 백업 해보기

실제 구 개발 서버의 경우는 MySQL을 직접 설치하여 사용 중

4-1. 구 개발서버 접속

$ ssh ~~~~~~~~~~ # 접속 내용

> password : {password}

4-2. mysqldump를 이용한 백업

각 DB별로 백업할 경우

#20240519_TestDB.sql 파일 생성
$ mysqldump -u diabuser -p --single-transaction --databases TestDB > /etc/mysql/backup/$(date +%Y%m%d)_TestDB.sql

> password : {password}

DB 전체 한번에 백업할 경우

$ mysqldump -u diabuser -p --single-transaction --all-databases > /etc/mysql/backup/$(date +%Y%m%d)_AllDevDB.sql

> password : {password}

백업 생성 여부 확인

$ cd /etc/mysql/backup
$ ls -al

-rw-rw-r-- 1 mars mars 190M  5월 21 17:31 20240521_AllDevDB.sql
-rw-rw-r-- 1 mars mars 163M  5월 21 17:19 20240521_TestDB.sql

4-3. 백업 파일을 신 개발서버로 이동 후 복원

scp 명령어 혹은 FTP 등을 이용하여 각자 편한 방식을 이용.
보안상 이 내용은 기재하지 않음.


4-4. 신 개발서버에 Docker(MySQL) 환경 구성

디렉토리 생성

# 신개발 서버 접속 후
$ mkdir /test/docker/mysql57        # mysql 관련 설정/데이터 파일들이 위치하도록
$ mkdir /test/docker/mysql57/backup # 백업 sql 파일들이 위치하도록

/test/docker/mysql57/docker-compose.yml 작성

version: '3'
services:
  mysql:
    image: mysql:5.7
    container_name: mysql-container
    restart: always
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD={password} # root 계정의 password
      - TZ=Asia/Seoul
    command:
      - --character-set-server=utf8mb4
    volumes:
      - ./db/mysql/data:/var/lib/mysql
      - ./db/conf.d:/test/docker/mysql57/conf.d
      - ./:/test/docker/mysql57

docker 실행

# docker-compose.yml을 작성한 장소(/test/docker/mysql57)에서 실행
$ sudo docker compose up -d # -d: 백그라운드 실행 옵션

# 실행 확인
$ sudo docker ps

4-5. 첫 접속시, 사용자 및 권한 설정이 필요

아래 내용은 기존 구 개발 서버에 위치하던 사용자 권한/정보와 동일하게 구성합니다.

# docker(mysql-container)의 bash 접속
$ sudo docker exec -it mysql-container bash

# 첫 실행 시, 루트 계정만 존재
# 암호는 docker-compose.yml에서 작성했던 내용
bash> mysql -u root -p
> password : {password}

# 사용자 생성 (localhost -> 내부 접근을 허용)
mysql> CREATE USER '{NEW_USER}'@'localhost' identified by '{NEW_PASSWORD}';

# 모든 권한 추가 예시. (권장하지 않음.)
mysql> GRANT ALL ON *.* to {NEW_USER}@localhost;

4-6. 백업본으로 복원하기

# docker(mysql-container) bash 접속
$ sudo docker exec -it mysql-container bash 

# 특정 DB만 백업하고 싶을 경우
bash> mysql -u {NEW_USER} -p DiabDB < /test/docker/mysql57/backup/20240521_testDB.sql

# 전체 DB 백업하고 싶을 경우
bash> mysql -u {NEW_USER} -p < /test/docker/mysql57/backup/20240521_AllDevDB.sql

password : {NEW_PASSWORD}

이후, DB에 접속하여 최근 데이터까지 제대로 복원이 되었는지 확인 필요.


5. 주기적으로 동작하도록 자동화하기

5-1. 백업 스크립트 작성

백업 파일을 주기적으로 생성하고, 오래된(30일이 지난) 파일들을 지우는 스크립트 작성

mysql57backup.sh 내용

#!/bin/bash

# MySQL 사용자 이름 및 비밀번호
USER="{NEW_USER}"
PASSWORD="{NEW_PASSWORd}"

# 백업 저장 경로
BACKUP_DIR="/test/docker/mysql57/backup"

# 각 DB별로 백업 파일 이름
TEST_BACKUP_FILE="$BACKUP_DIR/$(date +%Y%m%d)_TestDB.sql"

# 각 DB별로 백업 명령 실행
mysqldump -u $USER -p$PASSWORD --single-transaction --databases TestDB > $TEST_BACKUP_FILE

# DB 전체를 한번에 백업할 경우
# ALLDEVDB_BACKUP_FILE="$BACKUP_DIR/$(date +%Y%m%d)_AllDevDB.sql"
# mysqldump -u $USER -p$PASSWORD --single-transaction --all-databases > $ALLDEVDB_BACKUP_FILE

# mysqldump 명령어가 오래 걸릴 수 있으니, 바로 실행되지 않게 10초 대기
sleep 10

# 각 DB 파일별로 최근 3개 버전만 남기고 나머지를 삭제
ls -t $BACKUP_DIR/*TestDB.sql | tail -n +4 | xargs rm -f

실행 권한 부여

$ chmod +x /test/docker/mysql57/mysql57backup.sh

run_db_backup.sh

현재 신개발 서버에서 사용하는 계정은 루트 계정이 아니라, docker 명령어를 사용하기 위해 sudo를 사용해야 한다.

크론 탭에 sudo를 사용한 명령어를 입력하는 것은 좋지 못하니, sudo로 mysql57backup.sh 를 호출하는 스크립트를 작성하자.

#!/bin/bash

###
# NEW_USER 계정은 docker 명령어에 대한 권한이 없기 때문에, root로 호출해야 함.
# crontab에 작업 등록시 root로 등록하여야 함.
###
docker exec -i mysql-container sh /test/docker/mysql57/mysql57backup.sh

5-2. 신개발서버에 크론 탭 등록

$ crontab -e

# 1주 간격으로 실행
# 0시 0분 7일간격 * *
# -> 첫 실행 : 크론이 등록된 후 7일 뒤
0 0 */7 * * /test/docker/mysql57/run_db_backup.sh

등록된 크론탭 확인

$ crontab -l

크론 정보 수정시 반드시 재시작해주어야함

$ sudo service cron restart

# 이외 cron 명령어들
$ sudo service cron status
$ sudo service cron start
$ sudo service cron stop

5-3. mysqldump 백업 파일 생성 여부 모니터링용 슬랙 알림 추가

아래이 경우 슬랙 알림이 오도록 추가했다.

  • 백업 파일이 생성되지 않거나
  • 크기가 0인 파일이 생성될 경우

run_db_backup.sh

#!/bin/bash

# Slack 알림 함수
send_slack_notification() {
    local message="$1"
    curl -X POST -d "text=$1" -d "channel={슬랙 채널명}"  \
    -H "Authorization: Bearer {슬랙 API 토큰}" \
    https://slack.com/api/chat.postMessage
}

# 파일 존재 및 크기 확인 함수
validate_mysql_backup_file() {
    local file=$1
    if [ ! -e "$file" ]; then
        send_slack_notification "dev::: MySQL backup fail - file does not created!"
        return 1
    elif [ ! -s "$file" ]; then
        send_slack_notification "dev::: MySQL backup fail - file is empty!"
        return 1
    else
        return 0
    fi
}

# 신 개발서버 백업 저장 경로
# BACKUP_DIR="/test/docker/mysql57/backup"

# 백업 파일 이름
TEST_BACKUP_FILE="$BACKUP_DIR/$(date +%Y%m%d)_TestDB.sql"

# 신 개발서버 백업 실행 명령
# docker exec -i mysql-container sh /test/docker/mysql57/mysql57backup.sh
sleep 15

# 백업 파일이 제대로 생성 되었을 때만 각 DB 파일별로 최근 3개 버전만 남기고 나머지를 삭제

if  validate_mysql_backup_file "$TEST_BACKUP_FILE" &&
    validate_mysql_backup_file "${이외 다른 DB 백업1}"; then

    ls -t $BACKUP_DIR/*TestDB.sql | tail -n +4 | xargs rm -f
    ls -t $BACKUP_DIR/*{다른}DB.sql | tail -n +4 | xargs rm -f
    send_slack_notification "dev::: MySQL backup success"
fi

6. 참고자료

profile
이동엽
백엔드 개발자로 등 따숩고 배 부르게 되는 그 날까지

0개의 댓글