How to Identify the user who issued MySQL Shutdown command

Question

We have had several situations when our production MySQL server suddenly became unavailable. The error log just shows "Normal shutdown" followed by the typical shutdown messages. How can I determine the Linux or MySQL user account and host name of the connection issuing the shutdown command?

Answer 1

Check the secure log in the event it was run via a sudo service call

sudo grep mysql /var/log/secure

Check to see if there's any mysql related stuff going on in cronjobs

sudo grep mysql /var/log/cron

Check shell histories for mysqladmin calls

cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done

Check with people you know that either have sudo or mysql root access on this machine

"Hey, which one of you has been shutting down mysql?"

Answer 2

For security concern who executed what queries at what time etc.. you should use "Audit Plugins". You can refer my existing answer here https://dba.stackexchange.com/a/62477/6037

Ofcourse you can find "mysqladmin" using shell histories or tracing "General/Error log" of MySQL server.

For example if you have General log enabled either in a "Table" or "File" you can find out a "command_type" as "shutdown" and you will come to know who executed it.

SELECT * FROM general_log WHERE command_type='shutdown';

Answer 3

One perfect example why root access even for admins is bad. Maintaining a sudoers file and a centralized database of privileged users and groups(LDAP for example) would help solving problems like this.

One note: the wrongdoer might well be a cronjob. I'd check that.

Answer 4

If you want to see who issued shutdown from a Linux viewpoint and if you are real risk taker, I have a very dangerous but fun suggestion.

You could create a special log file that records when service mysql stop is issued or if mysqld_safe decides to shutdown.

Let's say you want the file to be called /var/lib/mysql/.shutdown_signals

Run the following two lines

echo -n > /var/lib/mysql/.shutdown_signals
chown mysql:mysql /var/lib/mysql/.shutdown_signals
cp /etc/init.d/mysql /etc/init.d/mysql_new
vi /etc/init.d/mysql_new

Look for these lines or something to this effect

'stop')
  # Stop daemon. We use a signal here to avoid having to know the
  # root password.

or

'restart')
  # Stop the service and regardless of whether it was
  # running or not, start it again.

Simply add these lines

DT=`date +"%Y-%m-%d %H:%M:%S"`
echo "Shutdown Issued : ${DT}"
whoami >> .shutdown_signals
w      >> .shutdown_signals

Now, find mysqld_safe

which mysqld_safe

It will echo

/usr/bin/mysqld_safe

Next, run these

cp /usr/bin/mysqld_safe /usr/bin/mysqld_safe_new
vi /usr/bin/mysqld_safe_new

Look for the word shutdown. You should find something like this

while true
do
  rm -f $safe_mysql_unix_port "$pid_file"       # Some extra safety

  eval_log_error "$cmd"

  if test ! -f "$pid_file"              # This is removed if normal shutdown
  then
    break
  fi

Change the if ... then to

  if test ! -f "$pid_file"              # This is removed if normal shutdown
  then
    DT=`date +"%Y-%m-%d %H:%M:%S"`
    echo "Shutdown Issued : ${DT}"
    whoami >> .shutdown_signals
    w      >> .shutdown_signals
    break
  fi

You will need to shutdown mysql and switch the files around

service mysql stop
mv /usr/bin/mysqld_safe /usr/bin/mysqld_safe_old
mv /usr/bin/mysqld_safe_new /usr/bin/mysqld_safe
mv /etc/init.d/mysql /etc/init.d/mysql_old
mv /etc/init.d/mysql_new /etc/init.d/mysql
service mysql start

Going forward, you simply run

tail -30 /var/lib/mysql/.shutdown_signals

So, if you are truly a risk taker...

GIVE IT A TRY !!!

WARNING !!! Administer databases responsibly. Do risky things in Staging or Dev VMs first. If you are 100% successful, then

GIVE IT A TRY !!!