How to properly shutdown MySQL before any maintenance activity

How to properly shutdown MySQL before any maintenance activity

We might have different scenarios once we need to stop MySQL service before performing either server or database activity like patching/upgrade.  

Before performing such activity, we need to make sure that we stop MySQL service properly to avoid any unforeseen crashes once our maintenance activity complete and MySQL service is started. 

Here I would like to share the below steps which should be performed and take care to properly stop MySQL service. 

Step 1:

Ensure we don't have any long-running queries. Manually verify by checking the processlist.

show processlist; 

Step 2:

If it is the master node, then ensure to stop application write hits on the database by doing either stop an app and put in maintenance OR if we have any failover solution then move write hits on another node. 

Step 3:

If it is a slave node, then STOP replication and note down the stopped replication co-ordinates. 

stop slave;

show slave status\G

Put skip_slave_start parameter inside my.cnf to avoid auto-start replication once MySQL service will be up. 

vim /etc/my.cnf

*

*

[mysqld]

skip-slave-start

Step 4:

Set innodb_fast_shutdown and innodb_max_dirty_pages_pct parameters global value to 0 dynamically.

mysql> set global innodb_fast_shutdown=0;

Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_max_dirty_pages_pct=0;

Query OK, 0 rows affected (0.00 sec) 

Step 5:

Observe below 2 status parameters and make sure, we don't have any dirty pages left to flush. Wait until below parameters reach to Zero(0).

mysql> show global status like '%dirty%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| Innodb_buffer_pool_pages_dirty | 0     |

| Innodb_buffer_pool_bytes_dirty | 0     |

+--------------------------------+-------+

2 rows in set (0.01 sec) 

Step 6:

Now we are ready to stop MySQL service safely.

systemctl stop mysqld

Step 7:

This step needs to run when we need to reboot the server. So before restarting the server, we need to make sure to disable the MySQL service from startup, so MySQL service won't start automatically once the server is coming UP. 

systemctl disable mysqld

Step 8:

MAINTENANCE

Step 9:

Once we are done with maintenance activity, Now we can enable MySQL startup service, start MySQL service and replication(if it is a slave node). 

systemctl enable mysqld

systemctl start mysqld

mysql > start slave;

Photo by Nicolas Cool on Unsplash