[출처] http://metalbear.egloos.com/tag/mysql/page/1

myisamchk 사용하기

MySQL 서버를 사용하다보면 예기치 못한 문제가 발생하여 데이터가 손상되는 경우가 생길 수 있다. 만약 테이블 타입이 InnoDB 라면 트랜잭션이 적용되기 때문에 그러한 경우 자동으로 데이터를 복구하지만 테이블 타입이 MyISAM 이라면 데이터가 손상될 확률이 좀 더 높다. 그런 경우 myisamchk 유틸리티를 사용하면 손상된 데이터를 수동으로나마 복구할 수 있다. 테이블을 체크하고 복구하는 SQL 로 CHECK TABLE 과 REPAIR TABLE 이 있으나 myisamchk 에 비해 속도는 느리다. ( 또한 테이블이 날아갈 확률도 높다. )

myisamchk 유틸리티는 데이터 파일의 상태를 체크하거나 손상된 데이터 파일을 복구하는 데 사용되는 유틸리티이다. 테이블을 안전하게 유지, 보수하고 싶다면 주기적으로 myisamchk 를 실행하여 상태를 검사해보는 것이 좋을 것이다.

myisamchk 유틸리티를 이용하여 데이터 파일의 상태를 보거나 체크할 때는 상관없지만 데이터 파일을 복구하려 할 때는 반드시 MySQL 서버를 종료한 상태에서 실행해야 한다.

만약 MySQL 서버를 종요할 수 없는 피치못할 상황이라면 테이블에 Lock 를 건 다음 myisamchk 유틸리티를 실행하도록 한다.

myisamchk 유틸리티의 사용 형식은 다음과 같다.

myisamchk [옵션] <테이블명>

이 때 <테이블명> 에는 테이블명만 써서 해당 테이블을 검사할 수도 있고, *.MYI 라고 패턴문자를 사용하여 해당 디렉토리 내의 모든 테이블을 검사할 수도 있다.

다음은 myisamchk 를 이용하는 예제인데 orders 테이블의 정보를 출력하는 모습을 보여주고 있다.

C:\windows\system32>

myisamchk -d c:\APM_Setup\Server\MySQL\data\shopsample\orders

MyISAM file:         c:\APM_Setup\Server\MySQL\data\shopsample\orders

Record format:       Fixed length

Character set:       latin1 (8)

Data records:                    8  Deleted blocks:                 0

Recordlength:                   17

table description:

Key Start Len Index   Type

1   2     4   unique  long

myisamchk 유틸리티에서 사용할 수 있는 옵션은 크게 Global 옵션, 체크 옵션, 복구 옵션 그리고 그 밖의 옵션으로 나눌 수 있다. Global 옵션은 유틸리티에서 전반적으로 사용되는 일반적인 옵션이고, 체크 옵션은 테이블의 상태를 체크하는데 사용하는 옵션이며, 복구 옵션은 테이블을 복구할 때 사용하는 옵션이다. 먼저 Global 옵션은 다음과 같다.

+--------------------+-----------------------------------------------------------------+

| Global 옵션             | 설명                                                                                           |

+--------------------+-----------------------------------------------------------------+

| -#, --debug=...        | Debug 로그를 출력한다.

| -?, --help               | 도움말을 출력한다.

| -t, --tmpdir=<경로>  | 임시 파일의 경로를 설정한다.

| -s, --silent             | 침묵 모드로 작동한다. 검사도중 에러가 발생했을 때만 출력한다.

| -v, --verbose         | 상세 정보를 출력한다.

|-V, --version           | 버전을 출력한다.

| -w, --wait              | 테이블에 Lock 이 걸려있으면 기다린다.

+--------------------+-----------------------------------------------------------------+

다음은 -d 와 -v 옵션을 이용하여 테이블의 정보를 상세하게 출력한 모습이다.

C:\windows\system32>

myisamchk -d -v c:\APM_Setup\Server\MySQL\data\shopsample\orders

MyISAM file:         c:\APM_Setup\Server\MySQL\data\shopsample\orders

Record format:       Fixed length

Character set:       latin1 (8)

File-version:        1

Creation time:       2006-10-20 23:51:37

Status:              changed

Auto increment key:              1  Last value:                    17

Data records:                    8  Deleted blocks:                 0

Datafile parts:                  8  Deleted data:                   0

Datafile pointer (bytes):        4  Keyfile pointer (bytes):        3

Datafile length:               136  Keyfile length:              2048

Max datafile length:   73014444030  Max keyfile length:   17179868159

Recordlength:                   17

table description:

Key Start Len Index   Type                     Rec/key         Root  Blocksize

1   2     4   unique  long                           1         1024       1024

- 체크 옵션은 다음과 같다.

+---------------------------+----------------------------------------------------------+

| 체크 옵션                           | 설명                                                                                |

+---------------------------+----------------------------------------------------------+

| -c, --check                       | 테이블에 오류가 없는지 체크한다.

| -e, --extend-check             | 테이블에 오류가 없는지 좀 더 세밀하게 검사한다.

|                                         | 오류를 수정했음에도 불구하고 계속해서 오류가 발생할때

|                                         |  이 옵션을 부여한다. 대신 체크 속도는 느리다.

| -F, --fast                           | 테이블이 제대로 닫혀있는지 체크한다.

| -C, --check-only-changed  | 마지막으로 체크한 이후로 변경된 테이블만 체크한다.

| -f, --force                          | 체크중 에러가 발견되면 에러 테이블을 표시해놓고

|                                         | 바로 복구를 시작한다.

| -i, --information                  | 체크된 테이블의 정보를 출력한다.

| -m, --medium-check           | --extend-check 체크보다 빠르게 체크한다.

|                                         | 일반적으로 많이 사용한다.

| -U, --update-state              | 에러가 발견된 테이블을 표시해둔다.

| -T, --read-only                  | 에러가 발견되어도 테이블에 표시해두지 않는다.

+---------------------------+----------------------------------------------------------+

다음은 -eis 옵션을 주고 테이블을 체크한 모습이다.

C:\windows\system32>

myisamchk -eis -v c:\APM_Setup\Server\MySQL\data\shopsample\orders

Checking MyISAM file: c:\APM_Setup\Server\MySQL\data\shopsample\orders

No recordlinks

block_size 1024:

Key:  1:  Keyblocks used:   6%  Packed:    0%  Max levels:  1

Total:    Keyblocks used:   6%  Packed:    0%

Records:                 8    M.recordlength:       17   Packed:             0%

Recordspace used:      100%   Empty space:           0%  Blocks/Record:   1.00

Record blocks:           8    Delete blocks:         0

Record data:           136    Deleted data:          0

Lost space:              0    Linkdata:              0

다음으로 복구 옵션과 그 밖의 옵션은 다음과 같다.

+-------------------------------+------------------------------------------------------+

| 복구 옵션                                | 설명

+-------------------------------+------------------------------------------------------+

| -B, --backup                          | .MYD 파일의 백업 파일을 '<파일명>-<생성시간>.BAK' 라는

|                                              | 이름으로 만든다.

| --correct-checksum                | 테이블의 checksum 정보를 바로 잡는다.

| -D, --data-file-length=<크기>   | 복구후 다시 생성할 데이터 파일의 최대 크기를 지정한다.

| -e, --extend-check                 | 로우 단위로 세밀하게 복구한다. 따라서 많을 쓰레기 값들이

|                                             | 생길 수 있다. 파일이 완전히 손상된 경우에만 이 옵션을

|                                             | 사용하도록 한다.

| -f, --force                              | 기존의 임시 파일을 덮어쓴다.

| -r, --recover                          | 거의 모든 것을 복구한다.

| -n, --sort-recover                   | 임시 파일이 매우 크더라도 강제적으로 정렬해서 복구한다.

| -o, --safe-recover                  | 예전 버전의 복구 방법을 사용하여 복구한다. -r 보다는

|                                             | 느리지만 -r 옵션에서 복구할 수 없는 몇가지 것들을

|                                             | 복구할 수 있다.

| --character-sets-dir=...           | 문자셋 디렉토리를 지정한다.

| --set-characater-set=<문자셋> | 문자셋을 지정한다.

| -q, --quick                             | 데이터 파일(.MYD) 를 제외하고 빠르게 복구한다.

+------------------------------+-------------------------------------------------------+

+------------------+-------------------------------------------------------------------+

| 그 밖의 옵션         | 설명                                                                                              |

+------------------+-------------------------------------------------------------------+

| -a, --analyze       | 키의 분포를 분석한다. 몇몇 JOIN 을 빠르게 할 것이다.

| -d, --description   | 테이블의 정보를 출력한다.

| -S, --sort-index    | 인덱스 블록을 정렬한다. 다음 값 읽기(read-next) 의 속도를 향상시킨다.

| -R, --sort-records | 인덱에 따라 로우를 정렬한다. SELECT 와 ORDER BY 속도를 향상 시킬

| =<인덱스 번호>     | 수 있다. <인덱스 번호> 는 SHOW INDEX FROM <테이블명> 으로

|                            | 알 수 있다.

+------------------+-------------------------------------------------------------------+

테이블의 데이터 파일은 시스템이 전원이 나가서 MySQL 서버가 비정상 종료된 경우나 하드웨어의 결함 등으로 손상될 수 있다.

테이블이 손상된 경우에는 다음과 같은 에러가 출력될 수 있다.

<테이블명>.frm' is locked against change

Can't find file <테이블명>.MYI' (Errcode: <에러코드>)

Unexpected end of file

Record file is crashed

테이블이 손상되면 myisamchk 를 이용하여 복구를 실행하게 되는데, 손상된 부분을 찾아 복구 하는 과정을 알아보자.

그전에 먼저 테이블의 데이터 파일에 대해 알아야 할 필요가 있다. 앞서 설명한 바가 있지만 하나의 테이블에 대해 기본적으로 3 개의 파일이 생성된다. 각각의 파일은 다음과 같다.

+------------------+-------------------------------------------------------------------+

| 파일                    | 설명                                                                                              |

+------------------+-------------------------------------------------------------------+

| <테이블명>.MYI   | 인덱스 파일이다.

| <테이블명>.frm     | 테이블의 구조를 정의하는 파일이다.

| <테이블명>.MYD  | 데이터 파일이다.

+------------------+-------------------------------------------------------------------+

에러 코드에 대한 에러 메세지를 보는 방법

MySQL 의 bin 디렉토리를 보면 perror 라는 유틸리티를 찾을 수 있다. 이 유틸리티는 MySQL 의 에러 코드에 대한 메시지를 확인할 때 사용할 수 있는 유틸리티이다. 사용 방법은 다음과 같다.

perror [옵션] [<에러코드1> [<에러모드2>...]]

perror 을 이용하여 몇 개의 에러 코드에 대한 메시지를 출력한 예이다.

C:\windows\system32>perror 126 127 132

MySQL error:  126 = Index file is crashed

MySQL error:  127 = Record-file is crashed

MySQL error:  132 = Old database file

테이블의 복구 과정은 다음과 같이 진행된다.

1 단계 : 테이블 체크

먼저 myisamchk *.MYI 를 실행하거나 시간이 더 있다면 myisamchk -e *.MYI 를 실행하여 어떤 테이블에 오류가 있는지 체크한다. 테이블의 오류가 체크되면 2 단계로 넘어가고, 체크가 되지 않거나 체크 도중 에러가 발생하면 3 단계로 넘어간다.

2 단계 : 쉬우면서 안전한 복구

오류가 있는 테이블을 발견하였으므로 myisamchk -r -q <테이블명> 을 실행하여 빠른 복구를 실행한다. -q 옵션을 부여하였기 때문에 데이터 파일(.MYD) 은 건드리지 않고 인덱스 파일을 복구할 것이다. 이런 식으로 계속해서 다음 테이블을 복구한다.

그래도 복구가 되지 않으면 다음과 같이 시도한다.

1. 데이터 파일을 백업해둔다.

2. myisamchk -r <테이블명> 을 실행하여 복구 모드로 복구를 실행한다. 오류가 있는 로우를 삭제하면서 새로운 인덱스 파일을 생성할 것이다.

3. 그래도 안되면 myisamchk --safe-recover <테이블명> 을 실행한다. 예전 버전의 복구 방법이지만 이 옵션으로 복구될 수도 있다.

복구 도중 에러가 발생하면 3 단계로 넘어간다.

3 단계 : 어려운 복구

이 단계는 인덱스 파일의 처음 16K 가 손상되거나 잘못된 정보를 가지고 있을 때 혹은 인덱스 파일이 없을 때 실시하게 된다. 이러한 경우 필요에 따라 다음과 같이 인덱스 파일을 생성하도록 한다.

  1. 데이터 파일(.MYD) 을 다른 장소로 이동한다. 복사가 아니라 이동이다.

  2. mysql> 에 접속하여 해당 테이블을 삭제한다. TRUNCATE TABLE 문장을 사용할 것을 권장한다.

  3. 앞서 이동시켜둔 데이터 파일을 원래 위치로 복사하자. 이 때는 이동이 아니라 복사를 시켜서 만약의 사태를 대비한다.

  4. myisamchk -r -q <테이블명> 을 실행하여 복구한다. .frm 파일과 .MYD 파일을 이용하여 인덱스 파일(.MYI) 이 복구될 것이다.

4 단계 : 매우 어려운 복구

이 단계는 .frm 파일마저 심하게 손상되었을 때 실행한다. 사실 최초 테이블을 생성항 이후 .frm 파일은 변경될 일이 없기 때문에 .frm 파일이 손상되는 일은 거의 발생하지 않는다. 이러한 경우 다음과 같은 방법으로 복구를 실시한다.

  1. 백업해둔 파일이 있다면 백업해둔 .frm 파일을 원래 자리로 복사시키고 3 단계를 실시한다.

     그리고 백업해둔 .MYI 파일을 원래 자리로 복사시키고 2 단계를 실시한다. 그리고 나서 마지막으로 myisamchk -r 을 실행한다.

  2. 백업해둔 파일은 없지만 테이블의 구조를 알고 있다면 .frm 파일과 .MYI 파일은 복구할 수 있다. 먼저 다른 곳에서 새로 테이블을 생성하여 .frm 과 .MYI 파일을 만들 수 있다. 물론  .MYD 파일은 새로 만들 수는 없을 것이다. 2 단계로 가서 기존의 .MYD 파일을 이용하여  .MYI 파일을 재구성한다.

출처 : 네이버 블로그 어딘가.. -_-;; 예전에 받아서 보고 정리해놓은... 거라...

평소와 동일하게 Auto_Incremental 컬럼이 포함된 테이블에 Insert 를 할 때 다음과 같은 에러가 발생하는 경우가 생겼다.

흠...스크립트는 아래와 같다.

auto_increment 값이 위의 값과 다른 것을 확인할 수 있다.

즉, Duplicate 가 생길 수 없는 것이다.

혹시나 하여 해당 ID 값을 조회 했지만 해당 하는 값이 존재하지 않는 것으로 나왔다.

여러가지 검색을 통해 해결 방법을 나열해 봤다.

하지만 내가 해결한 방법은 간단하게 테이블 repair 를 진행한 것이다.

먼저 내가 해결한 방법이다.

아래 방법들로 진행을 하고자 노력했지만 해결되지 않았고..테이블에 대해 문제가 생겼을 것으로 판단하여

복구를 진행한 것 뿐이다.

이것이 해결한 방법이다. (운이 좋았나....)

다음은 검색을 통한 방법 공유다.

[출처] http://ktdsoss.tistory.com/389

create table t (
    i integer auto_increment,
    other_field varchar(100),
    primary key(id)
);

mysql> insert into t values (2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t values ();
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

alter table t auto_increment = 0;

create temporary table t (
    v varchar(100), 
    i integer(2) auto_increment, 
    primary key(v, i)
) engine=myisam;
insert into t (v) values ('a'),('a'),('b'),('b'),('b');
select * from t;
+---+---+
| v | i |
+---+---+
| a | 1 |
| a | 2 |
| b | 1 |
| b | 2 |
| b | 3 |
+---+---+

또 다른 방법이다.

[출처] http://jason-heo.github.io/mysql/2014/03/05/manage-dup-key2.html

MySQL 중복 키 관리 방법 (INSERT 시 중복 키 관리 방법 (INSERT IGNORE, REPLACE INTO, ON DUPLICATE UPDATE)

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다. Stackoverflow에 올린 답변 중 한국에 있는 다른 개발자들에게도 도움이 될만한 Q&A를 보기 쉽게 정리했습니다. 가능한 경우는 SQLFiddle에 샘플 데이터도 같이 올려서 실습도 해 볼 수 있도록 하였습니다. 또한 전체 Q&A를 묶어서 PDF 파일로도 배포하고 있습니다. 방문해 주시는 많은 분들에게 도움이 되었으면 좋겠습니다.

Stackoverflow URL

http://stackoverflow.com/questions/20342518/on-duplicate-key-update-value-inserting-same-values-twice/20342598

질문

다음과 같이 INSERT 구문을 사용 중이다.

INSERT INTO person VALUES(NULL, 15, 'James', 'Barkely')
    ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);

중복된 값을 여러 번 INSERT한 뒤에 SELECT를 해 보면 중복된 값이 저장되어 있다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
|  3 |         15 | James      | Barkely   |
|  4 |         15 | Cynthia    | Smith     |
|  5 |         15 | James      | Barkely   |
+----+------------+------------+-----------+
5 rows in set (0.00 sec)

무엇이 잘못된 것인가?

답변

중복 키 관리를 위해서는 중복 방지를 할 컬럼이 PRIMARY KEY이거나 UNIQUE INDEX이어야 한다. 질문자의 경우 first_name과 last_name의 조합을 이용하여 중복 관리를 하려는 것 같다. 따라서다음과 같이 last_name, last_name을 PRIMARY KEY로 설정하거나,

ALTER TABLE person ADD PRIMARY KEY (first_name, last_name) UNIQUE INDEX를 추가해야 한다.

ALTER TABLE person ADD UNIQUE INDEX (first_name, last_name)

INSERT 시 중복 키 관리를 위한 방법에는 다음과 같이 3가지가 있다.

1. INSERT IGNORE
2. REPLACE INTO …
3. INSERT INTO … ON DUPLICATE UPDATE

중복 키 관리는 본 책의 앞 부분에서 잠시 언급되었는데 각각의 특징을 좀 더 자세히 알아보도록 하자.

앞의 person 테이블을 다음과 같이 생성한 뒤에 테스트를 진행하였다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  academy_id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  PRIMARY KEY (id),
  UNIQUE INDEX (first_name, last_name)
);

INSERT IGNORE

`INSERT IGNORE`는 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시하는 단순한 방법이다. 다음의 예를 보면 중복 키 에러가 발생했을 때 INSERT 구문 자체는 오류가 발생하지 않고, 대신'0 row affected'가 출력된 것을 볼 수 있다.

mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 0 rows affected (0.00 sec)

당연히 SELECT의 결과는 2건만 존재한다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

AUTO_INCREMENT 컬럼의 값이 1, 2인 것에 주목하라

REPLACE INTO

"REPLACE INTO"는 "INSERT INTO" 구문에서 INSERT를 REPLACE로 바꾼 구문이다. 사용 방법은 "INSERT INTO"와 완벽히 동일하다.

mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 1 row affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 1 row affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 2 rows affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 2 rows affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 2 rows affected (0.00 sec)

REPLACE INTO의 결과는 INSERT IGNORE와 다르게 중복 키 오류 발생 시 ‘2 rows affected’가 출력되었다. SELECT 결과는 다음과 같다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  4 |         15 | Cynthia    | Smith     |
|  5 |         15 | James      | Barkely   |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

id가 4, 5로 변하였다. 이를 ‘2 rows affected’와 함께 종합적으로 판단한다면 “REPLACE INTO”는 중복 키 오류 발생 시 기존 레코드를 삭제하고 새로운 레코드를 입력한 것이다. 그래서 ‘2 rows affected’가 출력되었다. 1건은 DELETE, 1건은 INSERT로 보면 되고, 새로운 레코드가 입력되면서 AUTO_INCREMENT 컬럼의 값이 매번 새롭게 발급되었다.

AUTO_INCREMENT는 흔히 레코드를 식별할 수 있는 id로 사용되는데 이 값이 변경될 수 있으므로 “REPLACE INTO”는 그다지 좋은 방법이 아니다.

ON DUPLICATE UPDATE

ON DUPLICATE UPDATE는 중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정할 수 있다는 장점이 있다. 우선 기본적인 사용 방법을 보자.

mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)

INSERT 결과만 보면 “INSERT IGNORE”와 동일하다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

SELECT 결과를 보니, 중복 키 오류 발생 시 기존 레코드는 그대로 남아 있는 것 같다. 즉, id 값이 변경되지 않았다. 그렇다면 “ON DUPLICATE UPDATE”는 “INSERT IGNORE” 대비 장점은 없을까?아니다. 복잡하고 어려운 대신에 중복 키 오류 발생 시 사용자가 원하는 행동을 지정할 수 있다는 장점이 있다. 예를 위해 person 테이블 구조를 다음과 같이 변경했다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  academy_id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  insert_cnt INT,
  PRIMARY KEY (id),
  UNIQUE INDEX (first_name, last_name)
);

그런 뒤 다음과 같은 INSERT 구문을 실행하였다.

mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 2 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 2 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;

Query OK, 2 rows affected (0.00 sec) SELECT를 해 보면 insert_cnt에는 해당 중복 값이 몇 번 INSERT 시도가 되었는지 기록되어 있을 것이다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+------------+
| id | academy_id | first_name | last_name | insert_cnt |
+----+------------+------------+-----------+------------+
|  1 |         15 | James      | Barkely   |          3 |
|  2 |         15 | Cynthia    | Smith     |          2 |
+----+------------+------------+-----------+------------+
2 rows in set (0.00 sec)

이 외에도 다양한 용도로 활용될 수 있다.

주의해야 할 점은 INSERT 구문에 주어진 값으로 UPDATE하고자 할 때는 항상 “VALUES(column)”과 같이 VALUES()로 감싸야 한다는 점이다. 만약 다음과 같이 VALUES() 없이 사용한다면 기존에존재하는 레코드의 컬럼 값을 의미하게 된다.

INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ON DUPLICATE KEY UPDATE academy_id = academy_id;

앞과 같이 사용했을 때, 기존 person 테이블에 존재하는 ‘James Barkely’의 academy_id가 13이었다면, INSERT 후에도 academy_id는 여전히 13이다.

요약

• INSERT IGNORE
• REPLACE INTO …
• INSERT INTO … ON DUPLICATE UPDATE

MS Sql to MySQL 로 Migration 을 테스트로 진행해 봤다.

간단한 테스트라 Index , View, SP 등을 만들어서 옮기지 않고, 테이블 2개 4만5천건?정도의 데이터를 옮겨봤다.

MySQL WorkBench 를 이용하였으며..

짧게 진행한 부분이라 많은 부분을 공유하기 보다는 어떻게 진행하였고..

문제점에 대해서 해결한 정도다.

여기서 발생한 문제는 lower_case_table_names 가 0 으로 지정되어 있어서 소문자로 테이블이 생겨 테이블이 없다는 에러이었으며,

2번째는 언어 character set 이 맞지 않아 문제가 발생 하였다. (보통 깨져서 들어가기 마련인데...전혀 들어가지 않았다.)

아래는 하나하나 캡쳐한 것이다.

1. Source DB 설정 부분이다.

  MS SQL Server라서 ODBC 설정이 필요하다. ODBC 설정은 검색하면 잘 나와 있으니 검색하기 바란다.

2. Target DB(MySQL) 

  둘다 Local 에 설치하였지만 Host에서 접속만 가능하다면 원격지에 있는 것으로 IP 설정하면 된다.

  실제로 원격지에 있는 곳에 설정도 해 보았다.

3. Connection Test

4. Source DB에서 어떠한 DB 를 옮길 것인지 선택

5. 다시 한번 Connection 확인

6. 어떠한 테이블 / 뷰 등을 옮길 것인지 선택

7. 이전 선택한 Type들에 대해 Show selection 을 선택하면 원하는 테이블도 직접 선택이 가능한 옵션

8. 선택한 object들에 대해 체크 및 SQL 생성

9. Migration 대상 표시 및 세부 설정 지정

10. Target DB(MySQL)에 테이블 생성 및 SQL Schema Script 생성

11. 생성 되는 동안 상태 표시

12. 완료 현항 표시

  - 만약 에러 발생시 우측 화면에 오류 스크립트 표시

13. Migration Data에 대한 어떤 형태로 진행할 것인지 선태

  - Online의 경우 바로 Target DB에 생성

  - batch file 의 경우 batch file(bat) 파일로 생성되며 바로 Online DB로 Migration 은 되지 않음..

   단, batch file 을 실행 할 경우 해당 Online (Target DB)로 Migration 진행

  - 위와 동일한 개념으로 shell script 파일로 생성도 가능

  - 하단의 Option 의 경우 Migration 진행하기 전 해당 Table 을 Truncate 여부 등을 선택 가능

  - (Driver seeds data already encoded as UTF-8 )encoded를 이미 UTF-8일 경우 dump 하는 경우 (체크 하였더니 한글 부분은 깨져서 표시 되는것을 확인 -> 미체크로 한 후 진행)

  - 이외의 것들은 테스트 해 보지 않아서 잘 모르겠네요.ㅠ

14. 오류 발생1. 

  - 해당 오류의 경우 Data Migration fair 된 경우로 생성된 테이블명이 달라서 발생된 문제이다.

  - MySQL lower_case_table_names 이 0으로 되어 있어서 리눅스 mysql의 경우(My.cnf) 1로, MS 윈도우 내의 MySQL의 경우(My.ini) 2로 변경 후 MySQL 재시작을 해야한다.

   오류 발생2.

  - MySQL이 기본으로 설치하여 Latin1 으로 설정되어 있었던 부분을 확인

  - 그래서 Latin1 을 utf8mb4 로 변경 및 테이블도 character 변환 후 작업 하였더니 제대로 동작하였다.

15. 완료 후 해당 테이블에 대한 데이터 결과 및 완료 화면

이렇게 MS SQL to MySQL Migration 을 진행해 보았다.

비록 MySQL Workbench 를 이용하였으며, 단순 테이블 / 데이터 이관이었지만 추후에 필요시 해당 내용을 바탕으로 진행 된다면

분명 좋은 결과를 가져올 것이라고 예상한다.