When mysqld kills mysqld

PREVIOUS POST
NEXT POST

Server ShutdownThe other day a colleague and friend of mine, Peter Boros, had a case where one of our clients had to track down the process shutting down MySQL. This blog is based on the discussion we had about that internally.

Our client wanted Peter to identify the culprit behind periodic shutdowns. This proved to be slightly more difficult than usual, for reasons that you might guess from the title of this blog.

Here is what Peter saw in the logs:

Some of you may recall that I wrote a blog post about tools that can help you identify other processes that send signals to mysqld. Peter chose SystemTap to track down the process. The script he used to trace it was from David Busby’s blog:

Using this SystemTap script Peter discovered that the “killer” was mysqld:

As you can see above, mysqld received a SIGTERM from mysqld. How is this possible? Let’s try to interpret what happened here!

According to the manual, server shutdown can be initiated in different ways. For instance:

  • SIGTERM is sent to mysqld by a UNIX user
  • server is shut down administratively via mysqladmin shutdown by a privileged mysql user

Let’s assume that we are talking about the first example, where a privileged process/script sends a SIGTERM to mysqld. If that was the case we would get:

The first line in the above output shows the client process (bash) that issued the TERM signal to MySQL. In response, MySQL started a signal handler thread and processed shutdown (COM_SHUTDOWN) using that thread. In turn the referenced function kill_mysqld() may send a signal to current_pid on behalf of the initiating process.

As a side note, in the above output you don’t see anything related to threads. You could get even more detail about MySQL’s operation if you were to modify the tapscript to include tgkill system calls and display related thread IDs as well:

While this might be useful to better comprehend how mysqld behaves, it is irrelevant in our search for the culprit process, so I’m not going to include the output of that script here – that exercise will be left to you, dear reader!

Now what happens if a MySQL user with administrative privileges initiates the shutdown via the console instead? We get:

You see that this time the sender was mysqld, which thoroughly resembles the original output that Peter had. Thus, we now know that what we were looking for was a program called mysqladmin shutdown!

Unfortunately, this means that the program may not be local and the client could connect from a different host. A local recursive grep may or may not solve our problem. However, if we enable general-log with log-warnings=2 it might yield something like:

Thus, we now know where to run our recursive grep for that rogue mysqladmin shutdown (or a similar, API-leveraging) process! In my case it was running on remote host 192.168.3.1 and connected as MySQL user ‘robert’.

Of course you could find alternative methods to track down MySQL connections but that’s beyond what I intended to include in this blog. Perhaps in the next?

PREVIOUS POST
NEXT POST

Share this post

Comments (5)

  • Jouni Järvinen Reply

    It’ll potentially be boots to the culprit.

    October 9, 2015 at 11:35 am
  • Nils Reply

    Just admit it Robert, it was you all along….

    October 9, 2015 at 2:58 pm
  • Frank79 Reply

    [den#haag (
    ]schlag7bridgestone7
    [largeband/runningdown/us
    [
    ]

    system:
    C:/

    October 12, 2015 at 2:43 am
  • David Harper Reply

    I “discovered” this curious behaviour of mysqld several years ago whilst using strace to investigate an unrelated problem. A search of the source code revealed that the server-side routine which handles the shutdown command issued by “mysqladmin shutdown” simply sends its parent thread a SIGTERM signal and then exits. Thus there is only ONE code path for the actual shutdown procedure, and it goes through the SIGTERM signal handler. At the time, I though this was a nice clean solution, but as Robert has demonstrated, if you don’t know about it, then it is utterly baffling 🙂

    October 12, 2015 at 9:00 am
  • Frank79 Reply

    I could save my SLQ by qwerty task using avoiding relative buzzyis. That’s why MY SLQ reach up nirvana interface

    November 13, 2015 at 4:08 am

Leave a Reply