mysql 백업 및 복구

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 mysql -uroot -pjeilpc study < hr_info.sql  (테이블을 특정 DB에 부을때) 대용량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 /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 mysql> load data infile '/백업디렉토리/테이블.txt' into table 테이블명 fields terminated by ',' lines terminated by '\r\n' fields enclosed by '"'; 참고1 --tab 옵션을 사용해서 CSV 형태로 백업할때는 --all-database 와 같은 옵션과 같이 사용할수없다. --tab 옵션은 하나의 DB 이하에서만 사용이 가능하다. 참고2 --fields-terminated-by=',' # 필드구분자 --fields-enclosed-by='"' # 필드를 특정기호로 감싸는것 --lines-terminated-by='\r\n' # 라인구분자(테이블 데이터의 Row 구분자) 참고3 필드명을 지정해서 복구하는 경우는 다음과 같이 load data infile 'datafile.txt' into table 테이블명 fields terminated by ',' enclosed by '"' lines terminated by '\r\n' (column1, column2, column3, ...);