How to safely close a MySQL instance

Reprinted from: http://imysql.com/2014/08/13/mysql-faq-howto-shutdown-mysqld-fulgraceful.shtml

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

shutdown Process :

1, launch shutdown, send sigterm signal

2. If necessary, create a new dedicated shutdown thread (shutdown thread)

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

If the SIGTERM signal is directly received, the thread responsible for signal processing will be responsible for shutting down the work, or creating a separate thread to take care of it.

When a separate shutdown thread cannot be created (for example, out of memory), 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, shuts down TCP/IP network snooping, shuts down UNIX sockets and other channels

4. Gradually close the current connection, transaction, idle connection, will be terminated immediately;

There is currently a connection to the transaction, SQL activity, which identifies it as killed and periodically checks its status for the next check to close it; (refer to KILL syntax)

If there is currently an active transaction, the thing is rolled back and if the non-transactional table is also modified in the transaction, the modified data cannot be rolled back and may only complete some changes;

If the master/slave replicates the master in the scene, the process of copying the thread is the same as the normal thread;

If it is a master/slave replication scene in the slave, then the IO, SQL thread will be closed, if the 2 threads are currently active, it will also be added killed identity, and then closed;

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

In MySQL 5.0.80 and previous versions, if the SQL thread is executing a transaction in the middle, the transaction is rolled back, and starting with 5.0.81, it waits for all operations to end, unless the user initiates a kill operation.

When slave's SQL thread performs an operation on a non-transactional table, it is forced to kill, which may cause master and slave data to be inconsistent;

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

Refresh all table caches to close all open tables;

Each storage engine is responsible for related shutdown operations, such as MyISAM flushes all pending writes, and InnoDB flushes the buffer pool to disk (starting with MySQL 5.0.5, if Innodb_fast_shutdown is not set to 2), Logs the current LSN to the tablespace, and then closes all internal threads.

6. MySQL Server Process exit

About the KILL command

Starting from 5.0, KILL supports specifying CONNECTION | Query two selectable 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 operation that the thread is currently committing, and the others remain unchanged;

After the kill operation is committed, a special kill token bit is set on the thread. It usually takes a while for a thread to actually close, because the kill tag bit is checked only under certain circumstances:

• 1. When a select query is executed, in the order by or group by loop, the kill token bit is checked each time a number of row record blocks are read, and if found, the statement terminates;
• 2. When an ALTER TABLE is executed, the kill tag bit is checked after reading some row record blocks 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 tag bit is checked each time a number of row record blocks are read and updated or deleted, and if found, the statement terminates, rolls back the transaction, and if the operation is on a non-transactional table, the data that has changed is not rolled back;
• 4, the Get_lock () function returns null;
• 5. INSERT delay thread will quickly add new records in 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 call is waiting to free up disk space, it will throw a "disk full" error and then terminate;
• 8, when the MyISAM table in the execution of repair table or OPTIMIZE table is killed, it will cause the table damage is not available, the Guide to repair the completion.

A few suggestions for safely shutting down MySQL

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

• 0, with super, all and other maximum permissions to connect MySQL account, preferably with a UNIX socket connection;
• 1, in version 5.0 and above, set Innodb_fast_shutdown = 1, allow fast shutdown innodb (no full purge, insert buffer merge), if it is to upgrade or downgrade 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, but also the final in addition to their current connection, do not allow the creation of new connections;
• 4. Close all inactive threads, that is, the thread ID with the status of sleep and time greater than 1;
• 5. Perform SHOW processlist to confirm that there are active threads, especially those that generate table locks, such as select with large data sets, or a wide range of update, or execute DDL, all with special care;
• 6. Perform SHOW ENGINE INNODB STATUS to confirm that the value of history list length is lower (generally less than 500), that is, purge transactions are rare, and that log sequence number, log flushed up t O, last checkpoint at three state values, that is, all LSN has been checked point;
• 7, then perform the flush Lockal TABLES operation, refresh all table caches, close the open table (the role of local is that the operation does not log binlog);
• 8, if it is slave server, it is best to close the Io_thread, waiting for all relay log to be applied, and then close sql_thread, to avoid sql_thread in the execution of large transaction is terminated, patient to its full application, if it is forced to close, It is better to wait until the big business ends and then close the Sql_thread;
• 9, the final re-execution mysqladmin shutdown.
• 10, emergency, you can set Innodb_fast_shutdown = 1, and then directly execute mysqladmin shutdown, or even directly in the operating system layer call kill or kill-9 kill Mysqld process (in Innodb_flus H_log_at_trx_commit = 0 may lose some of the transaction), but when the mysqld process starts again, it crash recovery work and needs to be weighed.

Wordy so much, in fact, the normal execution of mysqladmin shutdown is enough, if there is a blockage, and then refer to the above content for analysis and resolution it, haha:)

How to safely close a MySQL instance