MySQL 데이터를 Dump(Export)하고 Import 하기

“명령어가 잘 기억이 안 나시죠? 😅 저도 그렇습니다.”

MySQL 데이터를 다른 MySQL 인스턴스로 옮겨야 할 때가 있습니다.

예를 들면,

  • 운영 환경 DB 데이터를 개발 환경의 DB로 옮길 때
  • 온-프레미스로 운영하던 DB를 AWS RDS 같은 클라우드 서비스로 옮길 때
  • 데이터 백업, 아카이빙
  • MySQL 다시 설치

이 때 데이터 덤프와 임포트 작업이 필요합니다.

그런데 자주 하는 일이 아니라서, 어느날 막상 작업을 하려고 하면 어떻게 해야 하는지 잘 기억이 나지 않습니다.

  • 어떤 도구를 사용하는지, 명령어가 무엇이었는지
  • 파라미터를 어떻게 입력해야 하는지(--user, --host, …)
  • 각 상황(로컬인지 원격인지, 데이터만 옮기는지 구조도 옮기는지 등)별 요령

마침 제가 이 작업을 할 일이 생겼습니다. 역시 잘 기억이 나지 않아서 다시 인터넷을 찾아봐야 했습니다. 😅

이 사례를 가지고 MySQL 데이터 덤프와 임포트를 하기 위한 절차와 방법을 알아보겠습니다.

예시: 자주 사용하는 방법 (바쁘신 분들을 위해)

특정 데이터베이스의 스키마, 데이터, 이벤트, 스토어드 프로시저, 트리거를 모두 반출하고, 다시 반입하는 명령입니다.

# Dump
$ mysqldump \
  --host=${SOURCE_DB_HOST} \
  --user=${SOURCE_DB_USER} --password \
  --default-character-set=utf8mb4 --set-charset \
  --compact --extended-insert --quick --hex-blob \
  --single-transaction \
  --events --routines --triggers \
  --databases ${SOURCE_DB_NAME} > dump.sql

# Import
$ mysql \
  --host=${TARGET_DB_HOST} \
  --user=${TARGET_DB_USER} --password \
  ${TARGET_DB_NAME} < dump.sql

위의 예제에서 사용된 옵션이나 기타 옵션의 기능을 더 자세히 알아보려면 아래를 참조하십시오.

도구: mysql-client

MySQL의 데이터 덤프를 위한 도구는 많이 있습니다.

  • MySQL Workbench (GUI)
  • 3rd party DB 도구 (Toad, Tadpole, DBeaver, …)
  • mysql-client (CLI)

그런 공식 클라이언트인 MySQL Workbench를 쓰더라도 내부적으로는 mysql-client의 mysqldump 명령을 사용합니다. 그리고 3rd party 도구 중 일부도 역시 mysql-client에 의존합니다.

그래서 가장 일반적인 방법인 mysqldump 명령을 사용하는 방법을 알려드립니다.

mysqldump 명령을 사용하려면 우선 mysql-client를 설치해야 합니다.

  • macOS 
    $ brew install mysql-client
  • Linux 
    # Ubuntu 14
$ apt install mysql-client-core

# Ubuntu 16
$ apt install mysql-client
  • Windows (우분투 설치하고 😅 그럼 리눅스잖아…) 
    $ apt install mysql-client

설치가 잘 되었는지 확인해봅니다. (CLI로 DB 접속)

$ mysql --host=${DB_HOST} --user=${DB_USERNAME} --password

CASE STUDY: SSH로 원격 접속하여 MySQL Dump/Import 하기

다른 블로그에는 대개 로컬 환경을 가정하고 설명해놓은 내용이 많습니다.

그런데 SSH를 통해 원격 접속을 해서 mysqldump를 사용하려면 어떻게 해야 할까요?

AWS EC2 내부에 설치된 MySQL 데이터를 별도의 AWS RDS MySQL로 옮긴다고 가정해보겠습니다.

보통 MySQL 서버는 애플리케이션 서버에서만 접근할 수 있도록 제한되어 있으며, 방화벽 외부에서는 접근을 할 수 없습니다. 그래서 SSH를 통해서 애플리케이션 서버로 접속한 상태에서 mysql-client를 사용해야 합니다.

이 때 접속하는 SSH 서버에 mysql-client가 반드시 설치되어 있어야 합니다.

$ ssh -i ${SOURCE_PRIVATE_KEY_FILE_PATH} ${SOURCE_SSH_USERNAME}@${SOURCE_SSH_HOST} \
  "mysqldump \
    --user=${SOURCE_DB_HOST} --password \
    --databases ${SOURCE_DB_NAME}" > dump.sql

이렇게 하면 결국 dump.sql 파일이 로컬 경로에 원격으로 리다이렉션 되어 저장됩니다.

이제 SSH을 통해 원격으로 연결해서 데이터를 import 해보겠습니다.

$ ssh -i ${TARGET_PRIVATE_KEY_FILE_PATH} ${TARGET_SSH_USERNAME}@${TARGET_SSH_HOST} \
  "mysql \
    --host=${TARGET_DB_HOST}
    --user=${TARGET_DB_USERNAME} --password \
    ${TARGET_DB_NAME}" < dump.sql

마찬가지로 이전에 덤프해놓은 dump.sql 파일을 원격으로 리다이렉션해서 import 합니다.

주의할 점은, SSH를 통해서 접속할 경우 네트워크 지연으로 시간이 오래 걸릴 수 있습니다. 가능하면 대상 MySQL 인스턴스와 물리적으로 가까운 호스트에서 Dump, Import를 하는 것이 좋습니다.

SSH를 통한 mysql, mysqldump 명령어 사용 방법은 아래에서 참조하였습니다.

References