Common error analysis for MySQL crash recovery process

When I recently argued with a colleague about some of the common mistakes in MySQL crash recovery, he thought some of the parameters could be set up that would not recover from MySQL's crash, but I don't agree with that, although some parameter settings can cause data loss, However, it should not cause the database to crash after the failure to recover, so you want to sort out the MySQL crash recovery process to deepen learning!

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/2D/wKioL1gqdO6BPx4gAAC4cc9si2c853.jpg-wh_500x0-wm_3 -wmp_4-s_1130904350.jpg "title=" MySQL crash recovery process. jpg "alt=" wkiol1gqdo6bpx4gaac4cc9si2c853.jpg-wh_50 "/>

Figure one MySQL Wal process

under normal circumstances, the data write will write to Redo_buffer_pool first, and then write to Redo_log_file, which if the parameter is set incorrectly, may be lost, but does not affect the host's crash recovery, but the following two cases will affect the crash recovery process of the instance;

Scenario One: When the host is down, the LSN is not synchronously written to the File_header fil_page_file_flush_lsn, resulting in the record LSN value and log FILE header records Log_checkpoint_ LSN in LSN inconsistent, theoretically at this time fil_page_file_flush_lsn<log_checkpoint_lsn; crash recovery, will be redo redo_file in the difference, to achieve crash recovery, Roll forward operations based on the LSN of page;

Scenario Two: Since there is fil_page_file_flush_lsn>log_checkpoint_lsn, this situation will not exist, it does exist, when the dirty page refresh progress is greater than the progress of writing redo_log_file this situation exists, Because the refresh Redo_log_file and refresh PAGE do not interfere with each other, the Innodb_flush_log_at_trx_commit value does produce a result that causes fil_page_file_flush_lsn>log_ CHECKPOINT_LSN situation occurs, this situation to crash recovery will appear more classic error: error:page XXX log sequence number xxx is in the futher!

Table name now the LSN value of page is greater than redo_log_file LSN, resulting in the inability to rollback, this time only add force_innodb_recovery to start.

Therefore, improper parameter setting does affect the crash recovery process!!!

This article is from the "11869724" blog, please be sure to keep this source http://11879724.blog.51cto.com/11869724/1872928

Common error analysis for MySQL crash recovery process