Getting MySQL start and shutdown time

Question

How to write a trigger that updates a table before startup and shutdown of the database in MySQL 5.7 ?

Any suggestions that could be helpful to us?

Answer 1

STARTUP TIME

For everyone running MySQL 5.7

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';

For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
WHERE variable_name='Uptime';

Just mentioned this three(3) days ago (Make MySQL print a timestamp at each time it starts)

SHUTDOWN TIME

There are four ways to acquire a shutdown time

METHOD #1 : Search the Error Log

If the error log is called error.log, run this

grep "Shutdown complete$" error.log | tail -1 | awk '{print $1,$2}'

This will produce the date (YYMMDD) and time (HH:MM:SS)

METHOD #2 : 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}'

METHOD #3 : Binary Logging

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

METHOD #4 : 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.

CAVEAT ON SHUTDOWN

Method #1 is not any good to you if mysqld crashes.

The other three(3) methods will simply tell you the last time mysqld did any kind of writes, whether it was a clean shutdown or a crash.

YOUR ACTUAL QUESTION

First off, a trigger is not needed nor would it fire off on shutdown.

You cannot write a shutdown time to a table when mysqld is shutting down.

The best I could suggest is to capture the shutdown datetime with one of the four(4) aforementioned methods and write it in a script. You can execute that script as follows:

Create a file called /var/lib/mysql/startup_stuff.sql

Run chown mysql:mysql /var/lib/mysql/startup_stuff.sql

Write inside that script in such a way that the datetime is written to a table

Add this line to /etc/my.cnf (see MySQL Documentation on init-file)

init-file=`/var/lib/mysql/startup_stuff.sql

Startup mysql using service mysqld start

Answer 2

"Before" startup -- Well, TRIGGERs clearly cannot run before MySQL is running.

Before shutdown -- Well, if the server crashes (due to bug or power failure), there is no way for any code to be activated.

At any point, you can get the "startup" time by taking NOW() and subtracting the number of seconds from startup: SHOW STATUS LIKE 'Uptime';.

mysql> SELECT NOW() - INTERVAL variable_value SECOND
                FROM information_schema.GLOBAL_STATUS
                WHERE variable_name = 'Uptime';
+----------------------------------------+
| NOW() - INTERVAL variable_value SECOND |
+----------------------------------------+
| 2018-03-21 05:36:50.000000             |
+----------------------------------------+
1 row in set (0.00 sec)

(Your title and your description ask different questions!)