Steps Before Shutting Down MySQL Server

MySQL shutdown/restart is required when you applying non dynamic config changes, database maintenance, upgrade database etc. In this tutorial I am going to describe some basic steps before shutting down MySQL server.

Step #1: Double check the 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.

Step #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 the below command.

mysql> STOP SLAVE;

Execute below command to confirm.

mysql> SHOW SLAVE STATUS\G

Step #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:

Set the max percentage of dirty pages to zero:

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Monitor the dirty pages:

$ mysqladmin ext -i10 | grep dirty

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

Step #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!

Check the running queries:

mysql> SHOW PROCESSLIST;

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!

Step #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.

Dump the buffer pool at shutdown:

mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;

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

Check the reloading buffer pool status after the restart:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status';

I hope this article will help if you are thinking to shutdown MySQL server.

Thanks:)