MySQL shutdown process to understand and safely close MySQL method _mysql

This article analyzes the process of mysqld process shutdown, and how to safely and gently close the MySQL instance, the process is not clear to the students can refer to.

Shutdown process:

1, initiate shutdown, send out sigterm signal

2, if necessary, create a new shutdown thread (shutdown thread)

If it is a client-initiated shutdown, a new private shutdown thread is created

If you receive a sigterm signal to shut down directly, the thread responsible for the signal processing will be responsible for shutting down the work, or creating a new independent thread responsible for this.

When a separate shutdown thread cannot be created (for example, out of memory), the MySQL server emits an alert message similar to the following:

Error:can ' t create thread to kill server

3. MySQL server no longer responds to new connection requests

Turn off TCP/IP network monitoring and shut down channels such as UNIX sockets

4, gradually close the current connection, transaction

Idle connections, which will be terminated immediately;

There are currently transactions, SQL active connections that identify them as killed and periodically check their status so that they are closed at the next inspection; (refer to KILL syntax)

If there is a current active transaction, the thing is rolled back, and if the transaction table is also modified, the data that has been modified cannot be rolled back, and only a partial change may be completed;

If it is the master in the Master/slave replication scenario, the process of copying the thread is the same as for the normal thread;

If it is the slave in the Master/slave replication scene, the IO, SQL thread is turned off, and if the 2 threads are active at the moment, the killed identity is added and then closed;

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

In MySQL 5.0.80 and earlier versions, if the SQL thread was to execute a transaction to the middle, the transaction would be rolled back; starting with 5.0.81, it would wait for all operations to end unless the user initiated the kill operation.

When a slave SQL thread is forced to kill when performing an operation on a non-transaction table, it may cause master, slave data to be inconsistent;

5. The MySQL server process shuts down all threads, shutting down all storage engines;

Refreshes all table cache, closes all open tables;

Each storage engine is responsible for related shutdown operations, for example, MyISAM flushes all pending writes; InnoDB flushes the buffer pool to disk (starting with 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 Kill instructions

Starting from 5.0, KILL supports specified CONNECTION | Query two options:

@KILL connection the same as the original, stop rolling back the transaction, close the thread connection, release the related resources;

@KILL query only stops the action that the thread is currently committing to do, and the other remains unchanged;

After the kill operation is committed, a special kill Mark bit is set on the thread. It usually takes a while to actually close the thread, because the kill tag bit is checked only in a specific case:

1. When the select query is executed, the kill mark bit is checked after each reading of some row block, and the statement terminates if it is found, in the order by or group by loop;

2. When executing ALTER TABLE, the kill mark bit is checked every time a block of rows is read from the original table, and if found, the statement terminates and the temporary table is deleted;

3. When the update and delete are executed, the kill mark bit is checked every time a row is read and updated or deleted, and if it is found, the statement terminates, rolls back the transaction, and the data that has changed is not rolled back if the operation is on a non-transactional table;

4, Get_lock () function returns null;

5, INSERT delay thread will quickly in memory of the new record, 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 call is waiting to free disk space, it will throw the "Disk space full" error, and then terminate;

8, when the MyISAM table in the execution of repair table or OPTIMIZE tables when killed, will cause the table damage is not available, guidance to repair the completion.

Several suggestions for safely closing MySQL

To safely shut down the MYSQLD service process, it is recommended that you follow these steps:

0, with a super, all, such as the highest authorized account to connect MySQL, preferably with a UNIX socket connection;

1, in 5.0 and above version, set Innodb_fast_shutdown = 1, allow fast shutdown innodb (no full purge, insert buffer merge), if it is to upgrade or downgrade the MySQL version, do not set;

2, set innodb_max_dirty_pages_pct = 0, let InnoDB all dirty pages are flushed to disk;

3, set max_connections and max_user_connections for 1, and finally in addition to their current connection, do not allow a new connection to create;

4, close all inactive threads, that is, the state of sleep and time is greater than 1 thread ID;

5. Perform show processlist confirm that there are still active threads, especially threads that produce table locks, such as select with large datasets, or a wide range of updates, or execute DDL, with particular care;

6. Perform show ENGINE INNODB STATUS confirms that history list length is lower (typically less than 500), that is, not purge transactions, and confirm log sequence number, log flushed up t O, last checkpoint is the same as the value in three states, that is, all lsn have been checkpoint;

7, then perform flush Lockal tables operation, refresh all table cache, close the Open table (local role is the operation does not record Binlog);

8, if it is slave server, it is best to close the Io_thread, waiting for all relay log after the application, and then close the sql_thread, to avoid sql_thread in the execution of large business is terminated, patience for all of its application completed, if you want to forcibly shut down, It is best to wait for the end of the big business and then close sql_thread;

9, finally again executes mysqladmin shutdown.

10, in an emergency, you can set Innodb_fast_shutdown = 1, and then directly execute mysqladmin shutdown can, or even directly in the operating system layer call kill or kill-9 killed Mysqld process (in Innodb_flus H_log_at_trx_commit = 0 may lose part of the transaction, but when the mysqld process starts again, it will perform crash recovery work, which needs to be weighed.

Wordy so much, in fact, the normal implementation of Mysqladmin shutdown is enough, if there is congestion, and then refer to the contents of the above analysis and solve it, haha: