How to safely close a MySQL instance

How to safely close a MySQL instance
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 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:

• Like the original kill connection, KILL the transaction rollback, close the thread CONNECTION, and release related resources;
• Kill query only stops the operations currently submitted by the thread, and the 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 configure the security of the MySQL server?

The configuration files are also different because of Chroot. If you encounter similar situations in actual operations, but you do not know how to solve them correctly, so the following articles must be good teachers and friends for you. 1. Close the remote connection. First, close port 3306, which is the default listening port of MySQL. MySQL only serves local scripts, so remote connection is not required. Although the built-in security mechanism of MySQL is very strict, listening to a TCP port is still dangerous, because if the MySQL program itself has problems, unauthorized access can bypass the built-in security mechanism of MySQL. The method to disable network listening is very simple. In the [mysqld] section of the/chroot/mysql/etc/my. cnf file, remove "#" before # skip-networking. When the network is closed, how does a local program connect to the MySQL database? Local programs can be connected through mysql. sock, which is faster than network connections. I will discuss the specific situation of mysql. sock later. MySQL backup is usually performed using SSH. 2. Disabling MySQL from importing LOCAL files will prohibit MySQL from using the "load data local infile" command. This command uses MySQL to read local files to the database, and then the user can obtain sensitive information illegally. In order to disable the above command, in/chroot/mysql/etc/my. in the [mysqld] section of the cnf file, add the following statement: set-variable = local-infile = 0. For ease of management, MySQL management commands such as mysql, mysqladmin, and mysqldump in the system are generally used, all are system/etc/my. cnf file. If you want to connect to the mysql server, it will look for the/tmp/MySQL. sock file to try to connect to the MySQL server, but the connection here is the mysql server under the chroot. There are two solutions: one is to add -- socket =/chroot/mysql/tmp/mysql. sock after the management command. For example: #/usr/local/mysql/bin/mysql-root-p -- socket =/chroot/mysql/tmp/mysql. the other sock is in/etc/my. add socket =/chroot/mysql/tmp/mysql to the [client] section of cnf. sock. Obviously, the second method is much more convenient. 3. modify the root user ID and password of MySQL # chrootuid/chroot/mysql/usr/local/mysql/libexec/mysqld & #/usr/local/mysql/bin/mysql-uroot .. ..... mysql> set password for root @ localhost = PASSWORD ('new _ password'); try to enter a password in mysql, this is because it may be seen by others when input under Shell. Mysql> use mysql; mysql> update user set user = "wghgreat" where user = "root"; mysql> select Host, User, Password, Sele ...... remaining full text>

Why?