How to safely shut down a MySQL instance

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 at the next check; (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 ordinary 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, data inconsistency between the master and slave may occur.

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 two options: CONNECTION | QUERY:

KILL CONNECTION is the same as the original one, which stops rolling back the transaction, closes the thread connection, and releases related resources;
KILL QUERY only stops the operation currently submitted by the thread, and leaves the rest unchanged;
After submitting the KILL operation, a special kill flag will be set on the thread. It usually takes some time 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, 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 the MyISAM table is KILLed when executing REPAIR TABLE or OPTIMIZE TABLE, the table will be damaged and unusable. You can follow the instructions to repair it 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 BINLOG from recording this operation);

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 are the details on how to safely shut down a MySQL instance. For more information on shutting down a MySQL instance, please pay attention to other related articles on 123WORDPRESS.COM!