In-depth analysis of mysql login principles, analysis of mysql Login

In-depth analysis of mysql login principles, analysis of mysql Login

The first step in using the mysql database is to establish a connection and log on, and then execute the SQL command above. Whether it is through the mysql client, or through the C-API, JDBC standard interface to connect to the database, this process must be indispensable. Today, we will talk about the specific mysql login process, which involves the interaction between the client and the server, and show you this process through tcpdump packet capture.

TCP handshake protocol

Remote connection to the database, mysql uses TCP communication, the first step is to establish a connection, that is, three handshakes of TCP. The mysql server has a listening thread waiting for client requests. After the client initiates a request, it first sends a sync package to the server, and the server sends an ack package as a response to the client sync package, send a sync package to the client at the same time, and then the client sends an ack package to respond to the server. After three handshakes, the TCP connection is actually established. At this time, the mysql server allocates a connection for the client to use. Remember, the three handshakes of TCP are completed by the TCP protocol stack, and the application is not aware of them. 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 TCP connection establishment and disconnection.

MYSQL handshake protocol

After the TCP connection is established, the mysql client starts to communicate with the mysql server for mysql authentication. (1) The server first sends a handshake packet to the client. (2) The client then sends the authentication information (username, password, etc.) to the server. (3) After the server receives the authentication packet, checks whether the user name and password are valid and sends packets to the client for authentication. If it is valid, the logon is successful. Otherwise, the logon fails. Connection error. Sometimes, we can see through show processlist that the User is in unauthenticated user, which indicates that the connection process is between Step 2 and step 2, and the server is waiting for the client to send authentication information.

15922528       unauthenticated user   10.xx.2.74:53216 NULL   Connect NULL   Reading from net       NULL15923418       unauthenticated user   connecting host NULL   Connect NULL   login   NULL 

Tcpdump packet capture Verification

Next we will use tcpdump to capture network packets to verify our principles. As the test is carried out in the production environment, IP addresses are replaced to avoid IP address leakage in the production network segment, but the analysis process is not affected. Specifically, 10. aa. zz.142.10556 indicates the client, 10. bb. yy.104.3306 indicates the server, and 3306 indicates the listening port number of the server.

(1) Open the tcpdump command on the client and listen to the communication network package 10. bb. yy.104.3306. The command is as follows:

Tcpdump-S-nn-tttt-I eth0 host 10. bb. yy.104 and port 3306 and tcp-c 100-S output the serial number of tcp in absolute value instead of relative value. -Nn does not convert the port name. -Tttt outputs the default timestamp format processed by date in each row. -I eth0 indicates that the network interface host 10. bb. yy.104 and port 3306 of the listener is set to listen to the Network Package-c 3306 of 10. bb. yy.104: 100 indicates that the listener will end with 100 packets.

(2) remotely connect to the server 10. bb. yy.104 using the mysql command on the client,

mysql –h10.bb.yy.104 –P3306 –uxxx –pxxx

After successful login, execute exit and exit.

(3) analyze the network packets captured by tcpdump, and focus on the process of establishing TCP connections, MYSQL authentication, and TCP disconnection. For example, in the figure, Part 1 is the process of establishing a TCP connection, Part 2 is the process of MYSQL authentication, and part 3 is the process of sending basic metadata information after successful login, part 2 is the process of disconnecting. As shown in the figure, we can clearly see the three handshakes of TCP connection establishment, MYSQL authentication, and four waves of TCP disconnection.

 

Establish a connection

This process is mainly reflected in the first part, client 10. aa. zz.142.10556: First, send a SYN Packet numbered 1491894492. After the server receives the packet, it sends an ACK packet of 1491894492 + 1 and a 2727774925 SYN packet, finally, the client sends a 2727774925 + 1 packet for response.

MYSQL Authentication

This process is mainly reflected in the second part, Server 10. bb. yy.104: 3306 first sends an authentication package to the client, and then the client sends an authentication package containing the user password to the server. After the authentication is successful, the server finally sends a response to the client, the entire authentication process ends. Part 1 is the metadata information sent by the server and the client, such as the version information.

Disconnect

This process is mainly reflected in the fourth part. When the client initiates the exit command, it starts to trigger this action. The client first sends a FIN packet numbered 1491894724, then the server sends an ACK packet of 1491894724 + 1 as the response, and sends a FIN packet numbered 2727775120, finally, the client sends 2727775120 + 1 as the response, and the entire process ends.

Packet Tag Parsing

S = SYN initiates a connection flag, which is generally used to establish a TCP connection

P = PUSH data transfer mark, which is generally used for data transmission

F = FIN closes the connection flag, which is generally used to close TCP connections.

Ack indicates the response packet

RST = RESET exception close connection

. Indicates no flag

Source code implementation

MYSQL authentication code is mainly used in the native_password_authenticate function. The Calling level is login_connection-> check_connection-> acl_authenticate-> do_auth_once-> native_password_authenticate. The function logic is simple, it is to call write_packet to send an authentication package to the client, then call read_packet to wait for the client to return the package containing the user name, password and other information, and finally parse the information in the package for password verification. After successful, A successfully authenticated network packet will be sent by calling Protocol: send_ OK. This process can be fully reflected in step 2 in the figure. The underlying socket communication code is mainly concentrated in SQL/net_serv.cc. Specifically, the interface my_net_read is used for reading and the interface my_net_write is used for writing.

Problem

(1) What are the differences between Unix socket login and TCP login?

Unix socket is a way to implement inter-process communication. mysql supports the use of Unix socket to implement client-server communication, but the client and server must be on the same machine. For unix socket, it is also a socket. The listening thread listens to both TCP socket and Unix socket, receives the request and then processes it. The subsequent processing logic is consistent, however, the underlying communication methods are different.

Mysql-h127.0.0.1-P3306-uxxx-pxxx [TCP communication mode] mysql-uxxx-pxxx-S/usr/mysql. sock [unix socket communication mode]

(2) Does the listener socket share a port with the communication socket?

We know that the server has been listening for a socket at Port 3306, waiting for new customer requests. Once a request comes, the server will create a new communication socket, this new socket is used to communicate with this customer, while the socket listening continues to listen. Although there are two sockets, both the listening socket and the communication socket are on the same port. You can confirm this problem through netstat.

(3) When will the connection timeout parameter connect_timeout be used?

In essence, this parameter works in the MYSQL authentication process. If the client still does not send the password authentication package after the connect_timeout time, the connection is automatically disconnected.

References

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

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