mysql 백업(Backup) 및 복구(Recover) > db

출처 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=54465

Mysql 백업

우선 Mysql 이 /usr/local/mysql/에 설치되어있으며 Mysql configure시

--localstatedir=/usr/local/mysql/data 옵션을 주고 설치했다고 가정하겠다.

백업 방법으로는 크게 2가지가 있다.

Mysql DB 데이터 화일을 직접 백업하는 경우와 mysqldump 문을 이용하여 sql문을 백업받는방법이 있다

1. 데이터 화일 직접백업

보통의 경우 Mysql 을 사용하는 Type 이 InnoDB와 Myisam 이 있다.

두 Type 의 차이는 여러가지가 있지만 여기서 언급하는것은 데이터 화일 백업에 관련된것이므로

데이터 화일의 위치를 언급하겠다.

nnodb_data_home_dir = /usr/local/mysql/data/

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

InnoDB 의 경우 일반적으로 위의 /etc/my.cnf 에서 설정된 것과 같이

ibdata1, ibdata2 과 같은 화일에 index 및 파일데이터가 저장된다. 그리고 DB 및 테이블 정보는

/usr/local/mysql/data/DB명/테이블명.frm 과 같은 구조로 이루어져 있다.

그러므로 ibdata1 와 같은 화일과 /usr/local/mysql/data/DB명/테이블명.frm 화일들을 복사하면 백업하면된다.

Myisam 인 경우는 좀더 직관적이다.

/usr/local/mysql/data/DB명 에 있는 화일을 통째로 백업받으면 된다.

특정 DB의 특정 테이블만 백업받을 경우에는..

/usr/local/mysql/data/DB명/테이블명.frm # 테이블 구조

/usr/local/mysql/data/DB명/테이블명.MYD # Myisam Type 테이블의 DATA

/usr/local/mysql/data/DB명/테이블명.MYI # Myisam Type 테이블의 index

위의 3개 화일만 다운받으면 된다.

물론 data 디렉토리를 통째로 백업받을수도 있다.

다만 이런 직접적으로 파일을 백업받는 방법은...

Mysql 버젼이 달라졌을때 문제가 될수도 있으니...

가급적이면 mysqldump 를 이용하는것이 좋다.

2. mysqldump를 이용한 Backup

가장 널리 이용하는 방법이고 가장 확실한 방법이다.

다만 DB 데이터가 큰 경우 Backup 시간이 많이 걸릴수 있기 때문에

보통 크론등을 이용하여 주기적으로 새벽시간을 이용하여 백업받는다.

사용방법은 다음과 같다.

/usr/local/mysql/bin/mysqldump -uroot -pPassword [백업옵션] [환경옵션] > /BackUp/Mysql/Backup.sql

/BackUp/Mysql/ <== 임의로 정한 백업화일 위치이다.

[백업옵션] 의 내용은 아래의 4가지 형태만 알아도 될듯하다.

옵션들에 주의 해주면 다양한 형태로 백업을 받을수 있다.

--all-databases # mysql DB 전체를 백업다는것을 의미한다.

--databases DB1 DB2 DB3 # mysql 에서 특정 DB만 백업받는 것을 의미한다.

DB1 # DB1 이라는 DB 만 백업받을때 사용한다.

DB1 table1 # DB1 이라는 DB의 table1 이라는 테이블만 백업받을때 사용한다.

[환경옵션]은 백업시에 어떤환경으로 백업을 할것인가에 대한 옵션이다.

--default-character-set=utf8 # 지정된 캐릭터셋을 기본으로함

--set-charset # 기본지정된 캐릭터셋(default-character-set)을 SET NAMES default-character-set로 설정

--opt # 메모리에 로드하지 않고 바로 화일로 덤프

--create-options # create문 백업시에 테이블 설정을 포함함.

--compatible=DB # 백업sql이 특정 db에 호환되도록 함 예) mysql40, mysql41, oracle, mssql

--extended-insert=FALSE # insert 문을 한줄씩 만든다

--result-file=file # 지정된 file 로 바로 넣음.. "> /BackUp/Mysql/Backup.sql"  과 같은 의미

--triggers # 트리거 덤프

--no-create-db # DB 생성정보를 뺌

--no-create-info # 테이블 생성정보를 뺌

--no-data # 테이블의 데이터를 뺌

이외에도 많은 옵션이 있지만 거의 사용할일이 없어서 나도 잘모른다.

공부하고 싶으신분들은 mysqlkorea.com 이나 mysql.com 에서 찾아보시길..

복구(Recover)

복구는 상당히 간단하다.

/usr/local/mysql/bin/mysql -uroot -pPassword < /BackUp/Mysql/Backup.sql

위의 방법은 전체복구이며 단위 DB가 생성되어 있는상태에서 db별 백업은 다음과 같이 한다.

/usr/local/mysql/bin/mysql -uroot -pPassword DB명< /BackUp/Mysql/Backup.sql

대용량DB 복구를 위한 백업

팁으로 대용량의 Table 을 덤프 받아서 입력할때...

입력속도 때문에 문제가 생기는 경우가 있다.

아마도 대부분이 InnoDB라서 그런 문제가 생기지 싶다.

그럴때는 Table Type 을 InnoDB에서 Myisam 으로 바꾸고 Insert 그리고 다 입력된 다음..

Table Type을 다시 InnoDB로 바꾸는게 빠르다.

그래도 속도가 느리다면 Mysql 에서 LOAD DATA INFILE 을 실행하여.. CSV 같은 데이터를 입력받는게

가장 빠르다.

다음은 하나의 DB(DB에 포함된 모든 테이블 정보)에 관한 DDL문 하나와 각 테이블별 CSV 데이터를 만드는

방법이다. 아래의 명령을 실행시키기 이전에 우선 /백업디렉토리 설정을 먼저해야한다.

갑자기 왠 백업디렉토리 설정이냐라고 물을수 있는데.. CSV 로 만들경우 mysql 이라는 유저권한으로

파일들이 생성되기 때문에 mysql 이 쓰기권한이 있어야한다.

/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-data > /백업디렉토리/DB.sql

위의 dump 명령은 db 구조만 백업받는것이다.

/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-create-info --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'

여기서 주의해야 할것은 각 테이블별로 "--tab=/백업디렉토리" 정의된 곳에 table명.txt 화일로 CSV 화일이

생성된다는 것이다. 만약 --no-create-info 옵션을 주지 않는경우에는 "--tab=/백업디렉토리"에 정의된 대로

해당디렉토리에 테이블 DDL 문장이 테이블명.sql 로 각각 생성이된다.

각테이블별로 DDL문과 CSV 화일을 백업받고 싶다면..

/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'

이와 같이 하면된다.

만들어진 테이블DDL문과 CSV 데이터로 복구할때는 아래와 같이..

/usr/local/mysql/bin/mysql -uroot -pPassword < /백업디렉토리/DB.sql

/usr/local/mysql/bin/mysql -uroot -pPassword DB명

mysql> load data infile '/백업디렉토리/테이블.txt' into table 테이블명 fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

참고1

--tab 옵션을 사용해서 CSV 형태로 백업할때는 --all-database 와 같은 옵션과 같이 사용할수없다.

--tab 옵션은 하나의 DB 이하에서만 사용이 가능하다.

참고2

--fields-terminated-by=',' # 필드구분자

--fields-enclosed-by='"' # 필드를 특정기호로 감싸는것

--lines-terminated-by='\r\n' # 라인구분자(테이블 데이터의 Row 구분자)