Mysql – How do we identify service shutdown time in MySQL

MySQLmysql-5.7shutdown

How can we identify service shutdown time in MySQL community version 5.7.20. I presume that "statement/sql/shutdown" and "statement/com/Shutdown" are the events that occurs while shutdown a service, using this events I tried to set triggers to load the time. But I am unable to set the triggers too.

The way I identified is through error log. Do we have any other variable or a way to identify the time of last service down.

Best Answer

There are three(3) ways using file timestamps:

InnoDB Buffer Pool

If you have innodb_buffer_pool_dump_at_shutdown configured, look for the timestamp of the file that was written. The default filename is ib_buffer_pool. Mentioned this back on Dec 06, 2017 (Control InnoDB buffer pool allocation in MySQL (5.7+) ib_buffer_pool is usually written in folder set by datadir, you can run the following after shutdown:

cd /var/lib/mysql
ls -l --time-style=+"%Y-%m-%d %H:%M:%S" ib_buffer_pool | awk '{print $6,$7}'

Binary Logging

If mysql has binary logging enabled, get the timestamp of the last binary log after shutdown.

Error Log

You are probably saying, "I DON'T WANT TO LOOK INSIDE THE ERROR LOG !!!"

You don't have to. Just get the timestamp of the error log after the shutdown is complete.

Related Solutions

Mysql – Remotely shutdown a MySQL server

You can send commands via ssh (that is you don't have to actually log in and run the command):

ssh user@host 'mysqladmin shutdown'

Mysql – Optimize queries from MySQL SP/Triggers/Functions

You have actually asked two questions:

"If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file"

Answer: it will not be logged in standard MySQL server. Do take a look at the Percona Server extension for the slow log: in particular look at the log_slow_sp_statements configuration variable. It does what you want: the slow queries within routines are logged. Note that this does not apply to triggers.
"Do we need to optimize each SP/Triggers/Functions/Events individually?"

Answer: well, of course. Each and every query needs to be as optimized as you can, depending on your requirements. A routine is not a "magic solution" that causes inner queries to run faster...