MySQL shutdown process details and methods for safely disabling MySQL. mysql shutdown process details

Source: Internet
Author: User

MySQL shutdown process details and methods for safely disabling MySQL. mysql shutdown process details

This article analyzes the mysqld process and how to safely and easily shut down MySQL instances. If you are not clear about this process, refer to it.

Closing Process:

1. initiate shutdown and send a SIGTERM Signal

2. Create a shutdown thread if necessary)

If the thread is disabled by the client, a dedicated thread is created.

If the SIGTERM signal is directly received for shutdown, the thread responsible for signal processing will be responsible for shutdown, or a new independent thread will be responsible for this.

When an independent thread is not created (for example, the memory is insufficient), MySQL Server sends an alarm similar to the following:

Error: Can't create thread to kill server

3. MySQL Server no longer responds to new connection requests

Disable TCP/IP network listening and Unix Socket

4. Close the current connection and transaction gradually

Idle connection will be terminated immediately;

There are still connections for transactions and SQL activities. They are identified as killed and their status is checked regularly so that they can be closed during the next check. (Refer to KILL syntax)

If an active transaction exists, the transaction will be rolled back. If a non-transaction table is modified in the transaction, the modified data cannot be rolled back, and only some changes may be completed;

If it is a Master in the Master/Slave replication scenario, the process of the replication thread is the same as that of the common thread;

For the Master/Slave replication scenario, the I/O and SQL threads are closed sequentially. If the two threads are active, the killed ID is also added, then close it;

On the Slave server, the SQL thread allows you to stop the current SQL operation directly (to avoid replication issues) and then shut down the thread;

In MySQl 5.0.80 and earlier versions, if the SQL thread executes a transaction to the center at that time, the transaction will be rolled back; Starting from 5.0.81, it will wait until all the operations are completed, unless the user initiates the KILL operation.

When the Slave SQL thread executes operations on non-transaction tables, it is forced to KILL, which may cause data inconsistency between the Master and Slave;

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

Refresh the cache of all tables and disable all opened tables;

Each storage engine is responsible for related shutdown operations. For example, MyISAM will refresh all the operations waiting for write; InnoDB will refresh the buffer pool to the disk (starting from MySQL 5.0.5, if innodb_fast_shutdown is not set to 2), record the current lsn to the tablespace, and then close all internal threads.

6. MySQL Server process exited

About KILL commands

From 5.0, KILL supports specifying CONNECTION and QUERY options:

@ Kill connection: like the original one, stop the rollback transaction, close the thread CONNECTION, and release related resources;

@ Kill query: only the operations currently submitted by the thread are stopped, and other operations remain unchanged;

After the KILL operation is submitted, a special kill flag is set on this thread. It usually takes some time to actually close the thread, because the kill flag is only checked under specific circumstances:

1. When a SELECT query is executed, in the order by or group by loop, the kill flag is checked after reading some row record blocks each time. If yes, the statement is terminated;

2. When alter table is executed, the system checks the kill flag after reading some row record blocks from the original TABLE. If the block is found to exist, the statement terminates and deletes the temporary TABLE;

3. When UPDATE and DELETE are executed, the kill flag is checked every time some row record blocks are read and updated or deleted. If yes, the statement will terminate and roll back the transaction, if an operation is performed on a non-transaction table, the changed data is not rolled back;

4. The GET_LOCK () function returns NULL;

5. The insert delay thread will quickly add new records in the memory and terminate them;

6. If the current thread holds a table-Level Lock, it will be released and terminated;

7. If the write operation call of the thread is waiting to release the disk space, the system will directly throw the "disk space full" error and terminate it;

8. If the MyISAM TABLE is killed when executing the repair table or optimize table, the TABLE will be corrupted and unavailable. This will guide the REPAIR to be completed again.

Suggestions for safely disabling MySQL

To safely disable the mysqld service process, follow these steps:

0. Use an account with the highest permissions such as SUPER and ALL to connect to MySQL. It is best to use a unix socket to connect;

1. In versions 5.0 and later, set innodb_fast_shutdown = 1 to allow quick InnoDB Shutdown (full purge and insert buffer merge are not performed). To upgrade or downgrade MySQL, do not set it;

2. Set innodb_max_dirty_pages_pct to 0, so that InnoDB can refresh all dirty pages to the disk;

3. Set max_connections and max_user_connections to 1. In addition to the current connection, new connections cannot be created;

4. Shut down all inactive threads, that is, the thread ID in Sleep state and with Time greater than 1;

5. Execute show processlist to check whether there are active threads, especially those that will generate table locks, such as SELECT with large datasets, UPDATE in a large range, or execute DDL, be especially cautious;

6. Execute show engine innodb status to check that the value of History list length is low (generally lower than 500), that is, there are very few transactions without PURGE, check that the values of Log sequence number, Log flushed up to, and Last checkpoint at are the same, that is, all lsns have been checkpointed;

7. Execute the flush lockal tables operation to refresh all table caches and disable opened TABLES (the LOCAL operation does not record BINLOG );

8. If it is a server Load balancer server, it is best to disable IO_THREAD first, and then close SQL _THREAD after all RELAY logs are applied to prevent SQL _THREAD from being terminated when a large transaction is executed, wait patiently until all the applications are completed. If you have to force close the application, you 'd better wait until the end of the large transaction before closing SQL _THREAD;

9. Execute mysqladmin shutdown.

10. In an emergency, you can set innodb_fast_shutdown to 1, and then directly execute mysqladmin shutdown, even directly calling kill or kill-9 at the operating system layer to kill the mysqld process (some transactions may be lost when innodb_flush_log_at_trx_commit = 0), but when the mysqld process starts again, will carry out the crash recovery work, need to be weighed.

If there are so many things, it is enough to execute mysqladmin shutdown normally. If blocking occurs, refer to the above content for analysis and solution. haha :)

How to disable mysql?

Use cmd to go to the bin folder in the mysql installation path and run: mysqld-nt -- install
Manual: directly go to the bin folder of the mysql installation path and execute net start mysql.
If you do not want mysql to start the service when the computer is started, execute: mysqld-nt -- remove
You can also delete the corresponding service in HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices and restart the computer.
Close mysql: mysqladmin-uroot-p shutdown
Start mysql:
Mysqld-nt -- install
Net start mysql

For more information, see ,,

Disable mysql

If it is not the root password:
Have you forgotten the root password?
If not, use root to modify the mysql database user table.

For the root password:

Method 1:
MySQL provides command line parameters for skipping access control and starts the MySQL server by running the following command:
Safe_mysqld -- skip-grant-tables &
You can skip MySQL Access Control. Anyone can enter the MySQL database as an administrator on the console.
It should be noted that after the password is changed, the MySQL server should be stopped and restarted to take effect.

Method 2:
Follow these steps to reset the MySQL root Password:
1. First confirm that the server is in a secure state, that is, no one can connect to the MySQL database at will.
Because, during the resetting of the MySQL root Password, the MySQL database is completely out of password-free
Other users can log on to and modify MySQL information as needed. You can use
The external port is closed, and Apache and all user processes are stopped to implement server quasi-security.
Status. The safest status is to operate on the server Console and unplug the network cable.
2. Modify MySQL Logon Settings:
# Vi/etc/my. cnf
In the [mysqld] section, add skip-grant-tables.
For example:
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
Save and exit vi.
3. Restart mysqld
#/Etc/init. d/mysqld restart
Stopping MySQL: [OK]
Starting MySQL: [OK]
4. log on to and modify the MySQL root Password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 3 to server version: 3.23.56
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-
Database changed
Mysql> UPDATE user SET Password = password ('new-password') WHERE User = 'root ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Mysql> quit
5. Modify the MySQL Logon Settings.
# ...... Remaining full text>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.