강좌

HOME > 강좌 >
강좌| 리눅스 및 오픈소스에 관련된 강좌를 보실 수 있습니다.
 
MYSQL베이터베이스 백업기술 mysqldump 1편
조회 : 6,081  


기술문서 : MYSQL베이터베이스 백업기술 mysqldump

 

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 성수

 

ㅇ 본 기술문서의 자세한 정보 : 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,

  x509_issuer blob NOT NULL,

  x509_subject blob NOT NULL,

  max_questions int(11) unsigned NOT NULL default '0',

  max_updates int(11) unsigned NOT NULL default '0',

  max_connections int(11) unsigned NOT NULL default '0',

  PRIMARY KEY  (Host,User)

) TYPE=MyISAM COMMENT='Users and global privileges';

 

--

-- Dumping data for table 'user'

--

 

INSERT INTO user VALUES ('localhost','root','999999992b8c86c3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);

INSERT INTO user VALUES ('file.superuser.co.kr','root','999999992b8c86c3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);

INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);

INSERT INTO user VALUES ('file.superuser.co.kr','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);

INSERT INTO user VALUES ('localhost','picasso','5fcc7dddddd45938','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);

INSERT INTO user VALUES ('localhost','tempuser','5dddddd28e45938','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);

위의 결과를 보신바와 같이 mysqldump로 백업된 데이터는 모두 데이터베이스의 스키마 (데이터베이스 테이블구조)와 데이터베이스의 데이터내용을 생성할 수 있는 SQL문이라는 것을 알 수 있습니다. 따라서 mysqldump명령어로 백업된 데이터를 복구할 때에도 백업된 SQL문을 실행하여 복구를 합니다.  이점은 분명히 기억하고 이해해 두시기 바랍니다.

 

이제 위의 백업된 mysql.sql파일을 이용하여 다시 복구할 때에는 다음과 같이 하시면 됩니다.

 

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


주의 하실 것은 기존의 mysql데이터베이스가 이미 존재하고 있다면 복구가 되지 않을 수 있습니다. 

 

이번에는 MYSQL에 존재하는 일반 데이터베이스를 백업하여 복구하는 예를 보도록 하겠습니다.  현재 필자가 사용하고 있는 리눅스 서버의 MYSQL에는 temp_db2라는 데이터베이스가 존재합니다. 이 데이터베이스는 tempuser라는 MYSQL계정이 소유하고 있는 데이터베이스입니다. 따라서 아래와 같이 tempuser라는 MYSQL 계정명으로 temp_db2라는 데이터베이스를 백업하여 temp_db2.sql이라는 파일로 저장하는 예를 보인 것입니다.

 

[root@file bin]# pwd

/usr/local/mysql/bin

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

Enter password: ********

[root@file bin]#

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

-rw-r--r--    1 root     root          384  2 14 11:03 temp_db2.sql

[root@file bin]#

 

 

[참고사항]
MYSQL
root계정으로는 모든 데이터베이스를 백업할 수 있으므로 아래의 예에서 tempuser대신 root를 사용하여도 무방합니다. 하지만 특정 데이터베이스는 소유자와 root만 백업가능하므로 자기 소유가 아닌 다른 데이터베이스의 백업은 불가능하다는 점을 기억해 두시기 바랍니다.

 

위와 같이 백업한 데이터를 다시 복구할 때에는 다음과 같이 하시기 바랍니다.

[root@file bin]# ./mysql -u tempuser -p temp_db2 < ./temp_db2.sql


마찬가지로 주의하실 점은 복구대상이 되는 temp_db2라는 데이터베이스에 복구할 테이블이 이미 존재하고 있다면 복구실패가 될 것입니다. 따라서 위와 같이 복구하실 때에는 temp_db2라는 데이터베이스의 이름만을 생성하신 다음에 위의 명령어을 이용하셔야 정상적인 복구가 됩니다.

 

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 성수

 

ㅇ 본 기술문서의 자세한 정보 : http://www.superuser.co.kr/linuxcommandbible/

 

 

 


[원글링크] : https://www.linux.co.kr/home2/board/subbs/board.php?bo_table=lecture&wr_id=1402


이 글을 트위터로 보내기 이 글을 페이스북으로 보내기 이 글을 미투데이로 보내기

 
박성수
파파
헐렁고수