MySQL 백업 스크립트를 설정하는 방법은 무엇인가요?
💡 요약 정리
- mysqldump 명령어를 사용하여 MySQL 데이터베이스를 백업합니다
- 백업 스크립트를 작성하고 실행 권한을 부여합니다
- crontab에 등록하여 주기적으로 자동 백업을 수행합니다
- 백업 파일은 압축하여 저장하고 오래된 파일은 자동으로 삭제합니다
MySQL 데이터베이스의 안전한 운영을 위해서는 정기적인 백업이 필수입니다. 서버호스팅 환경에서 MySQL 백업 스크립트를 작성하고 자동화하는 방법을 안내해 드립니다.
1. MySQL 백업 명령어
mysqldump는 MySQL 데이터베이스를 논리적으로 백업하는 표준 도구입니다.
① 특정 데이터베이스 백업
mysqldump -uroot -p[mysql_password] [DB명] > [백업파일명].sql
예시:
mysqldump -uroot -pMyP@ssw0rd myapp_db > myapp_db_backup.sql
설명:
-u root: root 사용자로 접속-p[password]: 비밀번호 (공백 없이 바로 입력)myapp_db: 백업할 데이터베이스 이름> myapp_db_backup.sql: 백업 파일 저장 위치
② 전체 데이터베이스 백업
mysqldump -uroot -p[mysql_password] -A > all_databases_backup.sql
옵션:
-A또는--all-databases: 모든 데이터베이스 백업
③ 특정 테이블만 백업
mysqldump -uroot -p[mysql_password] [DB명] [테이블명] > [백업파일명].sql
예시:
mysqldump -uroot -pMyP@ssw0rd myapp_db users > users_table_backup.sql
비밀번호 보안 주의
커맨드라인에 비밀번호를 직접 입력하면 프로세스 목록이나 히스토리에 노출될 수 있습니다. 프 로덕션 환경에서는 다음과 같은 보안 방법을 사용하세요:
~/.my.cnf파일에 인증 정보 저장-p옵션만 사용하고 대화형으로 비밀번호 입력- MySQL 설정 파일에서 인증 정보 참조
2. 백업 스크립트 작성
자동화된 백업을 위한 쉘 스크립트를 작성합니다.
① 백업 디렉토리 생성
[root@test ~]# mkdir -p /root/backup
[root@test ~]# mkdir -p /home/db_backup
디렉토리 설명:
/root/backup: 백업 스크립트 저장 위치/home/db_backup: 백업 파일 저장 위치
② 백업 스크립트 작성
[root@test ~]# vi /root/backup/mysqldump.sh
스크립트 내용:
#!/bin/bash
# MySQL 백업 설정
MYSQL_USER="root"
MYSQL_PASS="your_mysql_password"
BACKUP_DIR="/home/db_backup"
DATE=$(date +%y%m%d)
RETENTION_DAYS=15
# 백업 디렉토리 확인
if [ ! -d "${BACKUP_DIR}" ]; then
mkdir -p ${BACKUP_DIR}
fi
# 백업 함수 정의
dump() {
# 백업할 데이터베이스 목록 가져오기
DATABASES=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")
for db in ${DATABASES}; do
echo "Backing up database: ${db}"
# mysqldump로 데이터베이스 백업
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} --single-transaction --routines --triggers ${db} > ${BACKUP_DIR}/${db}_${DATE}.sql
# SQL 파일을 tar.gz로 압축
cd ${BACKUP_DIR}
tar czf ${db}_${DATE}.tar.gz ${db}_${DATE}.sql
# 원본 SQL 파일 삭제
rm -f ${BACKUP_DIR}/${db}_${DATE}.sql
echo "Completed: ${db}_${DATE}.tar.gz"
done
# 15일 이상 지난 백업 파일 삭제
echo "Removing old backup files (older than ${RETENTION_DAYS} days)..."
find ${BACKUP_DIR} -name "*.tar.gz" -mtime +${RETENTION_DAYS} -exec rm -f {} \;
echo "Backup completed successfully on $(date)"
}
# 백업 실행
dump
스크립트 주요 기능:
- 모든 사용자 데이터베이스를 자동으로 백업
- 각 데이터베이스를 개별 파일로 저장
- tar.gz 형식으로 압축하여 저장 공간 절약
- 15일 이상 지난 백업 파일 자동 삭제
- 백업 로그 출력
mysqldump 옵션 설명:
--single-transaction: InnoDB 테이블을 잠그지 않고 일관성 있는 백업--routines: 저장 프로시저 및 함수 포함--triggers: 트리거 포함
3. 스크립트 실행 권한 설정
백업 스크립트에 실행 권한을 부여합니다.
[root@test ~]# chmod 700 /root/backup/mysqldump.sh
권한 설명:
700: 소유자만 읽기/쓰기/실행 가능 (보안 강화)chmod 755: 다른 사용자도 실행 가능하게 하려면 사용
권한 확인:
[root@test ~]# ls -la /root/backup/mysqldump.sh
출력 예시:
-rwx------ 1 root root 1234 Nov 18 15:30 /root/backup/mysqldump.sh
4. 백업 스크립트 수동 테스트
crontab에 등록하기 전에 스크립트가 정상 동작하는지 테스트합니다.
① 스크립트 실행
[root@test ~]# sh /root/backup/mysqldump.sh
또는
[root@test ~]# /root/backup/mysqldump.sh
② 실행 결과 확인
Backing up database: myapp_db
Completed: myapp_db_251118.tar.gz
Backing up database: wordpress
Completed: wordpress_251118.tar.gz
Removing old backup files (older than 15 days)...
Backup completed successfully on Mon Nov 18 15:30:00 KST 2025
5. 백업 파일 확인
백업이 정상적으로 생성되었는지 확인합니다.
① 백업 디렉토리 확인
[root@test ~]# ls -lh /home/db_backup/
출력 예시:
total 448K
-rw-r--r-- 1 root root 62K Nov 18 15:30 myapp_db_251118.tar.gz
-rw-r--r-- 1 root root 67K Nov 18 15:30 wordpress_251118.tar.gz
-rw-r--r-- 1 root root 578B Nov 18 15:30 test_251118.tar.gz
② 압축 파일 내용 확인
[root@test ~]# tar tzf /home/db_backup/myapp_db_251118.tar.gz
출력:
myapp_db_251118.sql
③ 백업 파일 용량 확인
[root@test ~]# du -sh /home/db_backup/
출력 예시:
448K /home/db_backup/
디스크 용량 주의사항
백업 경로는 /home/db_backup에 저장되며, 스크립트에서 15일 이상 지난 파일이 자동으로 삭제됩니다.
백업 파일 용량이 큰 경우:
/home/db_backup디렉토리의 용량을 주기적으로 확인하세요- 보관 기간(
RETENTION_DAYS)을 조정하세요 - 디스크 용량이 부족하면 백업이 실패할 수 있습니다
용량 확인 명령어:
df -h /home
6. 주기적인 자동 백업 설정 (Crontab)
crontab에 백업 스크립트를 등록하여 자동으로 실행되도록 설정합니다.
① crontab 편집
[root@test ~]# crontab -e
vi 에디터 모드로 창이 열립니다.
② crontab 설정 추가
매일 새벽 3시에 백업 실행:
0 3 * * * /root/backup/mysqldump.sh >> /var/log/mysql_backup.log 2>&1
crontab 형식 설명:
* * * * * 명령어
│ │ │ │ │
│ │ │ │ └─── 요일 (0-7, 0과 7은 일요일)
│ │ │ └──────── 월 (1-12)
│ │ └───────────── 일 (1-31)
│ └────────────────── 시 (0-23)
└─────────────────────── 분 (0-59)
다양한 백업 스케줄 예시:
# 매일 새벽 3시
0 3 * * * /root/backup/mysqldump.sh
# 매주 일요일 새벽 2시
0 2 * * 0 /root/backup/mysqldump.sh
# 매월 1일 새벽 4시
0 4 1 * * /root/backup/mysqldump.sh
# 6시간마다 (0시, 6시, 12시, 18시)
0 */6 * * * /root/backup/mysqldump.sh
# 평일 (월~금) 새벽 3시
0 3 * * 1-5 /root/backup/mysqldump.sh
③ crontab 저장 및 종료
- vi 에디터에서
i키로 입력 모드 진입 - 위 내용 입력
ESC키 누르고:wq입력 후Enter로 저장
④ crontab 설정 확인
[root@test ~]# crontab -l | grep mysqldump.sh
출력:
0 3 * * * /root/backup/mysqldump.sh >> /var/log/mysql_backup.log 2>&1
⑤ cron 서비스 재시작
# CentOS 6 이하
[root@test ~]# service crond restart
# CentOS 7 이상
[root@test ~]# systemctl restart crond
7. MySQL 백업 파일 복구
백업 파일로부터 데이터베이스를 복구하는 방법입니다.
① 압축 파일 해제
[root@test ~]# cd /home/db_backup
[root@test db_backup]# tar xzf myapp_db_251118.tar.gz
② 데이터베이스 복구
기존 데이터베이스가 있는 경우:
[root@test db_backup]# mysql -uroot -p[password] myapp_db < myapp_db_251118.sql
새로운 데이터베이스 생성 후 복구:
# 데이터베이스 생성
[root@test db_backup]# mysql -uroot -p[password] -e "CREATE DATABASE myapp_db;"
# 데이터 복구
[root@test db_backup]# mysql -uroot -p[password] myapp_db < myapp_db_251118.sql
③ 복구 확인
[root@test db_backup]# mysql -uroot -p[password] -e "USE myapp_db; SHOW TABLES;"
8. 백업 로그 확인
백업 스크립트 실행 로그를 확인하여 문제를 진단할 수 있습니다.
① 백업 로그 확인
[root@test ~]# cat /var/log/mysql_backup.log
② 실시간 로그 모니터링
[root@test ~]# tail -f /var/log/mysql_backup.log
③ 최근 백업 로그 확인
[root@test ~]# tail -n 50 /var/log/mysql_backup.log
9. 문제 해결
① 백업 실패 시 확인 사항
MySQL 비밀번호 오류:
# MySQL 접속 테스트
[root@test ~]# mysql -uroot -p[password] -e "SHOW DATABASES;"
디스크 용량 부족:
# 디스크 용량 확인
[root@test ~]# df -h /home
권한 문제:
# 디렉토리 권한 확인
[root@test ~]# ls -ld /home/db_backup
# 권한 부여
[root@test ~]# chmod 755 /home/db_backup
② crontab 실행 확인
# cron 로그 확인 (CentOS 7 이상)
[root@test ~]# grep mysqldump /var/log/cron
# cron 서비스 상태 확인
[root@test ~]# systemctl status crond
③ 백업 파일 손상 확인
# tar.gz 파일 무결성 검사
[root@test ~]# tar tzf /home/db_backup/myapp_db_251118.tar.gz > /dev/null && echo "OK" || echo "Corrupted"
백업 모범 사례
- 3-2-1 백업 규칙: 3개의 복사본을 2개의 다른 매체에, 1개는 오프사이트에 보관
- 정기적인 복구 테스트: 백업 파일이 실제로 복구 가능한지 정기적으로 테스트
- 백업 모니터링: 백업 실패 시 알림을 받을 수 있도록 설정
- 암호화: 민감한 데이 터는 백업 파일을 암호화하여 저장
- 원격 백업: 같은 서버가 아닌 다른 위치에 백업 복사본 보관
MySQL 백업 관련 문의
MySQL 백업 설정이나 복구에 문제가 있으신 경우, 아래 정보를 포함하여 1:1 문의게시판으로 문의해 주세요.
문의 시 포함 정보:
- 서비스 ID
- 운영체제: CentOS 버전, RHEL 버전 등
- MySQL 버전:
mysql --version실행 결과 - 백업 스크립트: 사용 중인 백업 스크립트 내용
- 오류 로그: 백업 실행 시 발생한 오류 메시지
- 디스크 용량:
df -h실행 결과 - 문제 상황: 구체적인 증상 및 재현 방법