Think of it this way:
- the database is the set of physical files, on disk. It's a completely passive "thing". It doesn't "do" anything on its own - it's just data.
- the instance is the software/processes (with its memory) that manages the database. It's the active part. It's what clients connect to, it's what processes SQL, reads data, updates it, maintains the transaction logs, etc.
When you start with nomount
, you start the instance (start a few processes, create the memory areas), but don't actually go look at the database. You'll have something in memory ready to mount a database, or do the few maintenance things you can do in that state (like creating a database), but that's all. The database is completely inaccessible at that point.
When you mount
, the control file (which contains a description of the database's files) is read, but the database is still not open. I.e. you can't access the data itself.
Only you finally open
the database can clients start interacting with the database.
Things get a bit more complicated with RAC, but the principle is the same: the database is only the set of files. The instances are what allow you to interact with the database.
Shutdown progresses in the opposite direction. For a normal, non-RAC shutdown, the instance flushes all the in-memory data that's not yet saved in the database, and closes the database files. Clients can no longer access the data.
Then the instance dismounts the database, closing the control files. At this point you're left with pretty much the same thing as with an instance in nomount
- a set of processes and memory that can't really do much.
The final step is the actual shutdown: the memory areas are released back to the OS and the processes die, which is the end of that instance.
The database doesn't ever do anything. The instances are the only active parts of the system.
For a more in-depth view, see Oracle Database Instance. It has an overview of the startup and shutdown sequence, and other essential information.
When you stop or start MySQL, your concern should be on the state of data and connectivity during the process.
PROCESSES (Shutdown)
PROCESSES (Startup)
- If myisam_recover_options is configured, MyISAM Recovery executed
- InnoDB Storage Engine is checked for functional availability
- If
skip-innodb
enabled, all InnoDB startup is bypassed
- If innodb log files are missing they are created
- If innodb log files are not the same size as innodb_log_file_size expects, mysqld dies
- If innodb_fast_shutdown is enabled, InnoDB crash recovery is executed
- Connections allowed after all these preparatory processes are done
REPLICATION
If MySQL Replication is up and running at the time of the shutdown, note the following Protocols:
MASTER
Protocol : mysqld is only concerned with
- killing DB Connections, including the IO thread of Remote Slaves
- closing up the current binary log
SLAVE
Protocol : mysqld will run STOP SLAVE;
- Updates the
master.info
file as to which SQL command from the Master was last downloaded, recorded in its relay logs and executed.
- Closes the most recent relay log.
MASTER/SLAVE
Protocol : If a MySQL Instance is both Master and Slave, individual Master and Slave protocols both apply
On startup, if MySQL Replication is configured on a Slave,
- Check for
master.info
- Make sure server_id on Master is explicitly defined
- Make sure server_id on Slave explicitly defined
- Make sure server_id of Slave and Master are different
- Connect IO Thread to Master using
master.info
and last relay log
- If connection is dead IO wise, retry in 60 seconds
- If connection fails authentication, IO thread remains dead, mysqld continues
- Message on success/failure is posted in mysqld's error log
- Connect SQL Thread to Slave using
master.info
DATA (MyISAM)
Since MyISAM tables are not transactional and the only caching done is for index lookups, the MyISAM key cache is simply discarded. All open file handles to the .frm
, .MYD
, and .MYI
are closed and open file handle count for any MyISAM is is decremented. If mysqld crashes, the open file handle count for any open MyISAM table are the time of a crash that is greater than 0 causes mysqld to view the MyISAM table as crashed. Running REPAIR TABLE
on that MyISAM may be necessary on startup (Must be done manually or you can have mysqld configured to do that automatically)
On startup, if MyISAM recovery option is enabled, then REPAIR TABLE
is executed for any closed MyISAM table that has a nonzero value for open file handles.
DATA (InnoDB)
Since InnoDB is a Transactional Storage Engine, there are steps to doing shutdown.
If innodb_fast_shutdown is enabled, any lingering transactions within the InnoDB infrastructure (double write buffer, InnoDB Log Files) are retained in the files on shutdown. The changes are acted upon during the next startup of mysqld.
If innodb_fast_shutdown is disabled, any lingering transactions within the InnoDB infrastructure (double write buffer, InnoDB Log Files) are completed on shutdown. This makes for a faster mysqld startup.
Best Answer
STARTUP TIME
For everyone running MySQL 5.7
For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode
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 thisThis 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 onDec 06, 2017
(Control InnoDB buffer pool allocation in MySQL (5.7+)ib_buffer_pool
is usually written in folder set bydatadir
, you can run the following after shutdown: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)Startup mysql using
service mysqld start