MySQL Protocol Analysis and Source Code Exploration

In order to study the MySQL protocol, the author first disables SSL in MySQL (version 8.0.20) by adding skip_ssl to the my.cnf file and restarting the service, then uses Wireshark with the --protocol=tcp client option to capture traffic.

Sample client commands are shown: $ bin/mysql -h localhost -u root -p'123456' --protocol=tcp mysql> use t1; mysql> select * from t1;

The captured packet list (Table 1‑2‑2) displays the three‑way TCP handshake (packets 1‑3), the MySQL server greeting (packet 5), login request (packet 7), authentication switch, and the four‑way TCP termination (packets 51‑54). The author illustrates the overall packet interaction flow with a diagram (Figure 1‑2‑3).

Analysis of the Server Greeting packet reveals fields such as protocol version, server version, thread ID, and authentication salt, matching the structure described in the official MySQL documentation. The subsequent Login Request packet shows the username root and the authentication plugin caching_sha2_password , followed by an Auth Switch Request and an OK response.

Further packets demonstrate the sequence of commands when selecting a database: SELECT DATABASE() (COM_QUERY), USE t1 (COM_INIT_DB), SHOW DATABASES , SHOW TABLES , and finally the SELECT * FROM t1 query (COM_QUERY). The author details the field‑definition packets and the data rows returned (Figures 1‑2‑14 to 1‑2‑15).

The article then shifts to source‑code analysis. It presents the run_plugin_auth function, which drives client‑side authentication plugins, and shows the enumeration enum_server_command that maps command IDs to names such as COM_QUIT , COM_QUERY , COM_INIT_DB , etc.

For MySQL 8.0.20, the default authentication plugin is caching_sha2_password , defined by the macro #define caching_sha2_password_plugin_name "caching_sha2_password" . The author traces how run_plugin_auth eventually calls caching_sha2_password_auth_client during the authentication state machine.

In summary, MySQL 8.0.20 uses the caching_sha2_password plugin (previous versions used mysql_native_password ), the plugin includes a cache, and a USE database statement generates multiple packets: SELECT DATABASE() , USE , SHOW DATABASES , and SHOW TABLES .