Follow best practices before shutting down MySQL instance.

In this post, you will learn what pre-checks are required before shutting MySQL down whether for a maintenance, applying non dynamic config changes, MySQL upgrade or other many reasons. Shutting down MySQL may not be as simple as just service mysql stop!

1. Double check exact instance you are going to shutdown!!

First of all, and before doing anything confirm first the instance you are going to shutdown. You definitely, don’t want to shutdown a wrong MySQL instance by mistake, especially, when you’re working on production environments.

2. Stop Replication.

Although MySQL stops the replication automatically in the shutting down process but if it didn’t stop for any reason before the timeout is reached, it will be killed. So, if that server is a slave, it’s better to stop the replication threads first before shutting down MySQL.

To stop the replication you can simply execute
mysql> STOP SLAVE; 
followed by
mysql> SHOW SLAVE STATUS\G;
to confirm it has been stopped.

Note: If the slave was too far behind the master or if you are using long time for a delayed slave, you may better wait until the slave apply all copied relay logs so far to avoid having the master pushing them again to the slave once you restart the replication after the mysql restarted which will add overhead on the master.

mysql> SHOW SLAVE STATUS\G;
will show you the Seconds_Behind_Master and
mysql> STOP SLAVE IO_THREAD;
will allow the SQL_THREAD to continue applying the copied files so far (or you may set the MASTER_DELAY = 0 in case of a delayed slave).

Once all relay logs have been replayed, you are free to stop the SQL_THREAD too and MySQL service after that. (Thanks Eric for the reminder!)

3. Flush the dirty pages.

MySQL must flush the dirty pages (pages were modified in memory but not yet flushed to disk) in the clean shutdown process, otherwise, an automatic crash recovery will take place when starting it.

You can flush the dirty pages in advance before shutting down MySQL to make the process faster by doing the following procedure:

a) Set the max percentage of dirty pages to zero:
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

b)Monitor the dirty pages:
shell$ mysqladmin ext -i10 | grep dirty

Wait until the number of dirty pages gets close to zero before you shutdown MySQL.

4. Check the long running transactions.

Long running transactions may take long time rolling back when shutting down MySQL and you may think that MySQL is broken and kill -9 it or you have to accept the long shutdown time which means your system will be down longer!

a) To check the running queries:
mysql> SHOW PROCESSLIST;

b) Kill the long running queries – if it is OK to interrupt them – (or wait until they finish):
mysql> kill thread_id;
Even if killing a query took long time, your system will still be up!

Note: if you found many running transactions, maybe the application is not aware of that maintenance or it may be a sign that you are going to shutdown a wrong instance!

5. Dump and reload the buffer pool.

You may want to avoid having a cold buffer pool after the restart. To do so, you need to dump the buffer pool before shutting down MySQL and reload it again after starting MySQL.

a) Dump the buffer pool at shutdown:
mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;

b) Reload the buffer pool after restart (this is a read only variable which needs to be added to the my.cnf:
# vi /etc/my.cnf
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON # to avoid setting it before every restart

c) To check the reloading buffer pool status after the restart:
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status';