MySQL dump 백업

## 테이블 단위 백업

$ mysqldump -u [계정명] -p [database이름] [테이블이름] > [백업하고자하는파일].sql
$ mysqldump -u root -p employee professor > backup_tab_pro.sql

-- 복구시
$ mysql -u 계정명 -p [database이름] < [백업한파일].sql
$ mysql -u root -p employee < backup_tab_pro.sql

 

## DB 단위 백업

-- 원하는 Database만 백업 하는 경우
$ mysqldump -u [계정명] -p [database이름] > [백업하고자하는파일].sql
$ mysqldump -u root -p employee > backup_employee.sql

-- 테이블 드롭진행

-- 복구시
$ mysql -u 계정명 -p [database이름] < [백업한파일].sql
$ mysql -u root -p employee < backup_employee.sql

 

## 전체 백업

-- Database 전체 백업
mysqldump --all-databases -u [계정명] -p > [백업하고자하는파일].sql

-- 복구시
mysql -u [계정명] -p < all.sql
mysql -u root -p < all.sql

※ 복구시 데이터베이스는 생성되어 있어야한다. 아니면 에러반환

 

 

 

## 옵션

--add-drop-database : 기존 데이터베이스가 있으면 drop 후 새로 생성

--add-drop-table : 기존 테이블이 있으면 drop 후 새로 생성

--add-drop-trigger : 기존 트리거가 있으면 drop 후 새로 생성

--no-data : 데이터를 제외하고 구조만 백업
--set-charset : charset을 뭘로 할지 결정

 

옵션에 대한 상세정보는 아래 공식홈페이지 참조.

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

 

 

## gzip 이용

만약 백업 파일 용량이 클경우 gzip이용 가능

mysqldump --all-databases -u [계정명] -p | gzip  > [백업하고자하는파일].sql

---

 

주의사항

 

 

1. 백업 파일 크기:
   • 백업 파일이 큰 경우, 파일 전송이나 저장 과정에서 문제가 발생할 수 있습니다. 이때는 gzip을 사용하여 백업 파일을 압축하면 디스크 공간을 절약할 수 있습니다.
   • 예시:복구 시에는 압축을 해제하고 실행합니다:
   • gunzip < backup_employee.sql.gz | mysql -u root -p employee
   • mysqldump -u root -p employee | gzip > backup_employee.sql.gz
2. 백업 시 데이터 무결성:
   • 백업 도중 데이터가 변경되는 것을 방지하기 위해 백업 전에 READ LOCK을 설정하여 데이터베이스의 일관성을 보장할 수 있습니다.
   • --single-transaction 옵션을 사용하면 트랜잭션을 활용해 백업 중에도 데이터베이스를 사용 가능하게 하면서도 무결성을 유지할 수 있습니다.
   • 예시:
     mysqldump --single-transaction -u root -p employee > backup_employee.sql
3. 복구 시 데이터베이스가 이미 존재하는 경우:
   • 백업 파일을 복구할 때 데이터베이스가 이미 존재하면 충돌이 발생할 수 있습니다. 이를 방지하기 위해 백업 시 --add-drop-database 또는 --add-drop-table 옵션을 사용하여 기존 데이터베이스나 테이블을 삭제한 후 복구할 수 있습니다.
   • 예시:
     mysqldump --add-drop-database -u root -p employee > backup_employee.sql
4. Foreign Key Constraints 비활성화:
   • 복구 과정에서 외래 키 제약 조건(Foreign Key Constraints)이 있을 경우 복구에 실패할 수 있습니다. 이를 방지하기 위해 외래 키 제약을 일시적으로 비활성화한 후 복구하고, 복구 완료 후 다시 활성화하는 방법을 사용할 수 있습니다.
   • 예시:
      

     SET foreign_key_checks = 0; source backup_employee.sql; SET foreign_key_checks = 1;
5. 백업 주기 및 보안:
   • 백업 파일은 민감한 정보를 포함할 수 있으므로 암호화 및 주기적인 백업 관리가 필요합니다. 백업 파일의 접근 권한 설정과 함께, 주기적으로 백업 스크립트를 실행하여 데이터 손실에 대비할 수 있습니다.