개요
linux crontab으로 mysqldump를 통해 DB dump 데이터를 주기적으로 백업하고 스케쥴링하는 방법을 소개한다.
linux crontab으로 mysql dump 백업 스케쥴링
이전 포스팅에서 소스 코드를 백업한 것처럼 쉘 스크립트를 작성하고 크론탭에 등록을 해주면 된다.
쉘 스크립트를 작성하고 s3로 업로드를 하기 위해 크론을 실행하자 몇 가지 에러가 발생해서 정리해본다.
<작업 순서>
1. 쉘 스크립트 작성 (mysqldump로 dump 파일 생성, 해당 파일 압축, s3 업로드)
2. crontab 등록 (해당 작업 반복하도록)
문제 1: mysqldump password 입력 시 특수문자의 경우 에러가 발생할 수 있음
db 패스워드가 틀린 건 아닌데 access denied 에러가 떠서 확인해보니 password에 특수문자가 포함되면 에러가 발생할 수 있다고 한다.
해결: 변수 선언 또는 따옴표로 감싸기
패스워드에 특수문자가 포함된 경우 변수를 따로 선언해주거나 따옴표로 감싸준다.
PASSWORD="[특수문자가 포함된 패스워드]"
echo "Creating backup of database [db] to $BACKUPNAME"
mysqldump -h [호스트 주소] -u [사용자] -p$PASSWORD [db] | gzip -9 > $BACKUPNAME문제 2: mysqldump: Got error: 1044: Access denied for user '[host]'@'%' to database '[db]' when using LOCK TABLES
이번에는 mysqldump: Got error: 1044: Access denied for user '[host]'@'%' to database '[db]' when using LOCK TABLES mysqldump: Got error: 1044: Access denied for user '[host]'@'%' to database '[db]' when using LOCK TABLES 에러가 발생했다.
에러 메시지를 해석하면 해당 아이디에 LOCK TABLE 권한이 없기 때문에 dump를 실행할 수 없다는 의미다. dump를 실행할 때는 데이터 정합성을 위해 테이블 LOCK이 걸린다.
(여담으로 말하자면 출근해서 백업 시도했다가 서버가 잠깐 멈춘 적이 있었다. 팀장님은 원인도 모르시고 다른 크론탭을 확인하셨는데... 알고 보니 이러한 연유로 인해 DB dump는 보통 새벽 시간대에 이뤄진다고 한다.)
우선 해당 아이디에 정말 LOCK 권한이 없는지 확인해봤다.
show grants for '[host 명]'@'%';(결과) GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON [db명].* TO [host 명]@%
부여된 권한을 살펴보니 LOCK 권한이 없었다. LOCK 권한을 부여하기 위해서는 슈퍼 관리자로 접속을 해야 하는데... 혹시 슈퍼 관리자가 아니어도 해결할 수 있는 방법이 있는지 찾아보기로 했다.
해결: --single-transaction 옵션 추가
서치해보니 lock을 걸지 않고도 dump를 수행할 수 있는 옵션이 있다고 하여 --single-transaction이라는 옵션을 추가해보니 백업에 성공했다.
일반적으로 InnoDB의 경우에는 single-transaction을 사용하며,
MyISAM 의 경우에는 lock-tables 옵션을 사용해야 한다. (물론 MyISAM의 경우 일관된 백업의 의미는 없다)
InnoDB에서 single-transaction 옵션이 사용되면, table에 대한 lock을 걸지 않고 MVCC의 Read view를 이용하기 때문에 mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.
*출처: http://intomysql.blogspot.com/2010/12/mysqldump.html
최종적으로 작성한 쉘 스크립트는 다음과 같다.
#!/bin/bash
NOWDATE=`date +%Y-%m-%d`
BACKUPNAME="$NOWDATE.sql.gz"
PASSWORD="[특수문자가 포함된 패스워드]"
echo "Creating backup of database [db] to $BACKUPNAME"
mysqldump -h [호스트 주소] -u [사용자] -p$PASSWORD [db] | gzip -9 > $BACKUPNAME
echo "Successfully created database backup"
echo "Uploading backup to Amazon S3 bucket…"
~/bin/s3cmd-2.1.0/s3cmd put $BACKUPNAME [S3 버킷 경로]
echo "Successfully uploaded backup to S3"
echo "Deleting backup file"
rm $BACKUPNAME
echo "Done"