Pre-CheckList before any maintenance activity in MySQL

In this article we will learn and see how to shutdown MySQL services before performing any maintenance activity either it is server or database activity like upgrade/patching etc. Before performing such activity, we need to make sure that we must stop MySQL services properly witch double check to avoid any unforeseen crashes once our maintenance activity complete and MySQL service is started.

Below are the steps which should be performed and take care to properly shutdown MySQL service.

Step 1:

Ensure we don't have any long-running queries. To verify manually check 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:

Complete your MAINTENANCE (Patches/Upgrades etc...)

Step 9:

Once our maintenance activity is over, Now we are good to enable MySQL startup service, start MySQL service and replication (if it is a slave node).

systemctl enable mysqld
systemctl start mysqld
mysql > start slave;

That's all in this article. Please like and share if it was helpful for you.