MySQL통신프로토콜 - 인증시의 패킷3

Client Authentication Packet

Client Authentication Packet에서는 클라이언트에서 서버로 유저명, 패스워드등의 인증정보를 통지한다. 

client flags는 클라이언트측에서 이용가능한 옵션을 통지한다. 

max packet size는 클라이언트⇔ 서버사이에서 주고 받는 패키의 최대크기를 통지한다. 

LONGTEXT형등의 큰 데이터를 주고 받기위해서이고  패킷헤더에서 관리하고 있는 3바이트제한(16MB)하고는 관계가 없다. 

큰 데이터를 주고 받기 위해서 필요한 경우에는 mysql의 접속 파라메터인 max_allowed_packet에 큰 수치를 설정하는 것이 정석이다.  이 부분에 반영된다. 

charset number는 클라이언트 측의 캐릭터셋을 서버에 통지한다. 

명령어 라인 툴인 mysql 디폴트에서는 0x08(latin1)이 된다. 

다만 Connector/J 라든지 skip-character-set-client-handshake을 지정한 상태로 mysqld를 기동한 경우에는 서버의 캐릭터셋에 맞추어지므로 Handshake Initialization Packet에서 보내지는 서버의 캐릭터셋하고 같은 번호가 세팅된다. 

계속해서 사용자명, 암호화된 패스워드, 초기 데이터베이스명(지정한 경우만)이 송신된다. 

유저명과 데이터베이스명은 「Null Terminated String」,패스워드는 「Length Coded String」표기가 된다. 

Null Terminated String이라는 것은 문장열 종단에 0x00(null)로 표시하는 것이다. 

문자열 자신에 null문자가 포함되는 경우는 사용할 수 없지만 유저명, 데이터베이스명에서는 그것이 쓰여질 이유가 없기 때문에 문제없다.  한편 Length Coded String은 선두 1바이트(또는 복수 바이트)로 문자열의 크기를 선언해 놓는 것이다. 이것은 Null Terminated String하고는 달리 예를 들어 null문자를 포함한 바이트열이 있어서 처리할 수 있게 된다. 

Client Authentication Packet

패킷헤더 4바이트

client_flags 4바이트 , 클라이언트에서 이용가능한 옵션

max_packet_size 4바이트

charset number 1바이트

filler 23바이트  ,   예약영역, 현재는 항상 0x00

유저명 n+1바이트, 유저명, 최후는 0x00.  따라서 유저명의 바이트수를 n바이트라고 하면 n+1이 된다. 

패스워드의 scramble크기 1바이트,  패스워드의 scramble크기를 16진수표기로 저장. 현재는 0x14(20)고정. 패스워드가 없는 경우에는 0x00고정

패스워드의 scramble  20바이트  패스워드의 암호화를 위한 문자열, 패스워드가 없는 경우에는 사용되지 않음. 

데이터베이스명 n+1 데이터베이스명. 최후에는 0x00.  데이터베이스명을 지정하지 않는 경우에는 사용되지 않음. 

MySQL통신프로토콜 - 인증시의 패킷2

Handshake Initialization Packet

클라이언트로가 서버에 접속요구를 했을 시점에 서버에서 클라이언트에 「Handshake Initialization Packet」이라는 패킷이 송신된다.

패킷 헤더, 서버가 처리가능한 프로토콜의 버전, 버전용의 문자열, 세션 식별ID, 암호화를 위한 문자열, 서버 캐릭터셋이 포함된다. 

프로토콜버전은 MySQL 4.1/5.0/5.1에서는 전부 0x0a이 된다. 

서버의 버전은 서버버전및 빌드옵션등의 문자열이 포함되어 있다. 

이것은 프로그램이 해석하기 위한 정보가 아니라 사람이 눈으로 보고 알 수 있는 정보를 제공하기 위해 준비되어있다.  명령어 라인툴 mysql에서 로그인하면 버전등의 정보가 표시되지만 이 같은 형태로 서버에서 보내어진 것을 표시하고 있는 것이다. 

Handshake Initialization Packet

*패킷헤더  :4바이트    ex>43 00 00 00

*프로토콜버전:  1바이트 4.1이후버전은 0x0a(10) ex>0a

*서버버전 : n+1바이트   바이트수는 바이너리에 의존, 종단은 0x00,

*스레드ID: 4바이트  MySQL에 접속원을 식별하기위한 ID ex>03 00 00 00 

*암호화  seed: 8바이트 패스워드 암호화를 위한 문자열 ex>33 7e 45 79 55 7a 28 54 

*filler: 1바이트  예약바이트 현재는 항상 0x00

*server capabilities: 2바이트 이용가능한옵션       

*캐릭터셋 번호: 1바이트  서버의 캐릭터셋ID(16진수) 

*server status: 2바이트  서버의 상태 ex>02 00    

*filler: 13바이트  예약바이트 현재는 전부 0x00

*난수문자열: 13바이트 난수문자열, 종단은 0x00

스레드ID는 세션번호를 말한다.  SHOW PROCESSLIST명령어등을 실행하면 그 mysqld에 접속해 있는 세션 일람을 번호로 보는게 가능한데 그걸 말한다. 

스레드ID는 클라이언트당 할당되고 그 이후에는 송수신 되지 않는다. 

예를 들어 TCP/IP접속이면 서버측은 스레드ID를 송수신 하지 않아도 클라이언트의 IP어드레스하고 포트번호로 클라이언트 식별이 가능하기 때문이다. 

암호화를 위한 문자열은 패스워드를 암호화하기 위해서 사용된다. 클라이언트에서 서버로 패스워드를 송신할 때 평문으로 주고 받는 것을 방지하기 위함이다. 

MySQL에서는 인증시에 패스워드만 암호화되어 주고 받게 되지만 그것은 사전에 암호화를 위한 문자열(공통키)를 서버에서 클라이언트에 보냈기 때문이라는 것을 알 수 있다. 

Server Capabilities라는 것은 서버가 처리가능한 옵션을 지정하고 있다.  예를 들어 4.0이전과 4.1이후 버전에서는 패스워드 송수신방법이 바뀌었기 때문에 4.1이후 환경에서는 old-password를 지정해서 mysqld를 기동하지 않는 한 4.0이전의 클라이언트에서의 접속은 되지 않는다. 

캐릭터셋번호는 mysqld의 default-character-set파라미터에서 설정한 캐릭터셋에 상당하는 번호이다.  utf8이면 0x53이다.  

Server Status는 자동커밋의 유무효라든지 풀스캔이 실행되었는지 아닌지에 대한 정보가 포함되어있다. 

   **SERVER_STATUS_IN_TRANS   트랜잭션제어중

       **SERVER_STATUS_AUTOCOMMIT   자동커밋모드가 유효

       **SERVER_MORE_RESULTS_EXISTS 결과셋이 계속되고 있음(SQL문을 한번에 여러번 실행하는 경우)

       **SERVER_QUERY_NO_GOOD_INDEX_USED 효과적인 인덱스가 사용되지 못했음.

       **SERVER_QUERY_NO_INDEX_USED 인덱스가 사용되지 못했음.

       **SERVER_STATUS_CURSOR_EXISTS 커서의 최종행에 도달해있지 않음(서버측 커서이용할 때만)

       **SERVER_STATUS_LAST_ROW_SENT 커서의 최종행에 도달했음(서버측 커서 이용할 때만)

      **SERVER_STATUS_DB_DROPPED 데이터베이스가 DROP되었음.

      **SERVER_STATUS_NO_BACKSLASH_ESCAPES escape기호로 0x5c(\)를 사용하지 않음. 

MySQL통신프로토콜 - 인증시의 패킷1

인증시의 패킷

인증시에는  다음과 같은 패킷을 주고 받는다. 

클라이언트                                                                서버

       ---------TCP또는 소켓통신------------------>

      <-------①Handshake Initialization Packet-----

       --------②Client Authentication Packet ------>

     <--------③Ok Packet 또는  ④Error Packet ----

※패킷헤더 

처음, 패킷헤더에 대해서 설명해 본다. 

패킷헤더라는 것은 모든 패킷의 선두에 할당되는 영역으로 4바이트로 구성된다. 

선두3바이트가 패킷의 길이(패킷헤더 길이는 빼고), 1바이트가 패킷의 연번이다. 

예를들어 Handshake Initialization Packet에서는 ox41 00 00 =65(10진수)이면 Handshake Initialization Packet의 총 바이트수가 65바이트라는 것을 의미한다. 

패킷의 내용은 전부 16진수로 표기한다. 

이 패킷길이처럼 수치를 16진수로 표현하는 게 있으면 문자열을 16진수로 표현하는 것이 있다. 

수치표현의 경우에는 전부 little endian이다. 

패킷번호는 00으로 시작해서 왔다갔다 할때마다 1개씩 증가한다. 

새로운 명령이 되면 또 00부터 번호가 할당된다.  클라이언트에서 1패킷보내면 서버에서 복수의 패킷이 되돌아오는 것도 있기 때문에 (검색할 때등) 그럴 때 사용하는 연번이다. 

3바이트는 24비트이기 때문에 다룰 수 있는 수치의 최대값은 16,777,215바이트(약 16MB)가 된다.  LONGTEXT형등의 거대한 필드에서는 열 크기가 이것을 넘는 경우도 있지만 그럴 때에는 다음처럼 2개이상의 패킷에 걸쳐서 송수신된다. 

FF FF FF 00 ...

FF FF FF 01 ...

13 01 00 02 ... 

패킷의 크기가 「0xFF FF FF」의 경우에는 그 패킷이 16MB이상의 되기 때문에 1패킷으로는 완결되지 못하고 후속의 패킷에 계속됨을 알려주고 있다.  이  경우에는 1개의 논리 패킷이 3개의 물리적인 패킷에 걸쳐져 있게 된다. 

MySQL통신프로토콜

여기에서는 MySQL 통신프로토콜에 대해서 좀더 알아보기로 한다. 

MySQL에서는 접속할 때라든지 SQL문실행을 할 때의 갖은 처리에 있어서 클라이언트⇔서버 사이에 MySQL독자의 규칙에 따른 패킷송수신을 한다. 

전제조건에 대해서

패킷의 내부라는 것을  보통 MySQL을 사용함에 있어서  의식하는 것은 거의 없다. 

그러나 기술적으로 흥미가 있는 사람은 적지 않을 것이다. 

또  PHP나 PERL등 각종 프로그래밍언어 전용의 컨넥터(DB접속용 드라이버 프로그램)를 개량하고 싶다라고 할 때에는  프로토콜에 대한  이해가 최소한 필요하다. 

MySQL의 C API를 사용하지 않고 드라이버를 만들 때에는 클라이언트측에서 통신내용을 직접 핸들링할 필요가 있기 때문이다. 

여기에서는 다음과 같은 처리를 할 때 ,  어떤 패킷이 송수신되는 것인지 소개해보겠다. 

1. 로그인할 때 

2. 보통 갱신계열의 SELECT문을 실행할 때

3. 보통 검색계열의 SQL문을 실행할 때 

4. Prepared Satement을 실행할 때 

5. 서버사이드 커서를 이용할 때 

예로써 다음과 같은 테이블, 레코드를  test데이터베이스에 만들어 놓았다는 것을 전제로 설명해보겠다. 

    *전제가 되는 테이블과 레코드예 

  CREATE TABLE tb11 ( col1 INTEGER PRIMARY KEY, col2 VARCHAR(10), col3 DATETIME);

  INSERT INTO tb11 VALUES(1, 'abc', NOW() );

  INSERT INTO tb11 VALUES(2, 'def', NOW() );

  INSERT INTO tb11 VALUES(3, 'abc', NOW() );

  INSERT INTO tb11 VALUES(4, 'xyz', NOW() );