Safely shut down MySQL

When closing mysql server, various problems may occur due to the closing method. The following steps can reduce the occurrence of problems.


1. Stop copying

In some special environments, the slave node may try to start from the wrong position. In order to reduce this risk, stop io thread first, so as not to receive new event information.

mysql> stop slave io_thread;

After the sql thread has applied all the events, stop the sql thread.

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

In this way, io thread and sql thread can be in the consistent position, so that the relay log only contains the executed events and relays_ log_ info_ The location information in the repository is also up-to-date.


For a slave with multi-threaded replication enabled, make sure that gaps are populated before the replication is turned off

mysql> stop slave;
mysql> start slave until sql_after_mts_gaps; #After applying gap in relay log
mysql> show slave status\G #Make sure you have stopped SQL before_ thread
mysql> stop slave ;


2. Commit, rollback or kill long-running transactions

Many things can happen in one minute. When shutting down, innodb must roll back uncommitted transactions. Transaction rollback is very expensive and can take a long time. Any transaction rollback can mean data loss, so ideally no transaction is open when it is closed.

If a read-write database is closed, write operations should be routed to other nodes in advance. If you have to close the database that is still receiving transactions, the following query will output session information that runs for more than 60 seconds. Based on this information, decide 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 = 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 close. We can help mysql manually.

Use Pt kill to view and kill active and sleeping connections. At this time, there should be no new write connection. We just deal with read connections.

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

Here, connections established by some users can be selectively excluded.


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';

disable innodb_fast_shutdown may make the shutdown process take several minutes or even hours, because you need to wait for the purge of undo log and the merge of changebuffer.

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 it is not always guaranteed if there is activity in mysql. Then, if the result of the detection is no longer smaller, you can continue to the next step:


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


5. Dump the contents of 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)

When starting, to load the dumped contents, check the parameter InnoDB_ buffer_ pool_ load_ at_ Configuration of startup.


6. Log


Now, you can close mysql.


Most of the time, we just execute the stop command. It is normal for MySQL to shut down and restart. Occasionally, I will encounter some problems.


Tags: MySQL

Posted by richcrack on Sat, 07 May 2022 01:14:22 +0300