mysql 백업에서 복구까지

31367 
Created at 2007-10-12 21:19:09 
153   0   0   0  
데이터베이스 관리자와 개발자들은 정기적인 데이터 백업의 중요성을 잘 알 것이다. 만약 디스크나 서버가 고장날 경우 백업을 미리 해 뒀는지 여부는 1년치 업무결과를 잃어버리느냐 아니면 몇시간만에 정상으로 복귀하느냐를 결정한다.

하지만 다행스럽게도 MySQL 사용자는 백업과 복구를 할 수 있는 간편하게 할 수 있는 내장 전용 툴을 사용할 수 있다. 이툴을 이용하면 이기종 플랫폼으로 MySQL 데이터베이스를 간편하게 옮길 수 있으며, 또 다른 포맷으로 레코드를 저장하거나불러오기도 가능하다.

데이터베이스 파일 복사
MySQL 데이터베이스 백업의 기본은 데이터베이스 파일 자체를 복사하는 것이다. MySQL은 이기종 플랫폼에서도 동일한 테이블포맷을 사용하므로 MySQL 테이블과 인덱스 파일을 플랫폼을 넘나들며 쉽게 복사할 수 있다(물론 양쪽 플랫폼에 동일한 MySQL버전이 설치돼 있어야 한다).

실제 복사하는 방법을 살펴 보자. 먼저 MySQL은 모든 데이터베이스를 data/ 디렉토리에 저장되며, 이는 다시 각데이터베이스를 위한 서브디렉토리로 구분된다. 테이블과 테이블 인덱스는 파일로 저장되고, 파일명은 테이블명과 확장자를 합쳐서붙여진다.

가장 쉬운 데이터베이스 복사 방법은 전체 data/ 디렉토리를 백업용 미디어에 복사하고, 언제든지 복구할 수 있도록 저장해 두는것이다. 복사를 자동화하는 스크립트를 작성하는 것도 좋은 방법이다. 스크립트를 작성했으면 이를 crontab에 추가해데이터베이스 업데이트 주기에 따라 매일 혹은 매주 단위로 실행되도록 설정할 수 있다.

한가지 주의할 것은 윈도우와 유닉스 플랫폼 사이에서 파일을 이동하는 경우다. 유닉스 파일명은 대소문자를 구분하지만 윈도우는그렇지 않으므로, MySQL 테이블명에 대소문자가 뒤섞인 있으면 문제가 발생할 가능성이 있다(물론 테이블내의 데이터는 영향을받지 않는다). 최악의 경우 데이블명과 코드를 수정하기 전까지 제대로 실행되지 않을 수도 있다. 따라서 최선의 방법은 테이블명을소문자만으로 작성하는 것이다.

테이블을 텍스트 파일로 저장하기
파일복사 외에 MySQL에 기본 설치된 mysqldump 툴을 사용하는 방법도 있다. 이툴은 테이블과 데이터베이스는 물론데이터베이스 전체를 텍스트 파일로 변환해 저장한다. 사용법도 매우 단순해, 내보낼 데이터베이스 이름을 인자로 다음과 같이실행하면 된다.

$ mysqldump -u root -p secret stocksdb

mysqldump 툴은 MySQL 서버와 접속해 주어진 암호정보로 로그인한 후, 일련의 SQL 명령어를 이용해 테이블 구조와 데이터를 다음과 같은 형태로 출력한다.

--
-- Dumping data for table 'portfolio'
--

INSERT INTO portfolio VALUES (1,'DHDU',2589,77.56); INSERT INTOportfolio VALUES (2,'YHOO',3200,45.65); INSERT INTO portfolio VALUES(3,'WMT',100,53.29);

물론 이를 콘솔에 출력하는 것은 그리 유용하지 못하다. 여러분이 원하는 것은 이를 파일로 저장해 백업하고나 복구하는데 이용할 수 있어야 한다. 이를 위해서는 출력 형태를 파일로 다시 정해주기만 하면 된다.

$ mysqldump -u root -p secret stocksdb > stocksdb.sql [/code]

만약 특정 테이블만 백업하려면 데이터베이스 이름 뒤에 테이블 이름을 붙이면 된다. 다음은 stocksdb 데이터베이스에서 users 테이블에 저장된 구조와 데이터만을 추출하는 구문이다.

$ mysqldump -u root -p secret stocksdb users > users.sql

또한 시스템 내의 모든 데이터베이스를 저잧하려면 다음과 같이 --all-databases 옵션을 이용한다.--all-databases 옵션을 사용하면, mysqldump 명령 결과에 각 데이터베이스를 초기화하는 CREATEDATABASE문이 포함된다. 이렇게 하면 데이터베이스 전체를 한 번에 복구하기가 더 쉬워진다.

$ mysqldump -u root -p secret --all-databases > backup.sql

테이블 구조만 백업하고자 하면 다음과 같이 --no-data 옵션을 사용하면 된다. 이 옵션은 애플리케이션을 처음 설치해서, 레코드가 없는 테이블의 복사본을 만들어야 하는 경우 대단히 유용하다.

$ mysqldump -u root -p secret --no-data stocksdb > stocksdb.sql

물론 테이블 구조는 제외하고 데이터만 백업하는 경우는 다음과 같은 구문을 이용하면 된다.

$ mysqldump -u root -p secret --no-create-info stocksdb > stocksdb.sql

텍스트 백업 파일로 MySQL 테이블 복구하기
지금까지 데이터베이스와 테이블 백업 방법을 살펴 봤다. 그러나 이것은 문제 해결의 절반에 불과하다. 나머지 절반은 실제 문제가발생했을 때 이를 복구하는 방법을 아는 것이다. 다행스럽게도 MySQL은 복구과정도 손쉽다. mysqldump 명령의 결과물은일련의 SQL 명령어로 구성돼 있기 때문에, MySQL 클라이언트가 간단하게 기존 데이터베이스 구조와 컨텐트를 재구축할 수 있다.

모든 데이터베이스를 mysqldump--all-databases 명령을 이용해 backup.sql 파일에 복사했다고 가정하면 다음과 같은 구문으로 이를 복구할 수 있다.

$ mysql -u root -p secret < backup.sql

--all-databases 옵션을 사용하지 않고 일부 테이블이나 데이터베이스를 선택적으로 백업한 경우는 MySQL 명령 실행시데이터베이스의 위치를 알려주는 -D 옵션을 추가한다. 예를 들어 stock2 데이터베이스의 stocksdb.sql 파일에 저장된테이블을 복구하는 구문이라면 다음과 같다.

$ mysql -u root -p secret -D stocks2 < stocksdb.sql

다른 포맷으로 내보내고 읽어들이기
MySQL 데이터베이스는 SQL 포맷 외에도 다양한 형식으로 저장할 수 있다. mysqldump 툴은 CSV나 사용자가 원하는구분문자(delimiter)를 이용해 다양한 포맷으로 레코드를 저장할 수 있는데, 이를 위해서는 mysqldump 명령어에--fields-terminated-by 인자만 추가하면 된다.

$ mysqldump -u root -p secret --no-create-info --tab=/tmp --fields-terminated-by=',' stocksdb

이와 같이 실행하면 mysqldump는 stocksdb 테이블에 있는 레코드의 각 필드를 쉼표로 분리해 /tmp 디렉토리에저장한다. 별도의 라인 터미네이터를 사용하려면 --lines-terminated-by 인자를 사용해 특정 구분문자로 라인을표시할 수 있다.

다른 데이터 포맷을 불러들이는 것도 가능하다. 예를 들어 다음과 같이 탭으로 분리한 테이블 레코드가 있다고 가정하자.

1 DHDU 2589 77.56
2 YHOO 3200 45.65
3 WMT 100 53.29

이 때 mysqlimport 툴을 이용하면 MySQL 데이터베이스 파일로 읽어들일 수 있다. 단 소스 파일의 베이스 이름이 데이터가 삽입될 위치를 결정한다는 점에 유의하자.

$ mysqlimport -u root -p secret --fields-terminated-by=' ' test /tmp/portfolio.txt

백업을 할때 항상 두 카피 이상을 유지하도록 한다(하나는 다른 위치에). 또한 MySQL 매뉴얼 내용 가운데 다양한 데이터를 내보내고 불러들이는 방법을 참고해 여러분의 환경에 응용할 수 있는 가장 좋은 방법을 찾길 바란다.

무엇보다 필자는 여러분이 문제가 발생한 데이터베이스를 복구해야 하는 상황에 놓이지 않기를 진심으로 바란다. 그러나 효율적으로복구할 수 있는 툴이 있고, 이를 이용하면 최악의 경우에도 사용자의 불편을 최소화할 수 있다는 사실만으로도 여러분은 좀더 쉽게잠을 청할 수 있을 것이다. @

Tags: DB백업 DB복구 mysql mysql백업 mysql복구 Share on Facebook Share on X

◀ PREVIOUS
블로그를 쉽게 할 수 있도록 도와주는 블로깅툴 - Windows Live Writer
▶ NEXT
특정 문자열을 기준으로 해서 배열로 만들어주는 방법
  댓글 0
로그인을 하시면 댓글을 등록 할 수 있습니다.
SIMILAR POSTS

mysql의 백업과 복구 (backup & restore) (created at 2008-01-04)

shell script를 이용한 mysql 자동 백업(backup) (created at 2008-01-04)

윈도우 환경에서 mysql 자동 백업하기 (created at 2008-01-04)

실수로 삭제한 mysql DB 복구방법 (created at 2007-01-30)

MySQL root 패스워드 분실했을 경우 (created at 2007-01-30)

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어 (created at 2014-09-03)

CentOS 6.x에 APM(Apache+PHP+MySQL) 설치 및 초기 설정 방법 (created at 2017-03-14)

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션 (created at 2018-07-02)

OTHER POSTS IN THE SAME CATEGORY

단축키로 웹페이지에 접속하기 (created at 2007-12-06)

절대 보이지 않는 숨김 파일 및 폴더 표시 (created at 2007-12-06)

마우스 움직임 속도 빠르게 하는 방법 (created at 2007-12-06)

컴퓨터에서 전기가 흐를때 (created at 2007-12-06)

데몬 SCSI 오류 OR 재부팅오류 해결법입니다 (created at 2007-12-06)

Windows 임시 폴더 자동 삭제 (created at 2007-12-06)

XP에서 Internet Explorer 다시 설치 방법 (created at 2007-12-06)

응용프로그램을 빠르게 실행하자 (created at 2007-12-06)

기존 비밀번호 몰라도 사용자의 암호 변경방법 (created at 2007-12-06)

로그인 비번 잊어먹었을때 (created at 2007-12-06)

간단하게 배워보는 DOS 명령어 (created at 2007-12-06)

Windows 부팅시 속도 향상시키는 방법 (created at 2007-12-06)

Windows ICS(Internet Connection Sharing) 사용하지 않도록(Turn Off) 하는 방법 (created at 2007-12-06)

GPL 2.0의 주요 내용과 개정배경 (created at 2007-11-28)

특정 문자열을 기준으로 해서 배열로 만들어주는 방법 (created at 2007-11-06)

블로그를 쉽게 할 수 있도록 도와주는 블로깅툴 - Windows Live Writer (created at 2007-10-10)

TV없는 거실..라운드형 거실, 평면의 변신 (created at 2007-09-10)

Flash와 PHP를 활용하여 멀티 파일 업로드 구현하는 방법 (created at 2007-08-14)

TComboBox Readonly로 만드는 방법 (created at 2007-08-09)

마이크로소프트 윈도우7 (WIndows 7) 미공개 바탕화면 (created at 2007-08-09)

비스타에 이은 마이크로소프트 윈도우 차기 버전「윈도우 7」 (created at 2007-08-09)

쓰기 불편해 XP로 U턴하는 이용자 늘어, 윈도비스타 '징검다리 OS' 되나 (created at 2007-08-09)

델파이에서 URL Encoding 하는 방법 (created at 2007-08-09)

한글을 유니코드 또는 UTF-8포멧으로 변환하는 방법 (created at 2007-08-09)

듀얼모니터를 쓸때 태스크바가 하나 밖에 없어서 불편했던 사람들을 위한 - 듀얼모니터용 프로그램 울트라몬 (UltraMon) (created at 2007-08-08)

Exchange Server 인증서 때문에 Windows Mobile 디바이스와 ActiveSync가 되지 않는 경우 해결법 (created at 2007-07-24)

PC 부팅할때 Num Lock 키 해제하는 방법 (created at 2007-07-21)

MFC에서 디스크 포멧(Disk Format)하기 (created at 2007-07-20)

소켓을 생성하여 CSocket에 Attach하는 방법 (created at 2007-07-19)

소켓 프로그래밍 기본 흐름 - Socket Programming Basic Flow (created at 2007-07-16)

UPDATES

자리 마음에 안든다고 6급 공무원 패는 농협 조합장 (created at 2024-03-26)

85세 딸 짜장면 사주는 102세 어머니 (created at 2024-03-26)

1990년대 감각파 도둑 (created at 2024-03-26)

치매에 걸린 69살의 브루스 윌리스가 전부인 데미무어를 보고 한 말 (updated at 2024-03-22)

일제강점기가 더 살기 좋았을지도 모른다는 조수연 국민의힘 후보 - 친일파? (updated at 2024-03-14)

성일종 인재육성 강조하며 이토 히로부미 언급 - 인재 키운 선례? (updated at 2024-03-13)

경제는 대통령이 살리는 것이 아닙니다 라던 윤석열대통령 - 상황 안좋아지자 여러 전략을 펼쳤지만, 부작용 속출했던 2024년의 봄 (updated at 2024-03-13)

극빈의 생활을 하고 배운것이 없는 사람은 자유가 뭔지도 모를 뿐 아니라 왜 개인에게 필요한지에 대한 필요성을 못느낀다는 윤석열 대통령 (updated at 2024-03-08)

조선일보를 안본다는 사람들이 말하는 그 이유 - 천황폐하, 전두환 각하, 김일성 장군 만세? (created at 2024-03-07)

광폭타이어를 장착하면 성능이 좋아질거라는 착각 (updated at 2024-03-03)

면허시험장에서 면허갱신하면 하루만에 끝나나? (updated at 2024-03-03)

신한은행/신한투자증권 금융거래 종합보고서 다운로드 방법 (updated at 2024-02-26)

100년 된 일본 장난감 회사가 내놓은 변신 기술에 난리난 과학계 (created at 2024-02-26)

알리에서 발견한 한글 지원하는 가성비 쩌는 무선 기계식키보드 (updated at 2024-02-25)

쌍팔년도가 1988년인줄 알았던 1인 (updated at 2024-02-23)

이쁜 색으로 변신한 테슬라 사이버트럭 (created at 2024-02-23)

2024년 카타르 아시안컵 4강전 전날 한국 대표팀 내부에 있었던 이강인의 폭주 (updated at 2024-02-21)

강릉 맛집 지도 (updated at 2024-02-20)

간이 안좋을 때 나타나는 증상 20가지 (updated at 2024-02-20)

배설물을 이용하여 일본에 저항했던 독립운동가 조명하 (updated at 2024-02-20)

요건 몰랐지롱? 이순신을 사랑한 외국인 (created at 2024-02-20)

원빈도 머리빨 (created at 2024-02-19)

대표적인 대한민국의 미남배우 중 하나인 원빈 (created at 2024-02-19)

백제의 건국 시조 온조왕 (updated at 2024-02-19)

700년동안 대한민국 고대국가의 한축이었던 백제시대 (created at 2024-02-19)

대머리들에게 주는 대머리의 조언 (created at 2024-02-17)

일본의 여성 락그룹 프린세스 프린세스의 "다이아몬드" (created at 2024-02-17)

결혼식 직전 연락두절된 신랑 (created at 2024-02-17)

대한민국 축구팀 파문으로 인해 중국 소셜미디어까지 등장한 탁구 전도사 이강인 (updated at 2024-02-16)

조국의 반격으로 흥미진진하게 흘러가는 한국의 정치판 - 데뷰와 동시에 한동훈 장관에게 던진 4개의 질문 (updated at 2024-02-15)

2024년 카타르 아시안컵 4강전 전날 내분사태로 갑자기 회자되는 이승우선수의 친화력 (created at 2024-02-15)

카카오뱅크 금융거래종합보고서/잔액증명서/거래내역서 발급 방법 (created at 2024-02-14)

아이가 최고의 스승이었다 (created at 2024-02-13)

이제는 국민 유행어로 등극한 한동훈의 "싫으면 시집가" (updated at 2024-02-13)

설 연휴 잔소리 메뉴판 - 이제 잔소리 하기전에 요금부터... (updated at 2024-02-10)

로버트 드니로의 70년 전 모습 (created at 2024-02-08)

카메라 어플로 만들어본 슈퍼걸 - 엄... 최종 작품은 왠지... (created at 2024-02-08)

앞트임 하고 새롭게 태어난 대한민국의 젊은 용사 (created at 2024-02-08)

비가 억수로 내리던 2024년의 2월 어느날 캘리포니아의 밤 카니예 웨스트와 그의 아나 비앙카 센소리 (updated at 2024-02-08)

스케방형사 1화 - 수수께끼의 전학소녀사키 (created at 2024-02-05)

백제와 일본의 교류가 가장 활발했던 시기는 근초고왕 시대 (created at 2024-02-05)

일에 찌들은 아빠가 꿈에서 깨어나지 않자 구출해주는 짱구 (created at 2024-02-03)

이제는 할아버지가 된 휴 그랜트(Hugh Grant)가 블랙핑크 콘서트에 다녀온 후 소감 (created at 2024-02-03)

다시 한번 감상해보는 추억의 날리면 패러디 (updated at 2024-02-01)

25년간 노예로 살다가 돌아온 남동생 (created at 2024-02-01)

가까우면서도 멀게 느껴지는 나라 일본 (created at 2024-02-01)

친구와 비교하다가 이혼하게 된 부부 (created at 2024-02-01)

요꼬 미나미노의 바람의 마도리걸(風のマドリガル) (created at 2024-01-30)

옛날 어린이들이 신문을 챙겨봤던 이유 (created at 2024-01-30)

Remaster된 요꼬 미나미노(南野陽子)의 판도라의 연인(パンドラの恋人) (updated at 2024-01-30)