{"id":11400,"date":"2014-10-29T12:49:33","date_gmt":"2014-10-29T03:49:33","guid":{"rendered":"http:\/\/labs.gree.jp\/blog\/?p=11400"},"modified":"2021-04-27T15:04:12","modified_gmt":"2021-04-27T06:04:12","slug":"post11400","status":"publish","type":"post","link":"https:\/\/labs.gree.jp\/blog\/2014\/10\/11400\/","title":{"rendered":"MySQL\u30e6\u30fc\u30b6\u30fc\u306e\u305f\u3081\u306eMySQL\u30d7\u30ed\u30c8\u30b3\u30eb\u5165\u9580"},"content":{"rendered":"

\u3055\u3044\u304d\u3093MySQL\u30e6\u30fc\u30b6\u30fc\u306e\u305f\u3081\u306e\u307b\u3052\u307b\u3052\u3001\u307f\u305f\u3044\u306a\u306e\u304c\u5df7\u3067\u6d41\u884c\u3057\u3066\u3044\u308b\u3088\u3046\u306a\u306e\u3067\u6687\u3064\u3076\u3057\u304c\u3066\u3089\u306b\u8aad\u3093\u3067\u3044\u308bMySQL\u30d7\u30ed\u30c8\u30b3\u30eb\u306b\u3064\u3044\u3066\u66f8\u3044\u3066\u307f\u3088\u3046\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u3044\u3084\u307e\u3041\u3001\u3053\u3046\u3044\u3046\u30d7\u30ed\u30c8\u30b3\u30eb\u304c\u8aad\u3081\u308b\u304b\u3089\u3068\u3044\u3063\u3066\u3059\u3054\u304f\u5f79\u7acb\u3064\u3068\u3044\u3046\u3053\u3068\u306f\u5168\u304f\u306a\u3044\u3093\u3067\u3059\u304c\u3001\u304a\u9152\u306e\u5e2d\u306e\u30cd\u30bf\u306b\u3067\u304d\u305f\u308a\u3001\u9ad8\u901f\u3001\u7c21\u5358\u3001\u7121\u6599\u3067\u8a66\u305b\u308bRDS MySQL\u304b\u3089Redshift\u3078\u306e\u30c7\u30fc\u30bf\u540c\u671f<\/a>\u306b\u51fa\u3066\u304f\u308b\u3088\u3046\u306areplication\u3092\u3044\u3058\u3063\u305f\u30c4\u30fc\u30eb\u3068\u304b\u306e\u30e1\u30f3\u30c6\u304c\u5bb9\u6613\u306b\u3067\u304d\u308b\u304b\u3082\u3057\u308c\u306a\u304b\u3063\u305f\u308a\u3001\u4ffamysqld\u3060\u305c\u3001\u307f\u305f\u3044\u306a\u4e8b\u304c\u3067\u304d\u305f\u308a\u3001\u306a\u3093\u304b\u3088\u304f\u308f\u304b\u3089\u306a\u3044\u3051\u3069\u3061\u3087\u3063\u3068\u30cf\u30c3\u30d4\u30fc\u306b\u306a\u308c\u305d\u3046\u3067\u3059\u306d\uff01
\n<\/p>\n

\u4eca\u65e5\u306f\u624b\u59cb\u3081\u306bMySQL\u3068mysql client\u304c\u3069\u3046\u3044\u3046\u901a\u4fe1\u3092\u3057\u3066\u3044\u308b\u306e\u304b\u898b\u3066\u3044\u304d\u3001\u5b9f\u969b\u306bInitial Handshake Packet\u3092parse\u3057\u3066\u307f\u308b\u3068\u3053\u308d\u307e\u3067\u3092\u3084\u3063\u3066\u307f\u307e\u3059\u3002<\/p>\n

Max OSX\u3067\u306e\u30bb\u30c3\u30c8\u30a2\u30c3\u30d7<\/h3>\n

\u666e\u6bb5homebrew\u3092\u4f7f\u3063\u3066\u3044\u308b\u306e\u3067mysql\u3068ngrep\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u3066\u307f\u307e\u3059\u3002ngrep\u306f\u304a\u624b\u8efd\u306atcpdump\u3060\u3068\u601d\u3063\u3066\u3044\u305f\u3060\u3051\u308c\u3070OK\u3067\u3059\u3002<\/p>\n

\nbrew install ngrep\nbrew install mysql<\/pre>\n
\u305d\u308c\u3067\u306fmysqld\u3092\u8d77\u52d5\u3057\u3066ngrep\u3092\u4f7f\u3063\u3066\u307f\u307e\u3057\u3087\u3046<\/p>\n
\nmysql.server start\nsudo ngrep -x -q -d lo0 '' 'port 3306'\n\n# \u5225\u30bf\u30fc\u30df\u30ca\u30eb\u3067mysql\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\nmysql -u root -h 127.0.0.1 -P 3306\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 1\nServer version: 5.6.20 Homebrew\n\nCopyright (c) 2000, 2014, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> Ctrl-C -- exit!\nAborted<\/pre>\n
\u3059\u308b\u3068ngrep\u3092\u3057\u3066\u3044\u305f\u30bf\u30fc\u30df\u30ca\u30eb\u3067\u4e0b\u8a18\u306e\u3088\u3046\u306a\u7d50\u679c\u304c\u5f97\u3089\u308c\u308b\u3068\u601d\u3044\u307e\u3059\u3002
\n<\/p>\n
\ninterface: lo0 (127.0.0.0\/255.0.0.0)\nfilter: (ip or ip6) and ( port 3306 )\n\nT 127.0.0.1:3306 ->; 127.0.0.1:49482 [AP]\n  4a 00 00 00 0a 35 2e 36    2e 32 30 00 01 00 00 00    J....5.6.20.....\n  21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80    !K:~0oax...!....\n  15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64    ...........X0&jd\n  3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61    :43E@S^.mysql_na\n  74 69 76 65 5f 70 61 73    73 77 6f 72 64 00          tive_password.  \n\nT 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]\n  a4 00 00 01 85 a6 7f 00    00 00 00 01 08 00 00 00    ................\n  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................\n  00 00 00 00 72 6f 6f 74    00 00 6d 79 73 71 6c 5f    ....root..mysql_\n  6e 61 74 69 76 65 5f 70    61 73 73 77 6f 72 64 00    native_password.\n  67 03 5f 6f 73 07 6f 73    78 31 30 2e 39 0c 5f 63    g._os.osx10.9._c\n  6c 69 65 6e 74 5f 6e 61    6d 65 08 6c 69 62 6d 79    lient_name.libmy\n  73 71 6c 04 5f 70 69 64    04 36 36 36 37 0f 5f 63    sql._pid.6667._c\n  6c 69 65 6e 74 5f 76 65    72 73 69 6f 6e 06 35 2e    lient_version.5.\n  36 2e 32 30 09 5f 70 6c    61 74 66 6f 72 6d 06 78    6.20._platform.x\n  38 36 5f 36 34 0c 70 72    6f 67 72 61 6d 5f 6e 61    86_64.program_na\n  6d 65 05 6d 79 73 71 6c                               me.mysql        \n\nT 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]\n  07 00 00 02 00 00 00 02    00 00 00                   ...........     \n\nT 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]\n  21 00 00 00 03 73 65 6c    65 63 74 20 40 40 76 65    !....select @@ve\n  72 73 69 6f 6e 5f 63 6f    6d 6d 65 6e 74 20 6c 69    rsion_comment li\n  6d 69 74 20 31                                        mit 1           \n\nT 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]\n  01 00 00 01 01 27 00 00    02 03 64 65 66 00 00 00    .....'....def...\n  11 40 40 76 65 72 73 69    6f 6e 5f 63 6f 6d 6d 65    .@@version_comme\n  6e 74 00 0c 08 00 08 00    00 00 fd 00 00 1f 00 00    nt..............\n  05 00 00 03 fe 00 00 02    00 09 00 00 04 08 48 6f    ..............Ho\n  6d 65 62 72 65 77 05 00    00 05 fe 00 00 02 00       mebrew......... \n\nT 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]\n  01 00 00 00 01                                        .....           <\/pre>\n
mysqld\u304clisten\u3057\u3066\u3044\u308bport\u306b\u63a5\u7d9a\u3059\u308b\u3068Server\u304b\u3089Initial Handshake Packet\u304c\u9001\u3089\u308c\u3066\u304d\u307e\u3059\u3002\u3053\u306e\u60c5\u5831\u3092\u5143\u306bClient\u306fHandshakeResponse41<\/a>\u306ePacket\u3092\u751f\u6210\u3057\u3066\u8fd4\u9001\u3057\u3001Server\u5074\u3067\u8a8d\u8a3c\u304c\u3068\u304a\u308c\u3070TextProtocol\u306e\u3084\u308a\u53d6\u308a\u306b\u9032\u3080\u3053\u3068\u304c\u51fa\u6765\u307e\u3059\u3002
\n<\/p>\n
 \u901a\u4fe1\u30e1\u30c3\u30bb\u30fc\u30b8\u306e\u5358\u4f4d - MySQL Packet<\/h3>\n
\u901a\u4fe1\u3092\u884c\u3046\u969b\u306fMySQL Packet\u3068\u3044\u3046\u30e1\u30c3\u30bb\u30fc\u30b8\u306e\u5358\u4f4d\u3067\u3084\u308a\u53d6\u308a\u3092\u3059\u308b\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002MySQL Packet\u306f4byte\u306eheader + payload\u3068\u3044\u3046\u69cb\u6210\u306b\u306a\u3063\u3066\u304a\u308a\u3001dev.mysql.com\u306e\u8cc7\u6599<\/a>\u304b\u3089\u5f15\u7528\u3059\u308b\u3068:<\/p>\n\n\n\n\n\n
size <\/th>\nname <\/th>\ndescription <\/th>\n<\/tr>\n
int<3> <\/td>\npayload_length <\/td>\nLength of the payload. The number of bytes in the packet beyond the initial 4 bytes that make up the packet header.<\/td>\n<\/tr>\n
int<1> <\/td>\nsequence_id <\/td>\nSequence ID<\/td>\n<\/tr>\n
string <\/td>\npayload <\/td>\n[len=payload_length] payload of the packet<\/td>\n<\/tr>\n<\/table>\n
<\/p>\n
4byte\u3057\u304b\u306a\u3044\u306e\u3067\u3055\u3063\u304f\u308aparse\u3067\u304d\u305d\u3046\u3067\u3059\u306d\u3002
\n\u305d\u308c\u3067\u306f\u5148\u307b\u3069ngrep\u3067\u8868\u793a\u3055\u308c\u3066\u3044\u305fpacket\u3092MySQL Packet\u3068\u3057\u3066\u624b\u3067parse\u3057\u3066\u307f\u307e\u3057\u3087\u3046<\/p>\n
\nT 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]\n  4a 00 00 00 0a 35 2e 36    2e 32 30 00 01 00 00 00    J....5.6.20.....\n  21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80    !K:~0oax...!....\n  15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64    ...........X0&jd\n  3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61    :43E@S^.mysql_na\n  74 69 76 65 5f 70 61 73    73 77 6f 72 64 00          tive_password.<\/pre>\n
MySQL Packet\u306f3byte\u306epayload length, 1byte\u306esequence id\u3001payload length - 4\u304cpayload\u3068\u306a\u308b\u306e\u3067<\/p>\n
\npayload_length: 4a 00 00\nsequence_id: 00\npayload: 0a 35 2e 36    2e 32 30 00 01 00 00 00 5.6.20.....<\/pre>\n
\u3068\u306a\u308b\u308f\u3051\u3067\u3059\u306d\u3002\u305d\u308c\u3067\u306fpayload\u3092\u66f4\u306bparse\u3057\u3066\u307f\u307e\u3057\u3087\u3046<\/p>\n
 Connection Phase Packet<\/h3>\n
\u63a5\u7d9a\u3057\u305f\u969b\u306b\u6700\u521d\u306bServer\u304b\u3089\u9001\u3089\u308c\u3066\u304f\u308bMySQL Packet\u304cConnection Phase Packet\u3068\u306a\u308a\u307e\u3059\u3002\u518d\u3073dev.mysql.com<\/a>\u306e\u8cc7\u6599\u304b\u3089\u5f15\u7528\u3057\u307e\u3059:<\/p>\n
\n1              [0a] protocol version\nstring[NUL]    server version\n4              connection id\nstring[8]      auth-plugin-data-part-1\n1              [00] filler\n2              capability flags (lower 2 bytes)\n  if more data in the packet:\n1              character set\n2              status flags\n2              capability flags (upper 2 bytes)\n  if capabilities & CLIENT_PLUGIN_AUTH {\n1              length of auth-plugin-data\n  } else {\n1              [00]\n  }\nstring[10]     reserved (all [00])\n  if capabilities & CLIENT_SECURE_CONNECTION {\nstring[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))\n  if capabilities & CLIENT_PLUGIN_AUTH {\nstring[NUL]    auth-plugin name\n  }<\/pre>\n
Connection Phase\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306f\u53ef\u5909\u9577\u306e\u30c7\u30fc\u30bf\u304c\u3042\u307e\u308a\u51fa\u3066\u3053\u306a\u3044\u306e\u3067\u7c21\u5358\u3067\u3059\u306d\u3002ngrep\u3067\u898b\u3066\u3044\u305f\u6642\u306f\u3053\u3093\u306a\u611f\u3058\u306epayload\u3060\u3063\u305f\u306e\u3067<\/p>\n
\n            0a 35 2e 36    2e 32 30 00 01 00 00 00\n21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80\n15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64\n3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61\n74 69 76 65 5f 70 61 73    73 77 6f 72 64 00<\/pre>\n
\u624b\u3067\u5206\u5272\u3057\u3066\u3044\u304f\u3068\u3053\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n
\nprotocol version: 0a\nserver version: 35 2e 36    2e 32 30 00\nconnection id: 01 00 00 00\nauth-plugin-data-part-1: 21 4b 3a 7e 30 6f 61 78\nfilter: 00\ncapability flags: ff f7\ncharacter set: 21\nstatus flag: 02 00\ncapability flags: 7f 80\nauth-plugin-data: 15\nreserved: 00 00 00 00 00 00 00    00 00 00\nauth-plugin-data-part-2: 58 30 26 6a 64 3a 34 33 45 40 53 5e 00\nauth-plugin-name: 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73    73 77 6f 72 64 00<\/pre>\n
<\/p>\n
\u306a\u3093\u3068\u306a\u304f\u305d\u308c\u3063\u307d\u3044\u30c7\u30fc\u30bf\u306b\u306a\u308a\u307e\u3057\u305f\u306d\u3002\u3068\u306f\u3044\u3048\u624b\u3067\u5206\u5272\u3057\u3066\u3066\u3082\u6642\u9593\u304b\u304b\u308a\u307e\u3059\u3057\u3001\u9762\u767d\u304f\u7121\u3044\u306e\u3067Initial Packet\u3092Go\u3067parse\u3057\u3066\u307f\u308b\u30d7\u30ed\u30b0\u30e9\u30e0\u3092\u66f8\u3044\u3066\u307f\u307e\u3057\u305f\u3002
\n\u3053\u308c\u3092\u5143\u306bnet.Dial\u3067\u3064\u306a\u3052\u3066\u307f\u305f\u308a\u3059\u308b\u3068Initial Packet\u3060\u3051\u306fparse\u3067\u304d\u305f\u308a\u3001\u7121\u99c4\u306b\u9811\u5f35\u308c\u3070\u30aa\u30ec\u30aa\u30ecMySQL Client\u304c\u4f5c\u308c\u308b\u306e\u3067\u53c2\u8003\u306b\u904a\u3093\u3067\u307f\u3066\u3082\u3089\u3048\u308c\u3070\u3001\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n
http:\/\/play.golang.org\/p\/Gvb4fxZETE<\/a><\/p>\n
\u3082\u3057\u9593\u9055\u3063\u3066\u3044\u305f\u3089\u7a81\u3063\u8fbc\u307f\u3044\u305f\u3060\u3051\u308b\u3068\u3042\u308a\u304c\u305f\u3044\u3067\u3059\u3002<\/p>\n
 \u305d\u306e\u307b\u304b<\/h3>\n