강좌
클라우드/리눅스에 관한 강좌입니다.
데이터베이스 분류

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

작성자 정보

  • 웹관리자 작성
  • 작성일

컨텐츠 정보

본문

최저가 서버호스팅 제공 : 85,000원/월

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

여러분들과 필자와 같은 서버관리자들이 서버관리업무를 할 때에 가장 예민한 부분이 아마도 해킹과 백업부분이 아닐까 생각됩니다. 물론, 서버의 성능과 안정성이 보장이 되어야하는 것은 기본이구요.

 

하지만 실질적으로 백업을 아무리 잘 해 두어도 불안한 마음은 완전히 가시지를 않습니다. 백업을 하는 도중에도 새로운 데이터가 쌓이고 있기 때문이죠. 그리고 백업을 완료한 순간부터 백업한 데이터는 현재의 데이터가 아닌 예전데이터가 되어버리기 때문입니다.

 

그리고 백업을 해 두어도 백업한 데이터로 정상적인 복구가 가능한가에 대한 의문점도 늘 존재하구요.  어쨌든 우리 서버관리자들은 여러가지면에서 심적인 부담을 떨쳐버릴 수가 없는 것은 사실인 것 같습니다. 그렇다고 백업을 아니할 수 있나요. 해야죠.

 

여기서 설명드릴 내용은 MYSQL 데이터베이스를 백업하는 여러가지 방법에 대한 것입니다.   필자의 경우 MYSQL은 다음과 같은 두가지 방법으로 백업을 하고 있습니다.

 

첫번째 백업방법 : /usr/local/mysql/전체를 매일 압축백업한다.
두번째 백업방법 : mysqldump명령어로 MYSQL스키마와 데이터만 백업한다.

 

여러분들께서도 잘 아시겠지만, 여기에서 설명드릴 방법은 두번째의 mysqldump명령어로 MYSQL의 필요한 부분이나 전체의 데이터를 SQL파일형태로 백업하는 방법입니다.  명령어의 위치와 mysqldump의 매뉴얼에 공식적으로 지정되어 있는 사용형식 3가지를 살펴보면 다음과 같습니다.

 

 

명령어위치 : /usr/local/mysql/bin/mysqldump

 

사용형식 1 : mysqldump [옵션]  DB [TABLES…] > 파일명

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

사용형식 3 : mysqldump [옵션] --all-databases [옵션] > 파일명

 

명령어 사용형식1에서 DB는 백업대상이 되는 데이터베이스명이고 TABLES는 지정한 백업대상 데이터베이스내에 존재하는 테이블명입니다. 따라서 이 백업의 의미는 DB의 데이터베이스내에 존재하는 테이블의 내용을 백업하여 “파일명”에 저장하라는 의미입니다.

 

명령어 사용형식2에서 --databases라는 옵션에 의해 DB1 DB2 DB3….의 데이터베이스들을 백업하여 “파일명”에 저장합니다.  즉 백업대상이 되는 데이터베이스가 2개이상이 될 때에는 --databases라는 옵션을 사용하시고 그 뒤에 백업할 데이터베이스를 지정해 주시면 됩니다.

 

명령어 사용형식 3에서 --all-databases라는 옵션은 MYSQL내에 존재하는 모든 데이터베이스를 백업대상으로한다는 의미입니다. 따라서 MYSQL내의 모든 데이터베이스들을 백업하여 “파일명”에 저장한다라는 의미가 됩니다.

 

그리고 이외에도 “mysqldump --help”를 보시면 mysqldump에서 사용할 수 있는 다양한 백업옵션들이 있습니다.

 

 

 

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

 

MYSQL의 특정 데이터베이스 하나만을 백업하는 방법에 대해서 알아보겠습니다. MYSQL의 백업유틸리티인 mysqldump를 이용하여 MYSQL에 존재하는 특정 데이터베이스를 백업하는 명령어 형식은 다음과 같습니다.

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

위의 형식을 이용하여 현재 필자가 사용하고 있는 MYSQL데이터베이스내의 mysql이라는 데이터베이스를 백업해 보도록 하겠습니다.

 

[root@file bin]# pwd

/usr/local/mysql/bin

[root@file bin]#
[root@file bin]# ./mysqldump -u root -p mysql > mysql.sql

Enter password: ********

[root@file bin]#

[root@file bin]# ls -l mysql.sql

-rw-r--r--    1 root     root         8073  2월 14 10:53 mysql.sql

[root@file bin]#

 

위의 예는 MYSQL의 root계정으로 mysql이라는 데이터베이스를 백업하여 mysql.sql파일에 저장한 예입니다. 정상적으로 백업이 되었다면 mysql.sql파일에는 mysql데이터베이스의 테이블 스키마와 데이터들이 SQL문으로 백업되어 있습니다.

실제로 mysqldump로 백업된 파일에 저장되는 SQL문의 형식을 방금 백업한 mysql.sql 파일의 내용을 예로 보시면 다음과 같습니다.  아래 백업결과 내용을 정확하게 살펴보기 위하여 필요한 부분들만 나타내다보니 좀 길어 졌습니다. 하지만 서버관리자라면 mysqldump로 백업되는 내용이 어떤형식과 내용으로 백업이 되는가를 정확하게 알아야하기 때문에 무리하여 길게 나타낸 것임을 양해 바랍니다.

[root@file bin]# cat mysql.sql

-- MySQL dump 9.08

--

-- Host: localhost    Database: mysql

---------------------------------------------------------

-- Server version       4.0.14

 

--

-- 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';

 

--

-- Dumping data for table 'columns_priv'

--

 

 

--

-- Table structure for table 'db'

--

 

CREATE TABLE db (

  Host char(60) binary NOT NULL default '',

  Db char(64) binary NOT NULL default '',

  User char(16) binary NOT NULL default '',

  Select_priv enum('N','Y') NOT NULL default 'N',

  Insert_priv enum('N','Y') NOT NULL default 'N',

  Update_priv enum('N','Y') NOT NULL default 'N',

  Delete_priv enum('N','Y') NOT NULL default 'N',

  Create_priv enum('N','Y') NOT NULL default 'N',

  Drop_priv enum('N','Y') NOT NULL default 'N',

  Grant_priv enum('N','Y') NOT NULL default 'N',

  References_priv enum('N','Y') NOT NULL default 'N',

  Index_priv enum('N','Y') NOT NULL default 'N',

  Alter_priv enum('N','Y') NOT NULL default 'N',

  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',

  Lock_tables_priv enum('N','Y') NOT NULL default 'N',

  PRIMARY KEY  (Host,Db,User),

  KEY User (User)

) TYPE=MyISAM COMMENT='Database privileges';

 

--

-- Dumping data for table 'db'

--

 

INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');

INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');

INSERT INTO db VALUES ('localhost','picasso','picasso','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

INSERT INTO db VALUES ('localhost','temp_db2','tempuser','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

 

--

-- Table structure for table 'func'

--

 

CREATE TABLE func (

  name char(64) binary NOT NULL default '',

  ret tinyint(1) NOT NULL default '0',

  dl char(128) NOT NULL default '',

  type enum('function','aggregate') NOT NULL default 'function',

  PRIMARY KEY  (name)

) TYPE=MyISAM COMMENT='User defined functions';

 

--

-- Dumping data for table 'func'

--

 

 

--

-- Table structure for table 'host'

--

 

CREATE TABLE host (

  Host char(60) binary NOT NULL default '',

  Db char(64) binary NOT NULL default '',

  Select_priv enum('N','Y') NOT NULL default 'N',

  Insert_priv enum('N','Y') NOT NULL default 'N',

  Update_priv enum('N','Y') NOT NULL default 'N',

  Delete_priv enum('N','Y') NOT NULL default 'N',

  Create_priv enum('N','Y') NOT NULL default 'N',

  Drop_priv enum('N','Y') NOT NULL default 'N',

  Grant_priv enum('N','Y') NOT NULL default 'N',

  References_priv enum('N','Y') NOT NULL default 'N',

  Index_priv enum('N','Y') NOT NULL default 'N',

  Alter_priv enum('N','Y') NOT NULL default 'N',

  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',

  Lock_tables_priv enum('N','Y') NOT NULL default 'N',

  PRIMARY KEY  (Host,Db)

) TYPE=MyISAM COMMENT='Host privileges;  Merged with database privileges';

 

--

-- Dumping data for table 'host'

--

 

 

--

-- Table structure for table 'tables_priv'

--

 

CREATE TABLE tables_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(60) binary NOT NULL default '',

  Grantor char(77) NOT NULL default '',

  Timestamp timestamp(14) NOT NULL,

  Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') NOT NULL default '',

  Column_priv set('Select','Insert','Update','References') NOT NULL default '',

  PRIMARY KEY  (Host,Db,User,Table_name),

  KEY Grantor (Grantor)

) TYPE=MyISAM COMMENT='Table privileges';

 

--

-- Dumping data for table 'tables_priv'

--

 

 

--

-- Table structure for table 'user'

--

 

CREATE TABLE user (

  Host varchar(60) binary NOT NULL default '',

  User varchar(16) binary NOT NULL default '',

  Password varchar(16) binary NOT NULL default '',

  Select_priv enum('N','Y') NOT NULL default 'N',

  Insert_priv enum('N','Y') NOT NULL default 'N',

  Update_priv enum('N','Y') NOT NULL default 'N',

  Delete_priv enum('N','Y') NOT NULL default 'N',

  Create_priv enum('N','Y') NOT NULL default 'N',

  Drop_priv enum('N','Y') NOT NULL default 'N',

  Reload_priv enum('N','Y') NOT NULL default 'N',

  Shutdown_priv enum('N','Y') NOT NULL default 'N',

  Process_priv enum('N','Y') NOT NULL default 'N',

  File_priv enum('N','Y') NOT NULL default 'N',

  Grant_priv enum('N','Y') NOT NULL default 'N',

  References_priv enum('N','Y') NOT NULL default 'N',

  Index_priv enum('N','Y') NOT NULL default 'N',

  Alter_priv enum('N','Y') NOT NULL default 'N',

  Show_db_priv enum('N','Y') NOT NULL default 'N',

  Super_priv enum('N','Y') NOT NULL default 'N',

  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',

  Lock_tables_priv enum('N','Y') NOT NULL default 'N',

  Execute_priv enum('N','Y') NOT NULL default 'N',

  Repl_slave_priv enum('N','Y') NOT NULL default 'N',

  Repl_client_priv enum('N','Y') NOT NULL default 'N',

  ssl_type enum('','ANY','X509','SPECIFIED') NOT NULL default '',

  ssl_cipher blob NOT NULL,

국내 최저가 네이버클라우드 서버 제공 : 88,000원/월
"무단배포금지: 클라우드포털(www.linux.co.kr)의 모든 강좌는 저작권에 의해 보호되는 콘텐츠입니다. 무단으로 복제하여 배포하는 행위는 금지되어 있습니다."

관련자료

댓글 0
등록된 댓글이 없습니다.
목록

공지사항

뉴스광장

  • 현재 회원수 :  60,037 명
  • 현재 강좌수 :  35,806 개
  • 현재 접속자 :  130 명