MySQL: Clean shutdown

You want to shutdown MySQL as cleanly as possible, perhaps because you want to create backups or want to do an upgrade.

Assumptions

• You are running MySQL 5.5 or later

Procedure

If possible, stop all incoming connections (for example, from application servers).

On a slave or replication server, stop the I/O thread (which grabs updates from the master) and wait for the SQL thread to process all:

# Use a sufficiently capable MySQL user; root will usually do. 
STOP SLAVE IO_THREAD;
SHOW SLAVE STATUS \G
# Grab the columns MASTER_LOG_FILE and MASTER_LOG_POS to use in the next statement
SELECT MASTER_POS_WAIT('master_log_file',master_log_pos);

On a stand-alone, slave, replication or master server, lock all tables:

SET GLOBAL innodb_fast_shutdown = 0;
FLUSH LOCAL TABLES;
FLUSH TABLES WITH READ LOCK;
# Keep this session open

In a different window, kill the MySQL server as usual, by example by a service mysql stop, mysql_admin shutdown or sending a TERM signal to the mysqld process.