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

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

작성자 정보

  • 관리자 작성
  • 작성일

컨텐츠 정보

본문

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

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라는 데이터베이스의 이름만을 생성하신 다음에 위의 명령어을 이용하셔야 정상적인 복구가 됩니다.   

 

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

관련자료

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

공지사항

뉴스광장

  • 현재 회원수 :  60,032 명
  • 현재 강좌수 :  35,773 개
  • 현재 접속자 :  246 명