Planet MySQL :: Planet MySQL - Archives - Shutting down with mysqld, mysqladmin, SIGTERM, or...

How do you shutdown mysqld? I tend to use SIGTERM. People where I work tend to use mysqladmin shutdown. When things get bad I use SIGKILL. These three methods will end up with a dead mysqld but the one you choose depends on the situation and can even result in lost data. On Linux the difference between SIGTERM and SIGKILL is significant and often times misunderstood.

Before processes start to die it is important to understand the relationship between mysqld_safe and mysqld. mysqld_safe is the watchdog script for mysqld. It is responsible for starting mysqld and keeping an eye on it. It does this by waiting for mysqld to exit then checking the return code. On a safe shutdown such as one done by mysqldadmin or a SIGTERM mysqld will return zero. When mysqld_safe sees a zero return code it will also exit. If the return code is anything else then mysqld_safe assumes mysqld crashed and starts a new instance of mysqld. This difference can help explain why mysqld sometimes just won’t go away.

The basic shutdown process is fairly well documented so I won’t cover that here. It is important to understand the difference between mysqladmin and sigterm. As described in the manual mysqladmin will create a shutdown thread. Using a TERM signal will also create a shutdown thread. The major difference between the two is that mysqladmin makes a connection to mysql and sends a shutdown packet. This means it can be used from a remote host. It also means that mysqladmin must pass mysql permissions before allowing the shutdown. SIGTERM is delivered through the signal mechanism in linux and must only pass the linux system user rules for delivering a signal. For example if mysqld is running as root then the ebergen user can’t deliver a signal to it without using sudo.

SIGTERM is handy for safely shutting down mysqld when the root password is lost or there are too many connections and the reserved super user connection is also taken up. I tend to use this method the most because I’m usually logged into the server via ssh and SIGTERM usually works. It is also slightly less typing. I do have a bad habit of using killall mysqld instead of kill -TERM mysqld. This will deliver a TERM signal to every mysqld on the system. If you happen to be on a machine that has multiple mysqld instances such as a shared developer workstation don’t do that. There are several different methods to find the mysqld process and send it a term signal. Here are a few examples where nnnn is the pid of the mysqld process. These also work with SIGKILL. Note that killall defaults to a TERM signal

kill -TERM nnnn
killalll mysqld
kill -TERM `pidof mysqld`
kill -TERM `cat /var/run/mysqld.pid`

Getting into SIGKILL or kill -KILL is where things get interesting. In Linux SIGKILL isn’t really a signal in that it never actually gets delivered to the process. Since it never gets delivered to the process it can’t be caught or blocked. This is where people usually tell me that SIGKILL can be blocked. My best guess on why this confusion happens is because the concept of a signal that can’t be blocked seems to defeat the purpose of a signal. Also I don’t think a lot of code checks for errors from the signal function call. Last there may be other operating systems that allow a SIGKILL to be blocked. Here is my proof that it can’t be blocked in linux.

ebergen@randy:(~) cat test.c
#include <unistd.h>
#include <signal.h>
#include <stdio.h>

int main (int argc, char **argv) {

if (signal(SIGKILL, SIG_IGN) == SIG_ERR)
 printf("Oops!!\n");
 else
 printf("Good to go!\n");

sleep(30);

return 0;
}
ebergen@randy:(~) gcc -Wall test.c
ebergen@randy:(~) strace ./a.out
munmap(0x2b90d5119000, 104917) = 0
rt_sigaction(SIGKILL, {0x1, [KILL], SA_RESTORER|SA_RESTART, 0x3fe00302d0}, {0x3fdfe1cc80, ~[HUP INT QUIT ILL ABRT KILL USR1 SEGV PIPE TERM STKFLT TSTP TTIN URG XCPU VTALRM WINCH IO RT_16 RT_17 RT_18 RT_19 RT_20 RT_21 RT_22 RT_23 RT_24 RT_25 RT_26 RT_27 RT_28 RT_29 RT_30 RT_31], SA_RESTORER|SA_INTERRUPT|SA_NODEFER|SA_RESETHAND|0x1272740, 0x1bc6ade2}, 8) = -1 EINVAL (Invalid argument)
write(1, "Oops!!\n", 7Oops!!
) = 7

nanosleep({30, 0}, <unfinished ...>

ebergen@randy:(~) ./a.out
Oops!!

What SIGKILL means is to remove a process from existence. The process never gets another chance to run to attempt to block the signal. Linux simply cleans it up. What this means for MySQL is that it doesn’t get a chance to perform any shutdown tasks like flushing indexes for myisam tables. To MySQL it is effectively the same as pulling the plug on the server except that the filesystem still has a chance to flush modified data to disk.

SIGKILL should really be a last resort or only used when you know your mysqld is safe to shutdown. There is plenty of discussion on this with respect to making consistent backups. The rules are basically the same. When you take a filesystem snapshot or lvm snapshot the way the snapshot looks is effectively the same as running SIGKILL on mysqld to remove it from existence.

MySQL Links