[DB] MySQL 백업 & 복원 (docker MySQL 컨테이너 사용)

최근에 기존 서버에 있던 DB를 다른 서버로 이전하는 작업을 진행했다.
기존 DB를 그대로 백업해서 새로운 서버에 복원하는 과정들을 기록해본다.

사용 환경

• docker MySQL 컨테이너 사용 (mysql:8.0.17)
• 기존 서버, 새로운 서버 모두 docker 컨테이너 사용

DB 백업

1. docker에서 MySQL 컨테이너 bash 셸을 실행한다.

   docker exec -it [mysql 컨테이너 이름] /bin/bash
   
   docker exec -it mysql_db /bin/bash

2. DB를 백업한다.(mysqldump 사용)

   • DB schema(구조) + 데이터 백업

     $ mysqldump -u 유저이름 -p [원본 DB명] > [백업 DB명].sql
     
     $ mysqldump -u root -p db_name > db_name_backup.sql

   • DB schema(구조) + 데이터 + 프로시저 + 함수 + 트리거 백업

     $ mysqldump [옵션] -u 유저이름 -p [원본 DB명] > [백업 DB명].sql
     
     $ mysqldump --routines --triggers -u root-p db_name > db_name_backup.sql

   • mysqldump 옵션
     • --routines : procedure, function 백업
     • --triggers : trigger 백업
     • -no-data : 데이터 백업 제외
     • -no-create-info : create SQL 문 백업 제외
     • --add-drop-table : 기존 테이블 삭제 후 백업 테이블 생성
   bash 셸에서 `mysqldump` 를 입력하고, 비밀번호를 입력하면 [백업 DB명]으로 백업파일이 만들어진다. `ls` 로 검색해보면, [백업 DB명]으로 백업파일이 만들어진 것을 확인할 수 있다.

   처음에 다른 옵션 없이 mysqldump만 사용해서 복원했는데, 프로젝트를 실행하니 오류가 발생했다.
   DB에 프로시저, 트리거가 옮겨지지 않아 발생한 문제였다.
   기존 사용 서버를 다른 서버로 이전해서 프로그램을 그대로 동작시키려면 프로시저, 트리거도 백업되도록 옵션을 추가해서 백업해야 한다.

   만약 DB명을 모른다면, mysql로 접속해서 database명을 검색하면 된다.

   • docker container MySQL 접속

     docker exec -it [mysql 컨테이너 이름] mysql -u root -p
     
     docker exec -it mysql_db mysql -u root -p

   • database명 조회

     show databases;

     • 조회되는 database 중 백업할 DB명을 찾으면 된다.

DB 복원

1. 백업 파일을 복원할 서버의 db 컨테이너 위치로 옮긴다.

   • docker mysql container에 있는 백업 파일을 docker가 설치된 기존 서버로 복사한다.

     docker cp [컨테이너 이름]:[파일 위치] [복사하려는 경로]
     
     docker cp mysql_db:/backup231103.sql /home/user/

   • 복사한 경로로 들어가 파일을 로컬로 다운로드 받은 후, 복원할 서버로 파일을 옮겨준다. (putty, mobaXterm 등 사용)
   • 복원 서버에 가져온 백업 파일을 mysql 컨테이너로 옮긴다.

     docker cp /home/user/backup231103.sql mysql2_db:/

2. 백업 파일을 사용해서 DB를 복원한다. 이 때, 복원할 DB에 [복원할 DB명]으로 database가 존재한 상태여야 한다.

   • docker mysql container에 bash 셸로 접속 후, 다음 명령을 입력한다.

     mysql -u 유저이름 -p [복원할 DB명] < [백업 DB명].sql
     
     mysql -u root -p db_name2 < backup231103.sql

   복원 후 DB에 접속하여 확인해보면 백업한 데이터베이스가 그대로 옮겨진 것을 확인할 수 있다.