MySQL Client/Server Protocol と そのGo実装(go-sql-driver/mysql)を読む
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
Booksになりました
これは何?
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について自習できるようになる。本書では、プロトコルの全てを解説することは目的としていない。しかしどこに何が記述されているかのあたりがつくようになるので、疑問に思ったことについてリファレンスや実装のコードに当たってみて調べることができるようになる。
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
MySQL Client/Server Protocol の解説
はじめに、MySQLクライアントがどのようにSQLを送信し、レスポンスを受け取っているのかについて、通信仕様から追っていく。
パケット
MySQLのクライアント・サーバ間通信は「パケット」という単位で行われる。
パケットの実体はバイト列であり、4バイトのヘッダと可変長のペイロードで構成される。
ヘッダの先頭3バイトはペイロードのバイト数(0~16メガバイト)。
ヘッダの4バイト目はパケットが分割された場合のシーケンスIDを表す。データが16MBを超える場合は16MBごとにパケットを分割して送受信する。
COM_QUERY コマンド
COM_QUERYは、クライアントがSQL文を送信するためのコマンドである。
COM_QUERYコマンドのパケットには、実行したいSQL文の文字列が含まれる。
多くのSQL文はこのコマンドで実行するが、一部のSQL文は別のコマンドを利用する。プリペアドステートメント・ストアドプロシージャなど。
COM_QUERY Response
COM_QUERYのレスポンス。下記のいずれかが返される。
- OK_Packet
- ERR_Packet
- LOCAL INFILE Request(の最初のパケット)
- Text Resultset(の最初のパケット)
どれが返されているかはリクエストヘッダ(最初の1バイト)から判断できる。例えばOK_Packetの場合は0x00となる。
OK_Packet
コマンドが正常に完了したことを通知するパケット。
以下の情報が含まれる。
- 影響を受ける行
- last insert ID
- 警告の数
- etc.
ERR_Packet
コマンドでエラーが発生したことを通知するパケット。
エラーコードや人間が読める形式のエラーメッセージなどが含まれる。エラーコードはこの辺りなどを参照。
LOCAL INFILE Request
LOAD DATA文専用のレスポンスである。ここでは省略。
Text Resultset
Text ResultsetはSELECT文のレスポンスであり、クエリで取得したデータを含む。
Resultsetは1つのパケットではなく複数のパケットから構成される。
-
Column Definition
- 列定義
- 1列1パケット
-
Text Resultset Row
- 実際のデータ
- 1行1パケット
最初に列定義を受け取り、実データを1行ずつ取得する、というような流れとなる。
Column Definition
列定義などのメタデータを含む複数のパケットで構成される。
下記のような情報が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
1行分のデータが入ったパケット。
このパケットは、NULL(0xFB)を除く全てのデータは後述するLengthEncodedString型で表現され並べられる。
補足: LengthEncodedString型について
MySQL Client/Server Protocolにおいて、データやメタデータなどの可変長文字列はLengthEncodedString型で表現される。
LengthEncodedString型とは、文字列にプラスして、その先頭にその文字列長を表す整数を付与したバイト列である。
これにより、パケットの中のデータの区切りがどこにあるのかを識別できる。
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
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)
- パラメータ付きクエリのパラメータを埋める
prepared, err := mc.interpolateParams(query, args)
- コマンドを送信する
err := mc.writeCommandPacketStr(comQuery, query)
- 最初のパケットを受け取り、ResultSet以外のパケットであれば処理を中断する
resLen, err = mc.readResultSetHeaderPacket()
- Resultsetのカラム定義パケットを読み取り、rows構造体に詰める
rows.rs.columns, err = mc.readColumns(resLen)
- 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つを受信する(readPacket)
- パケットに含まれるカラム定義情報を読み取っていき、必要なものをRows構造体に詰めていく。
- 1~2をカラムの数ループする。
- 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、ここまで取得したバイト列長、エラーを合わせて返す
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
RowsとRows.NextとRows.Scan
GoライブラリでSelect文を実行してその中身を取り出すための基本的な利用手順は下記の通りである。
- db.Queryを呼び出してrowsを受け取る
- rows.Nextを呼び出して1行進める
- 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の流れ
- 利用者が
sql.Rows.Next()
を呼び出す -
sql.Rows
がdriver.Rows.Next
の実装であるtextRows.readRow
を呼び出す。 -
textRows.readRow
がMySQLからデータを1行取得する。
上記の2~3についてもう少し説明すると下記の通り。
- MySQLサーバからパケットを1つ取得(1行のデータを取得)
- (1)で取得した1行分のデータ(バイト列)から、1列分のデータ(バイト列)だけを取り出す
- そのバイト列と、列の型情報を使って、変数にマッピングする
- マッピング元のMySQLの型情報はQueryでカラム情報を取得した時に取得済みなので、それを利用する
- マッピング先のGoの変数の型は int64, uint64, time.Time, float64, []byte のいずれかである
- 2~3 を全ての列で行う
- マッピングした変数を呼び出し元に返す
Rows.Scan
func (rs *Rows) Scan(dest ...any) error
Rows.Next
で取得したデータを取り出すためのメソッド。
Selectしたカラムと同数の変数(ポインタ)を引数に渡し、そこにデータが格納される。
内部的には型キャストであったりリフレクションを使ったりしており、できるだけ幅広い型を受け付けてデータを格納させることができるようになっている。
これのメソッドはMySQLとの通信は行なっていない。database/sql
に定義されているメソッドで、ドライバは関係しない。ドライバの役割は Next
でデータをMySQLに取得するところまでであり、取得したデータをアプリケーションに渡すのはドライバの責務になっていない。
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
接続フェーズについて
接続フェーズとは、MySQLサーバ・クライアント間でコネクションを確立するためのフェーズであり、コマンドフェーズの前に行われる。
前提知識の説明
MySQLのプロトコルの説明に入る前に、前提となる知識の説明を行う。
コネクションのライフサイクル
- MySQLサーバに接続してコネクションを作成する。
- コネクションに対してコマンドを実行をする。クエリを実行したり、Result Setから結果を取得したりする。
- コマンドが完了したらコネクションはアイドル状態(Sleep)となる。クライアントはコネクションをコネクションプールに戻す。
- 長時間アイドル状態になっているコネクションはクローズされる。
MySQLで使う通信プロトコル
MySQLサーバ・クライアント間通信では、通常、TCPが使われる。
また、TCPを扱う場合は通常ソケットを利用する。
TCP と 3way handshake
TCPは、クライアント・サーバ間でコネクションを確立して通信を行うプロトコルである。
具体的には、 3way handshake を行って確立する。
ソケット
ソケットはOSでコンピュータネットワークなどを扱うためのAPIであり、ライブラリとして提供される。
クライアントからサーバにTCPで通信する場合、実際のデータを送信するまでに、以下のような手順を踏むことになる。
-
socket
でソケットを作成 -
connect
でソケットを接続する(コネクションが確立される) -
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でもって認証が完了することもあるし、ハンドシェイクが終わったあとも何往復も通信することもある。
![taumu](https://res.cloudinary.com/zenn/image/fetch/s--We3qD5Av--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/e067f45831.jpeg)
接続フェーズのGo実装を読む
事前知識
コネクションプール
コネクションプールは database/sql で実装されている。
一度確立したコネクションは、SQL実行後、コネクションプールに戻される(Sleep, アイドル状態)。
更に詳しくは以下を参照
- https://please-sleep.cou929.nu/go-sql-db-connection-pool.html
- http://go-database-sql.org/connection-pool.html
どのタイミングで接続されるのか?
sql.Openを呼び出してもMySQLへの接続は行われない。
実際に Query, Execなど実際にMySQLとの接続が必要になったタイミングで呼び出される。
net パッケージ
GoでTCP/IPレイヤーの通信を行うためのパッケージ。
Go Driverでは、TCP接続を行う Dial
や接続後の通信で Read
Write
などのメソッドやインターフェースを利用している。
net パッケージの内部、主に通信に関わる実装では syscall パッケージや 一部の標準ライブラリがアクセスできる internal パッケージなど、更に低いレイヤーのパッケージが利用されている。
接続の流れ
- db.Queryが呼ばれる
- コネクションプールからアイドル状態のコネクションを払い出す
- アイドル状態のコネクションが存在しない場合、新規にコネクションを確立する
db.connector.Connect (connectorはdriverパッケージに定義されるインターフェース)
driverのconnector.Connectの流れ
- TCP/IPソケットソケットの接続を確立する
mc.netConn, err = nd.DialContext(ctx, mc.cfg.Net, mc.cfg.Addr)
- KeepAliveを設定する
tc.SetKeepAlive(true)
- Initial Handshake Packetを受け取る
authData, plugin, err := mc.readHandshakePacket()
- Auth Responseを作成する
authResp, err := mc.auth(authData, plugin)
- HandShake Response Packetを送信する
mc.writeHandshakeResponsePacket(authResp, plugin)
- Auth Packetの結果を受け取り処理する
mc.handleAuthResult(authData, plugin)
TCP/IPソケットソケットの接続を確立する
mc.netConn, err = nd.DialContext(ctx, mc.cfg.Net, mc.cfg.Addr)
この処理は大変壮大だが、中のコードを追っていくと以下の処理が呼ばれていることがわかる。
- netパッケージのsocketでsyscall.Socketを呼び出してソケットを作成し、ファイルディスクリプタを受け取る
- syscall.Connectを呼び出してサーバに接続する (なお、接続先がローカルアドレスの場合はsyscall.Bindが呼び出される)
なお、TCPレイヤーの操作(3 Way Handshake)は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しか受け付けないようにしている。
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の方を利用する。
送っているデータの概要は下記の通り。
- クライアント機能フラグ
- 文字セット。利用者が指定した値。Go Driverで設定可能な値はこちらの通り
- 最大パケットサイズ(デフォルト値固定)
- ユーザ名
- Auth Response(1つ前のプロセスで作成したハッシュ化したパスワード)
- 初期接続先データベース名
- 認証プラグイン名
TLSで通信する場合は、HandShake Response Packetを送信する前に以下の作業をする。
- クライアント機能フラグのSSLフラグを立てておく
- SSLリクエストパケットを事前に送信する。
- TLSハンドシェイクを行う
6. Auth Packetの結果を受け取り処理する
mc.handleAuthResult(authData, plugin)
こちらやこちら
で書かれているように、次にサーバから返されるパケットは条件分岐する。
レスポンスの最初の1バイトを読み取って判断して後続の処理を行う。
- OKパケット: 接続フェーズの完了を示す。これにてコマンドフェーズに移行する。
- AuthMoreDataパケット: 2段階認証などで、追加の認証を求めることを示す。
- AuthSwitchRequestパケット: ハンドシェイク時に使った認証メソッドが利用不可だったので、別の認証メソッドに切り替えて認証をやり直す必要があることを示す。
- ERRパケット: 認証失敗を示す。コネクションがクローズされて通信が終了する。
最終的に認証がOKになったら、コネクションを作成して呼び出し元に返す。