[MySQL & PHP] 14장 파일 사용하기

정리

1. 텍스트 파일을 이용해서 데이터 가져오기와 내보내기

1. 텍스트 파일로부터 데이터 가져오기 - 임포트

테이블에 대량의 데이터를 입력할 때, MySQL 콘솔창에서 일일이 입력하는 것은 생각보다 어려운 일입니다.

보통 몇천 건, 몇만 건의 데이터를 입력할 때에는 CSV(Comma Separated Values)형식과 같은 텍스트 파일을 사용합니다. 이런 형식의 파일을 읽어들이는 것을 가져오기 또는 임포트(import)라고 합니다.

1.1 CSV 파일

CSV는 '반점으로 구분한 값'이라는 뜻으로 그 파일의 내용은 데이터가 반점(,)으로 구분된 형식의 문자(텍스트)만으로 이루어져 있습니다.

▶︎ CSV 형식의 파일

한 행이 끝날 때마다 줄바꿈을 합니다.

1.2 파일 가져오기

파일에서 데이터를 가져올 때에는 LOAD DATA INFILE이라는 명령을 사용합니다.

파일에서 데이터 읽어들이기

LOAD DATA INFILE '파일_이름' INTO TABLE 테이블_이름 옵션_설정;

CSV 파일 이외의 텍스트 파일도 읽어들일 수 있습니다. 이때, '데이터 간의 구분 문자'와 '줄바꿈 문자', '◯ ◯행 째부터 읽기 실행'등 읽어들일 데이터의 형식을 지정할 수 있습니다. 이런 내용은 앞의 명령문의 옵션 설정 부분에 다음과 같이 기술합니다.

LOAD DATA INFILE 명령으로 데이터 형식을 지정하는 옵션

FIELDS TERMINATED BY 구분_문자 기본 설정은 '\t': 탭

LINES TERMINATED BY 줄바꿈_문자 기본 설정은 '\n': 줄바꿈

IGNORE 처음에_건너_뛸_행 LINES 기본 설정은 0

다음 파일은 테이블 tb1과 같은 형식입니다. 이번에는 이 파일에 t.csv라는 이름을 붙이고, 테이블 tb1과 같은 형식의 테이블 tb1N에 가져와 보겠습니다.

윈도우즈의 경우 C 드라이브에 data 폴더 안에 t.csv 파일이 있으면 됩니다.

data.zip

맥 사용자일 경우 한글이 깨져서 나오기 때문에 이걸 받으셔야 합니다.

data.zip

여기에서 사용하는 구분 문자는 반점(,)이기 때문에, 옵션에 FIELDS TERMINATED BY ','라고 지정합니다. 그리고 CSV 파일을 저장한 폴더를 지정할 때, 경로 설정은 Windows에서도 역슬래기(\)가 아닌 빗금(/)을 사용합니다(예 : C:\data\t.csv -> C:/data/t.csv).

그럼, 실제로 만들어 보겠습니다. CSV 파일 t.csv가 C:\data 폴더에 있다고 가정하고 이 파일을 테이블 tb1N에 가져와 보겠습니다(t.csv 파일은 위의 첨부파일을 다운 받으세요).

다음 명령을 실행한다.

LOAD DATA INFILE 'c;/data/t.csv' INTO TABLE tb1N FIELDS TERMIATED  BY ',';

SELECT * FROM tb1N;으로 테이블 tb1N의 내용을 확인합니다. 5개의 레코드가 추가된 것을 확인할 수 있습니다.

(윈도우의 경우 여기까지 따라 하셨다면 A101~A105에서의 칼럼 name부분은 다 깨져 나올것입니다. 이건 윈도우의 경우 cmd가 euckr을 지원하기 때문인데요 이때문에 MySQL 툴을 사용하시는걸 추천 드립니다. HeidiSQL 또는 phpmyadmin에서 직접... 맥의 경우엔 경로가 틀리기 때문에 이 부분 이해가 안가시면 댓글 부탁 드립니다.)

예를 들어, 데이터베이스의 종류가 달라도 CSV 파일과 같은 형식이라면 가져올 수 있습니다. 데이터베이스를 운용할 때 필수 기술이라고 할 수 있습니다.

1.3 데이터를 텍스트 파일에 내보내기 - 익스포트

가져오기와는 반대로 테이블에 있는 데이터를 CSV 파일 등의 텍스트 파일로 내보낼 수가 있습니다. 이처럼 데이터를 파일로 내보내는 것을 내보내기 또는 익스포트(export)라고 합니다.

내보내기한 파일은 다른 데이터베이스나 시스템에서 이용하거나, 만일을 대비해 백업용으로 활용합니다.

다음은 내보내기를 실행하는 명령입니다.

데이터를 텍스트 파일로 내보내기

SELECT * INTO OUTFILE '파일_이름' 옵션_설정 FROM 테이블_이름;

옵션 설정에는 내보내기할 텍스트 파일의 형식을 지정합니다. 설정 방법은 윗쪽의 가져오기 설정 방법과 같으니 참고하세요.

1.4 파일 내보내기

그럼, 내보내기를 실행해 보겠습니다. C:\data 폴더에 CSV 형식의 텍스트 파일 out.csv를 생성해 보겠습니다.

다음 명령을 실행합니다.(윈도우 의 경우)

SELECT * INTO OUTFILE 'C:/data/out.csv' FIELDS TERMINATED BY ',' FROM tb1;

C:/data 폴더에 out.csv 파일이 생성되었습니다. 내보내기한 파일의 내용은 메모장과 같은 텍스트 편집기로 확인할 수 있습니다.

(스샷에 나와있는 경로는 맥에서의 경로입니다. 윈도우 의 경우엔 파란줄을 입력해 주시기 바랍니다. 맥에서는 저 경로 외에 다른 경로가 안듣는것 같습니다. 해결방법 아시는 분은 댓글 부탁 드립니다.)

여기에서는 명령 프롬프트에서 TYPE 명령을 사용해 보겠습니다. 먼저, MySQL 콘솔창을 종료하고, 명령 프롬프트에서 다음 명령을 실행합니다.

TYPE C:\data\out.csv

TYPE C:\data... 의 사이에는 공백을 한 칸 입력합니다.

(저는 맥으로 하기 때문에 다릅니다. 윈도우의 경우엔 저렇게 TYPE 을 쓰지만 맥은 경우엔 file -f 을 씁니다.)

이처럼 명령 프롬프트에서 TYPE 명령을 사용하면 텍스트 파일의 내용이 표시됩니다.

내보내기한 out.csv 파일의 데이터는 14장의 1.2 파일 가져오기 명령으로 가져오기할 수 있습니다.

2. SQL을 한 곳에 모아 작성한 파일 실행하기

2. SQL 명령을 파일에서 읽어들여 실행하기

2.1 파일에서 작성한 SQL 명령을 MySQL 콘솔창에서 실행하기

복잡하고 긴 SQL 문을 실행할 때, 일일이 MySQL 콘솔창에 입력하는 것은 매우 불편합니다. 복잡한 SQL 문을 실행할 경우, 텍스트 파일에 SQL 문을 작성해서 저장하고 그 파일을 한 번에 실행합니다. 이렇게 하면, 작업을 효율적으로 진행할 수 있습니다. 또한, 텍스트 파일에 작성한 SQL 문을 MySQL 콘솔창에 복사해서 실행할 수도 있습니다.

SQL 문을 텍스트 파일로 저장해 두면 언제든지 다시 사용하거나 변형할 수 있습니다.

메모장과 같은 텍스트 편집기로 사전에 작성해 둔 SQL 문을 MySQL 콘솔창에서 SOURCE 명령으로 실행합니다.

SQL 문이 저장된 텍스트 파일 실행하기

SOURCE 텍스트_파일_이름

그럼, 실제로 실행해 보겠습니다. USE db1과 SELECT * FROM tb; 그리고 SELECT * FROM tb1;의 내용을 작성한 텍스트 파일 sql.txt를 C:\data 폴더에 생성하고 이 파일을 불러와서 실행해 보겠습니다.

① sql.txt를 작성합니다.

USE db1;

SELECT * FROM tb;

SELECT * FROM tb1;

맥의 경우

터미널을 키신 후 다음과 같이 진행해 주세요.

sql.txt 파일을 열어서 다음과 같이 작성해 줍니다.

② 다음 명령을 실행합니다.

SOURCE C:/data/sql.txt

맥의 경우

윈도우의 경우엔 C드라이브의 data 폴더에 sql.txt 파일을 만들어서 사용했습니다.

맥의 경우엔 위의 스샷 처럼 사용을 하였습니다. 경로는 어디로 만드셔도 상관 없어요. 정확한 경로를 기억 하신다면 드래그로 경로를 잡을 수 있습니다.

SOURCE는 SQL 명령이 아니기 때문에 구분 문자(;)는 사용하지 않습니다.

자주 사용하는 내용은 파일에 저장한다

몇번이고 반복해서 만들어야 하는 테이블이나 복잡한 테이블을 MySQL 콘솔창에서 생성하는 것은 비효율적입니다. 이럴 때에는 실행 순서대로 텍스트 파일에 작성하고 나서, SOURCE 명령을 실행해서 테이블을 만들도록 합니다.

텍스트 파일은 한 번 만들어 놓으면 몇 번이고 다시 사용할 수도 있고, 내용을 변경할 수도 있습니다. 무엇보다 MySQL 콘솔창에 직접 입력하는 것이 아니므로, 꼼꼼하게 입력할 수가 있어 입력 오류를 줄일 수 있습니다.

예를 들어, 아래 tb1_make.txt의 경우 사원정보 tb1을 생성하는 SQL 문을 정리한 것입니다.

텍스트 파일(tb1_make.txt)

DROP TABLE IF EXISTS tb1;

CREATE TABLE tb1(number VARCHAR(10),name VARCHAR(10), age INT);

INSERT INTO tb1 VALUES('A101', '강신우', 40);

INSERT INTO tb1 VALUES('A102', '김기덕', 28);

INSERT INTO tb1 VALUES('A103', '김민호', 20);

INSERT INTO tb1 VALUES('A104', '문소리', 23);

INSERT INTO tb1 VALUES('A105', '박문수', 35);

텍스트 편집기로 꼼꼼하게 작성하고 data 폴더에 저장합니다. MySQL 콘솔창을 실행해서 데이터베이스를 선택하고 다음 명령을 실행합니다.

SOURCE C:/data/tb1_make.txt

이제 이 파일을 사용하면 언제든지 같은 테이블을 만들 수가 있습니다.

또한, tb1_make.txt의 첫 번째 줄에는 IF EXISTS이라고 입력되어 있어서, 생성하고자 하는 테이블이 이미 존재할 때는 DROP 명령이 실행되고, 그렇지 않으면 DROP 명령이 실행되지 않습니다.

2.2 파일에 작성한 SQL 명령을 명령 프롬프트에서 실행하기

지금까지는 SQL 명령을 실행할 때 MySQL 콘솔창을 실행해서 mysql>이라는 프롬프트 뒤에 SQL 문을 입력했습니다. 사실은 굳이 MySQL 콘솔창을 실행하지 않아도 명령 프롬프트에서 직접 SQL 문을 실행할 수 있습니다.

즉, SQL 문을 작성한 텍스트 파일을 실행할 때에도 MySQL 콘솔창을 사용하지 않아도 됩니다. 이번에는 다음과 같은 예제 sql.txt 파일을 SOURCE 명령으로 실행해 보겠습니다.

USE db1

SELECT * FROM tb;

SELECT * FORM tb1;

명령 프롬프트 에서 SQL 문을 실행하려면 다음 명령을 실행합니다.

명령 프롬프트에서 SQL 문 실행하기

mysql 데이터베이스_이름 -u 사용자_이름 -p비밀번호 -e "MySQL_콘솔창의_명령문"

-e 옵션을 설정하고 그 뒤에는 큰 따옴표 (")로 감싸서 명령을 입력합니다. 명령은 작은 따옴표(')가 아닌, 큰따옴표(")를 사용합니다. 그리고, -p비밀번호와 -e, "MySQL 콘솔창의 명령문"사이에는 공백을 한 칸씩 입력합니다.

데이터베이스 이름을 지정할 필요가 없을 때는 생략할 수 있습니다. 여기서는 sql.txt파일의 내용 중에 USE db1;이 기술되어 있기 때문에 데이터 베이스 이름은 생략할 수 있습니다.

실제로 실행해 보겠습니다. C:\data 폴더에 있는 텍스트 파일 sq.txt를 mysql명령으로 실행해 보겠습니다.

다음 명령을 실행합니다.

mysql db1 -u root -p1234 -e "SOURCE C:/data/sql.txt"

전 맥으로 진행했기 때문에 사진과 같은 경로를 적었습니다.

MySQL 콘솔창을 실행하지 않아도 텍스트 파일에 작성한 SQL 명령문을 실행할 수 있습니다.

배치 파일에 SQL 명령 기록하기

명령 프롬프트에서 실행할 수 있는 명령은 .bat라는 확장자가 붙은 배치(일괄) 파일에 텍스트 파일로 정리하여 작성해 놓으면 편리합니다. MySQL의 실행 경로를 설정해 놓으면 어느 위치에서도 MySQL을 조작할 수 있습니다.

예를 들어, 14장의 1.4 파일 내보내기처럼 파일을 조작하는 내용을 배치 파일로 작성해서 바탕화면에 저장해 두면, 바탕화면에서 실행하여 언제든지 테이블에서 내보내기 할 수 있습니다.

다음은 데이터베이스 db1의 테이블 tb1을 C:\data\out.csv 파일에 내보내기할 수 있는 배치 파일 out_file.bat 입니다.

out_file.bat의 내용(반드시 줄바꿈 없이 한 줄에 입력한다.)

mysql db1 -u root -p1234 -e "SELECT * INTO OUTFILE 'C:/data/out2.csv' FIELDS TERMINATED BY ',' FROM tb1"

3. 질의 실행 결과를 파일에 저장하기

3. SQL 문의 실행 결과를 파일에 저장하기

MySQL 콘솔창에서 입력한 명령은 화면에 표시되고 그 결과도 화면에 표시됩니다. 사용자는 화면에 표시된 실행 결과를 보고 정보를 얻을 수 있고, 실행 결과를 데이터로 활용할 수도 있습니다.

이번에는 또 다른 파일 이용 방법을 소개하겠습니다.

* MySQL 콘솔창에서 tee 명령 실행하기

* 리다이렉트를 이용하여 결과를 표준 출력하기

3.1 리다이렉트로 SQL 문의 실행 결과를 텍스트 파일에 출력하기

컴퓨터에서는 데이터를 입력하고, 처리한 결과를 출력할 수가 있습니다. 이때, 일반적으로 키보드로 입력하고, 디스플레이 장치로 결과를 출력합니다. 키보드처럼 애초부터 준비된 입력 장치를 '표준 입력', 디스플레이 장치처럼 애초부터 준비된 출력 장치를 '표준 출력'이라고 합니다.

이 '표준 입력'과 '표준 출력' 장치를 변경할 수가 있습니다. 이런 변경 작업을 리다이렉트(redirect) 또는 넘겨주기 라고 합니다. 리다이렉트는 Windows나 Linux 등의 운영체제에도 있는 기능입니다. 리다이렉트 즉, 입출력 장치를 변경하려면 >등의 기호를 사용합니다.

프롬프트에서 리다이렉트 실행하기

예를 들어 Windows의 명령 프롬프트에서 dir(Linux 터미널에서는 ls)라고 입력하면 파일과 폴더의 정보가 표시됩니다.

즉, dir의 결과는 디스플레이 장치라는 표준 출력으로 출력됩니다. 이를 리다이렉트해서 표준 출력이 아닌 abc.txt라는 파일에 저장해 보겠습니다. 다음을 실행합니다(Linux에서는 ls > abc.txt).

dir > abc.txt

저의 경우엔 맥의 경우 입니다. 윈도우 의 경우엔 C드라이브 -> 사용자 or users 폴더 -> 사용자이름 폴더 경로로 접근하시면 됩니다.

이 명령을 실행하면 dir을 실행한 결과가 텍스트 파일 abc.txt에 출력됩니다. 이는 > abc.txt을 기술함으로써 출력 결과를 abc.txt에 저장했기 때문입니다.

출력 결과가 저장된 abc.txt 파일은 실행한 장소에 저장되어 있습니다. 앞의 예에서는 C드라이브의 \Users\사용자이름_폴더(디렉터리)에 저장되었습니다. 텍스트 편집기나 TYPE abc.txt로 확인할 수 있습니다. (맥의 경우 file -f abc.txt) dir을 실행했을 때와 같은 내용이 표시됩니다.

mysql 명령으로 리다이렉트하기

그럼 이 리다이렉트 기능을 MySQL에서도 사용해 보겠습니다. MySQL 콘솔창을 실행할때, 이제까지 다음과 같은 명령을 실행했습니다.

mysql -u root -p1234

이제 리다이렉트를 사용해서 추출 결과를 파일에 출력하도록 변경해 보겠습니다. 다음 명령을 실행해서 MySQL 콘솔창을 실행합니다.

 mysql -u root -p1234 > log.txt

앞의 명령을 실행해서 MySQL 콘솔창을 실행하면 SQL 문을 실행한 결과가 화면에 표시되지 않고, 리다이렉트로 지정한 log.txt 파일에 출력됩니다. MySQL 콘솔창에 아무 결과도 표시되지 않는다고 함부로 조작하지 않도록 조심합니다.

이제 데이터베이스를 지정하고, 테이블 tb1의 내용을 SELECT로 표시하고, 마지막으로 EXIT를 사용해서 MySQL 콘솔창을 종료해 봅시다. 다시 한 번 말하지만, 실행 결과는 아무것도 표시되지 않습니다.

USE db1;

SELECT * FROM tb1;

EXIT

명령 프롬프트 화면으로 돌아왔나요? 화면에 아무것도 표시되지 않으니 불편한 점이 있을것입니다.

그럼, log.txt 파일의 내용을 확인해 보겠습니다. log.txt 파일은 실행한 폴더(이 예에서는 c:\Users\사용자_이름)에 있습니다(맥의 경우 /Users/사용자_이름). 메모장 등의 텍스트 편집기로 log.txt 파일을 엽니다. 또는 TYPE log.txt (맥의 경우 file -f log.txt)라고 입력해도 됩니다. 항상 MySQL 콘솔창에서 확인하던 내용을 텍스트 파일로 확인할 수 있습니다.

(사진은 맥용 이지만 안의 내용은 윈도우 나 맥이나 똑같습니다.)

이처럼 SELECT * FROM tb1;의 결과가 표시됩니다. 이 파일은 단순한 텍스트 파일이기 때문에 다른 시스템이나 응용프로그램에서도 자유롭게 이용할 수 있습니다.

만일, 결과가 화면에 표시되지 않아 불편하다면 14장의 2.2 파일에 작성한 SQL 명령을 명령 프롬프트에서 실행하기에서 실행한 방법과 함께 사용할 수 있습니다. 명령 프롬프트에서 SQL 문의 텍스트 파일 sql.txt를 실행하고 그 결과를 그대로 리다이렉트해서 텍스트 파일 log.txt에 출력합니다.

mysql -u root -p1234 -e "SOURCE c:/data/sql.txt" > log.txt

 

리다이렉트로 SQL 문을 입력하고 리다이렉트로 파일에 출력하기

리 다이렉트로 입력하고 출력하는 예를 소개하겠습니다.

리다이렉트로 파일을 입력할 때에는 반대 방향의 < 을 사용합니다. 다음은 리다이렉트로 텍스트 파일(sql.txt)에 저장된 SQL 문을 실행하고, 리다이렉트로 텍스트 파일(log2.txt)에 출력하는 예입니다.

mysql -u root -p1234 < c:/daata/sql.txt > c:/data/log2.txt

(위 스샷은 맥으로 했을때 입니다. 윈도우 는 회색 박스 안의 명령문을 치시면 됩니다.)

도중에 경과가 보이진 않아도 문제없습니다. 단, SQL 문에 오류가 있다 하더라도, 출력된 텟스트 파일을 확인하기 전까지는 알 수가 없으니 이 점을 주의하기 바랍니다.

3.2 tee 명령으로 SQL 문의 결과를 파일에 저장하기

MySQL 콘솔창에서 tee 명령을 사용하면 리다이렉트와 마찬가지로 결과를 파일에 출력할 수 있습니다. MySQL 콘솔창에서 다음과 같이 tee 명령을 실행하면 그다음에 실행한 결과는 파일에 저장됩니다.

실행한 결과를 파일에 저장하기

tee 출력할_파일_이름;

실행한 결과를 파일에 저장하기

결과를 log3.txt 파일에 출력하려면 MySQL 콘솔창에서 다음 명령을 실행합니다.

tee log.3.txt

이제 빈 파일 log3.txt가 생성되었습니다. 이후의 실행 결과는 화면에 표시될 뿐만 아니라  log3.txt 파일에도 출력됩니다. 또한, log3.txt 파일이 저장되는 위치는 명령을 실행한 장소 입니다. 앞의 예에서는 C 드라이브의 \users\사용자_이름 폴더 입니다. (맥의 경우 /Users/사용자이름/ 입니다. 대소문자 구분)

tee를 실행한 상태에서 다음 명령을 실행합니다.

USE db1;

SELECT * FROM tb;

SELECT * FROM tb1;

실행 결과는 평상시와 같지만, 내용은 log3.txt 파일에도 출력됩니다.

파일 출력 중지하기

파일에 출력하는 것을 중지할 때에는 notee 명령을 사용합니다.

EXIT 명령으로 MySQL 콘솔창을 종료합니다. log3.txt 파일의 내용을 텍스트 편집기나 TYPE log3.txt 명령으로 확인합니다. MySQL 콘솔창에 표시된 내용이 텍스트 형태로 log3.txt 파일에 출력된 것을 확인할 수 있습니다(맥의 경우 file -f log3.txt). 출력된 텍스트 파일은 필요한 부분만 선택해서 [복사] -> [붙혀넣기]로 다른 시스템이나 응용프로그램에서 이용할 수 있습니다. 이처럼 tee와 notee는 기억해 두면 편리한 기능입니다.

4. 데이터베이스 전체를 백업하고 복원하기

4. 데이터베이스 전체를 백업하고 복원하기

4.1 백업과 복원을 하는 방법

데이터베이스의 구성, 테이블과 칼럼의 정의, 데이터 등 데이터베이스의 모든 정보를 파일로 출력할 수가 있습니다.

덤프

데이터베이스의 모든 내용을 추출하는 것을 덤프(dump)한다고 합니다. 덤프한 파일을 사용하면 다른 서버에 같은 내용의 데이터베이스를 구축하거나 만일의 사태를 대비해서 백업을 할 수도 있습니다.

MySQL의 데이터베이스를 덤프할 때에는 명령 프롬프트에서 mysqldump 명령을 실행합니다. mysqldump 명령은 데이터베이스의 구성과 데이터를 SQL 문으로 작성합니다. 즉, CREATE TABLE... 로 테이블을 만들고, INSERT INTO... 와 같은 SQL 문을 작성합니다.

덤프로 출력된 정보는 일반 SQL 문으로 되어 있는 텍스트 입니다. 이 텍스트에서 데이터베이스의 모든 정보를 가져올 수 있으며, '덤프로 출력된 내용은 데이터베이스 그 자체'라고도 말할 수 있습니다. 따라서 이 덤프 파일은 보안상 신중을 기해야 합니다.

복원

반대로 mysqldump 명령으로 출력한 데이터를 데이터베이스에 되돌려 놓는 것을 복원(restore)이라고 합니다. 복원을 한다는 것은 데이터베이스를 처음부터 새로 만든다는 것입니다. 이 작업은 SQL 문을 모아 놓은 텍스트 파일을 mysql 명령으로 리다이렉트만 하면 되는 매우 간단한 작업입니다.

4.2 mysqldump를 사용해서 추출하기

지금까지 구축한 데이터베이스 db1의 모든 정보를 mysqldump 명령을 사용해서 덤프파일로 만들어 보겠습니다. mysqldump 명령의 실행 결과를 리다이렉트해서 파일에 출력합니다.

데이터베이스 덤프하기

mysqldump -u 사용자_이름 -p비밀번호 데이터베이스_이름 > 출력_파일_이름

그럼 실제로 확인해 보겠습니다. 데이터베이스 db1의 정보를 db1_out.txt라는 파일 이름으로 덤프하겠습니다.

명령 프롬프트에서 다음 명령을 실행합니다.

C:\Users\사용자_이름> mysqldump -u root -p1234 db1 > db1_out.txt

종료하기까지는 약간의 시간이 소요됩니다. 출력된 파일은 단순한 텍스트 파일이기 때문에 텍스트 편집기로 파일의 내용을 확인할 수 있습니다.

문장의 맨 앞에 --이나 /*과 */으로 감싼 부분은 주석입니다. 주석이 끝나는 부분에 CREATE TABLE ~을 사용해서 테이블을 생성하고 있습니다.

복원에 실패할 경우

만약, 다음 절에서 다루는 복원에 실패한 경우에는 덤프할 때, 예를 들어 --defaultcharacter-set=utf8과 같이 문자 코드 옵션을 설정해서(euckr 인 경우는 euckr로) mysqldump -u root -p1234 db1 > db1_out.txt --defaultcharacter-set=utf8을 실행해 보기 바랍니다.

4.3 덤프 파일 복원하기

덤프한 파일을 복원해 보겠습니다. 명령 프롬프트에서 파일을 리다이렉트해서 데이터베이스에 복원합니다.

데이터베이스를 덤프하고 이를 복원할 때에는 데이터를 넣을 데이터베이스가 있어야 합니다. 따라서 다른 장소에 데이터베이스를 만들거나, 데이터베이스가 존재하지 않을 때에는 사전에 준비해 두어야 합니다.

그럼, 복원을 해보겠습니다. 데이터베이스 db2를 만들고 여기에 덤프한 파일 db1_out.txt를 복원해 보겠습니다.

명령 프롬프트에서 다음 명령을 실행하면 데이터베이 db2가 생성됩니다.

mysqladmin -u root -p1234 CREATE db2

명령 프롬프트에서 다음 명령을 실행합니다.

mysql -u root -p1234 db2 < db1_out.txt

이제 데이터베이스 db2가 복제되었습니다. 만일, 오류가 발생했다면 문자 코드가 원인일 가능성이 큽니다. 이럴 때에는 다음과 같이 덤프와 복원 모두 문자 코드를 설정해서 실행해 보기 바랍니다.

4.4 문자 코드 문제

대개는 앞의 명령으로 데이터베이스를 복제할 수 있지만, MySQL를 운용하는 데 종종 발생하는 문제가 '문자 코드'입니다.

예를 들어, 복원을 하는 도중에 다음과 같은 오류가 발생해서 진행 중이던 작업이 정지할 수가 있습니다.

C:\Users\사용자_이름> mysql -u root -p1234 db2 < db1_out.txt

ERROR 1067 (42000) at line 86: Invalid default value for 'name'

단순하게 덤프 -> 복원을 하면 한국어의 멀티바이트 코드가 문제가 되어 오류가 발생할 수 있습니다. 이는 덤프와 복원을 할 때 문자 코드를 지정하지 않으면, 기본으로 설정된 문자 코드로 파일을 입출력하기 때문입니다.

오류가 발생해서 덤프 -> 복원이 안 될 때에는 문자 코드를 지정하고, 덤프와 복원을 해보기 바랍니다. 문자 코드를 지정하는 옵션은 다음과 같습니다.

--default-character-set=문자_코드

예를 들어, EUC-KR을 나타내는 euckr을 지정할 때는, --default-character-set=euckr을 설정합니다.

다음은 이 옵션을 이용하여 문자 코드에 utf8을 지정하고, 덤프를 실행하는 예입니다. db3_out.txt 파일에 덤프를 출력해서 새로 생성한 데이터베이스 db3에 복원합니다.

mysqldump -u root -p1234 db1 > db3_out.txt --default-character-set=utf8

mysqladmin -u root -p1234 CREATE db3

mysql -u root -p1234 db3 < db3_out.txt --default-character-set=utf8

테이블 잠그기

테이블을 잠가서(13장 4.2 트랜잭션 시작하기) 조작에 제한을 설정하는 LOCK 명령을 소개하겠습니다.

테이블 잠그기

LOCK TABLES 테이블_이름 LOCK의_종류

지정한 테이블에 설정하고자 하는 'LOCK의 종류'에 따라 테이블의 편집이 제한됩니다. 다음은 주로 쓰이는 LOCK의 종류입니다.

LOCK의 종류

LOCK의 종류	제한 내용
READ	SELECT 이 외에는 아무 것도 할 수 없는 읽기 전용
READ LOCAL  ※ 여기에서 사용하는 InnoDB의 경우, READ와 같음	InnoDB 이외의 저장 엔진에서는 LOCK을 설정한 클라이언트는 SELECT 기능만 사용할 수 있다. 즉, 로컬만 읽기 전용
WRITE	LOCK을 설정한 클라이언트 이외에는 모든 조작을 할 수 없다.  LOCK을 설정한 클라이언트에서는 실행할 수 있다. 즉, 다른 클라이언트의 조작을 제한한다.

다음은 테이블 my_table에 READ를 설정한 예입니다.

LOCK TABLES my_table READ;

UNLOCK은 테이블에 설정한 LOCK을 해제하는 명령입니다.

테이블에 설정한 LOCK 해제하기

UNLOCK TABLES

현재의 잠금 상태로 설정된 모든 테이블의 LOCK을 해제하려면 다음 명령을 실행합니다.

UNLOCK TABLES;

대량의 데이터를 한 건씩 INSERT 명령으로 입력하는 것은 매우 비현실적입니다. 파일을 이용한 데이터 조작 방법을 반드시 기억해 두기 바랍니다.

체크!

★ LOAD DATA INFILE... 명령으로 데이터를 가져오기 할 수 있다.

★ SELECT * INTO OUTFILE... 명령으로 데이터를 내보내기 할 수 있다.

★ SOURCE 명령으로 SQL 문을 한꺼번에 실행할 수 있다.

★ 리다이렉트를 사용해서 실행 결과를 출력할 수 있다.

★ tee 명령을 사용해서 실행 결과를 출력할 수 있다.

★ mysqladmin 명령으로 데이터베이스 전체를 백업할 수 있다.

★ 리다이렉트를 사용해서 데이터베이스를 복원할 수 있다.