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

MySQL강좌23편: 특정데이터베이스 데이터 백업과 복구

작성자 정보

  • 관리자 작성
  • 작성일

컨텐츠 정보

본문

MySQL강좌23편: 특정데이터베이스 데이터 백업과 복구

 

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

 

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

 

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

 

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

Enter password: ********

[root@sulinux bin]#

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

-rw-r--r-- 1 root root 472778  4월 30 00:12 mysql.sql

[root@sulinux bin]#

 

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

 

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

 

[root@sulinux bin]#cat mysql.sql

-- MySQL dump 10.13  Distrib 5.1.32, for pc-linux-gnu (i686)

--

-- Host: localhost    Database: mysql

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

-- Server version       5.1.32

 

--

-- Table structure for table `columns_priv`

--

 

DROP TABLE IF EXISTS `columns_priv`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `columns_priv` (

  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',

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

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';

SET character_set_client = @saved_cs_client;

 

--

-- Dumping data for table `columns_priv`

--

 

LOCK TABLES `columns_priv` WRITE;

UNLOCK TABLES;

 

--

-- Table structure for table `db`

--

 

DROP TABLE IF EXISTS `db`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `db` (

  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

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

  KEY `User` (`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';

SET character_set_client = @saved_cs_client;

 

--

-- Dumping data for table `db`

--

-- Table structure for table `event`

--

 

DROP TABLE IF EXISTS `event`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `event` (

  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

  `name` char(64) NOT NULL DEFAULT '',

  `body` longblob NOT NULL,

  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

  `execute_at` datetime DEFAULT NULL,

  `interval_value` int(11) DEFAULT NULL,

`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND',

'MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND',

'MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')

 DEFAULT NULL,

  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `last_executed` datetime DEFAULT NULL,

  `starts` datetime DEFAULT NULL,

  `ends` datetime DEFAULT NULL,

  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',

`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `body_utf8` longblob,

  PRIMARY KEY (`db`,`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

SET character_set_client = @saved_cs_client;

 

--

-- Table structure for table `func`

--

중략…

 

DROP TABLE IF EXISTS `user`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `user` (

  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 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',

  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

SET character_set_client = @saved_cs_client;

 

--

-- Dumping data for table `user`

--

 

LOCK TABLES `user` WRITE;

/*!40000 ALTER TABLE `user` DISABLE KEYS */;

INSERT INTO `user` VALUES ('localhost','root','*E9D057131C22A0D76B4AAD2C61655BDFA706E637','Y',

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','','','','',0,0,0,0),('sulinux.net','root','*E9D057131C22A0D76B4AAD2C61655BDF

A706E637','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('127.0.0.1','root','*E9D057131C22A0D76B4A

AD2C61655BDFA706E637','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('localhost','','','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','','','','',0,0,0,0),('sulinux.net','','','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,

0,0,0),('localhost','picasso','*E9D057131C22A0D76B4AAD2C61655BDFA706E637','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','','','','',0,0,0,0),('localhost','sspark','*E9D057131C22A0D76B4AAD2C61655BDFA

706E637','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','','','','',0,0,0,0),('localhost','tempuser','*E9D057131

C22A0D76B4AAD2C61655BDFA706E637','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0),('%','super',

'*E9D057131C22A0D76B4AAD2C61655BDFA706E637','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0),

('192.168..231','super','*E9D057131C22A0D76B4AAD2C61655BDFA706E637','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','','','','',0,0,0,0),('192.168..250','super','*E9D057131C22A0D76B4AAD2C61655BDFA706E637',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','','','','',0,0,0,0),('192.168.0.250','super','*E9D057131C22A0D76B4AAD

2C61655BDFA706E637','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0),('192.168.0.231','super',

'*E9D057131C22A0D76B4AAD2C61655BDFA706E637','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);

/*!40000 ALTER TABLE `user` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

 

-- Dump completed on 2009-04-2915:12:03

[root@sulinux bin]#

 

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

 

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

 

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


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

 

이번에는 MYSQL에 존재하는 일반 데이터베이스를 백업하여 복구하는 예를보도록 하겠다. 

 

현재 필자가 사용하고있는 리눅스 서버의 MYSQL에는 sspark_db라는 데이터베이스가 존재한다. 이 데이터베이스는 sspark이라는 MYSQL계정이 소유하고있는 데이터베이스이다. 따라서 아래와 같이 sspark이라는 MYSQL 계정명으로 sspark_db라는 데이터베이스를 백업하여 sspark_db.sql이라는 파일로 저장하는 예를 보인 것이다.

 

[root@sulinux bin]#./mysqldump -u sspark -p sspark_db > sspark_db.sql

Enter password: ********

[root@sulinux bin]#

[root@sulinux bin]#ls -l sspark_db.sql

-rw-r--r-- 1 root root 2068  4월 30 00:19 sspark_db.sql

[root@sulinux bin]#

 

다음은 좀전에 백업한 ssaprk_db데이터베이스의 백업내용이다. 어떻게 백업되어있는가를 확인하기 바란다. 백업이 어떤내용으로 되어있다라는 것을 알아야 정확한 복구작업을 수행할 수 있기 때문이다.

 

[root@sulinux bin]#cat sspark_db.sql

-- MySQL dump 10.13  Distrib 5.1.32, for pc-linux-gnu (i686)

--

-- Host: localhost    Database: sspark_db

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

-- Server version       5.1.32

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `testtable`

--

 

DROP TABLE IF EXISTS `testtable`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `testtable` (

  `uid` mediumint(4) unsigned NOT NULL DEFAULT '0',

  `name` varchar(12) NOT NULL DEFAULT '',

  `email` varchar(20) NOT NULL DEFAULT '',

  `website` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`uid`)

) ENGINE=MyISAM DEFAULT CHARSET=euckr;

SET character_set_client = @saved_cs_client;

 

--

-- Dumping data for table `testtable`

--

 

LOCK TABLES `testtable` WRITE;

/*!40000 ALTER TABLE `testtable` DISABLE KEYS */;

INSERT INTO `testtable` VALUES (0,'ParkSungSoo','sspark@linux.co.kr',NULL),(1,'sontaesoo','shutterbug@orgio.net',

NULL),(2,'JungWooYoung','',NULL);

/*!40000 ALTER TABLE `testtable` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

-- Dump completed on 2009-04-2915:19:38

[root@sulinux bin]#

 

 

[참고] MYSQL의 특정데이터베이스 백업 권한에 대하여

MYSQL의 root계정으로는 모든 데이터베이스를 백업할 수 있으므로 아래의 예에서 sspark대신 root를 사용해도 무방하다. 하지만 특정데이터베이스는 소유자와 root만 백업가능하므로 자기 소유가 아닌 다른 데이터베이스의 백업은 불가능하다는 점을 기억해 두기 바란다. 하지만 MYSQL의 mysql이라는 관리데이터베이스 내에 있는 db테이블과 user테이블에서 다른 MYSQL유저들에게 백업할 수 있는 권한을 줄 수도 있다.

 

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

 

[root@sulinux bin]# ./mysql -u sspark -p sspark_db < ./sspark_db.sql

 

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

 
"무단배포금지: 클라우드포털(www.linux.co.kr)의 모든 강좌는 저작권에 의해 보호되는 콘텐츠입니다. 무단으로 복제하여 배포하는 행위는 금지되어 있습니다."

관련자료

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

공지사항

뉴스광장

  • 현재 회원수 :  60,156 명
  • 현재 강좌수 :  36,513 개
  • 현재 접속자 :  292 명