Understanding MySQL 8.0 Authentication Plugin: Packet Capture and TLS Encryption Analysis

Author: Qin FulangThe content focuses on the MySQL 8.0 authentication plugin.

Member of the DBA team at Aikesen, responsible for handling day-to-day project issues and troubleshooting platform problems for the company. Passionate about IT, enjoys cruising the internet, skilled in photography and cooking—a DBA who can’t cook isn’t a good driver, didi~

Source: Original Submission

*Produced by the Aikesen Open Source Community, original content is not allowed for unauthorized use, please contact the editor for reprint approval and mention the source.

In the previous community article “Quickly Mastering the Use of MySQL 8.0 Authentication Plugin,” it was mentioned that MySQL 8.0 uses the caching_sha2_password authentication plugin for encrypted connections, which provided a preliminary introduction to encrypted connections. This article takes a look from the perspective of network packet capture to see how MySQL 8.0 with the caching_sha2_password plugin establishes an encrypted connection.

MySQL 5.7.25

tcpdump 4.9.2

wireshark 3.2.2

1.1 Packet Capture

1.2 MySQL connects via TCP and executes a test SQL

1.3 Packet capture analysis through Wireshark

From the diagram above, the following information can be analyzed:

From packets 9-20, it can be seen that without using an encrypted connection plugin, after capturing packets with a packet capture tool, Wireshark can directly parse the query statements, which is very insecure.

Here’s a tip:In Wireshark, you can set whether to display SQL query statements in the info column of the parse window and set the default port for MySQL TCP under Edit – Preferences – Protocols – MySQL.

MySQL 8.0.15

tcpdump 4.9.2

wireshark 3.2.2

2.1 Packet Capture

2.2 MySQL connects via TCP and executes a test SQL

Here, view the status beyond the test SQL

The algorithm used here is DHE-RSA-AES128-GCM-SHA256

2.3 Packet capture analysis through Wireshark

From the diagram above, the following information can be analyzed:

2.4 Analyze the use of TLS encrypted connection handshake process

Firstly, why is it TLS instead of SSL here?

TLS is a transport layer security protocol used by MySQL to establish encrypted connections between the client and server. TLS is sometimes referred to as SSL (Secure Sockets Layer), but MySQL does not actually use the SSL protocol for encrypted connections due to its weak encryption. The TLS protocol ensures privacy and data integrity between two communicating applications by encrypting the data, preventing any third party from intercepting the communication. It also authenticates the peer to verify their identity. By providing a secure communication channel between two peers, the TLS protocol protects the message integrity and ensures it is not tampered with.

MySQL supports multiple TLS protocol versions, and this test uses the client version 8.0 as TLSv1.2.

Step-by-step analysis:

The ClientHello is sent from client to server to initiate the handshake process by greeting the server.

This includes: the TLS protocol version, a 32-byte random number used to compute the Master secret and encryption keys, a unique session ID for the client, and a list of supported cipher suites. By opening the cipher suite list, you can see MySQL 8.0 uses the DHE-RSA-AES128-GCM-SHA256 algorithm.

The server returns a ServerHello. Returning the greeting,

This includes: the server’s version number, a 32-byte random number used to generate the Master secret, a session ID to identify the session with the client, a cipher suite supported by both the server and client, which in this case is MySQL 8.0’s DHE-RSA-AES128-GCM-SHA256 algorithm, and a compression method, optional, here it is null.

The server sends a list of x.509 certificates to the client to authenticate the client’s identity, containing the public key within the certificate.

Server key exchange, here the server’s DH parameters are sent.

Client certificate request, here the server sends information to authenticate the client’s identity, including the required certificate types.

The server greeting ends, waiting for information return from the client.

Client certificates.

Client key exchange.

This includes: server verification of client protocol version and whether it matches the original client hello message, and a random number Pre-master secret generated by the client encrypted with the server’s public key to authenticate the server to the client.

The client notifies the server that all future messages will be encrypted with the just-negotiated algorithm and key.

Completes TLS message encryption, with data encrypted using the just-negotiated key and algorithm.

New session tag, records encryption parameters.

Server notifies the client that it will encrypt information using the existing algorithm and key.

Completing TLS message encryption, the client successfully decrypts and verifies the information, and the server has successfully passed authentication.

The TLS message encryption handshake process ends here, and subsequent Application Data is the encrypted data stream.

MySQL 8.0’s encryption plugin balances security and performance during usage, and it is recommended to keep it enabled by default. Versions after MySQL 8.0.16 support the TLSv1.3 protocol.

Currently, methods attempted online for TLS decryption, tested by the author, have not been able to decrypt MySQL 8.0 TLS encrypted messages on Wireshark. If there are new methods, feel free to leave a comment for discussion.

If you want to perform some wireshark packet captures of MySQL, and need to see the SQL query, it’s currently recommended to use the MySQL 5.7 version or disable encrypted communication by using skip-ssl on MySQL 8.0.