[MySQLFAQ] series-how to safely disable MySQL instances

This article analyzes the mysqld process and how to safely and easily shut down MySQL instances. If you are not clear about this process, refer to it. Shutdown: 1. initiate shutdown and send a SIGTERM signal. 2. If necessary, create a shutdownthread. If the shutdownthread is disabled by the client, a dedicated

This article analyzes the mysqld process and how to safely and easily shut down MySQL instances. If you are not clear about this process, refer to it. Shutdown: 1. initiate shutdown and send a SIGTERM signal. 2. If necessary, create a shutdown thread. If the shutdown thread is disabled by the client, a dedicated thread is created.

This article analyzes the mysqld process and how to safely and easily shut down MySQL instances. If you are not clear about this process, refer to it.

Closing Process:

1. initiate shutdown and send a SIGTERM Signal

2. Create a shutdown thread if necessary)

If the thread is disabled by the client, a dedicated thread is created.

If the SIGTERM signal is directly received for shutdown, the thread responsible for signal processing will be responsible for shutdown, or a new independent thread will be responsible for this.

When an independent thread is not created (for example, the memory is insufficient), MySQL Server sends an alarm similar to the following:

Error: Can't create thread to kill server

3. MySQL Server no longer responds to new connection requests

Disable TCP/IP network listening and Unix Socket

4. Close the current connection and transaction gradually

Idle connection will be terminated immediately;

There are still connections for transactions and SQL activities. They are identified as killed and their status is checked regularly so that they can be closed during the next check. (Refer to KILL syntax)

If an active transaction exists, the transaction will be rolled back. If a non-transaction table is modified in the transaction, the modified data cannot be rolled back, and only some changes may be completed;

If it is a Master in the Master/Slave replication scenario, the process of the replication thread is the same as that of the common thread;

For the Master/Slave replication scenario, the I/O and SQL threads are closed sequentially. If the two threads are active, the killed ID is also added, then close it;

On the Slave server, the SQL thread allows you to stop the current SQL operation directly (to avoid replication issues) and then shut down the thread;

In MySQl 5.0.80 and earlier versions, if the SQL thread executes a transaction to the center at that time, the transaction will be rolled back; Starting from 5.0.81, it will wait until all the operations are completed, unless the user initiates the KILL operation.

When the Slave SQL thread executes operations on non-transaction tables, it is forced to KILL, which may cause data inconsistency between the Master and Slave;

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

Refresh the cache of all tables and disable all opened tables;

Each storage engine is responsible for related shutdown operations. For example, MyISAM will refresh all the operations waiting for write; InnoDB will refresh the buffer pool to the disk (starting from MySQL 5.0.5, if innodb_fast_shutdown is not set to 2), record the current lsn to the tablespace, and then close all internal threads.

6. MySQL Server process exited

About KILL commands

From 5.0, KILL supports specifying CONNECTION and QUERY options:

Like the original kill connection, KILL the transaction rollback, close the thread CONNECTION, and release related resources;

Kill query only stops the operations currently submitted by the thread, and the other operations remain unchanged;

After the KILL operation is submitted, a special kill flag is set on this thread. It usually takes some time to actually close the thread, because the kill flag is only checked under specific circumstances:

Suggestions for safely disabling MySQL

To safely disable the mysqld service process, follow these steps:

If there are so many things, it is enough to execute mysqladmin shutdown normally. If blocking occurs, refer to the above content for analysis and solution. haha :)