Shulou(Shulou.com)06/01 Report--

The topic describes the standardized steps that need to be followed to adjust the innodb_log_file_size size or the number of filegroups for the configuration parameters of the already running MySQL database InnoDB storage engine. Solution 1 modifies the parameters so that clean shutdown occurs the next time MySQL shuts down.

Set global innodb_fast_shutdown=0

(there is no such recommendation in the official documentation, but for security reasons, clean shutdown is recommended to enable MySQL to write all changes to the data file and get rid of its dependence on innodb_log_file. If this parameter is set to 2, when the database is suddenly shut down, the system will immediately browse the transaction log to disk and cold shut down the mysqld service; transactions that are not committed will be lost, but transaction rollback recovery will occur when the mysqld service is started again.)

Clean shutdown means brushing dirty page data back to disk for storage. (2) stop applying the system. (if necessary, all database connections should be killed on the database side) 3 wait for the InnoDB dirty page to be brushed out. 4 observe the returned result of show global status like 'Innodb_buffer_pool_pages_dirty'; until the result is close to 0.

If this step takes more time than is acceptable, you can abort the operation at any time and resume the application.

5 shut down the MySQL database, during the shutdown period, there may be a large number of random read IO due to the need to flush the data in the memory buffer pool back to disk for storage.

This closure will take a long time and need to wait patiently. 6 check the error log to make sure there is no suspicious error message. 7 Edit the innodb_log_file_size parameter in the configuration file. 8 start the database, the system will modify the transaction log file size according to the newly set innodb_log_file_ size value, and check whether the database is running properly. 9 warm up the database to cache part of the data into the memory buffer pool to reduce the IO impact on the disk when the application is started (optional). 10 start the application and reconnect to the database. Knowledge point 1 if InnoDB checks that the number of innodb_log_file_size and redo log files is not equal, InnoDB will write a log checkpoint, close and move the old log file

Create a new log file of required size and open it. 2innodb_fast_shutdown:

Meaning: sets the mode in which the innoDB engine is shut down. The default value is: 1, the state of normal shutdown

0-before the mysqld service shuts down, complete data cleaning and buffer insertion merge are performed. If the data is dirty,

Factors such as more or server IO performance can cause this process to take several minutes or more

1-shut down the mysqld service normally and do nothing else for the innodb engine

2-if mysqld crashes, immediately swipe the transaction log to disk and cold shut down the mysqld service; no commit

The transaction will be lost, but the transaction rollback recovery will occur when the mysqld service is started again; 3Innodb_buffer_pool_pages_dirty:

Meaning: percentage of dirty pages in the InnoDB memory buffer pool. When the database is closed, this parameter is equal to 0, which means that the dirty page is brushed back to disk. 4Formula for calculating dirty page data: dirty page data size = Innodb_buffer_pool_pages_dirty*Innodb_buffer_pool_size. 5innodb_log_file_size:

Meaning: size of the transaction log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.