MySQL Protocolに入門する

ふと気になった。

ローカル開発環境でMySQLに接続するときにはTCPかunix domain socketを使用していた。ここで、例えばAPIにアクセスするときにはさらにHTTPなどのプロトコルを使用することになるが、MySQLでは一体何のプロトコルを使用しているんだろうということが気になった。HTTPではなさそうだし。

というわけで調べていくとMySQL ProtocolというMySQL独自のプロトコルを使用していることがわかった。

MySQL :: MySQL Internals Manual :: 14 MySQL Client/Server Protocol

では具体的に見ていこうということで、ここではまずMySQL接続時にサーバから送られるInitial Handshake Packetから解析する。次に、Handshake Response Packetを構築してクライアントからサーバに送信し、OK_packetが返ってくるところまで確認する。

コードとその出力結果は以下の通り(usernameとpasswordはブログを書くときに変更している)。

package main

import (
    "bufio"
    "bytes"
    "crypto/sha1"
    "encoding/binary"
    "encoding/hex"
    "fmt"
    "io"
    "net"
)

const (
    CLIENT_PLUGIN_AUTH       = 0x00080000
    CLIENT_SECURE_CONNECTION = 0x00008000
    CLIENT_PROTOCOL_41       = 0x00000200
    CLIENT_LONG_PASSWORD     = 0x00000001
    CLIENT_TRANSACTIONS      = 0x00002000
    CLIENT_LONG_FLAG         = 0x00000004
)

var (
    username = "xxx"
    password = "xxx"
)

func main() {
    conn, err := net.Dial("tcp", "127.0.0.1:3306")
    if err != nil {
        panic(err)
    }
    defer conn.Close()
    reader := bufio.NewReader(conn)

    // Parse connection phase packets
    // See https://dev.mysql.com/doc/internals/en/connection-phase-packets.html
    header := make([]byte, 4)
    reader.Read(header)
    fmt.Println("[header]")
    fmt.Printf("%s\n", hex.Dump(header))

    payloadSize := int(uint32(header[0]) | uint32(header[1])<<8 | uint32(header[2])<<16)
    payload := make([]byte, payloadSize)
    reader.Read(payload)
    fmt.Println("[payload]")
    fmt.Printf("%s\n", hex.Dump(payload))

    offset := 0
    protocolVersion := int(payload[offset])
    fmt.Printf("  [protocol_version] %d\n", protocolVersion)
    offset++

    idx := bytes.IndexByte(payload[offset:], 0x00)
    serverVersion := payload[offset : offset+idx]
    fmt.Printf("  [server_version] %s\n", serverVersion)
    offset += idx + 1

    connectionID := binary.LittleEndian.Uint32(payload[offset : offset+4])
    fmt.Printf("  [connection ID] %d\n", connectionID)
    offset += 4

    authPluginDataPart1 := payload[offset : offset+8]
    fmt.Print("  [auth-plugin-data-part1]\n")
    fmt.Printf("    %s", hex.Dump(authPluginDataPart1))
    offset += 8

    filter := int(payload[offset])
    fmt.Printf("  [filter] %d\n", filter)
    offset++

    capabilityLower := payload[offset : offset+2]
    offset += 2

    characterSet := int(payload[offset])
    fmt.Printf("  [character set] %d\n", characterSet)
    offset++

    statusFlags := binary.LittleEndian.Uint16(payload[offset : offset+2])
    fmt.Printf("  [status flags] %d\n", statusFlags)
    offset += 2

    capabilityUpper := payload[offset : offset+2]
    offset += 2

    capabilities := binary.LittleEndian.Uint32(append(capabilityLower, capabilityUpper...))

    var authPluginDataPart2 []byte
    var authPluginName []byte
    if capabilities&CLIENT_PLUGIN_AUTH > 0 {
        lengthOfAuthPluginData := int(payload[offset])
        fmt.Printf("  [length of auth-plugin-data] %d\n", lengthOfAuthPluginData)
        offset++

        // skipped reserved 10 bytes
        offset += 10

        if capabilities&CLIENT_SECURE_CONNECTION > 0 {
            lengthAuthPluginDataPart2 := lengthOfAuthPluginData - 8
            if lengthAuthPluginDataPart2 < 13 {
                lengthAuthPluginDataPart2 = 13
            }
            authPluginDataPart2 = payload[offset : offset+lengthAuthPluginDataPart2]
            fmt.Print("  [auth-plugin-data-part2]\n")
            fmt.Printf("    %s", hex.Dump(authPluginDataPart2))
            offset += lengthAuthPluginDataPart2

            idx = bytes.IndexByte(payload[offset:], 0x00)
            authPluginName = payload[offset : offset+idx]
            fmt.Printf("  [auth-plugin name] %s\n", authPluginName)
            offset += idx + 1
        }
    } else {
        panic("not supported")
    }

    // Handshake response

    // Secure Password Authentication
    // See https://dev.mysql.com/doc/internals/en/secure-password-authentication.html
    authResponse := sha1Sum([]byte(password))
    tmp := sha1Sum(append(append(authPluginDataPart1, authPluginDataPart2[0:12]...), sha1Sum([]byte(authResponse))...))
    for i := 0; i < 20; i++ {
        authResponse[i] ^= tmp[i]
    }

    bufferSize := 4                       // header
    bufferSize += 4                       // capability flags
    bufferSize += 4                       // max-packet size
    bufferSize++                          // character set
    bufferSize += 23                      // reserved
    bufferSize += len(username) + 1       // username
    bufferSize++                          // length of auth-response
    bufferSize += len(authResponse)       // auth-response
    bufferSize += len(authPluginName) + 1 // auth plugin name + NUL
    buffer := make([]byte, bufferSize)

    offset = 0
    size := bufferSize - 4
    buffer[0] = byte(size)
    buffer[1] = byte(size >> 8)
    buffer[2] = byte(size >> 16)
    buffer[3] = 0x01
    offset += 4

    // Capability flags
    // See https://dev.mysql.com/doc/internals/en/capability-flags.html#packet-Protocol::CapabilityFlags
    capabilityFlags := uint32(CLIENT_PROTOCOL_41 |
        CLIENT_SECURE_CONNECTION | CLIENT_LONG_PASSWORD |
        CLIENT_TRANSACTIONS | CLIENT_LONG_FLAG)
    buffer[offset] = byte(capabilityFlags)
    buffer[offset+1] = byte(capabilityFlags >> 8)
    buffer[offset+2] = byte(capabilityFlags >> 16)
    buffer[offset+3] = byte(capabilityFlags >> 24)
    offset += 4

    offset += 4 // Skip max-packet size

    charSet := 0x21
    buffer[offset] = byte(charSet)
    offset++

    offset += 23 // Skip reserved

    copy(buffer[offset:], username)
    offset += len(username) + 1

    buffer[offset] = byte(len(authResponse))
    offset++

    copy(buffer[offset:], authResponse)
    offset += len(authResponse) + 1

    copy(buffer[offset:], authPluginName)

    fmt.Printf("\n[write packets: %d bytes]: \n%s\n", len(buffer), hex.Dump(buffer))
    conn.Write(buffer)

    header = make([]byte, 4)
    reader.Read(header)
    fmt.Println("[header]")
    fmt.Printf("%s\n", hex.Dump(header))

    payloadSize = int(uint32(header[0]) | uint32(header[1])>>8 | uint32(header[2])>>16)
    payload = make([]byte, payloadSize)
    reader.Read(payload)
    fmt.Println("[payload]")
    fmt.Printf("%s\n", hex.Dump(payload))
}

func sha1Sum(data []byte) []byte {
    h := sha1.New()
    io.Copy(h, bytes.NewReader(data))
    return h.Sum(nil)
}

[header]
00000000  4a 00 00 00                                       |J...|

[payload]
00000000  0a 35 2e 37 2e 32 32 00  03 00 00 00 58 51 61 25  |.5.7.22.....XQa%|
00000010  52 49 5b 63 00 ff ff 21  02 00 ff c1 15 00 00 00  |RI[c...!........|
00000020  00 00 00 00 00 00 00 12  3d 25 6e 23 01 66 6c 40  |........=%n#.fl@|
00000030  1f 63 36 00 6d 79 73 71  6c 5f 6e 61 74 69 76 65  |.c6.mysql_native|
00000040  5f 70 61 73 73 77 6f 72  64 00                    |_password.|

  [protocol_version] 10
  [server_version] 5.7.22
  [connection ID] 3
  [auth-plugin-data-part1]
    00000000  58 51 61 25 52 49 5b 63                           |XQa%RI[c|
  [filter] 0
  [character set] 33
  [status flags] 2
  [length of auth-plugin-data] 21
  [auth-plugin-data-part2]
    00000000  12 3d 25 6e 23 01 66 6c  40 1f 63 36 00           |.=%n#.fl@.c6.|
  [auth-plugin name] mysql_native_password

[write packets: 90 bytes]: 
00000000  56 00 00 01 05 a2 00 00  00 00 00 00 21 00 00 00  |V...........!...|
00000010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000020  00 00 00 00 6f 61 75 74  68 5f 75 73 65 72 00 14  |....oauth_user..|
00000030  ff 59 45 be 61 d8 e2 0b  b3 8a 30 fb ea 5a 3f 05  |.YE.a.....0..Z?.|
00000040  2b dd fe 97 00 6d 79 73  71 6c 5f 6e 61 74 69 76  |+....mysql_nativ|
00000050  65 5f 70 61 73 73 77 6f  72 64                    |e_password|

[header]
00000000  07 00 00 02                                       |....|

[payload]
00000000  00 00 00 02 00 00 00                              |.......|

以下、各やりとりの補足。