Windows 환경에서 MySQL 데이터베이스를 데이터베이스별로 자동으로 백업하기

필자의 회사의 개발서버는 Windows 기반이고 다수의 웹서비스를 개발하고 있는 상황이라 데이터베이스를 보다 효과적으로 백업하기 위해 별도의 배치파일을 만들어 스케줄러로 동작시키고 있다.

 

Windows 환경에서 MySQL서버를 운영할때 여러 데이터베이스를 사용할때 추천한다.

 

 

아래 소스코드를 복사하여 배치파일을 만들어 붙여놓는다.

배치파일을 만들때는 일반 텍스트문서를 생성하여 저장한 뒤 확장자를 .txt 가 아닌 .bat으로 변경하면 된다.

@echo off
setlocal
set id=root
set pw=패스워드
set mysql_dir=C:\DB\bin\ (mysql설치폴더내 bin 디렉토리 경로)
set backup_dir=C:\backup_db\ (sql을 저장할 디렉토리)

del /s /q %backup_dir%
cd %mysql_dir%
mysql --user=%id% --password=%pw% -e "SHOW DATABASES" > %backup_dir%database_list.log
for /f "delims=" %%i in (%backup_dir%database_list.log) do ( mysqldump --user=%id% --password=%pw% --single-transaction %%i > %backup_dir%%%i.sql )

 

혹은 아래 파일을 다운로드 받아도 무방하다.

sql_backup.bat

0.00MB

 

배치파일을 실행하면 아래와 같이 데이터베이스별로 SQL이 생성되면서 백업이 수행되는것을 확인할 수 있다.

또한, database_list.log 라는 파일이 함께 생성되어 어떤 데이터베이스가 존재하는지 확인할 수 있다.

 

 

 

 

주기적으로 배치파일을 실행시키기 위해 작업스케줄러 등록을 진행한다.

작업스케줄러는 시작 > 제어판 > 관리도구 > 작업 스케줄러를 누르면 실행할 수 있다.

 

작업만들기를 누른다. (기본 작업만들기를 누르지 않도록 주의)

 

이름에 적당한 스케줄명을 적는다.

트리거에 추가를 누른뒤 아래와 같이 설정하면 매일 새벽 2시에 자동으로 실행된다.

시간을 조정하고 싶다면, 시작시간을 조정하면 되며, 주기는 작업 반복 간격을 조절하면 된다.

 

 

이후 동작에 아래와 같이 생성한 배치파일을 찾아온 뒤 저장을 하면 된다.

 

매일 오전 2시에 자동으로 데이터베이스별로 SQL파일을 생성해줄수 있다.

참고로 배치파일내에 아래와 같은 소스코드가 있음으로 매일 실행할때마다 이전의 백업본은 자동으로 삭제된다.

del /s /q %backup_dir%

 

이는 매일 새벽 2시에 백업을 수행하고 약 4시정도에 NAS로 백업본을 전송하여 저장하기 때문에 삭제하는 명령어가 포함되어 있다.

별도의 NAS나 백업서버가 없을 경우 상기 코드만 지워주면 삭제 없이 SQL 백업본을 유지할 수 있다.

 

mysql 데이터베이스중에 information_schema라든지 mysql과 같은 데이터베이스는 백업할 필요가 없어 예외처리를 해줘야 되지만 사용상에 큰 문제도 없고 무엇보다 귀찮아서 여기서 끝내는걸로 ㅋㅋㅋ