Shulou(Shulou.com)06/01 Report--

MYSQL Connection Login Process Analysis

In newer versions of MYSQL, thread pooling is not enabled by default, and each client connection has its own thread in the server process.

When a client (application) connects to a MySQL server, the server authenticates it based on the username, original host information, and password.

mysql can be logged in through Unix socket and TCP two ways,

Unix socket is a way to achieve inter-process communication, mysql supports the use of Unix socket to achieve client-server communication, but requires the client and server on the same machine. For unix socket, it is also a socket. The listening thread will listen to TCP socket and Unix socket at the same time, receive the request and then process it. The subsequent processing logic is the same, but the underlying communication method is different. Here is a detailed analysis of TCP's approach.

The specific process involves the interaction between client and server, and this process is demonstrated by tcpdump capturing packets.

MYSQL join handshake procedure

MYSQL remote login connection is based on TCP 3-way handshake mechanism, TCP connection successfully established, mysql client and mysql server began to communicate, mysql authentication process.

(1)The server first sends a handshake packet to the client,

(2)Then the client sends authentication information (username, password, etc.) to the server,

(3)After receiving the authentication packet, the server checks whether the username and password are valid, and sends a packet to inform the client of the authentication information. If it is legal, the landing is successful, otherwise, the landing fails. Connection error.

tcpdump procedure

xx.xxx.7.102 stands for client,

xx.xxx.7.104 stands for server side,

3306 is the listening port number of the server.

(1). Open the tcpdump command on the client and listen for communication network packets with xx.xxx.7.104 port 3306. The command is as follows:

tcpdump -S -nn -tttt -i bond0 host xx.xxx.7.104 and port 3306 and tcp -c 100-nn Do not convert port names. - tttt outputs a timestamp in the default format handled by date in each line. - i bond0 specifies the network interface host xx.xxx.7.104 and port 3306 to listen to xx.xxx.7.104 port 3306 network packet-c 100 indicates that listening to 100 packet ends

Use the following command to generate the cap file here. View it with wireshark software to see more detailed information, including the packet contents. tcpdump -i bond0 host xx.xxx.7.104 and port 3306 -w /tmp/1.cap --Generate cap file

(2). On the client side, use mysql command to remotely connect to the server at xx.xxx.7.104,

mysql -hxx.xxx.7.104 -P3306 -uxxx -pxxx

After landing successfully, then directly execute exit, exit

(3)Analyze the network packets captured by tcpdump as follows:

Packet tag parsing

S=SYN connection initiation flag, generally used to establish TCP connections

P=PUSH Transmit data flag, generally used to transmit data

F=FIN Close connection flag, generally used to close TCP connections

ack indicates an acknowledgement packet

RST= RESET Abnormal Close Connection

. It means there's no sign.

Part 1 is the 3-way handshake process for TCP connection establishment:

102 (client) initiates connection to 104 (server)----->

104 (server) initiates connection and response packet to 102 (client)---->

102 (client) initiates response to 104 (server)---->

TCP establishes the connection.

Part 2 is the MYSQL authentication process:

Steps 4 and 5 are that the server sends some information of the server to the client, which can be seen from the package content, including version, character set, etc.

The actual login request starts with Step 6:

102 (client) initiates a login authentication request to 104 (server), the user is root---->

104 (server) first returns an acknowledgement packet in tcp, indicating that tcp connection exists------>

Then, 104 (server) authentication passed, return OK----->

login is successful

The packet sent in step 6 contains the user password message.

The third part is the process of sending basic metadata information after successful login.

102 (client) initiates a query request to 104 (server)------>

104 (server) returns information to 102 (client)------->

102 (Client) Return Response Packet------->

Query complete.

Part 4 is the disconnection process

MYSQL listening and connecting threads

MYSQL listening is based on TCP protocol. On port 3306 of the server, MySQL service repeatedly listens for requests sent by clients:

When a new connection succeeds, the connection session creates a new foreground thread in the database.

This foreground thread has its own ID in the database, and there is a corresponding thread number corresponding to the OS level. This thread is under the mysqld process.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.