MySQL Start/Stop

Question

Could anyone explain to me what happens during MySQL startup and shutdown? And i would like to know about the safe startup/shutdown process.

Answer 1

When you stop or start MySQL, your concern should be on the state of data and connectivity during the process.

PROCESSES (Shutdown)

• 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

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.