By aPOST in SQL — 2024년 2월 2일

[sql] 리눅스 Cron으로 MySQL 데이터베이스를 자동 백업하기 - crontab

MySQL 데이터베이스를 백업하는 방법은 크게 세 가지 방법이 있습니다.

MySQL 로그인 정보를 작업 명령어에 직접 포함
MySQL 로그인 정보를 저장하는 .my.cnf 구성 파일을 사용
bash 셸 스크립트로 백업 스크립트 작성

세 번째 방법이 가장 좋은 방법이지만, 셸 스크립트를 작성하고 설정하는 몇 단계를 더 거쳐야 합니다.

커맨드 라인 명령으로 MySQL DB 백업하기

다음 명령을 실행하는 크론 작업을 만들어 MySQL 데이터베이스의 예약된 백업을 실행할 수 있습니다:

mysqldump --routines -u dbusername -p'dbpassword' dbname > ${HOME}/하위 디렉토리/backup.sql

mysqldump는 MySQL의 표준 커맨드라인 백업 프로그램입니다. MySQL 설치를 할 때 /usr/bin/ 폴더에 바이너리 실행 파일이 설치되기 때문에 아무 경로에서나 실행 가능합니다.

db_username은 데이터베이스 사용자, db_password는 데이터베이스 사용자의 패스워드, dbname은 백업할 데이터베이스, "하위 디렉토리"는 백업 파일을 저장할 내 홈 디렉토리 밑의 디렉토리 이름입니다.

패스워드는 (쌍)따옴표로 감싸서 표시하는 것을 권장합니다. 특수문자나 이스케이프를 해야 하는 문자를 패스워드에 사용했을 때 커맨드라인의 패스워드가 정상적으로 인식되지 않습니다.

${HOME}은 내 홈 디렉토리 경로 정보를 가진 변수입니다. "~/"으로 대체( ~/하위 디렉토리/backup.sql ) 할 수 있습니다.
명령이 성공적으로 실행되면 아무런 출력 내용 없이 커맨드가 종료합니다. 에러가 발생하면 에러 내용이 표시됩니다.

MySQL 로그인 정보를 환경 설정 파일에서 가져와 백업하기

DB백업 커맨드 라인에 접속 정보를 모두 표시하는 대신 나의 홈 디렉토리에 MySQL 로그인 정보를 저장하는 설정 파일을 만들어 사용할 수 있습니다.

"/home/내 계정" 디렉토리에 .my.cnf라는 파일을 만듭니다. 파일 이름 맨 앞에 "."이 있는 것에 주의해야 합니다. "home/내 계정"으로 경로를 사용하는 것보다는 "~/"을 사용하는 것을 권장합니다. ".my.cnf"는 MySQL에서 사용하는 표준 파일 이름입니다.
".my.cnf" 파일에 다음 내용을 작성한 후 저장합니다. db_username은 DB계정, db_password는 DB계정의 패스워드입니다. 패스워드는 (쌍)따옴표로 감싸서 표시해야 합니다.
host는 MySQL 서버의 IP입니다. 로컬 서버인 경우에는 IP대신 localhost로 표시할 수 있습니다.

[client]
user = db_username
password = "db_password"
host = localhost

3. 다음 명령을 실행하는 크론 작업을 만듭니다. dbname은 백업할 데이터베이스의 이름입니다.
예를 들어 작성한 다음 DB백업 커맨드라인은 내 홈 디렉토리 밑에 "backup" 디렉토리가 있고 이 디렉토리 안에 "dbbackup_240203.sql" 백업 파일이 생성됩니다.

mysqldump --routines dbname > ~/backup/dbbackup_240203.sql

bash 스크립트로 DB백업 스크립트 만들기

텍스트 에디터를 연 후 다음 bash 스크립트를 작성한 후 저장합니다. bash 스크립트로 DB 백업 스크립트를 만들면 백업 파일에 변수를 넣을 수 있어 백업할 때마다 파일 이름이 달라지기 때문에 백업 파일을 일정 주기별로 한 디렉토리 안에 누적할 수 있는 장점이 있습니다.

# MySQL 접속 정보
DB_USER="사용자명"
DB_PASSWORD="비밀번호"
DB_NAME="데이터베이스명"

# 백업 파일 경로와 이름
BACKUP_DIR="~/backup"
BACKUP_DATE=$(date + "%Y%m%d%H%M%S")
BACKUP_FILE="$BACKUP_DIR/db_backup_$BACKUP_DATE.sql"

# MySQL 백업 커맨드
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

저장한 bash 스크립트는 실행 권한을 부여해야 실행할 수 있습니다.

chmod +x dbbackup.sh

셸 스크립트를 실행하면 내 계정 루트 밑의 "backup" 디렉토리 안에 "db_backup_날짜.sql" 이름의 DB 백업파일이 생성됩니다.

MySQL 접속 정보와 백업 파일 경로 정보는 자신에게 맞게 적당히 수정해야 합니다.

cron으로 주기적인 백업하기

앞서 bash 스크립트로 만든 스크립트 파일을 크롭탭에 등록해서 주기적으로 DB 백업파일이 누적되도록 합니다.

cron 실행 목록을 수정하기 위해 cron 에디터를 엽니다.

crontab -e

2. 에디터 끝에 다음처럼 bash 스크립트 스케줄을 추가합니다. 매일 오전 1시에 한번씩 백업을 위한 bash 셸 스크립트를 실행합니다. 백업 디렉토리에 날짜별로 DB 백업 파일이 하나씩 누적됩니다.

0 3 * * * ~/scripts/dbbackup.sh

3. cron 서비스를 재시작합니다.

service cron restart

cron 은 MAILTO 환경 변수가 있고 공백이 아닐 경우 MAILTO 에 지정한 이메일 주소에 작업 결과를 발송합니다. 크론 작업이 실패했을 때 에러 내용을 등록한 이메일 주소로 발송하지 않으려면 백업 커맨드 행 끝에 "2>/dev/null" 을 추가합니다.

mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE 2>/dev/null

"2"는 STDERR(터미널에 출력되는 에러 메시지)이고 이 표준 에러 출력을 "/dev/null"로 보낸다는 의미입니다. "/dev/null"은 아무 것도 아닌 빈 장치를 가리키는 리눅스의 표준 장치 표시입니다.

쉽게 표준 에러 출력을 표시하지 않고 버리는 것입니다.