영삼이의 전문 정보 발행처

작성일 : 2012년 02월 16일 

작성자 : 김창현

제   목 : mysqldump을 이용하여 전체 데이터베이스시 일부 데이터베이스 제외시키기 

MYSQL 데이터 이전 시 많은 수의 DB 데이터를 이전하다 보면 --all-database를 통해 전체 DB데이터를 백업 받을 수가 있다. 그러나 신규 시스템 이전시에 전체 DB중 일부 DB는 제외하고 싶을 때가 있을 것이다. 그렇다고 일일이 DB를 DUMP하여 IMPORT 시키기에는 업무 효율성이 떨어질 것이다.

--all-database의 경우에는 시스템 데이터베이스인 mysql도 같이 DUMP가 되며, 일일이 에디터를 통해 제거하는 방법도 불편 하다.

시스템 데이터베이스외에 좀더 많은 DB에 대해 제외를 시키고자 한다면 정말 난감하다.

이를 해결하기 위해 리눅스의 파이프를 통해 해결해보자!!

표에 나와 있는 예제는 egrep을 이용하여 mysql|mysql.old라는 데이터베이스를 제외시키고 나머지 DB에 대해서 전체 DUMP를 한다.

mysql 유틸을 이용하여 SQL의 전체 데이터베이스를 확인 할 수 있는 show database를 활용하여 데이터베이스 모든 목록을 추출하고, 추출된 데이터베이스 내용을 파이프를 통해 egrep으로 전달하여 -v 옵션을 통해 제외하고 싶은 문자패턴들을 나열하고 제외된 데이터베이스 목록을 mysqldump로 넘겨 추려진 데이터베이스 목록에 대해서만 덤프를 받을 수가 있다. 추출된 목록을 넘기는 경우 한번에 받은 목록을 한번에 인자 처리 할 수 없는 관계로 xargs 명령어를 통해 라인단위로 인자를 넘겨 주었다.

MYSQL 을 백업하는 방법은 2가지가 있습니다.

1.  파일 복사 하기 

- data 디렉토리의 파일을 별도의 장소에 복사 놓는 방법입니다.  

 ① mysql 의 데이타가 저장되는 장소를 알아야 됩니다.  

      windows에 mysql이 설치된 경우 mysql 이 설치된 폴더의 my.ini 에서 저장된 곳을 알아 낼 수 있습니다. 
      메모장을 이용해서 my.ini를 열어 보면

      #Path to the database root
       datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

      datadir 이 mysql DB 가 저장되어 있는 위치 입니다.  

 ②  위의 경우 datadir의 위치의 해당 데이타베이스의 폴더를 다른 곳에 복사해서 저장해 놓으면 됩니다.

  ※  datadir폴더에 보면 여러 파일들이 있는데 그 용도는 다음과 같습니다. 

       *.frm - 테이블 구조 *.MYD - 데이타 *.MYI - index

  ※ 운영체제별 mysql DB 저장되는 곳의 위치는 아래 링크를 참고하세요.  

       http://www.mkyong.com/mysql/where-does-mysql-stored-the-data-in-my-harddisk/ 

  ※  InnoDB의 경우 조금 더 복잡한 과정을 거쳐야 한다고 합니다.  더 알게 되면 포스팅 하겠습니다.

       http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html 

     http://feedtome.springnote.com/pages/546778

      http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_databases

2. mysqldump를 이용해 테이블과 데이타를 쿼리문으로 백업하기

① 서버의 전체 DB 백업 하기

 mysqldump -u계정 -p비밀번호 -A > 모든DB.sql

(예) mysqldump -uroot -ppassword -A > All_database.sql

② 특정 DB 백업 하기 

     mysqldump -u계정 -p비밀번호 특정DB명 > 저장할파일명.sql

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

③ 특정 DB 의 특정 Table 만 백업

     mysqldump -u계정 -p비밀번호 특정DB명 특정Table명  > 저장할파일명.sql

     (예) mysqldump -uroot -ppassword northwind orders > northwind_orders.sql
 

④ 특정 DB 의 스키마만 백업하기

     mysqldump -u계정 -p비밀번호 -d 특정DB명   > 저장할파일명.sql

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

⑤  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와 테이블 스키마를 별도로 백업 받아 줘야 합니다.