select ...into OUTFILE을 이용한 백업&복구

select ...into OUTFILE 절은 select 의 결과를 file로 만들 수 있습니다.

특징은 파일 내부에 데이터 값만 저장된 파일로 생성된다는 것입니다.

따라서 테이블 구조가 삭제된 경우에는 복구가 불가능하고, 데이터의 값만 삭제되서 복구가 필요할 때 사용할 수 있습니다.

 

구문

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

 

테스트1. professor 테이블을 백업 후 professor2 테이블로 복구하기

 

1.professor 테이블 확인

mysql> select * from professor;
+-----+-----------+--------+-------------+
| _id | name      | belong | phone       |
+-----+-----------+--------+-------------+
|   1 | 유재석    | IDE    | 01112345678 |
|   2 | 황영조    | MSE    | 01121342443 |
|   3 | 케이멀    | ESE    | 01123424343 |
| 256 | 호날두    | IME    | 01134343222 |
| 257 | 리오넬    | IDE    | 01123432432 |
+-----+-----------+--------+-------------+
5 rows in set (0.00 sec)

 

2.백업(경로 지정해주지 않을지 DATA_DIR 밑에 스키마이름의 디렉토리 아래에 파일형식으로 저장됩니다)

mysql> select * into outfile 'professor.csv' from test.professor;
Query OK, 5 rows affected (0.01 sec)


[root@ysbae test]# cat professor.csv
1       유재석  IDE     01112345678
2       황영조  MSE     01121342443
3       케이멀  ESE     01123424343
256     호날두  IME     01134343222
257     리오넬  IDE     01123432432

 

 

3.professor2테이블 생성 후 복구

mysql> create table professor2 as select * from professor where 1=2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> load data infile 'professor.csv' into table professor2;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from professor2;
+-----+-----------+--------+-------------+
| _id | name      | belong | phone       |
+-----+-----------+--------+-------------+
|   1 | 유재석    | IDE    | 01112345678 |
|   2 | 황영조    | MSE    | 01121342443 |
|   3 | 케이멀    | ESE    | 01123424343 |
| 256 | 호날두    | IME    | 01134343222 |
| 257 | 리오넬    | IDE    | 01123432432 |
+-----+-----------+--------+-------------+
5 rows in set (0.00 sec)

 

 

테스트2.professor 테이블을 truncate table 후 데이터 복구하기

 

1.truncate table

mysql> truncate table professor;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from professor;
Empty set (0.00 sec)

 

2.복구

mysql> load data infile 'professor.csv' into table professor;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from professor;
+-----+-----------+--------+-------------+
| _id | name      | belong | phone       |
+-----+-----------+--------+-------------+
|   1 | 유재석    | IDE    | 01112345678 |
|   2 | 황영조    | MSE    | 01121342443 |
|   3 | 케이멀    | ESE    | 01123424343 |
| 256 | 호날두    | IME    | 01134343222 |
| 257 | 리오넬    | IDE    | 01123432432 |
+-----+-----------+--------+-------------+
5 rows in set (0.00 sec)

 

 

**추가

select ..into DUMPFILE

 

구문

SELECT ... INTO DUMPFILE 'file_path'

파일에서 구분 기호 없이 형식화되지 않은 단일 행에 대한 결과집합을 파일로 만드는 구문.
이 명령문은 바이너리형식이므로 BLOB 값을 file로 쓰는데 유용합니다.
이미지 또는 오디오 문서를 데이터베이스에서 파일로 복사하는데 사용할 수 있습니다.

 

 

테스트. professor테이블을 DUMPFILE형식으로 만든 후 professor3 테이블로 표시하기

 

professor3테이블은 따로 생성되지는 않고, 구분기호 없이 파일형식으로 생성되었고, DB 내에서 select 절로 확인했을 때는 Ox로 바이너리 형식으로 보이는것을 확인하였습니다.

 

mysql> select * from professor into dumpfile 'professor.txt';
ERROR 1172 (42000): Result consisted of more than one row

# cat professor.txt
1유재석IDE011123456782황영조MSE01121342443[root@ysbae test]#

mysql> select load_file('professor.txt') as professor3;
+------------------------+
| professor3             |
+------------------------+
| 0x                     |
+------------------------+
1 row in set (0.00 sec)