Could anyone explain to me what happens during MySQL startup and shutdown? And i would like to know about the safe startup/shutdown process.
When you stop or start MySQL, your concern should be on the state of data and connectivity during the process.
- No new DB Connections are allowed
- Active Connections are terminated
- All temporary tables are dropped
- If binary logging is enabled, wrap-up info is written in the most recent binary log. CAVEAT : Since most people do not use
sync-binlog, mysqld totally relies on the OS for flushing final binary log entries.
- mysqld hunts down the PID file for the MySQL Instance, get the Linux process ID. This verifies things in terms of shutting down a local instance. In some cases, mysqld tends to lose touch with the PID file. When this happens, mysqld must be shutdown using mysqladmin via TCP/IP
- If myisam_recover_options is configured, MyISAM Recovery executed
- InnoDB Storage Engine is checked for functional availability
- Connections allowed after all these preparatory processes are done
If MySQL Replication is up and running at the time of the shutdown, note the following Protocols:
MASTERProtocol : mysqld is only concerned with
- killing DB Connections, including the IO thread of Remote Slaves
- closing up the current binary log
SLAVEProtocol : mysqld will run
- Updates the
master.infofile as to which SQL command from the Master was last downloaded, recorded in its relay logs and executed.
- Closes the most recent relay log.
- Updates the
MASTER/SLAVEProtocol : 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
- 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.infoand 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
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
.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.
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.