Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
Hello and welcome to DBA.SE. I understand you're looking for generic information on the pid files and lock files, but your title seems to imply you're facing a specific error. If that's the case, could you please also post the error message you're getting so people can assist you in that context? (You can edit your post to add that information.) –  Mat Nov 28 '12 at 7:52
    
Is this for Linux or Windows ??? –  RolandoMySQLDBA Dec 5 '12 at 19:42
    
Its Linux @RolandoMySQLDBA I'll really keep reading your posts I find 'em informative n helpfull. Thanks for that. –  Vinay Dec 17 '12 at 16:37
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.