Brief analysis of MySQL Server Connection Process

  • 2020-09-16 07:48:45
  • OfStack

mysqld is the server side master process of MySQL. It can be said that mysqld is the real core of MySQL. All the work is carried out around the mysqld process. So when it comes to dissecting this behemoth, mysqld's code is the best place to start.

All 1 slices start with the familiar main() function, which is actually the mysqld_main() function. This code is in mysqld_main() then calls win_main)(). The win_main() function does most of the initialization.

After the initialization is complete, MySQL is ready to accept the connection. Then comes our main character, the Handle_connections_methods() function. The main job of this function is to create three sub-processes that accept TCP/IP, named pipes, and Shared memory connections, respectively. Generally, customers use TCP/IP (socket) to connect to MySQL server, which is the most flexible communication method. However, in the application environment of embedded software, the latter two communication modes need to be adopted.

Simplified handle_connections_methods() function:

static void handle_connections_methods()
  mysql_cond_init(key_COND_handler_count, &COND_handler_count, NULL);
  mysql_thread_create(key_thread_handle_con_namedpipes, &hThread, &connection_attrib, handle_connections_namedpipes, 0)) ;
  mysql_thread_create(key_thread_handle_con_sockets, &hThread, &connection_attrib, handle_connections_sockets_thread, 0)) ;
  mysql_thread_create(key_thread_handle_con_sharedmem, &hThread, &connection_attrib, handle_connections_shared_memory, 0))
  while (handler_count > 0)
    mysql_cond_wait(&COND_handler_count, &LOCK_thread_count);

After three new threads are created, the handle_connectins_methods() function enters a long loop and does not quit until all three connection threads have exited. Here I mainly look at the connection thread of socket, our research object is this handle_connections_sockets_thread. After this thread initializes itself, it calls handle_connections_sockets() directly;

The handle_connections_sockets() function USES the call select() to listen on mysqld's port and then wait for the client to connect. After a client connection, this function creates a new variable of type THD, which is a "social butterfly", starting with connection establishment, through SQL parsing, query execution, result return, and so on. This variable 1 is always there, but this is a very important variable.

There is also the structure struct st_vio, which is a relay station for 1 command. A structure of type vio is also defined in "Social Butterfly" THD. The function of this structure is to read the communication from the socket from the store, and then assign its own value to the vio variable of THD. The VIO type describes a request in detail, including the content of the request, the time, the socket address of the request, and so on. What happens is that the "social butterfly" is passed to the service thread, and create_thread_to_handle_connection() implements this function.

The following is the deleted code

void create_thread_to_handle_connection(THD *thd)
  if (cached_thread_count > wake_thread)
    mysql_thread_create(key_thread_one_connection, &thd->real_id, &connection_attrib, handle_one_connection, (void*) thd))) ;    

This function checks to see if there are idle cache threads (MySQL does not destroy disconnected service threads immediately, but caches them), if there are cache threads, and if not, creates a new thread to serve the connection. At this point, a connection enters the service thread and the connection thread returns to continue waiting for the connection.

The following contents are all implemented in the service thread. There is a very detailed code trace in The In-depth Understanding of MySQL for those interested to have a look. I enclose the sequence of function calls for your reference.


Related articles: