Mysql 데이터베이스 백업받기

데이터베이스 백업받기

데이터베이스의 백업의 중요성을 아무리 강조해도 지나침이 없습니다.

시스템의 갑자스런 다운이나 정전등의 사유로 인하여 예기치못한 데이터손실이 발생할 수도 있습니다.

따라서, 데이터베이스는 항상 백업을 해두어야하며, 언제든 손실된 데이터를 복구할 준비가 되어있어야만 합니다.

 

mysql에서 백업을 하는 방법은 다음과 같습니다.

mysqldump -u [DB사용자명] -p [백업할 데이터베이스명] > [외부로 저장할 파일명]

위와 같이 데이터베이스를 백업하기위해서는 mysqldump라는 명령어를 이용합니다.

 

먼저 지금껏 예를 들었던 TESTDB라는 데이터베이스를 백업해 보도록 하겠습니다.

이렇게 하고 나면 TESTDB_backup.sql이라는 파일이 새로 생성됩니다.

이파일에는 TESTDB를 원래대로 복원하기위한 SQL문이 가득 들어 있습니다.

참고로 백업된 TESTDB_backup.sql파일의 내용을 잠시 살펴보면 다음과 같습니다.

보신 바와같이 mysqldump로 백업된 sql파일에는 SQL문이 가득들어 있습니다.

보시는 바와 같이 mysqldump라는 명령어로 백업을 한다는 것은 결국 어떤 데이터베이스의 모든 테이블들을 백업한다는 의미입니다.

좀더 구체적으로는 현재 생성되어 있는 테이블들을 생성할 수 있는 SQL문과 이 테이블들에 들어갈 INSERT문들로 가득차 있습니다.

따라서, 다시 어떤 데이터베이스를 복구한다는 것은 백업된 이 SQL문들을 이용하여 테이블과 데이터들을 새로 생성해 낸다는 의미입니다.

 

2. 데이터베이스 복구하기

 

백업된 파일로 다시 복구를 할 때에는 생성된 이 파일을 유닉스 쉘프롬프트상에서 실행만 시키면 됩니다.

다음의 예와 같이 .......

 

참고로, mysql 백업을 할 때에는 특정 데이터베이스만을 백업받아서는 별 의미가 없습니다.

mysql데이터베이스도 함께 백업을 받아두어야 합니다.

그래야 어떤 데이터베이스들이 존재했는지를 알 수 있기 때문입니다.

유념하셔야 할 것은 MySQL전체의 데이터베이스를 백업할 때에는 반드시 mysql이라는 데이터베이스도 함께 백업해 두어야합니다.

그래야 어떤 데이터베이스가 있었나를 정확히 알 수 있기 때문이며, 어떤사용자들이 있었는지, 그리고 어떤권한들이 설정되어 있었는지를 정확히 알 수 있기 때문입니다.

 

다음과 같이......

 

MySQL 단순 백업

 MySQL은 일반적으로 /usr/local/mysql 디렉토리에 설치한다. 여기서는 /usr/local/mysql/data 디렉토리를 데이터베이스 파일이 위치한 디렉토리로 설명한다. 이 디렉토리 아래에 MySQL의 각 데이터베이스명과 동일한 디렉토리 이름들이 잇다. 각각의 디렉토리들이 하나의 데이터베이스이다.

 가장 간단한 MySQL 백업은 데이터베이스 파일이 위치한 디렉토리를 압축하여 보관하는 것이다.

    #!/bin/bash

    from_backup_dir="/usr/local/mysql/data"

    to_backup_dir="/backup/"

    today=`date +%Y%m%d`

    tar cvfz ${to_backup_dir}${today}.tar.gz ${from_backup_dir}

 

  위와 같이 하면 각 데이터베이스를 구분하지 않고 하나의 아카이브 압축파일로 보관하게 된다. 그러므로 사용자별로 백업 파일을 복구할 경우에는 불편하다.

  이번에는 각 디렉토리별로 백업하는 스크립트를 보도록 하겠다.

     #!/bin/bash

     from_backup_dir="/usr/local/mysql/data/"

     to_dir="/backup/"

     today=`date +%Y%m%d`

     source_dir=`ls -l "$from_backup_dir" | grep ^d | awk '{print $9}'`

     for i in `echo $source_dir`

     do

         tar cfz ${to_dir}${today}${i}.tar.gz ${from_backup_dir}${i} 2>/dev/null

     done

 

 

mysqldump를 이용한 백업

  위와 같이 데이터베이스 파일을 그래로 압축해서 백업해 놓은 방식은 문제점이 잇다. 파일로 직접 백업본을 만들어 놓을 경우, 복구할 때 리눅스 배포판이나 버전이 다를 경우, 시스템 아키텍처가 다를 경우 복구가 안 되는 경우가 많다.

  따라서 MySQL을 백업할 떄는 MySQL 자체적으로 지원되는 mysqldump 유틸리티를 이용하는 경우가 많다. mysqldump 유틸리티는 데이터베이스의 내용을 텍스트 파일에 SQL 문을 이용하여 백업한다. 이렇게 백업된 파일은 vi 편집기로 볼 수 있다.

  우선 콘솔에서 현재 데이터베이스가 어던 것들이 있는지 알아보자

     echo "show databases;" | mysql -u root -p

  명령 중에서 '|' 다음에 있는 'mysql -u root -p'부분은 MySQL에 접속하기 위한 명령이다. 유저가 root이고 패스워드 옵션을 넣었다. 다음 라인에서 패스워드를 물어보게 되고, 알맞게 입력하면 원하는 쿼리문을 볼 수 잇다.

  데이터베이스 중에서 damada라는 데이터베이스가 있다고 가정하고 이를 mysqldump 유틸리티를 이용하여 백업해 보겠다.

     mysqldump amanda -u root -p > amanda.sql

  amanda라는 데이터베이스를 라이디렉션을 통하여 현재 디렉토리에 amanda.sql이라는 파일로 저장했다. amanda.sql 파일은 vi 편집기로 볼 수 있는 텍스트 파일이다.

  mysqldump의 '--all-databases'옵션으로 모든 데이터베이스를 한 번에 백업할 수 있다.

     mysqldump --all-databases -u root -p > AllMySql.sql

  root의 패스워드를 입력하면 AllMySql.sal이라는 파일로 현재의 모든 데이터베이스가 백업된다. 여기서 root는 리눅스이 슈퍼유저 계정인 root와 다르다.

  다음은 '--all-databases'옵션을 이용하여 전체를 백업하는 스크립트이다.

     #!/bin/bash

     to_dir="/backup/"

     today=`date +%Y%m%d`

     mysqldump --all-databases -u root -pshell > ${to_dir}${today}mysql.sql

  마지막 라인에서 '-pshell'은 MySQL 접속 시에 root의 패스워드인 'shell'을 -p 옵션과 같이 붙여서 적은 것이다.

  이번에는 각 데이터베이스마다 백업을 하는 스크립트를 만들어보도록 하겟다. 우선 각 데이터베이스의 이름을 가져오는 방법으로 앞에서 사용했던 ls 명령과 awk를 이용했던 방법 대신에 다음과 같은 스크립트를 사용한다.

     echo "show databases" | mysql -u root -pshell | grep -v Database

  출력화면에서 가장 윗중에 나타나는 'Database'라는 문자를 없애주기 위해서 grep의 '-v' 옵션을 사용하였다.

  각 데이터베이스별로 mysqldump를 이용하여 백업한 후에 전체 파일을 하나의 파일로 압축한다. 따라서 각 데이터베이스별 백업파일을 임시 디렉토리에 만든 후에 압축하고, 임시 디렉토리를 삭제하면 된다.

  다음은 전체 스크립트 소스이다.

     #!/bin/bash

     to_dir="/backup/"

     today=`date +%Y%m%d`

     tmp_dir="${to_dir}${today}/"

     A=`echo "show databases" | mysql -u root -pshell | grep -v Database`

     mkdir ${tmp_dir}

     for i in `echo $A`

     do

         mysqldump ${i} -u root -pshell > ${tmp_dir}${i}.sql

     done

     tar cfz ${to_dir}${today}MySQL.tar.gz ${tmp_dir} 2>/dev/null

     rm -rf $tmp_dir