How to gracefully and safely shut down the MySQL process

Preface

This article analyzes the process of shutting down the mysqld process and how to shut down the MySQL instance safely and smoothly. Students who are not very clear about this process can refer to it.

Closing Process

1. Initiate shutdown and send SIGTERM signal

2. If necessary, create a shutdown thread

If the shutdown is initiated by the client, a dedicated shutdown thread will be created

If the SIGTERM signal is received directly to shut down, the thread responsible for signal processing will be responsible for the shutdown work, or a new independent thread will be created to do this.

When a separate shutdown thread cannot be created (for example, due to insufficient memory), MySQL Server issues a warning message similar to the following:

Error: Can't create thread to kill server

3. MySQL Server no longer responds to new connection requests

Close TCP/IP network monitoring, close Unix Socket and other channels

4. Gradually close the current connection and transaction

Idle connections will be terminated immediately;

Connections that currently have transactions and SQL activities will be marked as killed, and their status will be checked regularly so that they can be closed the next time they are checked; (refer to KILL syntax)

If there is an active transaction, the transaction will be rolled back. If non-transactional tables are modified in the transaction, the modified data cannot be rolled back, and only part of the change may be completed.

If it is the Master in the Master/Slave replication scenario, the processing of the replication thread is the same as that of the normal thread;

If it is a slave in a Master/Slave replication scenario, the IO and SQL threads will be closed in sequence. If these two threads are currently active, they will also be marked as killed and then closed.

On the slave server, the SQL thread is allowed to directly stop the current SQL operation (to avoid replication problems) and then close the thread;

In MySQL 5.0.80 and earlier versions, if the SQL thread is in the middle of executing a transaction, the transaction will be rolled back; starting from 5.0.81, it will wait for all operations to complete unless the user initiates a KILL operation.

When the SQL thread of the slave is forcibly killed while performing operations on a non-transactional table, it may cause inconsistent data between the master and slave.

5. The MySQL Server process closes all threads and all storage engines;

Refresh all table caches and close all open tables;

Each storage engine is responsible for its own shutdown operations. For example, MyISAM will flush all operations waiting to be written; InnoDB will flush the buffer pool to disk (starting from MySQL 5.0.5, if innodb_fast_shutdown is not set to 2), record the current LSN in the tablespace, and then close all internal threads.

6. MySQL Server process exits

About the KILL command

Starting from 5.0, KILL supports specifying CONNECTION | QUERY with two options:

KILL CONNECTION is the same as the original one, stopping the rollback transaction, closing the thread connection, and releasing related resources;
KILL QUERY only stops the operation currently submitted by the thread, and leaves the rest unchanged;
When a KILL operation is submitted, a special kill flag is set on the thread. It usually takes a while to actually shut down the thread, because the kill flag is only checked in certain circumstances:

1. When executing a SELECT query, in an ORDER BY or GROUP BY loop, the kill flag is checked after each block of rows is read. If it is found, the statement is terminated.

2. When executing ALTER TABLE, the kill flag is checked after reading a few row record blocks from the original table. If it is found, the statement will terminate and the temporary table will be deleted;

3. When executing UPDATE and DELETE, the kill flag is checked after reading some row record blocks and updating or deleting. If it is found to exist, the statement will be terminated and the transaction will be rolled back. If the operation is on a non-transactional table, the changed data will not be rolled back.

4. GET_LOCK() function returns NULL;

5. The INSERT DELAY thread will quickly add new records to the memory and then terminate;

6. If the current thread holds a table-level lock, it will be released and terminated;

7. If the thread's write operation call is waiting for the disk space to be released, it will directly throw a "disk space full" error and then terminate;

8. If a MyISAM table is killed during REPAIR TABLE or OPTIMIZE TABLE, the table will become damaged and unusable. This guide will guide you to repair the table again.

Some suggestions for safely shutting down MySQL

To safely shut down the mysqld service process, it is recommended to follow the steps below:

0. Use an account with the highest permissions such as SUPER, ALL, etc. to connect to MySQL. It is best to connect using unix socket;

1. In versions 5.0 and above, set innodb_fast_shutdown = 1 to allow InnoDB to be shut down quickly (without full purge or insert buffer merge). Do not set this if you are upgrading or downgrading the MySQL version.

2. Set innodb_max_dirty_pages_pct = 0 to let InnoDB flush all dirty pages to disk;

3. Set max_connections and max_user_connections to 1, which means that no new connections are allowed to be created except the current connection.

4. Close all inactive threads, that is, thread IDs whose status is Sleep and whose Time is greater than 1;
5. Execute SHOW PROCESSLIST to confirm whether there are any active threads, especially threads that may generate table locks, such as SELECT with large data sets, UPDATE with a large range, or DDL execution. Be especially cautious;

6. Execute SHOW ENGINE INNODB STATUS to confirm that the value of History list length is low (usually less than 500), which means that there are few unpurged transactions. Also, confirm that the values ​​of Log sequence number, Log flushed up to, and Last checkpoint at are the same, which means that all LSNs have been checkpointed.

7. Then execute the FLUSH LOCKAL TABLES operation to refresh all table caches and close the opened tables (the function of LOCAL is to prevent the operation from being recorded in BINLOG);

8. If it is a SLAVE server, it is best to close IO_THREAD first, wait until all RELAY LOGs are applied, and then close SQL_THREAD to avoid SQL_THREAD being terminated when executing a large transaction. Wait patiently for all of them to be applied. If you have to force it to close, it is best to wait until the large transaction is completed before closing SQL_THREAD.

9. Finally, execute mysqladmin shutdown.

10. In an emergency, you can set innodb_fast_shutdown = 1, and then directly execute mysqladmin shutdown, or even directly call kill or kill -9 at the operating system layer to kill the mysqld process (some transactions may be lost when innodb_flush_log_at_trx_commit = 0). However, when the mysqld process is started again, CRASH RECOVERY work will be performed, so you need to make a trade-off.

After all the talk, in normal circumstances, it is enough to execute mysqladmin shutdown. If blocking occurs, refer to the above content for analysis and resolution, haha:)

The above is the details of how to close the MySQL process elegantly and safely. For more information about closing the MySQL process, please pay attention to other related articles on 123WORDPRESS.COM!