MYSQL베이터베이스 백업기술 mysqldump 3편

기술문서 : MYSQL베이터베이스 백업기술 mysqldump 편<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><?xml:namespace prefix = st2 ns = "urn:schemas:contacts" />박성수   ㅇ 본 기술문서의 자세한 정보 : http://www.superuser.co.kr/linuxcommandbible/

 

 

 

 

 

 

 

 

   ------------------   목               차   ----------------

 

1  : MYSQL 백업명령어 mysqldump에 대하여

2  : mysqldump명령어로 특정 데이터베이스 백업과 복구

3  : mysqldump명령어로 특정 데이터베이스의 특정 테이블의 백업과 복구

4  : mysqldump로 여러 개의 데이터베이스 한번에 백업하기

5 : MYSQL의 전체 데이터베이스 데이터 백업하기

6  : 기존 테이블을 삭제후 백업된 파일로 복구하기 위한 백업방법

7  mysqldump로 데이터베이스 백업할 때에 에러발생을 무시하고 계속 진행하기

8 : mysqldump로 원격호스트의 데이터베이스 백업하기 #1

9 : mysqldump로 원격호스트의 데이터베이스 백업하기 #2 (포트번호지정)

10 : mysqldump로 백업시에 create database문을 생략하여 백업하기

11 : mysqldump로 백업시에 create table문을 생략하여 백업하기

12 : mysqldump로 백업시에 데이터는 백업하지않고 테이블 스키마만 백업하기

13 : mysqldump로 백업시에 where조건에 맞는 데이터만 백업하기

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6 : 기존 테이블을 삭제후 백업된 파일로 복구하기 위한 백업방법

 

부제 : mysqldump문으로 데이터베이스 백업시에 각각의 create table문 앞에 drop table문 삽입하기

 

MYSQL의 백업유틸리티인 mysqldump로 백업을 하게 되면 테이블의 데이터를 생성하는 SQL문외에도 데이터베이스의 스키마(테이블 구조)를 생성하는 “create table”의 SQL문들이 들어 있습니다.

 

따라서 mysqldump로 백업된 SQL파일을 이용하여 복구를 할 때에는 복구하고자하는 데이터베이스에 스키마(테이블구조)가 이미 존재할 경우에는 에러가 발생합니다.

 

즉, 데이터복구시에 기존의 테이블들을 삭제하고 새로 테이블구조를 만들어서 백업데이터를 입력하는 방법으로 복구를 한다면 매우 편리할 것입니다.

 

즉, mysqldump로 백업을 할 때에 --add-drop-table옵션을 사용하면 생성되는 백업파일내의 “create table”이라는 문장 바로 앞에 “drop table”문을 추가하게 됩니다. 즉, 이것의 의미는 백업된 파일로 복구를 할 때에 기존의 테이블을 완전히 삭제하고 테이블을 다시 만들어서 데이터를 입력하는 방법으로 복구를 하기 위함입니다.

 

[주의사항] 이 방법을 이용하여 백업과 복구를 할 때에 주의 하실 점이 있습니다. --add-drop-table옵션을 사용하여 백업된 파일로 복구를 할 때에 기존의 테이블의 내용들이 모두 삭제되고 백업데이터로 재생성되므로 백업파일의 백업시점 이후와 복구시점 사이의 데이터는 모두 “휘리릭~~~” 사라져 버린다는 점입니다. 이점 유념하시고 꼭 필요한 경우에 사용하시기 바랍니다.

 

자, 이제 직접 백업해 보도록 하겠습니다.

아래의 예는 temp_db2라는 데이터베이스를 백업하여 temp_db2.sql파일에 저장합니다.  이때  --add-drop-table이라는 옵션을 사용하였으므로 생성되는 temp_db2.sql파일의 “create table”문들의 앞에는 모두 “drop table”이라는 테이블 삭제하는 SQL문이 추가되어 저장됩니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p --add-drop-table temp_db2 > temp_db2.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l temp_db2.sql -rw-r--r--    1 root     root          416  2월 14 12:32 temp_db2.sql [root@file bin]#

 

이렇게 백업된 데이터를 잠시 살펴 보겠습니다. 아래의 예는 앞에서 백업명령어로 생성된 temp_db2.sql파일의 내용을 cat명령어로 살펴본 것입니다.

 

[root@file bin]# cat temp_db2.sql -- MySQL dump 9.08 -- -- Host: localhost    Database: temp_db2 --------------------------------------------------------- -- Server version       4.0.14   -- -- Table structure for table 'Demo_Domain' --   DROP TABLE IF EXISTS Demo_Domain; CREATE TABLE Demo_Domain (   d_id smallint(7) unsigned NOT NULL auto_increment,   d_name varchar(255) default NULL,   d_type varchar(10) default NULL,   d_sday varchar(20) default NULL,   d_eday varchar(20) default NULL,   d_email varchar(255) default NULL,   d_register varchar(60) default NULL,   hosting char(1) default NULL,   h_name varchar(255) default NULL,   h_homepage varchar(255) default NULL,   h_price varchar(20) default NULL,   h_sday varchar(20) default NULL,   h_eday varchar(20) default NULL,   h_memo text,   PRIMARY KEY  (d_id) ) TYPE=MyISAM;   -- -- Dumping data for table 'Demo_Domain' --   INSERT INTO Demo_Domain VALUES (1,'2000','.co.kr','2002/3/18','2004/3/18 ','papa@superuser.co.kr','수퍼유저코리아','1','수퍼유저','www.2000.co.kr ','11000','2002/11/18','2002/11/18',''); ….. …..

 

위의 내용을 보시는 바와같이 “CREATE TABLE..”문 앞에는 “DROP TABLE IF EXISTS Demo_Domain;” 문이 존재합니다. 즉, 이렇게 백업된 파일로 복구를 하면 복구하고자하는 테이블이 존재할 경우에 이를 삭제하고 새로 생성하게 됩니다.

 

아래의 예는 방금 백업된 내용으로 복구를 실행한 예입니다.  말씀드린바와 같이 기존의 테이블이 존재하는 경우라도 이를 삭제하고 새로 생성하여 복구하게 됩니다.

 

[root@file bin]# ./mysql -u root -p temp_db2 < ./temp_db2.sql Enter password: ******** [root@file bin]#

 

이와 같이 백업하는 방법은 복구하기에는 매우 쉬운 방법임에는 틀림이 없으나 기존 테이블데이터을 삭제하고 복구한다는 점에서 주의하셔야 합니다.

 

 

 

7 : mysqldump로 데이터베이스 백업할 때에 에러발생을 무시하고 계속 진행하기

 

MYSQL로 백업을 할 때에 SQL에러가 발생하는 경우가 있습니다.  이때 이를 무시하고 계속 진행하려면 -f 옵션을 사용합니다.  사용하는 형식은 다음과 같습니다.

 

사용형식 : mysqldump -u 사용자명 -p  -f  DB명 > 파일명

 

아래의 예는 MYSQL root계정으로 temp_db2 데이터베이스를 백업하면서 발생하는 SQL에러를 무시하고 temp_db2.sql파일로 백업하는 예입니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p -f temp_db2 > temp_db2.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l temp_db2.sql -rw-r--r--    1 root     root         6028  2월 14 15:41 temp_db2.sql [root@file bin]#

 

 

 

8 : mysqldump로 원격호스트의 데이터베이스 백업하기 #1

 

지금까지의 mysqldump로 백업하는 예들은 모두 현재 사용중인 로컬서버 내에서 MYSQL데이터를 백업하는 것이였습니다. 하지만 원격지 서버의 MYSQL 데이터를 백업하는 방법이 있습니다.  즉, mysqldump명령어로 백업할 때에 -h옵션 다음에 백업대상이 되는 원격서버의 IP주소나 호스트명 또는 도메인명을 입력하여 원격지 서버의 MYSQL데이터를 백업할 수 있습니다.  사용형식은 아래와 같습니다.

 

사용형식 : mysqldump -u 사용자명 -p -h 호스트명(IP주소) DB명 > 파일명

 

아래의 예는 192.168.0.111서버에 존재하는 MYSQL서버의 sspark이라는 데이터베이스를 현재 로컬서버의  sspark.sql파일에 저장하는 예입니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p -h 192.168.0.111 sspark > sspark1.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l sspark1.sql -rw-r--r--    1 root     root         6028  2월 14 15:43 sspark1.sql [root@file bin]#

 

 

이렇게 백업을 하기 위해서는 원격지 서버에 접속허용 설정이 되어 있어야합니다. 참고로 원격지에서 MYSQL 접속허용을 하려면 MYSQL의 관리데이터베이스인 mysql데이터베이스의 db테이블과 user테이블의 host필드값을 ‘%’또는 접속을 허용할 IP주소등을 설정함으로서 원격서버의 접속을 허용할 수 있습니다.

 

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 박성수   ㅇ 본강좌의 자세한 정보 : http://www.superuser.co.kr/linuxcommandbible/