A tutorial for safely turning off the MySQL service

Ordinary closed
My mysql is the tar package downloaded by myself, I set up the installation directory to install.

Stopping mysql is simple, but it's a bit of a headache if you don't know. Here for those of you who are starting out with mysql :)

To do this, go to the bin directory of mysql and execute

./mysqladmin -uroot -p shutdown

Then enter your password.

ps: Of course, if your root doesn't have a password, you don't need the -p option.

ps: Someone asks the way to boot, the correct way to boot is to go to mysql's bin directory, and then nohup. /mysqld_safe & Just fine.

Securely close the MySQL instance
Closing process:

1. Initiate shutdown and send SIGTERM signal
2. Create a new close thread if necessary (shutdown thread)
If it is a client-initiated shutdown, a dedicated shutdown thread is created

If you receive the SIGTERM signal directly to shut it down, a thread dedicated to signal processing will do so, or a separate thread will be created to do so

When a separate close thread cannot be created (for example, out of memory), MySQL Server sends an alert 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;

Currently there is a transaction, an SQL active connection, which is identified as killed and periodically checked for status so that it can be closed the next time it is checked; (See KILL syntax)

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

If it is Master in the Master/Slave replication scenario, the process for the replicated thread is the same as for the normal thread.

If it is 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 logo will be 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, if the SQL thread was executing a transaction right in the middle, that transaction would be rolled back. Starting at 5.0.81, it waits for all operations to end unless the user initiates the KILL operation.

When the SQL thread of Slave performs operation on non-transacted table, it is forced to KILL, which may cause the data of Master and Slave to be different.

5. MySQL Server process shuts down all threads and shuts down all storage engines;
Refresh all tables cache, close all open tables;

Each storage engine is responsible for the relevant shutdown operation. For example, MyISAM refreshes all operations waiting to be written; InnoDB will flush buffer pool to disk (starting with MySQL 5.0.5, if innodb_fast_shutdown is not set to 2), record the current LSN into the table space, and then close all internal threads.

6, MySQL Server process exit
About the KILL directive

Starting with 5.0, KILL supports specifying two options for CONNECTION | QUERY:

KILL CONNECTION and the original one, stop the rollback transaction, close the thread connection, and release related resources;
KILL QUERY stops only what the thread is currently committing to do, leaving everything else unchanged.
After the KILL operation is committed, a special kill tag bit is set on the thread. It usually takes a period of time to actually close the thread, because the kill tag bits are checked only under certain circumstances:

1. When executing SELECT query, in ORDER BY or GROUP BY loop, the kill marker bit will be checked after reading 1 row record block each time. If it is found to exist, the statement will be terminated;
2. During the execution of ALTER TABLE, the kill tag bit will be checked after every 1 row record block is read from the original table. If it is found to exist, the statement will terminate and the temporary table will be deleted.
3. During the execution of UPDATE and DELETE, the kill marker bit will be checked after reading some row record blocks and updating or deleting. If it is found to exist, the statement will be terminated and the transaction will be rolled back.
4. GET_LOCK() returns NULL;
5, INSERT DELAY thread will quickly add records in memory, and then terminate;
6. If the current thread holds the table-level lock, it will be released and terminated;
7, if the thread write operation call is waiting to free disk space, it will directly throw the "disk space full" error, and then terminate;
8. When THE MyISAM table is damaged by KILL during the execution of REPAIR TABLE or OPTIMIZE TABLE, the table will be damaged and not available. It is instructed to complete the repair again.
Several Suggestions for safely turning off MySQL

To safely shut down the mysqld service process, it is recommended to follow these steps:

0. Connect to MySQL with the account with the highest permissions, such as SUPER and ALL, preferably in the way of unix socket;
1. In version 5.0 or above, set innodb_fast_shutdown = 1 to allow quick closing of InnoDB (full purge, insert buffer merge); do not set for upgrading or downgrading of MySQL version;
2. Set innodb_max_dirty_pages_pct = 0 and let InnoDB flush all dirty pages to the disk;
3. Set max_connections and max_user_connections as 1, so that no new connection can be created except for the current one;
4. Close all inactive threads, that is, threads with the status of Sleep and Time greater than 1, ID;
5. Execute SHOW PROCESSLIST to confirm whether there are still active threads, especially those that will generate table locks. For example, SELECT with large data sets, or UPDATE with large scope, or DDL should be executed with special care.
6. Perform SHOW ENGINE INNODB STATUS to confirm that the value of History list is low (usually less than 500), that is, there are few transactions without PURGE, and confirm that the value of Log sequence number, Log flushed to, Last at3 states is one. That means that all LSN have already checked;
7. Then perform FLUSH LOCKAL TABLES operation, refresh all table cache, close the opened table (LOCAL is not used to record BINLOG);
8. If it is SLAVE server, it is better to close IO_THREAD first and wait for all RELAY LOG to be applied, then close SQL_THREAD to avoid the termination of SQL_THREAD in executing a large transaction. Be patient until all the THREAD are applied.
9. Execute mysqladmin shutdown at last.
10. In case of emergency, you can set innodb_fast_shutdown = 1 and then execute mysqladmin shutdown directly, or even directly call kill or kill-9 to kill the mysqld process at the operating system layer (some transactions may be lost when innodb_flush_at_ES272en = 0). However, when the mysqld process starts again, CRASH RECOVERY work will be performed and there will be tradeoffs.
In fact, mysqladmin shutdown is enough under normal conditions. If blocking occurs, please refer to the above content for analysis and resolution. Haha: