最近自作 MySQL Client にハマっています。どういう原理で MySQL につながっているのか知りたいなと思って始めましたが、MySQL Client についてはもちろんのこと、ネットワークの勉強にもなって結構楽しいです。
現在の MySQL では MySQL Client/Server Protocol (MySQL C/S Protocol) と X Protocol という2つのプロトコルが使われています。
C/S Protocol 準拠の Client はすでに作られている方の記事があるため、それらの記事を参考にして自分でもさっくり Client を作ることができました。
一方で X Protocol という新しい方のプロトコルに準拠した Client の作り方を解説している記事が見当たらず、どこから手を付けたら良いのかわからず苦労しました。
幸い SELECT 文を打つくらいならできるようになったので忘れないうちに記録に残しておこうと思います。
この記事では Python で SELECT 文を打って、その結果を表示するところまでを目標とします。
環境準備
Docker
ローカル環境を汚したくないので今回は Docker を使いました。今後全ての作業は Ubuntu のコンテナ内で行います。
Dockerfile
FROM ubuntu:20.04
ENV DEBIAN_FRONTEND=noninteractive
RUN apt-get update && \
apt-get install -y vim wget git lsb-release gnupg ngrep ipython3 python3-pip protobuf-compiler
RUN pip3 install mysql-connector-python
# MySQL SHELL
RUN wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb && \
dpkg -i mysql-apt-config_0.8.15-1_all.deb && \
rm mysql-apt-config_0.8.15-1_all.deb && \
apt-get update && \
apt-get install -y mysql-shell
WORKDIR /app
docker-compose.yml
version: '3'
services:
client:
build: .
tty: true
db:
image: mysql:8.0.20
environment:
- MYSQL_ROOT_PASSWORD=test
command: mysqld --default-authentication-plugin=mysql_native_password
パケットキャプチャしながら作業する関係で認証プラグインには mysql_native_password を使用しました。MySQL 8 からのデフォルトは caching_sha2_password ですが、こちらは暗号化通信必須なのに対し、mysql_native_password は通信が暗号化されていなくても使用できるためどんなパケットが飛んでいるのか確認しながら作業する場合はこちらのほうが都合が良いです。
データ準備 & protoc
まずは MySQL に適当なデータを入れておきます。
$ docker-compose up -d
$ docker-compose exec client bash
$ mysqlsh root@db:33060 -ptest --sql -e "CREATE DATABASE foo; CREATE TABLE foo.bar (id int, name varchar(20)); INSERT INTO foo.bar values (1, 'hoge'), (10, 'piyo')"
# データ入っているか確認
$ mysqlsh root@db:33060 -ptest --sql -e 'SELECT * FROM foo.bar'
Logger: Tried to log to an uninitialized logger.
WARNING: Using a password on the command line interface can be insecure.
id name
1 hoge
10 piyo
ここでは mysql (MySQL Command-Line Client) の代わりに mysqlsh (MySQL Shell) を使用しました。MySQL Shell は X Protocol に対応した新たな MySQL Client です。mysql コマンドも Shell 感ありましたが、MySQL Shell は mysqlsh のことであって mysql のことではありません。
--sql オプションつけずに起動すると JS の REPL が起動し、\py で Python の REPL に変わり、\sql で従来の SQL での操作ができます。
今まで mysql コマンドしか使ってこなかった私にはなかなかカルチャーショックでした。
Protocol Buffer
X Protocol は Protocol Buffer Based なので、proto ファイルを mysql/mysql-server から落としてきて Python 用にコンパイルしておきます。
$ git clone --depth 1 -b mysql-8.0.21 https://github.com/mysql/mysql-server
$ cp -r mysql-server/plugin/x/protocol/protobuf/ .
$ rm -rf mysql-server
$ mkdir lib
$ protoc -I=protobuf --python_out=lib protobuf/*.proto
$ export PYTHONPATH=$PYTHONPATH:lib
Protocol Buffer を今まで使ったことが無いという方は Google 公式の Tutorial をやることをおすすめします。ちなみに私は Protocol Buffer を X Protocol を勉強している段階で初めて知りました。
Protocol Buffer Basics: Python | Protocol Buffers | Google Developers
Client 作り終盤で Protocol Buffer のエンコーディング方法についても調べないといけない状況になりますが、C/S Protocol 準拠の Client を使ったことが有る人にはそんなに苦でないと思います。
Encoding | Protocol Buffers | Google Developers
Client 作り
パケットキャプチャ
パケットキャプチャしながら作業をしたいため、もう一つ terminal を開いておきます。
元々開いていたターミナルを terminal 1、新たにひらいたものを terminal 2 と呼ぶことにします。
試しに既存の X Protocol 対応の Client で Server につないでパケットを覗いてみます。
MySQL Shell は余分なパケットが結構飛ぶので、ここでは Python からつなぎに行くことにします。
@terminal 2
$ dokcer-compose exec client bash
# コンテナ内
$ ngrep -x -q '.' 'host db'
interface: eth0 (172.19.0.0/255.255.0.0)
filter: ( host db ) and ((ip || ip6) || (vlan && (ip || ip6)))
match: .
terminal 1 で iPython を起動し以下のように打ちます。
$ ipython3
In [1]: import mysqlx
In [2]: session = mysqlx.get_session({
...: 'host': 'db',
...: 'port': 33060,
...: 'user': 'root',
...: 'password': 'test',
...: 'ssl-mode': mysqlx.SSLMode.DISABLED,
...: 'compression':'disabled'})
In [3]: session.sql('SELECT * FROM foo.bar').execute().fetch_all()
...:
Out[3]: [<mysqlx.result.Row at 0x7f078bd62640>, <mysqlx.result.Row at 0x7f0788329ca0>]
In [4]: session.close()
terminal 2 の方を見ると、以下のように MySQL Server と Client の間に流れたパケットがみることができます。
以下の例では 172.19.0.2:37834 -> 172.20.0.3:33060 が Client から Server への送信(ClientMessage)、逆に 172.20.0.3:33060 -> 172.19.0.2:37834 が Server から Client への送信(ServerMessage)を表します。
この流れているパケットと同じようなパケットを生成・送信することができれば MySQL Server とデータのやり取りをすることができます。
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #4
05 00 00 00 0b 08 05 1a 00 .........
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #6
01 00 00 00 01 .....
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #8
50 01 00 00 02 0a 0f 0a 03 74 6c 73 12 08 08 01 P........tls....
12 04 08 07 40 00 0a 4d 0a 19 61 75 74 68 65 6e ....@..M..authen
74 69 63 61 74 69 6f 6e 2e 6d 65 63 68 61 6e 69 tication.mechani
73 6d 73 12 30 08 03 22 2c 0a 11 08 01 12 0d 08 sms.0..",.......
08 4a 09 0a 07 4d 59 53 51 4c 34 31 0a 17 08 01 .J...MYSQL41....
12 13 08 08 4a 0f 0a 0d 53 48 41 32 35 36 5f 4d ....J...SHA256_M
45 4d 4f 52 59 0a 1d 0a 0b 64 6f 63 2e 66 6f 72 EMORY....doc.for
6d 61 74 73 12 0e 08 01 12 0a 08 08 4a 06 0a 04 mats........J...
74 65 78 74 0a 1e 0a 12 63 6c 69 65 6e 74 2e 69 text....client.i
6e 74 65 72 61 63 74 69 76 65 12 08 08 01 12 04 nteractive......
08 07 40 00 0a 6e 0a 0b 63 6f 6d 70 72 65 73 73 ..@..n..compress
69 6f 6e 12 5f 08 02 1a 5b 0a 59 0a 09 61 6c 67 ion._...[.Y..alg
6f 72 69 74 68 6d 12 4c 08 03 22 48 0a 18 08 01 orithm.L.."H....
12 14 08 08 4a 10 0a 0e 64 65 66 6c 61 74 65 5f ....J...deflate_
73 74 72 65 61 6d 0a 15 08 01 12 11 08 08 4a 0d stream........J.
0a 0b 6c 7a 34 5f 6d 65 73 73 61 67 65 0a 15 08 ..lz4_message...
01 12 11 08 08 4a 0d 0a 0b 7a 73 74 64 5f 73 74 .....J...zstd_st
72 65 61 6d 0a 1c 0a 09 6e 6f 64 65 5f 74 79 70 ream....node_typ
65 12 0f 08 01 12 0b 08 08 4a 07 0a 05 6d 79 73 e........J...mys
71 6c 0a 20 0a 14 63 6c 69 65 6e 74 2e 70 77 64 ql. ..client.pwd
5f 65 78 70 69 72 65 5f 6f 6b 12 08 08 01 12 04 _expire_ok......
08 07 40 00 ..@.
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #10
21 01 00 00 02 0a 9d 02 0a 9a 02 0a 15 73 65 73 !............ses
73 69 6f 6e 5f 63 6f 6e 6e 65 63 74 5f 61 74 74 sion_connect_att
72 73 12 80 02 08 02 1a fb 01 0a 16 0a 04 5f 70 rs............_p
69 64 12 0e 08 01 12 0a 08 08 4a 06 0a 04 33 31 id........J...31
31 34 0a 1d 0a 09 5f 70 6c 61 74 66 6f 72 6d 12 14...._platform.
10 08 01 12 0c 08 08 4a 08 0a 06 78 38 36 5f 36 .......J...x86_6
34 0a 1d 0a 03 5f 6f 73 12 16 08 01 12 12 08 08 4...._os........
4a 0e 0a 0c 55 62 75 6e 74 75 2d 32 30 2e 30 34 J...Ubuntu-20.04
0a 26 0a 0c 5f 73 6f 75 72 63 65 5f 68 6f 73 74 .&.._source_host
12 16 08 01 12 12 08 08 4a 0e 0a 0c 36 66 30 66 ........J...6f0f
38 61 31 63 64 35 39 34 0a 30 0a 0c 5f 63 6c 69 8a1cd594.0.._cli
65 6e 74 5f 6e 61 6d 65 12 20 08 01 12 1c 08 08 ent_name. ......
4a 18 0a 16 6d 79 73 71 6c 2d 63 6f 6e 6e 65 63 J...mysql-connec
74 6f 72 2d 70 79 74 68 6f 6e 0a 23 0a 0f 5f 63 tor-python.#.._c
6c 69 65 6e 74 5f 76 65 72 73 69 6f 6e 12 10 08 lient_version...
01 12 0c 08 08 4a 08 0a 06 38 2e 30 2e 32 31 0a .....J...8.0.21.
24 0a 0f 5f 63 6c 69 65 6e 74 5f 6c 69 63 65 6e $.._client_licen
73 65 12 11 08 01 12 0d 08 08 4a 09 0a 07 47 50 se........J...GP
4c 2d 32 2e 30 L-2.0
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #12
01 00 00 00 00 .....
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #14
0a 00 00 00 04 0a 07 4d 59 53 51 4c 34 31 .......MYSQL41
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #16
17 00 00 00 03 0a 14 16 5a 4c 48 21 0b 65 31 65 ........ZLH!.e1e
2b 23 0a 65 3f 43 42 62 56 62 00 +#.e?CBbVb.
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #18
33 00 00 00 05 0a 30 00 72 6f 6f 74 00 2a 35 66 3.....0.root.*5f
62 32 66 64 37 64 66 66 39 30 35 61 65 33 31 34 b2fd7dff905ae314
66 35 64 64 32 65 63 62 34 34 65 33 33 36 32 36 f5dd2ecb44e33626
36 64 61 39 61 36 00 6da9a6.
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #20
0f 00 00 00 0b 08 03 10 02 1a 08 08 0b 12 04 08 ................
02 18 05 03 00 00 00 04 0a 00 ..........
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #22
1f 00 00 00 0c 0a 15 53 45 4c 45 43 54 20 2a 20 .......SELECT *
46 52 4f 4d 20 66 6f 6f 2e 62 61 72 1a 03 73 71 FROM foo.bar..sq
6c 20 00 l .
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #24
21 00 00 00 0c 08 01 50 0b 12 02 69 64 1a 02 69 !......P...id..i
64 22 03 62 61 72 2a 03 62 61 72 32 03 66 6f 6f d".bar*.bar2.foo
3a 03 64 65 66 28 00 00 00 0c 08 07 40 ff 01 50 :.def(......@..P
50 12 04 6e 61 6d 65 1a 04 6e 61 6d 65 22 03 62 P..name..name".b
61 72 2a 03 62 61 72 32 03 66 6f 6f 3a 03 64 65 ar*.bar2.foo:.de
66 0b 00 00 00 0d 0a 01 02 0a 05 68 6f 67 65 00 f..........hoge.
0b 00 00 00 0d 0a 01 14 0a 05 70 69 79 6f 00 01 ..........piyo..
00 00 00 0e 0f 00 00 00 0b 08 03 10 02 1a 08 08 ................
04 12 04 08 02 18 00 01 00 00 00 11 ............
T 172.20.0.2:37834 -> 172.20.0.3:33060 [AP] #26
01 00 00 00 07 .....
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #28
01 00 00 00 00 .....
X Protocol のパケット
X Protocol のパケットの構造は以下のようになっています。
4 byte length (little endian)
1 byte message type
a message_payload of length .length - 1 (protocol buffer)
Message Structure より
例えば、先程の一番始めに流れていたパケットは以下のように分解されます。
T 172.20.0.3:33060 -> 172.20.0.2:37834 [AP] #4
05 00 00 00 0b 08 05 1a 00 .........
↓
length 05 00 00 00
message type 0b
message_payload 08 05 1a 00
パケットの解釈の仕方ですが、このパケットは Servec -> Client に流れたものなので、ServerMessage で、message type は 0x0b = 11 です。
ServerMessage の type 11 は何かというは proto ファイルの ServerMessage のところを見て、field が 11 となっているところを見ます。
これより、このパケットは Notice であることがわかります。
message ServerMessages {
enum Type {
OK = 0;
ERROR = 1;
CONN_CAPABILITIES = 2;
SESS_AUTHENTICATE_CONTINUE = 3;
SESS_AUTHENTICATE_OK = 4;
// NOTICE has to stay at 11 forever
NOTICE = 11;
RESULTSET_COLUMN_META_DATA = 12;
RESULTSET_ROW = 13;
RESULTSET_FETCH_DONE = 14;
RESULTSET_FETCH_SUSPENDED = 15;
RESULTSET_FETCH_DONE_MORE_RESULTSETS = 16;
SQL_STMT_EXECUTE_OK = 17;
RESULTSET_FETCH_DONE_MORE_OUT_PARAMS = 18;
COMPRESSION = 19;
}
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx.proto#L99-L123
これらの情報より Python 側で Notice object を作り、飛んできたバイト列をパースします。(mysql-connector-python と Protocol Buffer で生成したコードが喧嘩するので一旦 iPython を起動し直してください)
In [1]: import mysqlx_notice_pb2
In [2]: notice = mysqlx_notice_pb2.Frame()
In [3]: notice.ParseFromString(b"\x08\x05\x1a\x00")
Out[3]: 4
In [4]: print(notice)
type: 5
payload: ""
この例では ServerMessage でしたが、Client -> Server に送る message は ClientMessage の field を見てよしなに Protocol Buffer binary format を生成して Server に送ります。
こんな感じで、送りたい・送られた message type を調べて、それに応じた payload を Protocol Buffer で処理して Server とデータのやりとりします。
ここでは例として Notice をパースしてみましたが、実は Notice は通信に一切関与してこないので実際に Client を作るときは無視します。
実際に Client を作ってみる
MySQL C/S Protocol と違って Payload の Byte 列を手作りすることはないのでさくさく作っていきます。
Connection
MySQL Server につなぐまでの流れは以下の図のようになっています。
Life Cycle より
ただ、この Negotiation phase はなくても繋がるので、簡単のため今回は飛ばします。
そんなわけで、一気に Authentication phase から始めます。
認証は AuthenticateStart から始まります。AuthenticateStart は proto ファイルでは以下のようになっています。
冒頭で今回は mysql_navite_password を使うと言いましたが、この場合は mech_name に MYSQL41 を入れます。
message AuthenticateStart {
required string mech_name = 1;
optional bytes auth_data = 2;
optional bytes initial_response = 3;
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_session.proto#L63-L69
Python のコードはこんな感じになります。
import socket
import struct
import mysqlx_session_pb2
def read_packet(sock):
size = sock.recv(4)
size = struct.unpack('<I', size)[0]
typ = sock.recv(1)
payload = sock.recv(size - 1)
if typ == b"\x0b":
return read_packet(sock)
return typ, payload
host = "db"
user = "root"
password = "test"
port = 33060
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, port))
auth = mysqlx_session_pb2.AuthenticateStart()
auth.mech_name = 'MYSQL41'
payload = auth.SerializeToString()
size = struct.pack("<I", len(payload) + 1)
SESS_AUTHENTICATE_START = 4
typ = struct.pack('B', SESS_AUTHENTICATE_START)
sock.send(size + typ + payload)
上のコードを流すと下のようなパケットがながれます。
# AuthenticateStart
T 172.20.0.2:38106 -> 172.20.0.3:33060 [AP] #9
0a 00 00 00 04 0a 07 4d 59 53 51 4c 34 31 .......MYSQL41
# Notice
T 172.20.0.3:33060 -> 172.20.0.2:38106 [AP] #10
05 00 00 00 0b 08 05 1a 00 .........
# AuthenticateContinue
T 172.20.0.3:33060 -> 172.20.0.2:38106 [AP] #13
17 00 00 00 03 0a 14 75 5c 15 2c 36 25 43 1e 78 .......u\.,6%C.x
2b 5a 0b 23 39 37 22 0f 48 4d 00 +Z.#97".HM.
2つ目のパケットは Notice で、先程言ったように通信に全く関与してこないので無視します。
AuthenticateStart を送ると Server は AuthenticateContinue (ServerMessage: type 0x03)を返してきます。
message AuthenticateContinue {
required bytes auth_data = 1;
}
この送られてきた auth_data を使って認証(MYSQL41 Authentication)をします。
まず、公式ドキュメントに書いてあるように response を次のようにして作ります。
HEX(SHA1(password) ^ SHA1(challenge + SHA1(SHA1(password))))
ここで challenge とは先程 Server から送られてきた auth_data のことです。
C/S Protocol のときの Native41 と概ね一緒ですが、最後に HEX にするところだけ異なっています。
C/S Protocol のときは SHA1(password) ^ SHA1(challenge + SHA1(SHA1(password))) で求めた 20 bytes を Server に送りましたが、X Protocol ではこの 20 bytes を16進数表記にし、各桁の英数字を ASCII Code として送ります。
一例を上げると x = 0x01 0x10 0x65 0x2a のとき、HEX(x) = "0110652a" = 0x30 0x31 0x31 0x30 0x36 0x35 0x32 0x61 ということです。
計算した response と user_name を次のように並べた byte 列を ClientMessage の AuthenticateContinue (type 5) として送ります。同じ AuthenticateContinue ですが、ServerMessage か ClientMessage かによって message type が異なるので注意してください。
0x00 user_name 0x00 0x2a response 0x00
ここで公式ドキュメントでは user_name の response で挟まれる真ん中のバイト列が null だけになっていますが、0x2a を抜かすと認証に失敗します1。
from hashlib import sha1
import mysqlx_notice_pb2
typ, payload = read_packet(sock)
auth_conti = mysqlx_session_pb2.AuthenticateContinue()
auth_conti.ParseFromString(payload)
challenge = auth_conti.auth_data
x = sha1(password.encode()).digest()
y = sha1(challenge + sha1(x).digest()).digest()
auth_data = bytes([i ^ j for i, j in zip(x,y)]).hex().encode()
auth_data = b"\x00" + user.encode() + b"\x00\x2a" + auth_data + b"\x00"
auth_conti = mysqlx_session_pb2.AuthenticateContinue()
auth_conti.auth_data = auth_data
payload = auth_conti.SerializeToString()
size = len(payload) + 1
size = struct.pack('<I', size)
CLIENT_SESS_AUTHENTICATE_CONTINUE = 5
typ = struct.pack('B', CLIENT_SESS_AUTHENTICATE_CONTINUE)
sock.send(size + typ + payload)
typ, payload = read_packet(sock)
auth_ok = mysqlx_session_pb2.AuthenticateOk()
auth_ok.ParseFromString(payload)
AuthenticateOk が返ってきたら、認証成功です。無事にクエリを打つ準備ができました。
# AuthenticateContinue
T 172.20.0.2:38106 -> 172.20.0.3:33060 [AP] #15
33 00 00 00 05 0a 30 00 72 6f 6f 74 00 2a 38 35 3.....0.root.*85
37 30 33 30 33 64 61 37 61 63 30 34 62 66 33 65 70303da7ac04bf3e
65 32 39 34 35 61 35 31 35 61 36 39 62 34 31 36 e2945a515a69b416
38 37 30 62 35 39 00 870b59.
# Notice / AuthenticateOk
T 172.20.0.3:33060 -> 172.20.0.2:38106 [AP] #17
0f 00 00 00 0b 08 03 10 02 1a 08 08 0b 12 04 08 ................
02 18 17 03 00 00 00 04 0a 00 ..........
※ ServerMessage の前半は Notice で、後半に AuthenticateOk があります(03 00 00 00 04 0a 00)。
認証に失敗するとおなじみのエラーが返ってきます。
# Error
T 172.22.0.2:33060 -> 172.22.0.3:50148 [AP] #82
4f 00 00 00 01 08 00 10 95 08 1a 40 41 63 63 65 O..........@Acce
73 73 20 64 65 6e 69 65 64 20 66 6f 72 20 75 73 ss denied for us
65 72 20 27 72 6f 6f 74 27 40 27 31 37 32 2e 32 er 'root'@'172.2
32 2e 30 2e 33 27 20 28 75 73 69 6e 67 20 70 61 2.0.3' (using pa
73 73 77 6f 72 64 3a 20 59 45 53 29 22 05 48 59 ssword: YES)".HY
30 30 30 000
ServerMessage: type 1 は Error です。
Command
StmtExecute
クエリを打つ準備が整ったので実際に SELECT 文を打ってみます。
冒頭で上げた C/S Protocol 記事では COM_QUERY を使われていましたが、X Protocol でそれに相当するのは StmtExecute です。
message StmtExecute {
optional string namespace = 3 [ default = "sql" ];
required bytes stmt = 1;
repeated Mysqlx.Datatypes.Any args = 2;
optional bool compact_metadata = 4 [ default = false ];
option (client_message_id) = SQL_STMT_EXECUTE; // comment_out_if PROTOBUF_LITE
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_sql.proto#L54-L66
stmt にクエリを入れる以外はデフォルト値でよいのでそのままにしておきます。
import mysqlx_sql_pb2
stmt_execute = mysqlx_sql_pb2.StmtExecute()
stmt_execute.stmt = b"select * from foo.bar"
payload = stmt_execute.SerializeToString()
size = len(payload) + 1
size = struct.pack('<I', size)
SQL_STMT_EXECUTE = 12
typ = struct.pack('B', SQL_STMT_EXECUTE)
sock.send(size + typ + payload)
クエリを打つのに成功すると Resultset と StmtExecuteOk が返ってきます。
※ Resultset は package の名前で message の名前ではありません。
この Resultset をパースすれば Client の作成終了です。
# StmtExecute
T 172.20.0.2:38106 -> 172.20.0.3:33060 [AP] #19
18 00 00 00 0c 0a 15 73 65 6c 65 63 74 20 2a 20 .......select *
66 72 6f 6d 20 66 6f 6f 2e 62 61 72 from foo.bar
# Resultset / StmtExecuteOk
T 172.20.0.3:33060 -> 172.20.0.2:38106 [AP] #21
21 00 00 00 0c 08 01 50 0b 12 02 69 64 1a 02 69 !......P...id..i
64 22 03 62 61 72 2a 03 62 61 72 32 03 66 6f 6f d".bar*.bar2.foo
3a 03 64 65 66 28 00 00 00 0c 08 07 40 ff 01 50 :.def(......@..P
50 12 04 6e 61 6d 65 1a 04 6e 61 6d 65 22 03 62 P..name..name".b
61 72 2a 03 62 61 72 32 03 66 6f 6f 3a 03 64 65 ar*.bar2.foo:.de
66 0b 00 00 00 0d 0a 01 02 0a 05 68 6f 67 65 00 f..........hoge.
0b 00 00 00 0d 0a 01 14 0a 05 70 69 79 6f 00 01 ..........piyo..
00 00 00 0e 0f 00 00 00 0b 08 03 10 02 1a 08 08 ................
04 12 04 08 02 18 00 01 00 00 00 11 ............
Resultset
Resultset はいくつかの ServerMessage の集まりなのでまずは分解してみます。
ColumnMetaData
はじめの塊は ServerMessage, type 0x0c = 12 なので ColumnMetaData です。
message ColumnMetaData {
enum FieldType {
SINT = 1;
UINT = 2;
DOUBLE = 5;
FLOAT = 6;
BYTES = 7;
TIME = 10;
DATETIME = 12;
SET = 15;
ENUM = 16;
BIT = 17;
DECIMAL = 18;
}
// datatype of the field in a row
required FieldType type = 1;
optional bytes name = 2;
optional bytes original_name = 3;
optional bytes table = 4;
optional bytes original_table = 5;
optional bytes schema = 6;
optional bytes catalog = 7;
optional uint64 collation = 8 /* ifdef PROTOBUF3 [jstype = JS_STRING] */;
optional uint32 fractional_digits = 9;
optional uint32 length = 10;
optional uint32 flags = 11;
optional uint32 content_type = 12;
option (server_message_id) = RESULTSET_COLUMN_META_DATA; // comment_out_if PROTOBUF_LITE
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_resultset.proto#L461-L502
今回のテーブルはカラム数が2個なので ColumnMetaData も2つ飛んできます。
C/S Protocol では一番始めにカラム数が返ってきましたが、X Protocol の場合だとカラム数は実際に送られてきた ColumnMetaData の数を数えるまでわかりません。
# RESULTSET_COLUMN_META_DATA
21 00 00 00 0c 08 01 50 0b 12 02 69 64 1a 02 69 !......P...id..i
64 22 03 62 61 72 2a 03 62 61 72 32 03 66 6f 6f d".bar*.bar2.foo
3a 03 64 65 66 :.def
28 00 00 00 0c 08 07 40 ff 01 50 (......@..P
50 12 04 6e 61 6d 65 1a 04 6e 61 6d 65 22 03 62 P..name..name".b
61 72 2a 03 62 61 72 32 03 66 6f 6f 3a 03 64 65 ar*.bar2.foo:.de
66 f
import mysqlx_resultset_pb2
typ, payload = read_packet(sock)
resultset_id = mysqlx_resultset_pb2.ColumnMetaData()
resultset_id.ParseFromString(payload)
print(resultset_id)
typ, payload = read_packet(sock)
resultset_name = mysqlx_resultset_pb2.ColumnMetaData()
resultset_name.ParseFromString(payload)
print(resultset_name)
※ 本来ならば loop を回して message type が ColumnMetaData でなくなったら break とするべきなのでしょうが、今回は ad hoc にいきます。
C/S Protocol のときの ColumnDefinition41 と内容としては大して変わりませんね。パースは Protocol Buffer がしてくれるので圧倒的に楽ですけど。
Row
ColumnMetaData のあとには各行のデータが入った Row が来ます。
message Row {
repeated bytes field = 1;
option (server_message_id) = RESULTSET_ROW; // comment_out_if PROTOBUF_LITE
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_resultset.proto#L512-L516
Row のデータは常に byte 列で飛んできます。この byte 列を先に飛んできた metadata の type をもとにパースします。
今回のテーブルは2行なので Row も2個飛んできます。
0b 00 00 00 0d 0a 01 02 0a 05 68 6f 67 65 00 ..........hoge.
0b 00 00 00 0d 0a 01 14 0a 05 70 69 79 6f 00 ..........piyo.
typ, payload = read_packet(sock)
row1 = mysqlx_resultset_pb2.Row()
row1.ParseFromString(payload)
print(row1.field)
typ, payload = read_packet(sock)
row2 = mysqlx_resultset_pb2.Row()
row2.ParseFromString(payload)
print(row2.field)
field の1個目が id カラムで、2個目が name カラムの値です。
id は Signed Integer で、もともとは 1 と 10 でしたが、0x02 と 0x14 という不思議な数字が降ってきました。
これは Protocol Buffer における数字の表現方法で zigzag encoding されるとこういう形になります。
zigzag encoding を簡単に説明すると絶対値が小さい数字から順に 0 から数字を割り当てていくエンコーディング方法です。
0 -> 0
-1 -> 1
1 -> 2
-2 -> 3
2 -> 4
...
というふうにマッピングされます。
そのため、0x02 をデコードすると 0x02 = 2 -> 1, 0x14 をデコードすると 0x14 = 20 -> 10 となり、もとの 1 と 10 を復元できます。
それ以外はなんでもない Row の中身のように見えますが、個人的にはこの Row のデータの受け取り方は結構衝撃的でした。
というのも C/S Protocol では各行のデータを全て string として受け取っていました(Text Resultset Row)。
例えば、MySQL Server が 1000 という数字を送る場合、各数字を ASCII で送っていたので 0x31 0x30 0x30 0x30 という 4 bytes のデータがネットワーク上を流れていました。
一方で X Protocol の場合、Base 128 Varints という形で飛んできます。この方法だと 1000 という数字は 2 bytes のデータとして飛んできます。MySQL C/S Protocol のときに比べて半分のデータ量しか流さないのでとてもオトクですね。(実際は Protocol Buffer 用の付加情報がつくのでここまで単純ではありませんが)
普段使うときは圧縮も使うので、これだけで全体としての処理が早くなるのかは疑問ですが、検証してみる価値はあるのかなと思っています。
FetchDone
Row のデータを全て取り終わると区切りとして FetchDone (ServerMessage: type 0x0e = 14) が来ます。
message FetchDone {
option (server_message_id) = RESULTSET_FETCH_DONE; // comment_out_if PROTOBUF_LITE
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_resultset.proto#L145-L147
01 00 00 00 0e
StmtExecuteOk
最後に StmtExecuteOk (ServerMessage: type 0x11 = 17) で終了です。お疲れさまでした。
message StmtExecuteOk {
option (server_message_id) = SQL_STMT_EXECUTE_OK; // comment_out_if PROTOBUF_LITE
}
https://github.com/mysql/mysql-server/blob/mysql-8.0.21/plugin/x/protocol/protobuf/mysqlx_sql.proto#L64-L66
# Notice
0f 00 00 00 0b 08 03 10 02 1a 08 08 04 12 04 08 02 18 00
# StmtExecuteOk
01 00 00 00 11
import socket
import struct
from hashlib import sha1
import mysqlx_session_pb2
import mysqlx_notice_pb2
import mysqlx_sql_pb2
import mysqlx_resultset_pb2
def read_packet(sock):
size = sock.recv(4)
size = struct.unpack('<I', size)[0]
typ = sock.recv(1)
payload = sock.recv(size - 1)
if typ == b"\x0b":
return read_packet(sock)
return typ, payload
host = "db"
user = "root"
password = "test"
port = 33060
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, port))
auth = mysqlx_session_pb2.AuthenticateStart()
auth.mech_name = 'MYSQL41'
payload = auth.SerializeToString()
size = struct.pack("<I", len(payload) + 1)
SESS_AUTHENTICATE_START = 4
typ = struct.pack('B', SESS_AUTHENTICATE_START)
sock.send(size + typ + payload)
typ, payload = read_packet(sock)
auth_conti = mysqlx_session_pb2.AuthenticateContinue()
auth_conti.ParseFromString(payload)
challenge = auth_conti.auth_data
x = sha1(password.encode()).digest()
y = sha1(challenge + sha1(x).digest()).digest()
auth_data = bytes([i ^ j for i, j in zip(x,y)]).hex().encode()
auth_data = b"\x00" + user.encode() + b"\x00\x2a" + auth_data + b"\x00"
auth_conti = mysqlx_session_pb2.AuthenticateContinue()
auth_conti.auth_data = auth_data
payload = auth_conti.SerializeToString()
size = len(payload) + 1
size = struct.pack('<I', size)
CLIENT_SESS_AUTHENTICATE_CONTINUE = 5
typ = struct.pack('B', CLIENT_SESS_AUTHENTICATE_CONTINUE)
sock.send(size + typ + payload)
typ, payload = read_packet(sock)
auth_ok = mysqlx_session_pb2.AuthenticateOk()
auth_ok.ParseFromString(payload)
stmt_execute = mysqlx_sql_pb2.StmtExecute()
stmt_execute.stmt = b"select * from foo.bar"
payload = stmt_execute.SerializeToString()
size = len(payload) + 1
size = struct.pack('<I', size)
SQL_STMT_EXECUTE = 12
typ = struct.pack('B', SQL_STMT_EXECUTE)
sock.send(size + typ + payload)
typ, payload = read_packet(sock)
resultset_id = mysqlx_resultset_pb2.ColumnMetaData()
resultset_id.ParseFromString(payload)
print(resultset_id)
typ, payload = read_packet(sock)
resultset_name = mysqlx_resultset_pb2.ColumnMetaData()
resultset_name.ParseFromString(payload)
print(resultset_name)
typ, payload = read_packet(sock)
row1 = mysqlx_resultset_pb2.Row()
row1.ParseFromString(payload)
print(row1)
typ, payload = read_packet(sock)
row2 = mysqlx_resultset_pb2.Row()
row2.ParseFromString(payload)
print(row2)
def rshift(val, n):
return val>>n if val >= 0 else (val+0x100000000)>>n
def decode_zigzag(val):
return rshift(val, 1) ^ - (val & 1)
row1_id = decode_zigzag(struct.unpack('B', row1.field[0])[0])
row1_name = row1.field[1].decode('utf-8')[0:-1]
print(f"{row1_id=}, {row1_name=}")
row2_id = decode_zigzag(struct.unpack('B', row2.field[0])[0])
row2_name = row2.field[1].decode('utf-8')[0:-1]
print(f"{row2_id=}, {row2_name=}")
typ, payload = read_packet(sock)
fetch_done = mysqlx_resultset_pb2.FetchDone()
fetch_done.ParseFromString(payload)
typ, payload = read_packet(sock)
stmt_execute_ok = mysqlx_sql_pb2.StmtExecuteOk()
stmt_execute_ok.ParseFromString(payload)
出力
type: SINT
name: "id"
original_name: "id"
table: "bar"
original_table: "bar"
schema: "foo"
catalog: "def"
length: 11
type: BYTES
name: "name"
original_name: "name"
table: "bar"
original_table: "bar"
schema: "foo"
catalog: "def"
collation: 255
length: 80
field: "\024"
field: "piyo\000"
row1_id=1, row1_name='hoge'
row2_id=10, row2_name='piyo'
参考
今回使ったコード
Julia でも書いてみた
github.com
