MySQL 백업하기

 

 

[MySQL] 특정 테이블만 백업하기 

 

※ 경고   높이뜬새의 모르는 사람이 없는 팁시리즈 입니다.   이 팁시리즈는 지면낭비라는 항의시에 즉각 중단됩니다.

 

터미널이나 SSH를 이용해 접속하셨다면 리눅스 쉘상에서 다음과 같이 입력합니다.

 

 

[MySQL 테이블 백업]

형식) 내계정~]$mysqldump -u [사용자명] -p [데이타베이스명] [백업받을 테이블명] > [백업받을파일명]

 

sae~]$ mysqldump -u testuser -p testDB testTable > test.sql   Enter password:

 

위에서는 testDB에 있는 testTable만 test.sql이란 파일로 백업받고 있습니다. Enter password: 란에 비밀번호만 제대로 입력하면 test.sql이란 파일에 testTable만 백업되어 있을것 입니다.

 

-p옵션뒤에 비밀번호를 바짝 붙여쓰면 비밀번호를 묻는 프롬프트를 생략할 수 있지만 보안상 저는 이방법이 더 낫다고 생각합니다.

 

 

 

[MySQL 백업된 테이블 복구]

형식) 내계정~]$mysq -u [사용자명] -p [데이타베이스명] < [백업받은 파일명]

 

sae~]$ mysql -u testuser -p testDB < test.sql   Enter password:

 

테이블 복구는 테이블 한놈만 백업받든, 열놈을 백업받든 어차피 데이타베이스 내에 복구하는 것이므로 데이타베이스 복구와 명령어 차이가 없습니다. 

 

역시 리눅스 쉘에서 명령어를 입력합니다.

 

 

 

 

 

▣ 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

 

 

 

출처: https://bizadmin.tistory.com/entry/MySQL-백업-및-복구하기 [Happy Resource]