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

기술문서 : 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조건에 맞는 데이터만 백업하기

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

앞의 예에서는 MYSQL데이터베이스 내에 존재하는 특정 데이터베이스의 백업과 복구방법에 대해서 알아 보았습니다.  이번에는 특정 데이터베이스 내에 존재하는 특정 테이블만을 백업하고 복구하는 방법에 대해서 설명하도록 하겠습니다.

 

즉, 데이터베이스를 관리하다 보면 흔히 있는 일은 아니지만 특정 데이터베이스 전체가 아닌 특정 데이터베이스내의 특정 테이블의 데이터만을 백업 저장해야하는 경우가 있습니다. 예를들어 데이터베이스 내에 고객별 테이블이 존재한다라고 했을 경우에 그 고객이 다른 곳으로 이전을 했거나 아니면 그 고객이 자기의 데이터만을 백업해 달라고 요구했을 경우가 이에 해당 합니다.

 

따라서 여러분들과 저 같은 서버관리자들은 이런 요청이 있을 때 특정 데이터베이스의 내용을 전체 백업하는 것이 아니라 특정 데이터베이스 내에 존재하는 특정 테이블의 내용만을 백업받을 수 있어야 할 것입니다. 다음은 특정 데이터베이스 내의 특정 테이블을 백업하고 복구하는 사용형식입니다.

 

백업형식 : mysqldump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할파일명
복구형식 : mysql -u DB계정명 -p 데이터베이스명 < 저장한파일명

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p picasso domain > domain.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l domain.sql -rw-r--r--    1 root     root         5146  2월 14 11:19 domain.sql [root@file bin]#

 

위의 예는 MYSQL의 root계정으로 picasso라는 데이터베이스 내의 domain이라는 테이블의 데이터를 domain.sql이라는 파일에 백업하여 저장한 것입니다. 백업이 정상적으로 이루어 졌다면 domain.sql파일이 새로 생성되어 picasso데이터베이스의 domain테이블의 데이터가 SQL문의 형식으로 저장되어 있을 것입니다.

 

그리고 다음은 이렇게 백업된 domain.sql파일을 이용하여 복구를 하는 방법입니다.

 

[root@file bin]# ./mysql -u root -p picasso < ./domain.sql

 

위와 같이 하시면 domain.sql에 저장된 SQL문에 의해 picasso데이터베이스 내에는 domain이라는 새로운 테이블을 생성하고 그 테이블내에 백업된 데이터들을 넣어주게 됩니다.

 

참고로 앞의 예에서 보셨듯이 mysqldump로 백업된 데이터들은 모두 SQL문으로 저장이 되기 때문에 복구를 할 때에도 백업파일(domain.sql)내의 SQL문을 사용하여 복구가 이루어 진다는 것을 꼭 알아두셔야 합니다.

 

 

 

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

 

지금까지의 mysqldump로 데이터베이스를 백업하는 예는 모두 특정 데이터베이스 하나만을 백업대상으로 하였습니다.  하지만 서버관리자의 입장에서는 여러 개의 데이터베이스를 동시에 백업해야하는 경우도 가끔씩 있을 것입니다. 특히 호스팅서버를 관리하고 있는 서버관리자분들이라면 호스팅사용자 한명이 여러 개의 데이터베이스를 사용하는 경우에 특정 사용자가 사용하는 모든 데이터베이스를 백업해야하는 경우가 이에 해당합니다.

 

사용형식 : mysqldump -u root -p --databases [옵션] DB1 [DB2 DB3…] > 파일명

복구방법 : mysql -u root -p < ./파일명

 

즉, 위의 DB1, DB2, DB3등은 모두 데이터베이스명을 의미합니다. --databases라는 옵션에 의해서 그 뒤에 나오는 모든 이름은 데이터베이스명으로 인식하게 됩니다. 만약 --databases라는 옵션이 생략되었다면 앞의 첫번째 DB1뒤의 이름들은 모두 테이블명으로 인식될 것이므로 여러 개의 데이터베이스를 한번에 백업할 때에는 반드시 --databases옵션을 사용하셔야 합니다.

 

그리고 --databases라는 옵션으로 백업된 데이터를 복구할 때에는 위에서 보시는 바와 같이 데이터베이스를 지정하시면 않됩니다.  왜냐하면 --databases라는 옵션으로 백업을 하시게 되면 백업파일에 “CREATE DATABASE..” 라는 SQL문이 함께 생성되므로 복구시에 데이터베이스를 생성할 수 있기 때문입니다.  이 설명은 뒤에서 자세히 다룹니다.

 

아래는 picasso라는 데이터베이스와  temp_db2라는 데이터베이스를 동시에 백업하여 dbs.sql파일에 백업하여 저장하는 예입니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p --databases picasso temp_db2 > dbs.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l dbs.sql -rw-r--r--    1 root     root         6220  2월 14 12:08 dbs.sql [root@file bin]#

 

위와 같이 --databases라는 옵션을 사용하여 백업된 파일에는 일반적인 백업파일과는 달리 “CREATE DATABASE”라는 SQL문과 “USE DB…”라는 SQL 문이 추가되어 저장되어 있습니다.  이것은 백업된 파일로 복구를 할 때에 원본 데이터베이스명과 동일한 데이터베이스를 생성하고 생성된 데이터베이스에 데이터를 복구하기 위한 것입니다.

 

아래의 예는 위에서 백업된 dbs.sql파일을 cat명령어로 살펴본 것입니다. 보시는 바와 같이 앞에서는 볼 수 없었던 CREATE DATABASE문과 USE문이 추가되어 있는 것을 확인하실 수 있습니다.

 

[root@file bin]# cat dbs.sql   -- MySQL dump 9.08 -- -- Host: localhost    Database: temp_db2 --------------------------------------------------------- -- Server version       4.0.14   -- -- Current Database: temp_db2 --   CREATE DATABASE /*!32312 IF NOT EXISTS*/ temp_db2;   USE temp_db2;   -- -- Table structure for table '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' --

 

이렇게 여러 개의 데이터베이스를 백업하였을 경우에 백업된 파일을 이용하여 복구하는 방법은 특별하지 않습니다. 즉,  이 또한 다음의 예에서 보시는 바와 같이 SQL문으로 백업되어 저장되었기 때문에 일반적인 복구방법과 동일하다는 것을 알 수 있습니다.

 

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

 

 

 

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

 

MYSQL의 모든 데이터베이스의 스키마와 데이터들을 전체 백업하는 방법에 대해서 알아보겠습니다. 앞의 예들에서는 특정 데이터베이스들에 대한 백업방법을 설명하였습니다.  서버관리자의 입장에서 본다면 개별적인 데이터베이스를 백업하여 저장하는 방법도 필요하겠지만 전체 데이터베이스를 한번에 백업해 두는 것이 매우 편리할 것입니다.

 

MYSQL의 전체 데이터베이스를 백업하는 사용형식은 다음과 같습니다.

 

사용형식 : ./mysqldump -u root -p --all-databases > 파일명.sql

 

위의 사용형식에서 보신바와 같이 --all-databases옵션을 사용하면 MYSQL의 모든 데이터베이스 데이터를 하나의 파일에 SQL문으로 백업을 하게 됩니다. 참고로 이 방법은 앞의 예에서 보았던 --databases옵션을 사용하여 모든 데이터베이스를 지정하는 것과 --all-databases와 동일하게 모든 데이터베이스 데이터를 하나의 파일에 백업할 수 있습니다.

 

아래의 예는 현재 필자가 사용하고 있는 리눅스 서버의 MYSQL에 저장되어 있는 모든 데이터베이스와 데이터들을 ALLDATA.sql파일에 저장한 예입니다.

 

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqldump -u root -p --all-databases > ALLDATA.sql Enter password: ******** [root@file bin]# [root@file bin]# ls -l ALLDATA.sql -rw-r--r--    1 root     root        14929  2월 14 12:25 ALLDATA.sql [root@file bin]#

 

이렇게 백업이 된 ALLDATA.sql파일의 내용을 살펴보시면 MYSQL의 관리데이터베이스인 mysql데이터베이스와 일반 데이터베이스들의 스키마(각 데이터베이스와 테이블구조)와 모든 데이터들이 SQL의 형태로 백업되어 있습니다.

 

위와 같이 --all-databases라는 옵션을 사용하여 백업된 파일에는 일반적인 백업파일과는 달리 “CREATE DATABASE”라는 SQL문과 “USE DB…”라는 SQL 문이 추가되어 저장되어 있습니다.  이것은 백업된 파일로 복구를 할 때에 원본 데이터베이스명과 동일한 데이터베이스를 생성하고 생성된 데이터베이스에 데이터를 복구하기 위한 것입니다.

 

아래의 예는 위에서 백업된 ALLDATA.sql파일을 cat명령어로 살펴본 것입니다. 보시는 바와 같이 CREATE DATABASE문과 USE문이 추가되어 있는 것을 확인하실 수 있습니다.

 

 

[root@file bin]# cat ALLDATA.sql   -- MySQL dump 9.08 -- -- Host: localhost    Database: --------------------------------------------------------- -- Server version       4.0.14   -- -- Current Database: locli --   CREATE DATABASE /*!32312 IF NOT EXISTS*/ locli;   USE locli;   -- -- Current Database: mysql --   CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;   USE mysql; -- -- Table structure for table 'columns_priv' --   CREATE TABLE columns_priv (   Host char(60) binary NOT NULL default '',   Db char(64) binary NOT NULL default '',   User char(16) binary NOT NULL default '',   Table_name char(64) binary NOT NULL default '',   Column_name char(64) binary NOT NULL default '',   Timestamp timestamp(14) NOT NULL,   Column_priv set('Select','Insert','Update','References') NOT NULL default '',   PRIMARY KEY  (Host,Db,User,Table_name,Column_name) ) TYPE=MyISAM COMMENT='Column privileges';   --

 

이 방법은 MYSQL의 재설치나 서버이전 및 정규백업의 경우에 사용되는 방법으로 필자도 자주 이용하는 방법입니다.