MySQL 데이터베이스 백업 방법 파헤치기

데이터베이스 백업 과정은 중요한 절차입니다. 시스템 내에 장애가 발생했을 때 특정 지점의 데이터로 복구해야 하는데 복구 지점의 대상 데이터가 없을 경우 치명적인 손해를 입을 수도 있기 때문입니다. 데이터 백업은 파일을 백업 서버로 업로드하면 된다지만, DB 데이터들은 일반적으로는 내보내기가 어렵습니다.

 

MySQL 데이터베이스 백업

---

데이터베이스 백업 방식을 정의하자면 아래와 같습니다. (일부 데이터베이스 엔진이나 데이터베이스 종류에 따라 달라질 수 있습니다.)

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

아래에 기술된 백업 방식은 대부분 콜드 백업 방식을 사용합니다. 때문에 백업 도중 또는 이후에 발생한 데이터 변경에 대해서는 백업 파일에 반영되지 않기 때문에 지속적으로 백업 파일을 생성해주어야 하는 단점이 있습니다. 그러나 일부 InnoDB와 같은 데이터베이스 엔진에 따라 예외적으로 핫 백업 방식을 사용하기도 합니다. 대표적으로 mysqldump 명령어가 이 방법을 사용합니다.

 

데이터베이스 백업 시에는 다음 파일들을 백업하게 됩니다.

파일 종류	설명
Data file	데이터를 저장한 파일
Control file	데이터베이스의 구조나 정보에 대한 상태를 저장한 바이너리 파일
Redo log file	데이터 변경 처리사항을 저장한 파일 (복구 시 사용됨)

여기서 Redo log file은 데이터베이스 백업 시 사용되는 파일입니다. 핫 백업이 진행되면 수많은 Redo log가 작성되어 디스크 용량을 잡아먹지만 이 파일을 사용하여 백업 이후 발생한 데이터의 변경 사항을 유연하게 처리할 수 있게 됩니다. 반대로 콜드 백업 시에는 Redo log가 작성되지 않습니다. (그러나 Redo log 파일을 동시에 백업할 수는 있습니다.)

따라서 적절한 상황에 맞게 백업 방식을 선택하는 것이 중요합니다. 일반적으로 데이터가 적을 경우 서버를 잠시 중지한 후 콜드 백업을 해도 되지만, 데이터가 많아질 수록 핫 백업 방식을 채택하는 것이 좋습니다.

 

백업 – phpMyAdmin 사용

---

MySQL 데이터베이스 백업 방법은 여러가지가 있습니다. phpMyAdmin 서비스를 사용 중이라면 관리 패널에서 버튼 클릭만으로 콜드 백업이 가능합니다. 해당 Admin 패널에 접속하면 상단 메뉴에 ‘내보내기’와 ‘가져오기’가 있습니다. 이 메뉴를 사용하여 간단히 백업이 가능합니다.

• 상단 메뉴의 내보내기 선택

• 내보내기 방식 선택 – 형식 선택 – 실행 클릭

 

만약 보안상 또는 기타 이유로 phpMyAdmin을 사용하지 않는다면 아래 커맨드라인 기반의 인터페이스(CLI)에서 MySQL (또는 MariaDB) 데이터베이스를 백업할 수 있는 방법도 있습니다.

 

 

백업 – Linux CLI 환경

---

Linux 운영체제를 기준으로 터미널 환경을 이용하여 데이터베이스를 백업할 수 있습니다. 여기서는 MariaDB로 진행하겠습니다. (MySQL도 동일한 동작과 결과가 표시됩니다.)

 

방법 1 : Data 디렉토리 직접 백업하기

먼저 Data 디렉토리 전체를 복사하는 방법이 있습니다. 파일 자체를 복사하는 방법이므로 추후 복구 속도가 빠른 장점이 있지만 안정성과 데이터 신뢰에 있어 크고작은 문제가 발생할 수 있으며 다른 장비간의 이동이 어려워 권장하지 않는 방법입니다.

먼저 아래 명령어로 MySQL에 접속합니다. 아래 과정은 가급적이면 모두 root 권한으로 진행합니다. (유저도 특별한 경우가 아니면 root 유저를 사용합니다.)

# mysql -u[유저명] -p[패스워드]

접속 후 아래 명령어를 사용하여 데이터 폴더의 위치를 확인합니다.

MariaDB [(none)]> show variables like 'datadir';

datadir 의 값이 /var/lib/mysql/ 로 나왔습니다. 이제 단순히 이 경로에 있는 파일을 복사하여 어딘가에 보관하는 것입니다.

 

방법 2 : 오픈소스나 외부 백업 도구 사용

InnoDB Hot Backup, xtraback과 같은 외부 백업 프로그램을 사용하는 방법이 있습니다. InnoDB Hot Backup의 경우 이름 그대로 InnoDB에 대한 핫 백업을 지원하지만 유료입니다.

 

방법 3 : mysqlhotcopy 사용

mysqlhotcopy는 MyISAM 엔진이나 Archive 형식의 데이터베이스에 대해서만 사용되는 핫 백업 도구입니다.

아래와 같이 사용하실 수 있습니다.

# mysqlhotcopy -p [데이터베이스 패스워드] [데이터베이스명] [백업 경로]

 

방법 4 : mysqldump 사용

mysqldump 명령어를 사용합니다. 이 방법은 InnoDB 엔진에 한해 핫 백업 방식의 백업이 지원되며, 논리 백업 방식을 사용하면서도 간단하게 실행할 수 있어 그나마 무난하게 사용될 수 있습니다. (그러나 데이터 수가 많아질 수록 백업의 속도는 느려집니다.)

 

먼저 모든 데이터베이스를 백업하는 명령어입니다.

# mysqldump -u[유저명] -p[패스워드] -A > [백업파일명].sql

특정 데이터베이스만 백업하려는 경우 아래와 같이 사용합니다.

# mysqldump -u[유저명] -p[패스워드] > [백업파일명].sql

이번엔 특정 테이블만 백업하는 방법입니다.

# mysqldump -u[유저명] -p[패스워드] [데이터베이스명] [테이블명] > [백업파일명].sql

응용하여 데이터는 백업하지 않고 특정 DB의 테이블 구조만 백업하려면 아래 명령어를 사용합니다.

# mysqldump -u[유저명] -p[패스워드] --no-data [데이터베이스명] > [백업파일명].sql

 

 

 

Cron을 사용하여 예약 작업 만들기

---

이러한 백업 작업을 스크립트 파일로 작성하여 예약 실행할 수 있도록 지정할 수 있습니다. 아래는 mysqldump 방식으로 백업하는 것에 대한 작업을 예약 작업으로 만든 예시입니다.

먼저 스크립트 파일을 생성한 후, 해당 파일을 엽니다. (root 권한으로 진행합니다.)

# cd ~
# mkdir scripts
# cd scripts
# touch backupDB.sh
# chmod 700 backupDB.sh
# vim backupDB.sh

아래는 백업 스크립트 예시입니다. 만들어진 파일에 아래 내용을 붙여넣고 먼저 아래 파일에서 필요한 부분을 채워주세요. (접속 유저, 패스워드, 파일 이름, 파일 경로)

#!/bin/sh
dateToday=$(date "+%Y-%m-%d")
dbUser="[데이터베이스 접속 유저]"
dbPass="[데이터베이스 접속 패스워드]"
siteName="[백업파일 이름]"
savePath="[백업파일 저장 경로]"
# ======================================================
# Create SQL Backup File
# ======================================================
mysqldump -u$dbUser -p$dbPass -A > $savePath$siteName-db-backup-$dateToday.sql
chmod 700 $savePath$siteName-db-backup-$dateToday.sql
echo "Successfully Created Backup File. ($dateToday)"

이 파일을 저장합니다. 이제 이 스크립트를 특정 시간 마다 자동으로 실행할 수 있도록 예약 작업을 만들어보겠습니다.

crontab을 이용하여 예약 작업 편집 도구를 엽니다.

# vim /etc/crontab 또는
# crontab -e

아래 등록 예시를 참조하여 예약 규칙을 만듭니다.

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

매일 오전 1시에 스크립트를 실행한다고 가정하고 아래와 같이 입력한 후, 저장하고 나옵니다.

0 1 * * * /root/scripts/backupDB.sh

설정이 완료되었다면 cron 데몬 서비스를 재시작합니다.

# service cron restart (또는 systemctl restart cron)

이제 예약 작업에 대한 모든 작업이 완료되었습니다.