最近自作 MySQL Client にハマっています。どういう原理で MySQL につながっているのか知りたいなと思って始めましたが、MySQL Client についてはもちろんのこと、ネットワークの勉強にもなって結構楽しいです。
現在の MySQL では MySQL Client/Server Protocol (MySQL C/S Protocol) と X Protocol という2つのプロトコルが使われています。 C/S Protocol 準拠の Client はすでに作られている方の記事があるため、それらの記事を参考にして自分でもさっくり Client を作ることができました。
- The client library, part 4: How to write a simple MySQL client in C using CMake and pkg-config | MySQL Server Blog
- 第81回 MySQLプロトコルでハンドシェイクレスポンスを返してみる:MySQL道普請便り|gihyo.jp … 技術評論社
- MySQLユーザーのためのMySQLプロトコル入門 | エンジニアブログ | GREE Engineering
- GitHub - goropikari/MySQLClient.jl: Pure Julia MySQL Client
- 上記のサイトを参考に作った Julia 製の 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 Shell
まずは 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)
例えば、先程の一番始めに流れていたパケットは以下のように分解されます。
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; } }
これらの情報より 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 につなぐまでの流れは以下の図のようになっています。
ただ、この 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; }
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": # Notice は無視 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)) # C -> S: SESS_AUTHENTICATE_START auth = mysqlx_session_pb2.AuthenticateStart() auth.mech_name = 'MYSQL41' payload = auth.SerializeToString() size = struct.pack("<I", len(payload) + 1) # 数字を little endian に変換する 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 # S -> C: AuthenticateContinue typ, payload = read_packet(sock) auth_conti = mysqlx_session_pb2.AuthenticateContinue() auth_conti.ParseFromString(payload) challenge = auth_conti.auth_data # C -> S: AuthenticateContinue 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) # S -> C: AuthenticateOk 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 }
stmt
にクエリを入れる以外はデフォルト値でよいのでそのままにしておきます。
# C -> S: SQL_STMT_EXECUTE 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 }
今回のテーブルはカラム数が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 # id typ, payload = read_packet(sock) resultset_id = mysqlx_resultset_pb2.ColumnMetaData() resultset_id.ParseFromString(payload) print(resultset_id) #=> type: SINT #=> name: "id" #=> original_name: "id" #=> table: "bar" #=> original_table: "bar" #=> schema: "foo" #=> catalog: "def" #=> length: 11 # name typ, payload = read_packet(sock) resultset_name = mysqlx_resultset_pb2.ColumnMetaData() resultset_name.ParseFromString(payload) print(resultset_name) #=> type: BYTES #=> name: "name" #=> original_name: "name" #=> table: "bar" #=> original_table: "bar" #=> schema: "foo" #=> catalog: "def" #=> collation: 255 #=> length: 80
※ 本来ならば 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 }
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) #=> [b'\x02', b'hoge\x00'] typ, payload = read_packet(sock) row2 = mysqlx_resultset_pb2.Row() row2.ParseFromString(payload) print(row2.field) #=> [b'\x14', b'piyo\x00']
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 }
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 }
# 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
Python スクリプトまとめ
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": # Notice は無視 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)) # C -> S: SESS_AUTHENTICATE_START 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) # S -> C: AuthenticateContinue typ, payload = read_packet(sock) auth_conti = mysqlx_session_pb2.AuthenticateContinue() auth_conti.ParseFromString(payload) challenge = auth_conti.auth_data # C -> S: AuthenticateContinue 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) # S -> C: SESS_AUTHENTICATE_OK = 4 typ, payload = read_packet(sock) auth_ok = mysqlx_session_pb2.AuthenticateOk() auth_ok.ParseFromString(payload) # C -> S: SQL_STMT_EXECUTE 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) # id typ, payload = read_packet(sock) resultset_id = mysqlx_resultset_pb2.ColumnMetaData() resultset_id.ParseFromString(payload) print(resultset_id) # name typ, payload = read_packet(sock) resultset_name = mysqlx_resultset_pb2.ColumnMetaData() resultset_name.ParseFromString(payload) print(resultset_name) # Row 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) # https://gist.github.com/mfuerstenau/ba870a29e16536fdbaba#file-zigzag-encoding-readme-L18 # https://stackoverflow.com/a/5833119 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=}") # RESULTSET_FETCH_DONE typ, payload = read_packet(sock) fetch_done = mysqlx_resultset_pb2.FetchDone() fetch_done.ParseFromString(payload) # SQL_STMT_EXECUTE_OK 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'
参考
- MySQL Internals Manual: Chapter 15 X Protocol
- 20.5.5 Connection Compression with X Plugin
- Introducing the MySQL 8 Document Store
今回使ったコード
Julia でも書いてみた
-
これに引っかかって半日溶かしました。↩