Prepare MySQL for a Safe Shutdown

We’ve all started up MySQL only to receive some scary, unexpected error. Or maybe we’ve waited hours for it to finish shutting down while it just hangs on some task.

Whether going down for maintenance or upgrades, there are steps we can take to safely prepare MySQL in the hopes of clean error logs.

Note: The following list assumes that an async slave is being shut down. 

1. Stop Replication.

Under some (rare) circumstances, a slave may try to startup in the incorrect position. To help minimize this risk, stop the IO thread first so it’s not receiving new events.

STOP SLAVE IO_THREAD;

Wait for the SQL thread to apply all events, then stop it too.

SHOW SLAVE STATUS\G
STOP SLAVE SQL_THREAD;

This puts both of the replication threads in a consistent position so that the relay log only contains executed events and the relay_log_info_repository positions are up-to-date. We’ll flush the logs soon for good measure.

For multi-threaded slaves, make sure to fill in the gaps when stopping replication.

STOP SLAVE;
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
SHOW SLAVE STATUS\G # Make sure the SQL_thread has stopped before proceeding.
STOP SLAVE;

2. Commit, Rollback, or Kill Long-Running Transactions.

A lot can happen in 1 minute and InnoDB must rollback uncommitted transactions during a shutdown. This is expensive and may take a long time. Open transactions should be dealt with while MySQL is still up.

The below query will report transactions running > 60 seconds and return some metadata. Use these details to research further and decide how to proceed.

mysql> SELECT trx_id, trx_started, (NOW() - trx_started) trx_duration_seconds, id processlist_id, user, IF(LEFT(HOST, (LOCATE(':', host) - 1)) = '', host, LEFT(HOST, (LOCATE(':', host) - 1))) host, command, time, REPLACE(SUBSTRING(info,1,25),'\n','') info_25 FROM information_schema.innodb_trx JOIN information_schema.processlist ON innodb_trx.trx_mysql_thread_id = processlist.id WHERE (NOW() - trx_started) > 60 ORDER BY trx_started;
+--------+---------------------+----------------------+----------------+------+-----------+---------+------+---------------------------+
| trx_id | trx_started         | trx_duration_seconds | processlist_id | user | host      | command | time | info_25                   |
+--------+---------------------+----------------------+----------------+------+-----------+---------+------+---------------------------+
| 511239 | 2020-04-22 16:52:23 |                 2754 |           3515 | dba  | localhost | Sleep   | 1101 | NULL                      |
| 511240 | 2020-04-22 16:53:44 |                   74 |           3553 | root | localhost | Query   |   38 | update t1 set name="test" |
+--------+---------------------+----------------------+----------------+------+-----------+---------+------+---------------------------+
2 rows in set (0.00 sec)

Note: The query will also report XA PREPARED transactions which likely should not be manually committed, rolled back, or killed.

3. Clean up the Processlist.

MySQL is going to shut down and terminate all connections. So in the spirit of teamwork, we can lend a hand!

Usept-kill to check and kill active and sleeping connections. In my example, I’m also ignoring the percona and orchestrator user.

pt-kill --host="localhost" --victims="all" --interval=10 --ignore-user="percona|orchestrator" --busy-time=1 --idle-time=1 --print [--kill]

This will take care of any sneaky, long sleeping connections and active queries using up resources on the server.

4. Configure InnoDB for Max Flushing.

You’ll often see a similar recommendation when shutting down MySQL for an upgrade. Ideally, it’s performed each time to safely prepare for shutdown.

SET GLOBAL innodb_fast_shutdown=0;
SET GLOBAL innodb_max_dirty_pages_pct=0;
SET GLOBAL innodb_change_buffering='none';

Now monitor the dirty pages count. The desired result is 0 but it’s not always possible if there is still activity on MySQL. If it seems like there is no more gain (the count is not continually getting any lower), call it good and move onto the next step.

SHOW GLOBAL STATUS LIKE '%dirty%';

Reducing innodb_max_dirty_pages_pct will add time to the preparation steps as you wait for the buffer pool dirty page count to be as close to 0 as possible. However, this decreases the shutdown time since the pages are already flushed.

Disabling innodb_fast_shutdown can add minutes or hours to the actual shutdown as you wait for a full undo log purge and change buffer merge, so we have also stopped the  innodb_change_buffering . If usingPMM, you can review the size and activity with the “InnoDB Change Buffer” graphs.

5. Dump the Buffer Pool.

Most likely this host is going to be re-introduced to the cluster. Dumping and loading the buffer pool contents will greatly reduce the warm up time after startup.

Dump the buffer pool now, while MySQL is still running.

SET GLOBAL innodb_buffer_pool_dump_pct=75;
SET GLOBAL innodb_buffer_pool_dump_now=ON;

Monitor the status and wait for it to complete.

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 200429 14:04:47 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

To make sure the buffer pool will get loaded, check that innodb_buffer_pool_load_at_startup is not being disabled in the CNF (this is a global read only variable that’s ON by default).

6. Flush the Logs.

As mentioned in Step 1, we’ll flush logs for good measure.

FLUSH LOGS;

MySQL is now prepared for a safe shutdown!

Summary

Most of the time, DBAs just issue the stop command and MySQL goes down and comes back up just fine (this is true). However, the one time it does not go well…what happens? New procedures (similar to these steps) are put into place to ensure a safe shutdown.

Don’t wait for that “one time” to happen. Create a safe procedure now and be patient with the extra steps!