MySQL 백업 및 복구하기

▣ mysql 백업 4가지 방법

 

1. Data 디렉토리 백업

- Data 디렉토리를 정기적으로 백업하고 문제 발생했을 때 덮어쓰면 된다.

 

2. mysqldump 를 사용하는 방법

- 백업 시에 데이터베이스에 락을 걸수 없어 변경이 발생하면 다시 백업해야한다.

 

3. mysqlhotcopy 를 사용하는 방법

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

- Myl 및 ARCHIVE 테이블만 Hotbackup을 지원 한다. ( innoDB 지원하지 않는다. )

 

4. xtraback 을 사용하는 방법

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

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

 

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

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

 

 

■ mysqldump 사용하는 방법

 

전체 데이터베이스 또는 특정 데이터베이스를 백업하거나 특정테이블만 백업 할 수 있다.

 

▷ 전체 데이터베이스 백업

 

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

 

mysqldump -uroot -p -A > alldatabase.sql

 

▷ 특정 데이터베이스 백업

 

- test 데이터베이스만 백업한다.

 

mysqldump -uroot -p test > testdb.sql

 

▷ 특정 테이블만 백업

 

- test 데이터베이스의 board 테이블만 백업 한다.

 

mysqldump -uroot -p test board > testdbboard.sql

 

 

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

 

mysqldump -uroot -p --no-data test > testdbschema.sql

 

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

 

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

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

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

mysqldump -u계정 -p비밀번호 --routines 특정DB명 > 함수프로시져트리거.sql

(예) mysqldump -uroot -ppassword --routines  northwind > northwind.sql

 

 

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

 mysqldump -u계정 -p비밀번호 --routines  --no-create-info --no-data --no-create-db --skip-opt 특정DB명 > 함수프로시져트리거.sql 

(예) mysqldump -uroot -ppassword --routines --no-create-info --no-data --no-create-db --skip-opt northwind > northwind_only_sp_trigger_function.sql

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

 

■ 일정 시간마다 자동 백업

 

- 데이타베이스 백업은 아무리 강조해도 지나치지 않다. 그러나 잊어버리지 않고 사람이 하기엔 너무 귀찮다. 정기적으로 자동으로 실행되게 하려면 리눅스에서는 Shell 프로그램을 작성하여 cron에 등록시키면 된다.

 

▷ 백업할 디렉토리 만들기

 

sudo mkdir /backup

 

▷ 백업 디렉토리 권한 주기

 

sudo chmod 755 /backup

 

▷ shell 프로그램 작성

 

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

 

#!/bin/sh

 

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

# 백업 시간을 년-월-일 형식으로 지정한다.
DATE=`date +"%Y%m%d%H%M%S"`

 

# 사용자 계정과 비밀번호

USERNAME="MySQL계정"

PASSWORD="비밀번호"

 

# 백업할 데이타베이스

DATABASE="test"

 

# 백업 작업
mysqldump -u$USERNAME -p$PASSWORD  $DATABASE > /backup/mysql_db_bak_${DATE}.sql 

 

※ USERNAME, PASSWORD, DATABASE 다음(=)은 꼭 붙여쓴다

- 실행권한 부여

 

sudo chmod +x /usr/local/bin/mysqldump.sh

 

▷ cron 만들기 - 일정한 시간에 실행 되게 한다.

 

sudo vi /etc/crontab

 

- 04시 30분에 자동으로 실행하게 설정

 

30 4 * * * root /usr/local/bin/mysqldump.sh

 

▷ 크론 데몬 재실행
 

sudo /etc/init.d/cron restart

 

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

 

sudo vi /etc/rc.local

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

 

/usr/local/bin/mysqldump.sh