Deep parsing MySQL Login principle

The first step in using the MySQL database must be to establish a connection login and then execute the SQL command on it. This process must be done either through the MySQL client or by connecting to the database via the C-API,JDBC standard interface. Today we talk about the MySQL landing process, which will involve the client and server interaction, and through the Tcpdump capture the package to show you the process.

TCP Handshake Protocol

remote connection to the database, MySQL uses TCP protocol communication, the first step is to establish a connection, that is, TCP 3 handshake. MySQL server side has a listener thread waiting for the client request,after the client initiates the request, first sends a sync packet to the service side, the service sends an ACK packet as the response to the client Sync packet, and sends a sync packet to the client. Finally, the client sends an ACK packet as a response to the server. With 3 handshakes, the TCP connection is really set up, and at this point the MySQL server allocates a connection for the client to use. Remember, TCP's 3-time handshake is done by the TCP protocol stack, and the application is not aware. However, so far, the entire MySQL connection process has only completed the first step-establishing a TCP connection.

The following is a schematic diagram of how TCP establishes connections and disconnects.

MySQL Handshake protocol

After the TCP connection is successfully established, the MySQL client communicates with the MySQL server to perform the MySQL authentication process. (1) The server will first send a handshake packet to the client, (2) then the client sends authentication information (user name, password, etc.) to the server, and (3) after the server receives the authentication package, it checks whether the user name and password are legitimate and sends the packet to inform the client of the authentication information. If legal, the landing is successful, otherwise, the landing failed. Connection error. Sometimes, we see the user in unauthenticated user through show processlist, which means that the connection process is in between steps 1th and 2nd, and the server waits for the client to send authentication information.

15922528       User   10.xx. 2.74:53216null   nullfrom    net       null 15923418       User   NULL   NULL   Login   NULL

Tcpdump Capture Package Verification

Below we verify our principle by tcpdump grasping the network packet. Because the test is carried out in the production environment, in order to avoid the IP leakage of the production network segment, the IP is replaced, but does not affect the analysis process. Specifically,10.aa.zz.142.10556 represents the client, 10.bb.yy.104.3306 represents the server side, and 3306 is the server's listening port number.

(1). Open the Tcpdump command on the client, listening to the communication network packet with 10.bb.yy.104.3306, with the following command:

tcpdump-s-nn-tttt-i eth0 host . Bb.yy. 104 and Port 3306 and tcp-c  -S outputs the serial number of TCP as an absolute value, not as a relative. -NN does not perform a conversion of port names. -TTTT prints the timestamp of the default format processed by date in each row. -i eth0 the network interface host that specifies the listenerTen. bb.yy.104  andPort3306Set the listening 10.bb.yy.104: 3306 of network packets-C -Indicates that the listening 100 packets will end.

(2). On the client side, use the MySQL command to remotely connect to the server 10.bb.yy.104,

Mysql–h10.bb.yy. 104 –p3306–uxxx–pxxx

After the successful login, and then directly execute exit, exit

(3) Analysis tcpdump Crawl network packet, focus on the establishment of TCP connection, MySQL authentication and TCP disconnection process. For example, the 1th part of the figure is the process of TCP connection establishment, the 2nd part is the MySQL authentication process, the 3rd part is the process of sending basic metadata information after successful landing, Part 4 is the process of disconnecting. From the graph, we can clearly see the TCP 3 handshake, MySQL authentication and TCP disconnect 4 wave process.

Establish a connection

This process is mainly reflected in the first part, the client 10.aa.zz.142.10556, first send a SYN packet numbered 1491894492, after the server received, sent the 1491894492+1 ACK packet, and sent a 2727774925 SYN packet, Finally, the client sends a 2727774925+1 packet to answer.

MySQL Certification

This process is mainly reflected in the second part, the server 10.bb.yy.104:3306 first send an authentication package to the client, and then the client sends the authentication package containing the user's password to the servers, after verifying the success, the service end to the client a response, then the entire certification process is over, As for the 3rd part, the service side and the client send each other metadata information, such as version information.

Disconnect Connection

This process is mainly reflected in the fourth part, when the client initiates the exit command, it starts to trigger the action. The client first sends a FIN packet numbered 1491894724, and then the server sends a 1491894724+1 ACK packet as an answer and sends a FIN packet numbered 2727775120, and the client sends 2727775120+1 as the answer, The whole process is over.

Packet Token parsing

S=syn initiates a connection flag, which is typically used to establish a TCP connection

P=push transmit data flags, typically used to transmit data

F=fin close connection flag, typically used to close a TCP connection

ACK indicates the reply package

rst= RESET Closes the connection unexpectedly

. Indicates that there are no flags

Source Code Implementation

for MySQL authentication code is mainly concentrated in the function native_password_authenticate, the specific call hierarchy is: Login_connection->check_connection ->acl_authenticate-> Do_auth_once->native_password_authenticate, the function logic is very simple, is to call Write_packet to send a authentication packet to the client, and then call Read_ Packet waits for the client to return a package containing information such as user name, password, and finally resolves the information in the package for password authentication, and after success, it will send a successful authentication network package in the call to PROTOCOL::SEND_OK, which can be fully reflected in the second step of the diagram. The underlying socket communication code is mainly concentrated in the sql/net_serv.cc, specifically read the interface My_net_read, write using interface my_net_write.

Problem

(1). What is the difference and connection between the Unix socket and the TCP login?

UNIX sockets are a way of implementing interprocess communication, and MySQL supports the use of UNIX sockets for client-server communication, but requires the client and server to be on the same machine. For UNIX sockets, it is also a socket, the listener thread will listen to both the TCP socket and the UNIX socket, accept the request and then processing, the subsequent processing logic is consistent, but the underlying communication is not the same way.

MySQL  -h127.  0.0. 1 –p3306–uxxx–pxxx  [TCP communication mode ]mysql  -uxxx–pxxx –S/usr/mysql/mysql.sock  [UNIX Socket communication mode ] 

(2). does the monitor socket have a port common to the communication socket?

We know that the server has a listening socket on the 3306 port listening, waiting for a new incoming customer request, once a request comes in, the server will re-create a new communication socket, the new socket is dedicated to communicate with this customer, while the listening socket continues to listen. Although it is 2 sockets, the listener socket and communication socket are the same port, which can be confirmed by Netstat.

(3). When does the connection timeout parameter connect_timeout function?

This parameter is essentially in the MySQL authentication process, if in this process, the client over connect_timeout time still does not send the password Authentication package come over, will be actively disconnected.

Reference documents

http://www.pythian.com/blog/what-is-an-unauthenticated-user/

Http://www.tuicool.com/articles/mY7nmu

Deep parsing MySQL Login principle