Zenn
Closed6

MySQL Client/Server Protocol と そのGo実装(go-sql-driver/mysql)を読む

Go
MySQL
ピン留めされたアイテム
taumutaumu

Booksになりました

https://zenn.dev/taumu/books/3cb2aac1ac1283

これは何?

MySQLの通信仕様である、MySQL Client/Server Protocolの解説書である。

本書の特徴は、その解説をMySQLの公式ガイドと実際の実装(go-sql-driver/mysql)の両輪から試みる点にある。

実際の実装を読むことにより、公式ガイドの説明をより深く理解できるようになる。

ゴール

本書ではMySQL Client/Server ProtocolとそのGoクライアント実装に触れて理解することをゴールとしている。それによる得られる効果として、主に以下の3つを挙げる。

  • MySQL(RDB)の仕様の理解を深めることができる。MySQLのクライアントやORMを使っている時に観測する挙動が、MySQLとクライアントライブラリのどこで定義された仕様なのかが分かるようになる。また、コネクションやメモリなどのリソースの使われ方やライフサイクルが理解できる。
  • MySQL周辺の低レイヤーの技術を勉強できる。MySQLの通信仕様はTCP/IPやOSや暗号化技術の上に成り立っているため、通信仕様やその実装を追っていくことで、低レイヤーの技術の実装を理解できたり、観測する挙動がどのレイヤーで定義されている仕様に基づくのかがわかるようになる。
  • MySQL Client/Server Protocolについて自習できるようになる。本書では、プロトコルの全てを解説することは目的としていない。しかしどこに何が記述されているかのあたりがつくようになるので、疑問に思ったことについてリファレンスや実装のコードに当たってみて調べることができるようになる。
taumutaumu

MySQL Client/Server Protocol の解説

はじめに、MySQLクライアントがどのようにSQLを送信し、レスポンスを受け取っているのかについて、通信仕様から追っていく。

パケット

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_packets.html

MySQLのクライアント・サーバ間通信は「パケット」という単位で行われる。
パケットの実体はバイト列であり、4バイトのヘッダと可変長のペイロードで構成される。
ヘッダの先頭3バイトはペイロードのバイト数（0~16メガバイト）。
ヘッダの4バイト目はパケットが分割された場合のシーケンスIDを表す。データが16MBを超える場合は16MBごとにパケットを分割して送受信する。

COM_QUERY コマンド

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query.html

COM_QUERYは、クライアントがSQL文を送信するためのコマンドである。

COM_QUERYコマンドのパケットには、実行したいSQL文の文字列が含まれる。

多くのSQL文はこのコマンドで実行するが、一部のSQL文は別のコマンドを利用する。プリペアドステートメント・ストアドプロシージャなど。

COM_QUERY Response

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response.html

COM_QUERYのレスポンス。下記のいずれかが返される。

  • OK_Packet
  • ERR_Packet
  • LOCAL INFILE Request（の最初のパケット）
  • Text Resultset（の最初のパケット）

どれが返されているかはリクエストヘッダ（最初の1バイト）から判断できる。例えばOK_Packetの場合は0x00となる。

OK_Packet

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html

コマンドが正常に完了したことを通知するパケット。

以下の情報が含まれる。

  • 影響を受ける行
  • last insert ID
  • 警告の数
  • etc.

ERR_Packet

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_err_packet.html

コマンドでエラーが発生したことを通知するパケット。

エラーコードや人間が読める形式のエラーメッセージなどが含まれる。エラーコードはこの辺りなどを参照。

LOCAL INFILE Request

LOAD DATA文専用のレスポンスである。ここでは省略。

Text Resultset

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_resultset.html

Text ResultsetはSELECT文のレスポンスであり、クエリで取得したデータを含む。

Resultsetは1つのパケットではなく複数のパケットから構成される。

最初に列定義を受け取り、実データを1行ずつ取得する、というような流れとなる。

Column Definition

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_resultset_column_definition.html

列定義などのメタデータを含む複数のパケットで構成される。

下記のような情報が1カラム1パケットに含まれる。

  • データベース名
  • テーブル名
  • カラム名
  • 文字セット
  • データ型
  • etc.

文字セットのidの出力例

mysql> SELECT id, collation_name FROM information_schema.collations ORDER BY id;
+-----+--------------------------+
| id  | collation_name           |
+-----+--------------------------+
|   1 | big5_chinese_ci          |
|   2 | latin2_czech_cs          |
|   3 | dec8_swedish_ci          |
|   4 | cp850_general_ci         |
|   5 | latin1_german1_ci        |
...
|  33 | utf8_general_ci          |
...
|  45 | utf8mb4_general_ci       |
...
| 192 | utf8_unicode_ci          |
...
| 224 | utf8mb4_unicode_ci       |
...
| 250 | gb18030_unicode_520_ci   |
+-----+--------------------------+
222 rows in set (0.02 sec)

Text Resultset Row

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_resultset_row.html

1行分のデータが入ったパケット。

このパケットは、NULL(0xFB)を除く全てのデータは後述するLengthEncodedString型で表現され並べられる。

補足: LengthEncodedString型について

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_dt_strings.html#sect_protocol_basic_dt_string_le

MySQL Client/Server Protocolにおいて、データやメタデータなどの可変長文字列はLengthEncodedString型で表現される。

LengthEncodedString型とは、文字列にプラスして、その先頭にその文字列長を表す整数を付与したバイト列である。

これにより、パケットの中のデータの区切りがどこにあるのかを識別できる。

taumutaumu

GoのMySQLクライアント実装

ここでは、Select文をsql.DB.Queryで実行したときの流れを見ていく。

サンプルコード
var names []string
rows, _ := db.QueryContext(ctx, "select p.name from people as p") // この部分の説明
for rows.Next() {
    var name string
    err = rows.Scan(&name)
    if err != nil {
        break
    }
    names = append(names, name)
}

ExecとQuery

database/sql ではクエリ実行のインターフェースとして Exec と Query の2種類が提供されている。

func (db *DB) Exec(query string, args ...any) (Result, error)
func (db *DB) Query(query string, args ...any) (*Rows, error)

go-sql-driver/mysql の実装を読んでいくと、双方ともCOM_QUERYコマンドを呼び出していることがわかる。

クエリを実行するまでのフローはExecとQueryでほぼ変わらない。

違いは、レスポンスが異なることである。

  • Exec は OK_Packet に対応したレスポンスを返す。
  • Queryは Resultset に対応したレスポンスを返す。

ExecはDML、QueryはSelectで利用することが理解できる。

Queryの流れ

func (mc *mysqlConn) query(query string, args []driver.Value) (*textRows, error)

  1. パラメータ付きクエリのパラメータを埋める
    prepared, err := mc.interpolateParams(query, args)
  2. コマンドを送信する
    err := mc.writeCommandPacketStr(comQuery, query)
  3. 最初のパケットを受け取り、ResultSet以外のパケットであれば処理を中断する
    resLen, err = mc.readResultSetHeaderPacket()
  4. Resultsetのカラム定義パケットを読み取り、rows構造体に詰める
    rows.rs.columns, err = mc.readColumns(resLen)
  5. rows構造体を返す

Queryの責務は、コマンド(Select文)を送信し、カラム定義を含むrows構造体を返すところまでであり、行データの取得はQueryは行わない。

パラメータ付きクエリのパラメータを埋める

prepared, err := mc.interpolateParams(query, args)

db.Query("SELECT name FROM foo WHERE user_id=?", userID)

Queryは上記のようにパラメータ付きクエリをサポートしている。すなわち、文字列「?」をプレースホルダとして、引数にわたってきたパラメータを置換する。それを実現するのがこのプロセスである。

エスケープ処理も実装されておりSQLインジェクション対策ができる。

パラメータ付きクエリはMySQLサーバではなくクライアントライブラリ側で提供している機能である。パラメータマッピングをMySQLサーバ側で行う仕組みがプリペアドステートメントである。

コマンドを送信する

err := mc.writeCommandPacketStr(comQuery, query)

byte型のスライスに COM_QUERY パケットを詰めて送信する。
コネクションの構造体がOpenしているファイルディスクリプタに対して、writeシステムコールを呼び出している。
これらのネットワーク仕様については後述する。

最初のパケットを受け取り、ResultSet以外のパケットであれば処理を中断する

resLen, err = mc.readResultSetHeaderPacket()

最初のパケットの1バイト目には、そのパケットが何のパケットであるかを識別するための情報が含まれる。
もしそれがOKパケット(0x00)、エラーパケット(0xff)、LocalInFileパケット(0xfb)であれば、そのパケットを適切に処理し終了する。処理によってエラーを返すか空のRowsを返す。
それ以外であれば、ResultSetとみなす。その場合はカラム数がパケットに含まれるので、それを取得しておく。

Resultsetのカラム定義パケットを読み取り、Rows構造体に詰める

rows.rs.columns, err = mc.readColumns(resLen)

  1. パケット1つを受信する(readPacket)
  2. パケットに含まれるカラム定義情報を読み取っていき、必要なものをRows構造体に詰めていく。
  3. 1~2をカラムの数ループする。
  4. EOFパケットを受信したら処理を終了して、Rowsを返す。

ResultSetのヘッダパケットにカラム数の情報が含まれるので、その数ループしている。このことから、1パケットに1カラムの情報が含まれることがわかる。

補足: readLengthEncodedInteger について

カラムや実データなどの可変長データは、MySQLサーバではLengthEncodedString型で定義される。
これは、文字列の先頭に文字列長を表す整数を付与したものである。
ただし「整数」「文字列」と言っているが実体はバイト列であり、「文字列長」もバイト列長を表す。

この型のデータを扱うメソッドがreadLengthEncodedIntegerとしてdriverに実装されている。
処理フローを追うことでLengthEncodedString型のデータを理解できる。

  • 先頭1バイト: 以下のいずれかを表す
    • 0~250: 文字列のバイト列長
    • 251: NULL
    • 252, 253, 254: 文字列のバイト列長が2, 3, 8バイトであることを示す（すなわち文字長が251以上ある場合に使われる）
  • 先頭1バイトが252, 253, 254であった場合、次の2~8バイトを取得してバイト列長を取得する
    • なお、この整数値はリトルエンディアンで表記される（これに限らず、MySQLプロトコルでは、数値をリトルエンディアンで扱っている）
  • ここまでで取得したバイト列長分のバイト列を取得して返す
    • バイト列以外に、isNULL、ここまで取得したバイト列長、エラーを合わせて返す
taumutaumu

RowsとRows.NextとRows.Scan

GoライブラリでSelect文を実行してその中身を取り出すための基本的な利用手順は下記の通りである。

  1. db.Queryを呼び出してrowsを受け取る
  2. rows.Nextを呼び出して1行進める
  3. rows.Scanを呼び出して行の内容を取り出す
サンプルコード
var names []string
rows, _ := db.QueryContext(ctx, "select p.name from people as p")
for rows.Next() {
    var name string
    err = rows.Scan(&name)
    if err != nil {
        break
    }
    names = append(names, name)
}

(1)のQueryは前節で説明したので、(2)~(3)について説明する。

Rowsとは

Select文で取得した行を扱う構造体。

ただしドライバの実装を追っていくにあたりコンテクストによって複数種類存在することに触れておく。

(1) sql.Rows

  • database/sql で定義
  • 構造体
  • db.Queryのレスポンス
  • MySQLクライアントの利用者から利用される
  • 後述するdriver.Rowsをフィールドに持つ

(2) driver.Rows

  • database/sql で定義
  • インターフェース
  • ドライバの実装者から実装される
  • driver.QueryerContextインターフェースのQueryContextのレスポンス
  • つまり、ドライバのクエリメソッドから driver.Rows の実装が返される

(3) textRows

  • go-sql-driver/mysql で定義
  • 構造体
  • driver.Rowsの実装

(1)~(3)の関係性は下記の通り。

Rows.Nextの流れ

  1. 利用者が sql.Rows.Next() を呼び出す
  2. sql.Rows が driver.Rows.Next の実装である textRows.readRow を呼び出す。
  3. textRows.readRow がMySQLからデータを1行取得する。

上記の2~3についてもう少し説明すると下記の通り。

  1. MySQLサーバからパケットを1つ取得(1行のデータを取得)
  2. (1)で取得した1行分のデータ（バイト列）から、1列分のデータ（バイト列）だけを取り出す
  3. そのバイト列と、列の型情報を使って、変数にマッピングする
  • マッピング元のMySQLの型情報はQueryでカラム情報を取得した時に取得済みなので、それを利用する
  • マッピング先のGoの変数の型は int64, uint64, time.Time, float64, []byte のいずれかである
  1. 2~3 を全ての列で行う
  2. マッピングした変数を呼び出し元に返す

Rows.Scan

func (rs *Rows) Scan(dest ...any) error
https://pkg.go.dev/database/sql#Rows.Scan

Rows.Next で取得したデータを取り出すためのメソッド。

Selectしたカラムと同数の変数(ポインタ)を引数に渡し、そこにデータが格納される。

内部的には型キャストであったりリフレクションを使ったりしており、できるだけ幅広い型を受け付けてデータを格納させることができるようになっている。

これのメソッドはMySQLとの通信は行なっていない。database/sql に定義されているメソッドで、ドライバは関係しない。ドライバの役割は Next でデータをMySQLに取得するところまでであり、取得したデータをアプリケーションに渡すのはドライバの責務になっていない。

taumutaumu

接続フェーズについて

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html

接続フェーズとは、MySQLサーバ・クライアント間でコネクションを確立するためのフェーズであり、コマンドフェーズの前に行われる。

前提知識の説明

MySQLのプロトコルの説明に入る前に、前提となる知識の説明を行う。

コネクションのライフサイクル

  1. MySQLサーバに接続してコネクションを作成する。
  2. コネクションに対してコマンドを実行をする。クエリを実行したり、Result Setから結果を取得したりする。
  3. コマンドが完了したらコネクションはアイドル状態（Sleep）となる。クライアントはコネクションをコネクションプールに戻す。
  4. 長時間アイドル状態になっているコネクションはクローズされる。

MySQLで使う通信プロトコル

MySQLサーバ・クライアント間通信では、通常、TCPが使われる。

https://dev.mysql.com/doc/refman/8.0/en/connecting.html

また、TCPを扱う場合は通常ソケットを利用する。

TCP と 3way handshake

TCPは、クライアント・サーバ間でコネクションを確立して通信を行うプロトコルである。

具体的には、 3way handshake を行って確立する。

https://ja.wikipedia.org/wiki/3ウェイ・ハンドシェイク

ソケット

https://ja.manpages.org/socket/7

ソケットはOSでコンピュータネットワークなどを扱うためのAPIであり、ライブラリとして提供される。

クライアントからサーバにTCPで通信する場合、実際のデータを送信するまでに、以下のような手順を踏むことになる。

  1. socket でソケットを作成
  2. connect でソケットを接続する（コネクションが確立される）
  3. send でデータを送信する

MySQL 接続フェーズの説明

ここからMySQLのプロトコルの説明に入る。

接続フェーズで行われるのは、ハンドシェイクと認証である。

それらが完了すると、OKパケットを受信してコマンドフェーズに移行する。

MySQL: Initial Handshake Packet

MySQLの接続フェーズの最初にハンドシェイクを行う。

MySQLのハンドシェイクはサーバから開始する。

サーバから送信された initial handshake packet をクライアントが受け取り、レスポンスを返して、内容に問題がなければ接続を開始できる。

通信を暗号化する場合は、SSLハンドシェイクを行う。

ハンドシェイクパケットの中身は以下を参照。

MySQL: 認証

ハンドシェイクに続いてクライアントの認証を行う。

まず、サーバから認証に使用する方法が通知される。これに対してクライアントが適切に応答する。サーバは応答を確認し、必要であれば追加の認証を求める。ということを繰り返す。

認証に使用される方法は、mysql.user テーブルの plugin 列に保存されている。

mysql> select User, plugin from mysql.user;
+---------------+-----------------------+
| User          | plugin                |
+---------------+-----------------------+
| user          | mysql_native_password |
+---------------+-----------------------+

なお、実際はハンドシェイクの中で認証は始まっており、最短ではクライアントのHandshake Response Packetでもって認証が完了することもあるし、ハンドシェイクが終わったあとも何往復も通信することもある。

taumutaumu

接続フェーズのGo実装を読む

事前知識

コネクションプール

コネクションプールは database/sql で実装されている。

一度確立したコネクションは、SQL実行後、コネクションプールに戻される(Sleep, アイドル状態)。

更に詳しくは以下を参照

どのタイミングで接続されるのか？

sql.Openを呼び出してもMySQLへの接続は行われない。

実際に Query, Execなど実際にMySQLとの接続が必要になったタイミングで呼び出される。

net パッケージ

https://pkg.go.dev/net

GoでTCP/IPレイヤーの通信を行うためのパッケージ。

Go Driverでは、TCP接続を行う Dial や接続後の通信で Read Write などのメソッドやインターフェースを利用している。

net パッケージの内部、主に通信に関わる実装では syscall パッケージや 一部の標準ライブラリがアクセスできる internal パッケージなど、更に低いレイヤーのパッケージが利用されている。

接続の流れ

  1. db.Queryが呼ばれる
  2. コネクションプールからアイドル状態のコネクションを払い出す
  3. アイドル状態のコネクションが存在しない場合、新規にコネクションを確立する

db.connector.Connect (connectorはdriverパッケージに定義されるインターフェース）

driverのconnector.Connectの流れ

  1. TCP/IPソケットソケットの接続を確立する mc.netConn, err = nd.DialContext(ctx, mc.cfg.Net, mc.cfg.Addr)
  2. KeepAliveを設定する tc.SetKeepAlive(true)
  3. Initial Handshake Packetを受け取る authData, plugin, err := mc.readHandshakePacket()
  4. Auth Responseを作成する authResp, err := mc.auth(authData, plugin)
  5. HandShake Response Packetを送信する mc.writeHandshakeResponsePacket(authResp, plugin)
  6. Auth Packetの結果を受け取り処理する mc.handleAuthResult(authData, plugin)

TCP/IPソケットソケットの接続を確立する

mc.netConn, err = nd.DialContext(ctx, mc.cfg.Net, mc.cfg.Addr)

この処理は大変壮大だが、中のコードを追っていくと以下の処理が呼ばれていることがわかる。

  1. netパッケージのsocketでsyscall.Socketを呼び出してソケットを作成し、ファイルディスクリプタを受け取る
  2. syscall.Connectを呼び出してサーバに接続する （なお、接続先がローカルアドレスの場合はsyscall.Bindが呼び出される）

なお、TCPレイヤーの操作（3 Way Handshake）はGoのコードでは行われておらず、ネイティブのライブラリをバインドして利用している。

libexec/src/syscall/zsyscall_darwin_arm64.go
//go:cgo_import_dynamic libc_bind bind "/usr/lib/libSystem.B.dylib"

// THIS FILE IS GENERATED BY THE COMMAND AT THE TOP; DO NOT EDIT

func connect(s int, addr unsafe.Pointer, addrlen _Socklen) (err error) {
	_, _, e1 := syscall(abi.FuncPCABI0(libc_connect_trampoline), uintptr(s), uintptr(addr), uintptr(addrlen))
	if e1 != 0 {
		err = errnoErr(e1)
	}
	return
}

func libc_connect_trampoline()

KeepAliveを設定する

tc.SetKeepAlive(true)

このdriverではTCP Keep Aliveを有効にしている。

簡易的に説明すると、以下のコードのように、ソケットのオプションを設定するシステムコールを送信することで有効にしている。

fd.pfd.SetsockoptInt(syscall.SOL_SOCKET, syscall.SO_KEEPALIVE, boolint(keepalive))

Initial Handshake Packetを受け取る

authData, plugin, err := mc.readHandshakePacket()

ここからMySQLのレイヤーの通信が始まる。

Initial Handshake Packetはv9とv10があるが、Go Driverは、最初にバージョンでバリデーションしてv10しか受け付けないようにしている。

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_packets_protocol_handshake_v10.html

Initial Handshake Packetにはさまざまなデータが含まれるが、driverで実際に利用されるのは mc.readHandshakePacket の戻り値になっている値だけである。

  • authData: auth-plugin-data-part-1 auth-plugin-data-part-2 を結合した文字列である。これは「Nonce」や「スクランブル」と呼ばれるランダムなデータである。
  • plugin: auth_plugin_name を指す。 "caching_sha2_password" "mysql_native_password" など。

auth-plugin-data はチャレンジ・レスポンス認証のチャレンジに当たるものである。

4. Auth Responseを作成する

authResp, err := mc.auth(authData, plugin)

authData(Nonce)とplugin(認証方式)から、パスワードをハッシュ化する。

これを次のプロセスでサーバに送信する。

ハッシュ化のロジックはpluginによって異なる。

"caching_sha2_password" の場合は以下のようになる。 ref

XOR(SHA256(password), SHA256(SHA256(SHA256(password)), Nonce))

"mysql_native_password" も同じロジックで、 SHA-256 ではなく SHA-1 が使われているのが異なる。

SHA-1は現在では安全性の懸念から使用が避けられつつあり、 "mysql_native_password" も同様である。

5. HandShake Response Packetを送信する

mc.writeHandshakeResponsePacket(authResp, plugin)

HandShake Response Packet(Client Authentication Packet)を作って送信する。

パケットの定義は下記の通りで、320と41という2種類のプロトコルがあるが、Go Driverでは必ず41の方を利用する。

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_packets_protocol_handshake_response.html#sect_protocol_connection_phase_packets_protocol_handshake_response41

送っているデータの概要は下記の通り。

  • クライアント機能フラグ
  • 文字セット。利用者が指定した値。Go Driverで設定可能な値はこちらの通り
  • 最大パケットサイズ（デフォルト値固定）
  • ユーザ名
  • Auth Response（1つ前のプロセスで作成したハッシュ化したパスワード）
  • 初期接続先データベース名
  • 認証プラグイン名

TLSで通信する場合は、HandShake Response Packetを送信する前に以下の作業をする。

  1. クライアント機能フラグのSSLフラグを立てておく
  2. SSLリクエストパケットを事前に送信する。
  3. TLSハンドシェイクを行う

6. Auth Packetの結果を受け取り処理する

mc.handleAuthResult(authData, plugin)

こちらやこちら
で書かれているように、次にサーバから返されるパケットは条件分岐する。

レスポンスの最初の1バイトを読み取って判断して後続の処理を行う。

  • OKパケット: 接続フェーズの完了を示す。これにてコマンドフェーズに移行する。
  • AuthMoreDataパケット: 2段階認証などで、追加の認証を求めることを示す。
  • AuthSwitchRequestパケット: ハンドシェイク時に使った認証メソッドが利用不可だったので、別の認証メソッドに切り替えて認証をやり直す必要があることを示す。
  • ERRパケット: 認証失敗を示す。コネクションがクローズされて通信が終了する。

最終的に認証がOKになったら、コネクションを作成して呼び出し元に返す。

このスクラップは2日前にクローズされました