How to safely shut down MySQL

When shutting down the MySQL server, various problems may occur depending on the way it is shut down. The following steps can help reduce the occurrence of problems.

1. Stop copying

Under some special circumstances, the slave node may try to start from the wrong position. To reduce this risk, stop the io thread first so that no new event information is received.

mysql> stop slave io_thread;

After the sql thread has applied all events, the sql thread is also stopped.

mysql> show slave status\G
mysql> stop slave sql_thread;

In this way, the io thread and sql thread can be in a consistent position, the relay log only contains executed events, and the position information in the relay_log_info_repository is also the latest.

For slaves with multithreaded replication enabled, make sure that the gaps are filled before shutting down replication.

mysql> stop slave;
mysql> start slave until sql_after_mts_gaps; #Apply the gap in the relay log
mysql> show slave status\G #Make sure that sql_thread has been stopped before
mysql> stop slave;

2. Commit, rollback, or kill long-running transactions

A lot can happen in 1 minute, and on shutdown, InnoDB must roll back uncommitted transactions. Transaction rollbacks are very expensive and can take a long time. Any transaction rollback could mean data loss, so ideally there are no transactions open when closing.

If the read-write database is closed, write operations should be routed to other nodes in advance. If you must shut down a database that is still receiving transactions, the following query will output information about sessions that have run for more than 60 seconds. Based on this information, decide on the next step:

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)

3. Clear processlist

mysql is about to disconnect and shut down. We can manually help mysql.

Use pt-kill to view and kill active and sleeping connections. There should be no new write connections coming in at this time. We only handle read connections.

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

Here you can selectively exclude connections established by certain users.

4. Configure innodb to complete the maximum flush

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

Disabling innodb_fast_shutdown may cause the shutdown process to take several minutes or even hours because it is necessary to wait for the undo log to be purged and the changebuffer to be merged.

To speed up shutdown, set innodb_max_dirty_pages_pct=0 and monitor the results of the following query. The expected value is 0, but this is not always guaranteed if there is activity in MySQL. Then, if the result does not continue to get smaller, you can proceed to the next step:

SHOW GLOBAL STATUS LIKE '%dirty%';

If you use pmm monitoring, you can view the diagram of "innodb change buffer".

5. Dump the contents of the buffer pool

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

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 load the dumped content at startup, check the configuration of the parameter innodb_buffer_pool_load_at_startup.

6. Brush log

FLUSH LOGS;

Now, you can shut down mysql.

Most of the time, we just execute the stop command, and it is normal for MySQL to shut down and restart. Occasionally there are problems.

The above are the details on how to safely shut down MySQL. For more information on safely shutting down MySQL, please pay attention to other related articles on 123WORDPRESS.COM!