Mysql DB 백업하기

 mysql 백업 4가지 방법

1. Data 디렉토리 백업

   - Data 디렉토리를 정기적으로 백업하고 문제 발생시 백업 파일을 덮어쓰면 됩니다.

2. mysqldump 를 사용하는 방법

- 이 방법은 아래에서 자세히 다룰 예정

3. mysqlhotcopy 를 사용하는 방법

- mysql 백업 방법 중 속도가 바르며 DB 디렉토리를 다른 위치에 Copy 한다.

PS. Myl 및 ARCHIVE 테이블만 Hotbackup을 지원며 innoDB 지원하지 않는다. 

4. xtraback 을 사용하는 방법

- mysql 서버 중단하지 않고 InnoDB를 핫백업할 수 있다.

- InnoDB Hot Backup은 핫백업을 지원하나 상용이나 xtraback 은 무료로 사용이 가능.

※ Hot Backup : DB 서버가 온라인 상태에서 DB를 백업하는 방법

   Cold Backup : DB 서버를 중단시키고 백업하는 방법  

■ mysqldump 사용하는 방법

▷지원 법위

: 전체 데이터베이스 백업 기능 지원
: 특정 데이터베이스 백업 기능 지원
: 특정테이블 백업 기능 지원

▷ 전체 데이터베이스 백업 방법

  : 서버의 전체 데이터베이스를 alldb.sql로 백업한다.

  mysqldump -uroot -p --all-databases > alldb.sql

▷ 특정 데이터베이스 백업

  : g5 데이터베이스만 백업한다.

▷ 특정 테이블만 백업

   : g5데이터베이스의 g5_board 테이블만 백업 한다.
   : 문법 : mysqldump -u사용자ID -p Database명 테이블명 > 백업파일명.sql

※ 특정 데이타베이스의 테이블생성(schema) 정보만 백업하는 방법

- mysqldump -? 명령어로 여러 옵션을 확인 할 수 있다.

▷ InnoDB에서 트리거 , 프로시져, 함수 포함하여 백업하기

  : 트리거는 default값으로 백업이 실행되나 저장 프로시져는 백업되지 않습니다.

  : 저장 프로시져가 백업되게 하기 위해서는 옵션에  --routines 을 넣어줘야 합니다.

[ 문법 ] : 파일 구조 Data , sp, 트리거가 모두 포함되어 배업됩니다.

  mysqldump -u계정 -p비밀번호 --routines 특정DB명 > 파일이름.sql

 ※ 트리거 , 프로시져, 함수 만 백업하기 - 쿼리문만  

[ 문 법 ]

 mysqldump -u계정 -p비밀번호 --routines  --no-create-info --no-data --no-create-db --skip-opt 특정DB명 > 파일명.sql 

이렇게 트리커 프로시져 함수만 백업한 경우는 반드시 Data와 테이블 스키마를 별도로 백업 받아 줘야 합니다

■ 백업 스케쥴 설정 방법

: IT쪽에는 백업에 실패한 관리자는 용서가 안된다는 야기가 있을 정도로 백업은 매우 중요하다.

 

[ 방 법 ]

1) 백업할 디렉토리 만들기 : sudo mkdir /backup

 
2) 백업 디렉토리 권한 주기 : sudo chmod 755 /backup

3) shell 프로그램 작성

  sudo nano /usr/local/bin/mysqldump.sh

#!/bin/sh

# 백업 위치를 /backup 아래로 정한다.

# 백업 시간을 년-월-일 형식으로 지정한다. 

Now=$(date +"%Y%m%d%H%M%S")

# 사용자 계정과 비밀번호

USERNAME="MySQL계정"

PASSWORD="비밀번호"

# 백업할 데이타베이스

DATABASE="g5"

# 백업 작업

mysqldump -u$USERNAME -p$PASSWORD  $DATABASE > /backup/mysql_db_bak_$Now.sql 

4) 실행권한 부여 : sudo chmod +x /usr/local/bin/mysqldump.sh

5) cron 만들기 - 일정한 시간에 실행 되게 한다. : sudo nano /etc/crontab

   - 04시 10분에 자동으로 실행하게 설정    :  10 4 * * * root /usr/local/bin/mysqldump.sh

crontab 파일 형식을 모르는 분을 위한 추가 설명


------    --------  ---------------------------------------------------
필  드    의  미    범  위
------    --------  ---------------------------------------------------
첫번째    분        0-59
두번째    시        0-23
세번째    일        0-31
네번째    월        1-12
다섯번째  요일      0-7 (0 또는 7=일요일, 1=월, 2=화,...)
여섯번째  명령어    실행할 명령을 한줄로 쓴다.
------    --------  ---------------------------------------------------

예제

        분 시간 날짜 달 요일 : 구분은 Space

        15,45 3 * * *  : 매일 3시 15분관 45분에 수행

        5 9 * * 6      : 매주 금요일 9시 5분에 수행

        15 1,18 16 * * : 매달 16일 1시15분과 오후 6시 15분에 수행

5) 크론 데몬 재실행  :  sudo /etc/init.d/cron restart

6) 시스템 시작 시 스크립트 실행되도록 한다.

    : sudo nano /etc/rc.local

    : 아래와 같이 입력하고 저장한다.

      /usr/local/bin/mysqldump.sh