초보자를 위한「MySQL 백업·복구」강좌

출처 : http://www.zdnet.co.kr/builder/dev/db/0,39031604,39129480,00.htm

 

데이터베이스 관리자와 개발자들은 정기적인 데이터 백업의 중요성을 잘 알 것이다. 만약 디스크나 서버가 고장날 경우 백업을 미리 해 뒀는지 여부는 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 INTO portfolio 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 명령 결과에 각 데이터베이스를 초기화하는 CREATE DATABASE문이 포함된다. 이렇게 하면 데이터베이스 전체를 한 번에 복구하기가 더 쉬워진다.

$ 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='t' test /tmp/portfolio.txt

백업을 할때 항상 두 카피 이상을 유지하도록 한다(하나는 다른 위치에). 또한 MySQL 매뉴얼 내용 가운데 다양한 데이터를 내보내고 불러들이는 방법을 참고해 여러분의 환경에 응용할 수 있는 가장 좋은 방법을 찾길 바란다.
무엇보다 필자는 여러분이 문제가 발생한 데이터베이스를 복구해야 하는 상황에 놓이지 않기를 진심으로 바란다. 그러나 효율적으로 복구할 수 있는 툴이 있고, 이를 이용하면 최악의 경우에도 사용자의 불편을 최소화할 수 있다는 사실만으로도 여러분은 좀더 쉽게 잠을 청할 수 있을 것이다. @