When you stop or start MySQL, your concern should be on the state of data and connectivity during the process.
- If myisam_recover_options is configured, MyISAM Recovery executed
- InnoDB Storage Engine is checked for functional availability
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
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
- 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
- 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
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.