MySQL INTO OUTFILE 활용한 백업 방법

『 '데이널'의 컨텐츠에 포함된 정보는? 』

 

MySQL에서 데이터를 백업하려면 몇가지 방법이 있습니다.

 
그 중 하나가 INTO OUTFILE을 이용한 백업 방법입니다. 데이터베이스 백업이라고 하면 어렵게 들릴 수 있지만, 걱정하지 않아도 될 것이 sql만 실행하면 됩니다. 

 

MySQL INTO OUTFILE 활용한 백업

 

MySQL INTO OUTFILE 이란?

INTO OUTFILE은 MySQL에서 데이터를 파일로 내보내는 방법 중 하나입니다. 마치 여러분이 select문을 작성해서 그 내용을 바로 파일로 저장하는 방식입니다. 약간 오라클의 spool과 느낌이 비슷하죠?
 

SELECT name, start_date INTO OUTFILE '/tmp/my_data.txt' FROM emp ;

 
이 명령을 실행하면, emp 테이블의 원하는 컬럼의 데이터를 'my_data.txt'라는 파일에 저장됩니다.  INTO OUTFILE의 좋은 점은 데이터를 빠르게 내보낼 수 있다는 거예요.
 
특히 대용량 데이터를 처리할 때 유용한데요. 실제로 많은 기업들이 대량의 데이터를 백업하거나 이동할 때 INTO OUTFILE을 사용합니다. 
 
하지만 주의할 점도 있는데요. INTO OUTFILE을 사용하려면 파일을 생성할 권한이 있어야 합니다. 그리고 이미 같은 이름의 파일이 있다면 오류가 발생할 수 있으니 주의하시기 바랍니다. 
 
그리고 AWS의 rds를 사용하고 있는데, ec2에서 클라이언트로 접속해서 파일을 저장하려고 하면 역시 안될 가능성이 높습니다. 만약 안되면 권한이 충분이 있는지 확인하세요. 
 

INTO OUTFILE의 다양한 활용법

가장 기본적인 사용법은 전체 테이블을 파일로 저장하는 거지만 일부도 가능합니다. 예를 들어, 특정 조건에 맞는 데이터만 저장하고 싶다면 아래와 같이 하면 됩니다. 

SELECT name, start_date INTO OUTFILE '/tmp/my_data.txt' FROM emp WHERE start_date > '2020-01-01' ;

 
이 명령은 2020년 이후에 입사한 직원의 이름과 입사일만 파일로 저장합니다. 또 다른 재미있는 기능은 데이터 형식을 지정할 수 있다는 거예요. CSV 파일로 저장하고 싶다면 이렇게 할 수 있습니다. 

SELECT * INTO OUTFILE '/tmp/my_data .csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM emp ;

 
이렇게 하면 각 필드가 쉼표로 구분되고, 문자열은 따옴표로 감싸진 CSV 파일이 만들어집니다. 실제로 많이 이런 방식으로 데이터를 내보내고 있어요. MySQL에서는 spool 대신 INTO OUTFILE을 사용한다고 보시면 됩니다. 
 

LOAD DATA INFILE 사용 방법

이번에는 INTO OUTFILE의 짝꿍인 LOAD DATA INFILE에 대해 알아보시죠. LOAD DATA INFILE은 INTO OUTFILE과 반대로 작동합니다. 파일의 데이터를 데이터베이스로 가져오는 거죠. LOAD DATA INFILE의 기본 사용법입니다. 

LOAD DATA INFILE '/tmp/my_data.txt' INTO TABLE emp;

 
이 명령은 'my_data.txt' 파일의 내용을 emp 테이블로 load해 줍니다. LOAD DATA INFILE의 좋은 점은 INTO OUTFILE로 내보낸 데이터를 그대로 가져올 수 있다는 겁니다. 예를 들어, 우리가 앞서 CSV 형식으로 내보냈던 데이터를 다시 가져오고 싶다면 이렇게 하면 됩니다. 

LOAD DATA INFILE '/tmp/my_data .csv' INTO TABLE emp FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ;

 
이렇게 하면 CSV 파일의 데이터가 원래의 테이블 구조에 맞게 잘 들어갑니다. LOAD DATA INFILE은 정말 빠른 방법이에요. 일반적인 INSERT 문보다 훨씬 빠르게 데이터를 입력할 수 있습니다. 
 

INTO OUTFILE vs mysqldump와 차이점

INTO OUTFILE과 mysqldump 둘 다 데이터를 백업하는 방법입니다. 하지만 사용 목적과 방식이 조금 다릅니다. mysqldump는 데이터베이스 전체를 백업하는 도구죠.
 
테이블 구조, 데이터, 심지어 저장 프로시저나 트리거까지 모두 백업할 수 있어요. 반면에 INTO OUTFILE은 특정 테이블의 데이터만을 파일로 저장합니다. 테이블 구조나 다른 객체들은 저장하지 않습니다.
 
예를 들어, mysqldump를 사용하면 아래 명령어로 전체 데이터베이스를 백업할 수 있습니다. 

mysqldump -u username -p database_name > backup.sql

 
이렇게 하면 데이터베이스의 모든 것이 'backup.sql' 파일에 저장됩니다. 
 
mysqldump의 장점은 완전한 백업이 가능하다는 거예요. 나중에 이 백업으로 데이터베이스 전체를 복원할 수 있죠. 하지만 대용량 데이터를 처리할 때는 시간이 오래 걸릴 수 있어요.
 
INTO OUTFILE은 특정 데이터만 빠르게 백업하고 싶을 때 유용합니다. 대용량 데이터를 처리할 때도 mysqldump보다 빠릅니다. 실제로 한 테스트에서는 INTO OUTFILE이 mysqldump보다 약 30% 더 빠르게 데이터를 내보냈다고 합니다. 
 
 

마치며

오늘 우리는 MySQL의 INTO OUTFILE에 대해 알아봤습니다. 데이터를 파일로 내보내고, 다시 가져오는 방법, 그리고 mysqldump와의 차이점까지 살펴보았는데요.