In Percona’s Managed Services, we start up and shut down MySQL servers all the time. Over the years, we’ve seen various issues occur due to the way servers are shut down. No matter if it is a situation where unexpected errors appear in the log or you’re stuck waiting for hours for a server to finishing shut down… Here are some steps we can take to minimize any trouble and prepare MySQL for shutdown.
This list of “super safe practices” was born from lessons we’ve learned in a wide variety of MySQL environments.
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.
1 | STOP SLAVE IO_THREAD; |
Wait for the SQL thread to apply all events, then stop it too.
1 2 | SHOW SLAVE STATUSG 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.
For multi-threaded slaves, make sure to fill in the gaps when stopping replication.
1 2 3 4 | STOP SLAVE; START SLAVE UNTIL SQL_AFTER_MTS_GAPS; SHOW SLAVE STATUSG # 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. Any rolled back transaction may mean data loss, so ideally there are none open at shutdown.
If shutting down a read/write server, the write traffic should be pointed to another server beforehand (ex: from a failover). Or perhaps the server is a BI (Business Intelligence) slave replicating from a master but receiving writes to BI databases. In this case, coordinate a shutdown time with your internal teams to stop those writes.
If you must continue to shutdown a server receiving transactions, the below query will report transactions running > 60 seconds and return some metadata. Use these details to research further and decide how to proceed (commit, rollback, kill).
1 2 3 4 5 6 7 8 | 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!
Use pt-kill to review or kill both active and sleeping connections. Again, at this point, there should be no incoming write traffic. We are now handling read only connections.
This command will match all sleeping and active connections, but ignore connections from the percona and orchestrator user.
1 | pt-kill --host="localhost" --victims="all" --interval=10 --ignore-user="percona|orchestrator" --busy-time=1 --idle-time=1 --print [--kill] |
4. Configure InnoDB for Max Flushing.
You’ll often see a similar recommendation when shutting down MySQL for an upgrade.
1 2 3 | SET GLOBAL innodb_fast_shutdown=0; SET GLOBAL innodb_max_dirty_pages_pct=0; SET GLOBAL innodb_change_buffering='none'; |
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.
To help speed up the shutdown time, set innodb_max_dirty_pages_pct to 0 and monitor the count with the below query. 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.
1 | SHOW GLOBAL STATUS LIKE '%dirty%'; |
Also, we have stopped the innodb_change_buffering. If using PMM, you can review the size and activity with the “InnoDB Change Buffer” graphs.
5. Dump the Buffer Pool.
Most likely this server 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.
1 2 | 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.
1 2 3 4 5 6 7 | 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.
We’ll flush logs for good measure.
1 | FLUSH LOGS; |
MySQL is now prepared for 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 help ensure a clean shutdown.
Don’t wait for that “one time” to happen. Create a procedure now and be patient with the extra steps!
Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.
Along with that, how about setting the Master to READ ONLY if you want to stop a Master
SET GLOBAL READ_ONLY = ON;
and wait for couple of minutes until you can confirm that all slaves caught up.
should globals be reset to their old values after restart?