해당 문서는 "Real Mysql 8.0" 서적을 참고하여 도출한 내용 입니다.

(아래 링크는 전혀 수익의 댓가가 없습니다. 오로지 공유 목적 입니다.)

https://book.naver.com/bookdb/book_detail.nhn?bid=20877661 

• General log 는 모든 쿼리가 남아 쿼리의 내역을 모두 확인이 힘든 경우 발생
• Percona의 pt-query-digest 를 이용하여 general log를 분석 가능
• RDS MySQL 5.7.19에서 발생한 General log 를 이용하여 아래 테스트를 진행

Real Mysql 8.0 (151 page)
Percona에서 개발한 Percona Toolkit 의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

현재 Local machine이 Windows 라 percona toolkit 을 바로 설치할 수 없었고, WSL 를 이용하여 진행 했습니다.

percona tookit 설치 진행

Percona-toolkit : https://www.percona.com/doc/percona-toolkit/3.0/installation.html

WSL 설치

https://docs.microsoft.com/ko-kr/windows/wsl/install

이렇게 설치까지 끝나고 나면 직접 General log를 AWS RDS 에서 다운받고 분석 진행 하면 됩니다.

여기서 WSL 로 파일 복사는 간단합니다.

windows 폴더에서 아래 위치를 찾아가서 옮기면 됩니다.

C:\Users\<컴퓨터명>\AppData\Local\Packages\CanonicalGroupLimited.UbuntuonWindows_<고유번호?>\LocalState\rootfs\home\<유저>

general log를 pt-query-digest 로 분석 진행 합시다.

명령어 : pt-query-digest --type='genlog' General로그파일명 > 분석파일명
$ pt-query-digest --type='genlog' mysql-general.log.2021-10-28.0 > db5-2-general.log

실재 실행한 내역

• 실행하면 아래와 같이 분석 완료까지 얼마나 남았는지 시간과 비율을 보여줘서 좋더군요.

Output 파일 내용

General log 분석에 많은 도움이 되었습니다!

Slow query 의 경우에는 아래 명령어로 진행 하면 됩니다. 

(Real Mysql 8.0 서적 152page 참고)

pt-query-digest --type='slowlog' mysql-slow.log > slowlog.log

능력이 부족하여 초심으로 돌아가 Real Mysql 8.0 공부를 시작합니다. 

Real MySQL 8.0 은 모두 2권으로 구성되어 있기 때문에, 1, 2부로 나누어서 진행 합니다.

빠르게 1회 완독을 하려고 합니다.

1차 (1부)

Start : 2021-10-19

Goal : 2021-10-31

End : 2021-11-02 (완료)

With : https://m.blog.naver.com/PostList.naver?blogId=tpgpfkwkem0 

1차 (2부)

Start : 2021-11-04

Goal : 2021-11-26

2차 (1부)

- 정리 하면서 조금 더 디테일하게 진행 합니다.

- 중요한 부분만 캐치 하는 형태로 진행 합니다.

Start : 2021-11-08

Goal : 2021-12-31

2차 (2부)

공부를 한다고 하지만..어느 순간되면 자만에 빠지기 마련이다.
내가 그런 케이스이다..

Hash index 와 B-Tree index에 대해서 제대로 몰라 진땀을 흘렸다...

다시 공부하다가 이렇게 좋은 경험담 덕분에 제대로 이해했다..

다시 한번 느낀다...공부에는 끊이 없다. 學不可以已 (학불가이이)

좋은 내용은 아래를 참고하자..

http://gywn.net/2015/01/innodb-adaptive-hash-index/

비슷한 내용이다(동일하다..)

http://tech.kakao.com/2016/04/07/innodb-adaptive-hash-index/

우연히 ARCHIVE Storage 엔진에 대한 소식을 듣고 공식 홈페이지를 통해 구글 번역과 참고를 통해 

정리해 보았다.

구글 번역이 점점 자연스러워 지고 있네..!!!!(감사합니다.ㅠ)

어디에 어떻게 적용해 볼지 고민을 해봐야겠다.(Log DB를 생각중...)

혹시라도 도움을 주실 수 있다면 댓글로 감사히 받겠습니다. 경험담 좀 부탁 드립니다.ㅠ

• 대용량의 데이터를 작은 단위의 풋프린트(footprint)없이 저장하기 위해 사용
• 설치 하려면 source 에서 configure에 --with-archive-storage-engine 옵션 추가해야 함
• •  CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE   (look in the storage/archive directory)(mysql공식 홈페이지)
• 체크 >> show variables like ‘have_archive’
• 테이블을 생성하면 하나의 테이블 포맷 파일을 생성(*.frm)
• 데이터 파일의 경우 *.ARZ / *.ARM 파일 생성
• ARN 파일은 최적화(Optimization operations)를 하는 동안 나타날 수도 있음
• 하지만 mysql5.7 에서는 ARZ/ARN/FRM 파일 3개만 생성 되
• 테이블 하나당 대략 4개의 파일이 생성된다고 보면 될 듯
• 
  
• INSERT와 SELECT만 가능(DELETE, REPLACE, UPDATE 미지원)
• • mysql5.7에서는 INSERT / REPLACE / SELECT 지원 (Order by 미지원, BLOB 등 미지원)
• Auto Increment속성 지원(다른 컬럼으로 index 를 생성되지 않음)
• row level lock
• row 추가 시(INSERT) zlib 무손실 데이터 압축 진행
• Optimizer Table를  사용하여 테이블 분석할 수 있음
• Check Table를 사용할 수 있음
• • 데이터 추가 시 압축 버퍼로 넣으며, 필요한 만큼 버퍼 플러시가 이루어 지는데, 이 때 데이터 보호를 위해 lock이 지원
  • select의 경우 강제 플러시 실행
• Bulk Insert의 경우 동시간에 다른 Insert가 발생하지 않더라도 완료 후에 볼수 있으며 부분적으로는 볼 수 있음
• 검색: 캐쉬에 없을 경우 압축을 해제한 후 select 진행
• • select 는 consistent read 로 진행
  • 대량 INSERT진행 시 SELECT를 사용하면 압축 관련 지연될 것이다.
• ARCHIVE 의 효과를 올리기 위해서는 Optimize Table , Repair Table을 사용할 수 있음
• ARCHIVE 테이블의 데이터 수는 show table status를 통해 항상 확일할 수 있음

좋은 정보가 있어서 공유 합니다.

https://www.cockroachlabs.com/docs/stable/cockroachdb-in-comparison.html#yes-feedback

CockroachDB in Comparison

* In DynamoDB, distributed transactions and ACID semantics across all data in the database, not just per row, requires an additional transaction library.

[출처] 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 를 이용하였으며, 단순 테이블 / 데이터 이관이었지만 추후에 필요시 해당 내용을 바탕으로 진행 된다면

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

MySQL 3.2 에서 MySQL5.6으로 올라오면서 바뀐것 중 하나가 password 함수이다.

즉, password 함수의 구현 알고리즘이 달라져서 암호화 된 내용이 달라졌다.

그러다 보니 해당 password 함수를 사용하는 것들이 바뀌어서 접속이 안되는 현상이 발생한다.

그래서 기존 password 함수를 사용하고자 한다면 old_password 를 설정하면 된다.

이후, DB를 재기동한 후 아래 명령어를 한번 더 확인해 보자

mysql> set old_passwords = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> update user set plugin = 'mysql_old_password';

결국 이것 저것 해보다 해답을 찾았다.

아래와 같이 진행하면 정상적으로 생성 및 접속이 가능한 것을 확인할 수 있다.

이후 접속하면 정상적으로 접속 되는 것을 확인할 수 있다.

더 좋은 해결방안이 있으면 알려주세요.ㅠ

출처 : http://jhw0604.tistory.com/125

아래 내용 중 http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/ 사이트의 내용은

출처에서 알려주는 내용처럼 sp가 컴파일되고 connection을 끊고 다시 sp 를 사용하려면 기존 컴파일을 재사용 되지 않는 다는 것이다.

그래서 응용 프로그램에서는 connection pool 연결하여 connection을 끊지 않고 캐쉬의 내용을 재사용하는 방법이 있다.

(JDBC connection pooling / 동일 connection에서 sp 를 지속적으로 실행 / 제한된 수의 sp로 설정하여 메모리 사용의 이슈가 없도록 설정)

- 발번역이라도 해서 올리려고 시도하다가 오역을 알려줄수 있다는 생각과 자괴감에 번역하다 포기했습니다....겨우 오픽im1주제에....ㅋㅋㅋ

이 문제로 많은 고민을 했었는데

MySQL은 SQL Server나 Oracle과 다르게 SP가 처음 한번만 컴파일 되고 재사용 되는것이 아니다.

http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

위 링크를 참고하면 커넥션 별로 컴파일이 관리되는데 말인데

즉 어플단에서 쿼리로 실행하나 SP로 실행시키나 컴파일하고 실행하는 과정에서 성능상 이득을 취하긴 어렵다는것!

물론 SP를 사용함으로서 프로그램과 데이터 조작과의 관계를 분리 할 수 있고(추후 SQL 튜닝 및 스키마 수정에 유리)

SP실행 권한만 줌으로서 인젝션과 같은 공격에서 상대적으로 안전하며

짧은 요청 문장으로 인한 트래픽 감소

트리거를 사용하지 않고도 데이터의 참조 무결성 유지

http://ko.wikipedia.org/wiki/%EC%A0%80%EC%9E%A5_%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80

등의 장점은 여전히 남아 있다.

그러면 컴파일 된 프로시저를 그냥 아깝게 버리느냐... 하면

mysql-proxy나 sqlrelay 등을 사용해서 Connection Pool을 구축한다면 커넥션을 한번만 맺고 재사용하기에 극복 할 수 있으니

MySQL을 사용하면 SP는 사용하면 안된다 하지 말고 커넥션 풀을 구축해서 사용하면 커넥션 비용도 감소하고 SP 컴파일도 재사용 가능하니 일석이조의 효과가!!

http://sqlrelay.sourceforge.net/

http://dev.mysql.com/downloads/mysql-proxy/