How to quickly and effectively shut down the MySQL service when the database is busy

If the InnoDB_buffer_size parameter is too large and there is too much dirty data in the buffer, when you turn off MySQL

Dirty data must be flushed to disk, which can be a lengthy process, resulting in a long shutdown of the MySQL service.

You can set innodb_max_dirty_pages_pct=0 temporarily, and then manually shut down the database after most of the dirty data has been flushed to disk (by looking at the Modified db pages in the output of the show engine innodb status command, this value should be very small).

Method 1:

Mysql > set global innodb_max_dirty_pages_pct=0

Query OK, 0 rows affected (0.00 sec)

Run the command mysqladmin-uroot ext-i10 | grep dirty, check the value of the status variable, and turn it off when it is close to zero. If it is a system with busy production, this value may always be too high. When the value of innodb_max_dirty_pages is very small, you can.

Shut down MySQL with mysqladmin.

# mysqladmin-uroot ext-i10 | grep dirty

| | Innodb_buffer_pool_pages_dirty | 0 | |

| | Innodb_buffer_pool_bytes_dirty | 0 | |

| | Innodb_buffer_pool_pages_dirty | 0 | |

| | Innodb_buffer_pool_bytes_dirty | 0 |

Method 2:

Mysql > set global innodb_fast_shutdown=2; defaults to 1

Query OK, 0 rows affected (0.00 sec)

Although this allows for a quick shutdown of mysql, more recovery operations are required at startup.

The meaning of the three values of Innodb_fast_shutdown:

Innodb_fast_shutdown tells innodb what to do when it shuts down. There are three values to choose from:

0 means that purge all and merge insert buffer,flush dirty pages are required when innodb is turned off. This is the slowest way to shut down, but restart is also the fastest. The meaning of the three purge all,merge insert buffer,flush dirty pages will be introduced later.

1 means that when innodb is turned off, it does not need purge all,merge insert buffer, only flush dirty page.

2 means that when innodb is turned off, it does not need purge all,merge insert buffer or flush dirty page, but only flush the logs in log buffer to log files. So it is the most time-consuming to recover later.