12. MySQL 데이터베이스 백업 및 복구

MySQL 데이터베이스 백업의 개요

1. MySQL 백업의 필요성

 - MySQL과 연동되는 애플리케이션이 정상 작동하여도 DB 시스템 오류 발생 시 서비스 불가

 - 서비스 가용성을 위하여 최신화된 데이터 백업 자료가 필요

 - DB 시스템 대,개체 및 이전 시 절체 작업에 활요

 

윈도우용 MySQL 백업

1. MySQL Workbench 실행

1) 대시보드 MySQL Connections 클릭

2) root 계정 비밀번호 입력

3) Data Export 클릭

4) Date Export 옵션 선택

 - 백업하고자 하는 DB 선택

 - Objects to Export의 Dump Stored Procedures and Functions, Dump Events, Dump Triggers 모두 선택

 - Export to Self-Contained File 항목 체크 후 백업 파일이 저장될 경로와 이름 설정하기

 - Create Dump in a Single Transaction 및 include Create Schema 체크하기
 - Start Export 버튼 클릭하기

5) Data Export 진행

6) Data 백업 확인

 - 지정한 경로에 백업된 파일 확인

 - 백업된 파일은 TEXT 형태의 형식으로 구성

 

리눅스 서버 MySQL 백업

1. MySQL 데이터베이스 테이블 백업

1) MYSQLDUMP 명령을 이용한 백업 - 리눅스 서버에서 mysql DB 백업에 진행하는 명령어

2) 기본 명령어 형식 - MYSQLDUMP 데이터베이스 테이블명 > 작명.dat(또는 txt)

ex) MYSQLDUMP test account > account.dat

3) 데이터베이스 접속 로그인 포함 명령 구문 형식

MYSQLDUMP -U ROOT -P*******

데이터베이스명 테이블명 > 작명.dat(또는 txt)

ex) MYSQLDUMP -U ROOT -P test account > account.dat

4) 백업된 파일 내용 확인

 

2. 데이터베이스 전체 백업

1) 개요 - 리눅스 서버에서 데이터베이스 전체 백업이 가능

2) 명령어 형식 - MYSQLDUMP 데이터베이스명 > 작명.dat(또는 txt)

3) 적용 예

ex) test라는 데이터베이스 전체 백업

 - text라는 DB를 test.dat라는 파일로 백업

 - 백업 파일명은 DB명과 일치시킬 필요 없이 작명하면 됨

 - 확장자 역시 dat, txt, sql 등 작명 가능

 (1) 쿼리문

  MYSQLDUMP -U ROOT -P text > test.dat

 (2) 실행 결과

 

일정 시간 주기 자동 백업

1. 개요 및 필요성

- 리눅스 서버의 cron 데몬을 활용하여 일정 시간 주기를 백업

- 서버 접속 후 수동 백업 과정보다 효율성, 안정성이 우수

- 정기 백업을 통한 DB 이력 분석이 가능

 

2. cron의 개념

- 일정 간격의 시간 주기를 두고 정기적인 작업을 자동 처리하는 데몬(프로그램)

 

3. cron의 절차

 

4. MySQL 백업 실행 스크립트 작성

1) 리눅스 셸(shell) 스크립트

- 운영 체제상에서 사용자가 입력하는 명령어를 읽고 해석하여 대신 실행해 주는 프로그램임

- 스크립트 작성 후 파일을 'chmod 755 파일명' 명령어로 실행 권한 부여

 

2) 셸 스크립트 작성 예시

셸 스크립트명 - mysqlbackup.sh

 (1) #!/bin/sh

  - Unix 계열 Shell Script 파일의 필수적인 구문

 (2) date='date'+%y-%m-%d.%H-%M''

  - date 구문

  - 현재 시각을 변수명으로 대입

 (3) /usr/bin/mysqldump

  - 실행 경로

  - mysqldump 실행 파일 위치

 (4) -u사용자ID -p사용자비밀번호DB명

  - MySQL 접속 계정

 (5) /home/사용자계정/작명$date

  - 백업 경로

  - 백업 파일 생성 위치 및 파일명

 

5. 일정 시간 주기 자동 백업 등록

1) cron 스케줄 개요

 - 리눅스 서버의 cron 데몬을 활용하여 일정 시간 주기 백업을 진행

 

2) cron 스케줄 등록

 - ***** 실행경로/실행파일(프로그램)

 

3) cron 스케줄 편집

 - crontab -e 명령어 실행하기

  -> *의 의미는 '항상'을 의미

  -> 매주, 매월, 매일 00시 30분마다 /home/사용자계정/mysqlbackup.sh을 실행하라는 의미

 

4) cron 스케줄 확인

 - crontab -I 명령어 실행

  -> 다른 스케줄이 등록되어 있다면 결과 화면에 모두 출력됨

 

MySQL 데이터베이스 복원

1. MySQL 데이터베이스 복원의 개념

 - MySQL의 데이터베이스를 특정 시점의 상태로 전환시키는 과정

 - MySQL 데이터베이스 운용 중 오류 발생 또는 장애 시 서비스 정상화에 유용

 

윈도우용 MySQL 데이터베이스 복원

1. MySQL Workbench 실행

 1) 대시보드 MySQL Connections 클릭

 2) root 계정 비밀번호 입력

 3) Data Import/Restore 클릭

 4) Data Import 메뉴 설정

  - Import from Self-Contained File 체크 및 '...' 표시된 버튼 클릭으로 백업된 파일 지정

  - 복구하고자 하는 DB명 지정하기

  - Start Import 버튼 클릭으로 복원 진행하기

 

2. MySQL 데이터베이스 복원 예시

 1) 'test'라는 DB에 두 개의 테이블(account, manage)이 존재

 

2) account 테이블 장애 가정을 위한 삭제가 진행

 

3) 백업된 account 테이블 복원 후 테이블 상태가 확인

 

리눅스에서의 MySQL 데이터베이스 복원

1. 데이터베이스 복원 명령어 및 절차

1) 데이터베이스 백업

#> MYSQLDUMP 데이터베이스명 > 작명.sql

 

2) 데이터베이스 임시 복원 및 검증

#> MYSQLADMIN CREATE 새데이터베이스명

#> MYSQL 새데이터베이스명 < 작명.sql

 

3) 데이터베이스 테이블 상태 및 칼럼 상태 확인

 

4) 기존 DB 및 새 DB 삭제

MYSQL>DROP DATEBASE 기존데이터베이스명;

MYSQL>DROP DATEBASE 새데이터베이스명;

 

5) 데이터베이스 복원

#> MYSQL 기존데이터베이스명 < 작명.sql

 

2. 데이터베이스 생성 생략 백업, 복원 방식

1) 데이터베이스 백업

#> MYSQLDUMP --DATABASES 데이터베이스명 > 작명.sql

 

2) 데이터베이스 복원

#> MYSQL < 작명.sql

 

3. MySQL 셸(프롬프트) 상에서의 직접 복원 방식

- 데이터베이스 복원

MYSQL> SOURCE 작명.sq

 

MySQL 데이터베이스 복원 예

1. 데이터베이스 확인 및 백업 데이터베이스 산정

 

2. 데이터베이스 백업

- DB명 'test'

 

3. 임시 데이터베이스 생성 및 검증

 

4. 데이터베이스 테이블 상태 확인

 

5. 데이터베이스 테이블의 칼럼 상태 확인

 

6. 기존 데이터베이스 및 새 데이터베이스 삭제

 

7. 데이터베이스 복원