MySQL 백업, 복원 비교 (xtrabackup/mysqldump)

MySQL 백업/복원

---

방식특징장점/단점

핫 백업 (Hot Backup/Open Backup)	DB 서버를 온라인 상태로 유지한 채 데이터 백업	- 데이터베이스 서버를 중지하지 않고 백업 가능 - 트랜잭션, 스냅샷, 로그 등을 이용하여 실시간 백업 - Archive Log 모드에서만 백업을 수행할 수 있음 - 디스크 용량이 추가로 요구될 수 있음 - 핫 백업 도구를 사용할 때의 비용이 발생할 수 있음
콜드 백업 (Cold Backup/Close Backup)	DB 서버를 중지한 후 데이터 백업	- 쉬운 백업이 가능함 - 데이터 백업을 요청한 시기 이후의 데이터는 백업할 수 없음 (따라서 일부 데이터 손실이 발생할 수 있음)
논리 백업 (Logical Backup)	각 오브젝트를 SQL문 등으로 저장	- 데이터의 쉬운 검토 가능 - 데이터 백원 및 복원 시의 안정성을 증가시킴 - 백업과 복원 속도가 느리고 작업 시 시스템 자원을 많이 차지하게 됨 - 다른 PC(서버)간의 데이터 이전이 용이함
물리 백업 (Physical Backup)	파일 자체를 그대로 백업	- 빠른 속도의 백업과 복원 - 디스크 용량을 크게 차지함 - 문제 발생에 대한 파악과 검토가 어려움 - 다른 PC(서버)간의 데이터 이전이 어려울 수 있음

 

작업 방식에 따른 소요 시간 비교

---

동일 장비 약 40G dbfile / xtrabackup2.4 기준

백업복원복원 상세

mysqldump	1시간 3분	1시간 30분	-
xtrabackup	37분	15분	xtream: 3분 decompress : 4분 prepare : 2분 file copy : 2분

• 백업 기준 약 2배/ 복원 기준 약 6배 차이 발생
• 순수 백업 및 복원 시간 기준 (준비 및 검증 소요 시간 제외)

 

mysqldump (Hot Logical Backup)

---

• 바이너리 : mysql 5.7 기준
• 백업
  • mysqldump --host=호스트명--port=포트번호 --user=유저명 --password=비밀번호 --single-transaction --routine --all-databases --add-drop-table | gzip  > /data/backup/백업파일명.tar.gz
    
     

• 복원
  • mysql -u계정 -p --execute="source 백업파일명.sql"
• 적용 대상
  • MySQL 버전 업그레이드가 필요한 경우
  • 데이터 파일 사이즈가 작을 경우
  • 기타 논리적인 백업이 필요할 경우

 

xtrabackup (Hot Physical Backup)

---

• 바이너리 : percona-xtrabackup-2.4.13 기준
• 백업 

  • xbstream (https://www.percona.com/doc/percona-xtrabackup/2.4/xbstream/xbstream.html)

    • 동시 압축과 스트리밍을 지원하는 방식 : backup 파일을 copy 하는 대신 tar 또는 xbstream 포멧의 STDOUT으로 보냄

    • stream 기능을 사용하면 자동으로 암호화 

    • 압축이 활성화 되어 있을 경우 meta, non-InnoDB 를 제외한 파일은 압축

    • quicklz 알고리즘의 qpress 사용 (http://www.quicklz.com/) 

    • *.qp 형식의 압축 포멧

  • compact

    • compact 옵션을 사용하여 백업을 실행할 경우 xtrabackup_checkpoint 파일에 메타데이터 저장

      backup_type = full-backuped from_lsn = 0 to_lsn = 2888984349 last_lsn = 2888984349 compact = 1

  • backup command

    innobackup --defaults-extra-file=/etc/my.cnf --no-lock --user=유저명 --password=비밀번호 --socket=/data/mysql.sock --no-timestamp --stream=tar ./ | gzip > /data/backup/백업파일명.tar.gz innobackup --defaults-extra-file=/etc/my.cnf --no-lock --user=유저명 --password=비밀번호 --socket=/data/mysql.sock --no-timestamp --stream=xbstream --compress ./ > 백업파일명.xbs

• 복원 

  • xbstream 에서 추출

    xbstream -x < /data/backup/백업파일명.xbs

  • qpress를 통한 decompress 로 백업 압축 해제

    xtrabackup --decompress --remove-original --target-dir=타겟디렉토리명

  • prepare 작업을 하기 전까지 백업 파일의 동시성을 보장할 수 없으므로 prepared 작업 진행

    xtrabackup --prepare --target-dir=타겟디렉토리명

  • 서비스 중지

  • 준비된 파일을 data directory로 이전

  • 이전 된 파일의 권한 수정

    chown -R mysql:mysql *

  • 서비스 시작

• 적용 대상

  • 데이터 파일이 사이즈가 큰 경우

  • MasterDB에서 백업을 할 수 없을 경우 Live 서비스 성능에 영향을 주지 않는 Slave에서 백업

  • 원격 백업이 필요할 경우 (xstream을 통해 mount 된 storage 또는 remote 서버로 백업과 동시에 압축을 진행)

 

xtrabackup 백업 정보

---

• Backup File 정보
  • backup-my.cnf : 백업 중 사용된 옵션 정보
  • ibdata : Tablespace file 백업
  • xtrabackup_binary : 백업에 사용된 xtrabackup 바이너리
  • xtrabackup_binlog_info : MySQL이 최근 사용한 바이너리 로그 파일 정보
  • xtrabackup_checkpoint : 백업 타입, 백업 상태, LSN 범위에 대한 정보
  • xtrabackup_logfile : 백업을 위한 redo log 파일
• xtrbackup 옵션 정보 : https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html