• notice
  • Congratulations on the launch of the Sought Tech site

Mysql Close Process Detailed and Safely Close Mysql

This article analyzes the process of closing the mysqld process, and how to shut down the MySQL instance safely and gracefully.Students who are not very clear about this process can refer to it.

Close process:

1.Initiate shutdown and send out SIGTERM signal

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

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

   If it is closed by receiving the SIGTERM signal directly, the thread specifically responsible for signal processing will be responsible for the shutdown, or a new independent thread will be responsible for this matter

   When an independent shutdown thread cannot be created (such as insufficient memory), MySQL Server will issue 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;

   There are currently transactions and SQL active connections, which will be marked as killed, and their status will be checked regularly so that they can be closed during the next check; (refer to KILL syntax)

   If there is currently an active transaction, the transaction will be rolled back.If the non-transactional table is also modified in the transaction, the modified data cannot be rolled back, and only partial changes may be completed;

   If it is the Master in the Master/Slave replication scenario, the processing of the replication thread is the same as the normal thread;

   If it is the slave in the Master/Slave replication scenario, the IO and SQL threads will be closed in turn.If these two threads are currently active, the killed flag will also be added and then closed;

   On the Slave server, the SQL thread is allowed to directly stop the current SQL operation (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 will be rolled back; starting from 5.0.81, it will wait for all operations to end, unless The user initiates a KILL operation.

   When Slave's SQL thread is forced to KILL when performing operations on non-transactional tables, it may cause inconsistent Master and Slave data;

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

   Refresh all table cache, close all open tables;

   Each storage engine is responsible for related shutdown operations.For example, MyISAM will refresh 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 If it is 2), record the current LSN in the table space, 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, stop rolling back the transaction, close the thread connection, and release related resources;

@KILL QUERY only stops the operation currently submitted by the thread for execution, and the others remain unchanged;

   After submitting the KILL operation, a special kill flag bit will be set on the thread.It usually takes a while to actually shut down the thread, because the kill flag is only checked under certain circumstances:

1.When executing a SELECT query, in the ORDER BY or GROUP BY loop, the kill flag bit will be checked every time some row record block is read.If it is found to exist, the statement will be terminated;

2.When ALTER TABLE is executed, the kill flag will be checked after reading some row record blocks from the original table.If it is found to exist, the statement will be terminated and the temporary table will be deleted;

3.When performing UPDATE and DELETE, every time some row record block is read and updated or deleted, the kill flag bit will be checked.If it is found to exist, the statement will be terminated and the transaction will be rolled back.If it is an operation 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 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 operation call is waiting to release the disk space, it will directly throw a "disk space full" error and then terminate;

8.When the MyISAM table is KILL during the execution of REPAIR TABLE or OPTIMIZE TABLE, the table will be damaged and unusable, and the repair will be completed 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 authority such as SUPER and ALL to connect to MySQL, preferably using unix socket to connect;

1.In version 5.0 and above, set innodb_fast_shutdown=1 to allow fast shutdown of InnoDB (without full purge, insert buffer merge), if it is to upgrade or downgrade MySQL version, do not set it;

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

3.Set max_connections and max_user_connections to 1, which means that in the end, no new connections are allowed to be created except for your current connection;

4.Close all inactive threads, that is, thread IDs whose status is Sleep  and Time is greater than 1;

5.Execute SHOW PROCESSLIST  to confirm whether there are active threads, especially threads that will generate table locks, such as SELECTs with large data sets, or large-scale UPDATEs, or execute DDL.You must be particularly cautious ;

6.Execute SHOW ENGINE INNODB STATUS to confirm that the value of History list length is low (generally lower than 500), that is, there are few transactions without PURGE, and confirm the Log sequence number, Log flushed up to, and Last checkpoint at three The value of each state is the same, that is, all LSNs have been checked;

7.Then execute the FLUSH LOCKAL TABLES operation, refresh all table caches, and close the opened tables (the function of LOCAL is that this operation does not record BINLOG);

8.If it is a SLAVE server, it is best to close IO_THREAD first, wait for all RELAY LOGs to be applied, and then close SQL_THREAD to prevent SQL_THREAD from being terminated during the execution of large transactions, and wait patiently for all of its applications to be completed, if you have to force it If it is closed, it is best to wait for the end of the big transaction 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 call kill or kill-9 directly at the operating system layer to kill the mysqld process (parts may be lost when innodb_flush_log_at_trx_commit=0 Transaction), but when the mysqld process starts again, it will perform CRASH RECOVERY work, which needs to be weighed.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy atorvastatin 80mg for sale & lt;a href="https://lipiws.top/"& gt;atorvastatin where to buy& lt;/a& gt; order atorvastatin 80mg without preion

Kxrzni

2024-03-08

Leave a Reply

+