MySQL 간단백업

MySQL 간단 백업

MySQL 백업하기
****************************************************

MySQL 의 백업에는 두가지가 있습니다.
테이블 내부의 데이터만 백업하는 방법과 어떤 데이터 베이스에서 각 테이블 구조와 데이터를
몽땅 백업하는 두가지의 방법입니다.
앞쪽의 내부 데이터만 백업하는 방법은 SQL LEVEL BACKUP 이라고 하구요.
MySQL 안에 들어가서 작업을 합니다.

두번째방법인 전체 백업은 FULL BACKUP 이라고 하구요.
mysql 안에서 하는것이 아니고 mysqldump 라는 유틸리티를 이용해서 백업을 합니다.
이 방법은 테이블의 구조까지 다 백업을 해준다는 장점이 있습니다.

여러분들이 사용할때는 각각의 목적에 맞게 수행하셔야 합니다.
목적에 맞게 라는 말이 좀 그런데요. 각자의 상황에 맞게 대처하셔야 한다는 이야기입니다.
그럼 먼저 데이터만 백업하는 방법을 사용해 보도록 하겠습니다.
먼저 테이블을 하나 만들어 보져.

create table backupTest (
num int unsigned not null auto_increment primary key,
name varchar(20) not null,
email varchar(50) not null,
addr varchar(150) not null,
tel varchar(20) not null
);

insert into backupTest values
(null, '유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020'),
(null, '유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020'),
(null, '송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '憫梔?,'park@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858'),
(null, '유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938'),
(null, '이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');

select * from backupTest; 로 잘 들어갔는지 확인을 해봅니다.

잘 들어가 있다면 테이블의 자료들을 특정 File 로 백업을 하겠습니다.

구문은 아래와 같습니다.

mysql> select * into outfile '/tmp/backupTest.sql' from backupTest;
Query OK, 15 rows affected (0.00 sec)

mysql>

이제 백업이 되었는지 확인을 해 봐야 합니다.

터미널을 하나 더 띠우고 /tmp 디렉토리에 backupTest.sql 이 만들어져 있는지 확인을 해봅니다.

# vi /tmp/backupTest.sql 이라고 치시면
각내용이 보이게 됩니다.

[tornado@dolpung tornado]$ vi /tmp/backupTest.sql
1 유희성 tornado@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
2 홍길동 hong@dolpung.pe.kr 한양 워디메쯤 02-202-2020
3 유오성 ohSung@dolpung.pe.kr 부산.. 칭구동.. 챔피 02-222-2020
4 송종국 song1@dolpung.pe.kr 주소 모름 02-202-2020
5 박지성 park@dolpung.pe.kr 주소 모름 02-202-2020
6 희동구 ddink@dolpung.pe.kr 주소 모름 02-202-2020
7 안정환 ahn@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-5858
8 유상철 sangchul@dolpung.pe. 서울시 뭐라구, 안들? 02-202-4938
9 이을용 ul_yong@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
10 이천수 tenhand@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
11 최진철 choi1@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
12 김남일 kim111@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
13 이영표 pyo@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
14 홍명보 hong2@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
15 황선홍 hwang@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
~
~

위와 같은 내용이 잘 들어가 있다면 성공하신 겁니다.
각각의 필드 사이에는 TAB 으로 구별이 되어있습니다.
데이터를 작성하실때도 TAB 으로 구별해서 작성하시면 다시 인서트가 가능합니다.

그리고 백업을 할때 꼭 TAB 으로 구별을 하라는 법은 없습니다.
백업 구문 뒷쪽에 옵션 하나를 붙여주게 되면 됩니다.
각각의 문자로 구별을 하실수 있습니다.

아래와 같이 쿼리문장을 수행합니다.

mysql> select * INTO OUTFILE
-> '/tmp/backupTest1.sql' FIELDS TERMINATED BY ','
-> from backupTest;
Query OK, 15 rows affected (0.00 sec)

mysql>

이제 결과 값이 어떻게 나왔는지 봅시다.
다른 터미널 창에서 아래와 같이 입력합니다.
# vi /tmp/backupTest1.sql

1,유희성,tornado@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
2,홍길동,hong@dolpung.pe.kr,한양 워디메쯤,02-202-2020
3,유오성,ohSung@dolpung.pe.kr,부산.. 칭구동.. 챔피,02-222-2020
4,송종국,song1@dolpung.pe.kr,주소 모름,02-202-2020
5,박지성,park@dolpung.pe.kr,주소 모름,02-202-2020
6,희동구,ddink@dolpung.pe.kr,주소 모름,02-202-2020
7,안정환,ahn@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-5858
8,유상철,sangchul@dolpung.pe.,서울시 뭐라구, 안들?02-202-4938
9,이을용,ul_yong@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
10,이천수,tenhand@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
11,최진철,choi1@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
12,김남일,kim111@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
13,이영표,pyo@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
14,홍명보,hong2@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
15,황선홍,hwang@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020

아까는 각 필드의 구별이 TAB 으로 띠워져 있었습니다.
Fields TERMINATED BY ',' 라고 옵션을 붙였더니 TAB 대신 , 가
필드들의 구별자 역할을 해주는 것을 볼 수있습니다.
가끔 MYSQL 데이터를 MSSQL 이나 ACCESS 로 이전할때 각 필드의
공백문자를 인식 못할때가 있을 수 있습니다.
이때 위와 같이 필드 구별자를 지정하여 사용하시면 됩니다.

이제 데이터를 백업했으니 테이블에 집어 넣어야 합니다.
위에서 한 방법은 SQL 레벨 백업이라고 했습니다.
그러니 백업한 데이터를 복구할때도 SQL 차원에서 해결해야 합니다.

사용방법은 아래와 같습니다.

mysql> delete from backupTest;

mysql> LOAD DATA INFILE '/tmp/backupTest.sql'
-> INTO TABLE backupTest;
Query OK, 15 rows affected (0.00 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

mysql>
mysql> select * from backupTest;
+-----+--------+----------------------+----------------------+-------------+
| num | name | email | addr | tel |
+-----+--------+----------------------+----------------------+-------------+
| 1 | 유희성 | tornado@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 2 | 홍길동 | hong@dolpung.pe.kr | 한양 워디메쯤 | 02-202-2020 |
| 3 | 유오성 | ohSung@dolpung.pe.kr | 부산.. 칭구동.. 챔피 | 02-222-2020 |
| 4 | 송종국 | song1@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 5 | 박지성 | park@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 6 | 희동구 | ddink@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 7 | 안정환 | ahn@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-5858 |
| 8 | 유상철 | sangchul@dolpung.pe. | 서울시 뭐라구, 안들?| 02-202-4938 |
| 9 | 이을용 | ul_yong@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 10 | 이천수 | tenhand@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 11 | 최진철 | choi1@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 12 | 김남일 | kim111@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 13 | 이영표 | pyo@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 14 | 홍명보 | hong2@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 15 | 황선홍 | hwang@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
+-----+--------+----------------------+----------------------+-------------+
15 rows in set (0.00 sec)

mysql>

잘 들어간걸 확인 하실 수 있습니다.
그럼 이번에는 두번째 백업한 파일인 backupTest1.sql 을 복구시켜보겠습니다.

기존에 들어가 있는 데이터를 다 지우고 Load Data 구문을 실행합니다..
mysql> delete from backupTest;
mysql> LOAD DATA INFILE '/tmp/backupTest1.sql'
-> INTO TABLE backupTest FIELDS TERMINATED BY ',';
Query OK, 15 rows affected (0.00 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

이제 검사를 해봅시다.

mysql> select * from backupTest
-> ;
+-----+--------+----------------------+----------------------+-------------+
| num | name | email | addr | tel |
+-----+--------+----------------------+----------------------+-------------+
| 1 | 유희성 | tornado@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 2 | 홍길동 | hong@dolpung.pe.kr | 한양 워디메쯤 | 02-202-2020 |
| 3 | 유오성 | ohSung@dolpung.pe.kr | 부산.. 칭구동.. 챔피 | 02-222-2020 |
| 4 | 송종국 | song1@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 5 | 박지성 | park@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 6 | 희동구 | ddink@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 7 | 안정환 | ahn@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-5858 |
| 8 | 유상철 | sangchul@dolpung.pe. | 서울시 뭐라구, 안들?| 02-202-4938 |
| 9 | 이을용 | ul_yong@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 10 | 이천수 | tenhand@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 11 | 최진철 | choi1@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 12 | 김남일 | kim111@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 13 | 이영표 | pyo@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 14 | 홍명보 | hong2@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 15 | 황선홍 | hwang@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
+-----+--------+----------------------+----------------------+-------------+
15 rows in set (0.01 sec)

mysql>

위와 같이 들어갔다면 정상적으로 된것입니다.
만약 번호만 입력이 되고 데이터가 안들어갔다면 FIELDS TERMINATED BY 로 지정한
컬럼 구분자가 틀렸다는 이야기 입니다.
그것을 확인하시고 입력을 하신다면 데이터가 입력되는데 전혀 문제가 없을 것입니다.

이제 MySQL 의 풀레벨 백업을 해 보겠습니다.

/usr/local/mysql/bin 디렉토리를 보게 되면 mysqldump 라는 명령이 있습니다.
이 명령어를 이용해서 테이블 구조와 모든 레코드 들을 백업하겠습니다.

일단 MySQL 을 종료시켜야 합니다.
quit 나 q 를 쳐서 빠져 나옵니다.

# cd /usr/local/mysql/bin
# ./mysqldump -u root -p test > /tmp/fullBackup1.sql
Enter password:
#

-u 옵션은 유져를 지정하고
-p 는 패스워드를 지정합니다.
test 는 test 데이터베이스를 지정한 것이고
> 표시는 test 데이터 베이스의 내용을 /tmp/fullBackup1.sql 로 백업을 하겠다
라는 이야기 입니다.
만약 에러가 난다면 에러메세지를 잘 읽어보시고 대처하시기 바랍니다.

이제 vi editor 로 파일을 열어보겠습니다.
# vi /tmp/fullBackup1.sql

내용이 확실히 보이시지요??


아래의 쿼리를 다시 날려보고 먼저 백업한 파일과 비교를 하겠습니다.

# ./mysqldump --add-drop-table -u root -p test > /tmp/fullbackup1.sql

결과치의 일부분은 아래와 같습니다.


DROP TABLE IF EXISTS backuptest;
CREATE TABLE backuptest (
num int(10) unsigned NOT NULL auto_increment,
name varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
addr varchar(150) NOT NULL default '',
tel varchar(20) NOT NULL default '',
PRIMARY KEY (num)
) TYPE=MyISAM;

설명 없습니다.


아래는 -e 옵션을 붙인 겁니다.
-e 옵션은 빠른 insert 문으로 쿼리를 작성해 줍니다.

# ./mysqldump -e -u root -p test > /tmp/fullbackup2.sql


INSERT INTO backuptest VALUES (1,'유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(2,'홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020')
,(3,'유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020')
,(4,'송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020')
,(5,'박지성','park@dolpung.pe.kr','주소 모름','02-202-2020')
,(6,'희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020')
,(7,'안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858')
,(8,'유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938')
,(9,'이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(10,'이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(11,'최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(12,'김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(13,'이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(14,'홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(15,'황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');




-t 옵션을 줘서 한번 백업을 해보져
-t 옵션은 데이터만 백업합니다. 테이블 구조는 백업 안합니다.


# ./mysqldump -t -u root -p test > /tmp/fullbackup3.sql

아래와 같은 구조가 되겠지요.


--
-- Dumping data for table 'backuptest'
--

INSERT INTO backuptest VALUES (1,'유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (2,'홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020');
INSERT INTO backuptest VALUES (3,'유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020');
INSERT INTO backuptest VALUES (4,'송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (5,'박지성','park@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (6,'희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (7,'안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858');
INSERT INTO backuptest VALUES (8,'유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938');
INSERT INTO backuptest VALUES (9,'이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (10,'이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (11,'최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (12,'김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (13,'이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (14,'홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (15,'황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');



아래는 -d 옵션을 준겁니다. -d 옵션은 테이블만 덤프합니다.

# ./mysqldump -d -u root -p test > /tmp/fullbackup4.sql

결과는 다음과 같습니다.


-- MySQL dump 9.07
--
-- Host: localhost Database: test
---------------------------------------------------------
-- Server version 4.0.4-beta-max-nt

--
-- Table structure for table 'aaa'
--

CREATE TABLE aaa (
id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

--
-- Table structure for table 'backuptest'
--

CREATE TABLE backuptest (
num int(10) unsigned NOT NULL auto_increment,
name varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
addr varchar(150) NOT NULL default '',
tel varchar(20) NOT NULL default '',
PRIMARY KEY (num)
) TYPE=MyISAM;

--
-- Table structure for table 'bbb'
--

CREATE TABLE bbb (
id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;


즐 MySQL 합시다.