MySQL shutdown process to understand and safely shut down MySQL method

MySQL shutdown process to understand and safely shut down MySQL method www.hongkevip.com time: the- ,- + xx:Genevaread: Finishing: VIP sharing to:0Red Guest VIP (http://www.hongkevip.com): MySQL shutdown process to understand and safely shut down MySQL methodThis 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, initiating shutdown, issuing sigterm signals.2, if necessary, create a new shutdown thread (shutdown thread) if it is a client-initiated shutdown, a new dedicated shutdown thread is created if the SIGTERM signal is closed directly, the thread responsible for the signal processing will be responsible for shutting down the work. Or create a new independent thread. 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 server3, MySQL server no longer responds to new connection requests to close TCP/IP network monitoring, shutting down UNIX sockets and other channels4, gradually closes the current connection, the transaction idle connection, will be immediately terminated; There is currently a connection to the transaction, SQL activity, which is identified as killed, and the status is checked on a regular basis so that it will be closed on the next check, and if the event is currently active, the thing will be rolled back if it Also modifies non-transactional tables, the data that has been modified cannot be rolled back and may only complete partial transformations;/slave Copy the master in the scene, the process of copying the thread is the same as the normal thread;/slave Copy the slave in the scene, the IO, SQL threads are closed in turn, and if the 2 threads are currently active, the killed identity is added and then closed, and the SQL thread is allowed to stop the current SQL operation directly (in order to avoid replication problems). ), and then close the thread; in MySQL5.0In. 80 and previous versions, if the SQL thread was executing exactly one transaction into the middle, the transaction would be rolled back; from 5.0. 81 starts by waiting 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 closes all threads, shuts down all the storage engines, refreshes all table caches, closes all open tables, and each storage engine is responsible for related shutdown operations, such as MyISAM flushing all pending writes; InnoDB will buffer Pool is flushed to disk (from MySQL5.0.5 Start if Innodb_fast_shutdown is not set to2), record the current LSN in the tablespace, and then close all internal threads. 6, MySQL server process exits about kill instruction starting from 5.0, KILL supports specifying CONNECTION|query two options: @KILL connection and original, stop rolling back the transaction, close the thread connection, release the related resources; @KILL query stops only the actions that the thread is currently committing, and the others remain the same; after committing the KILL operation, A special kill ID memento flag bit is set on the thread. It usually takes a while for a thread to actually shut down, because the kill identity memento flag bit is checked only under certain circumstances:1, when a select query is executed, in the order by or group by Loop, the kill identity Memento flag is checked each time a number of row record blocks are read, and the statement terminates if found to exist;2, when an ALTER TABLE is executed, the kill identity memento flag bit is checked after each read of some row record blocks from the original table, and if found, the statement terminates and the temporary table is deleted;3, perform update and delete, each read some row record block and update or delete will check the kill identity memento flag bit, if found, the statement will terminate, rollback the transaction, if the operation on a non-transactional table, the transformed data will not be 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 is freed and terminated;7, if the thread's write call is waiting to free disk space, it throws a "disk full" error and then terminates;8, when the MyISAM table is killed when executing repair table or OPTIMIZE table, it will cause the table to become corrupted, and guide the repair to complete again. Secure shutdown MySQL A few suggestions to safely close the mysqld process, it is recommended that you follow these steps:0, with super, all and other highest privileges of the account to connect MySQL, preferably with a UNIX socket connection;1, in version 5.0 and above, set Innodb_fast_shutdown =1, allow fast shutdown of innodb (no full purge, insert buffer merge), do not set if you are upgrading or downgrading the MySQL version;2, Set innodb_max_dirty_pages_pct =0, let InnoDB all the dirty pages to the disk;3, set max_connections and max_user_connections to 1, and finally, in addition to their current connection, do not allow the creation of new connections;4, close all inactive threads, that is, the state is sleep and time is greater than1the thread id;5, execute SHOW processlist to confirm that there are still active threads, especially those that generate table locks, such as select with large datasets, or a wide range of update, or execute DDL, are particularly cautious;

MySQL shutdown process to understand and safely shut down MySQL method