mysql關于redo事務日志ib_logfile的理解

總結

1、redo事務日志就是ib_logfile,兩個ib_logfile開頭的文件，它們就是log group中的redo log file，而且它們的大小完全一致且等于變量innodb_log_file_size定義的值

2、redo事務日志的作用就是用于crash recovery，crash recovery是數據庫重啟時自動的行為，無需為DBA執行任何額外工作

3、MySQL以循環方式寫入重做日志文件，如果最后1個 ib_logfile 被寫滿，而第一個ib_logfile中所有記錄的事務對數據的變更已經被持久化到磁盤中，將清空并重用之。

4、redo事務日志的概念類似oracle的online redo log，里面包含commit和uncommit的數據

5、寫redo事務日志有幾種方式，每隔1秒或每次事務提交，所以里面可以包含沒有提交uncommit的數據

6、show engine innodb status可以看到redo log的信息

     Log sequence number：表明當前redo log的最新LSN。

     Log flushed up to：表明當前已經刷新到磁盤上redo log的LSN。

     Last checkpoint at ：redo log記錄的更新已經刷新到磁盤上的檢查點LSN，該LSN之前的redo log上記錄的更新已全部刷新到磁盤上，可以被覆蓋重復使用。

7、查看ib_logfile里的內容的方法

[root@mydb ~]# strings /var/lib/mysql/ib_logfile0

相關參數

innodb_log_file_size ：每個redo log文件大小

innodb_log_files_in_group ：redo log日志組成員個數

innodb_log_group_home_dir ：redo log存放目錄

innodb_page_size ：InnoDB表空間的頁面大小，默認16K

innodb_flush_log_at_timeout ：日志刷新頻率，單位秒

Write and flush the logs every N seconds. innodb_flush_log_at_timeout allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. The default setting for innodb_flush_log_at_timeout is once per second.

每N秒寫入并刷新日志。 innodb_flush_log_at_timeout允許增加刷新之間的超時時間，以減少刷新并避免影響二進制日志組提交的性能。 innodb_flush_log_at_timeout的默認設置是每秒一次。

innodb_flush_log_at_trx_commit ：控制commit動作是否刷新log buffer到磁盤

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. 

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit

With a setting of 0, logs are written and flushed to disk once per second

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second

控制提交操作的嚴格ACID合規性與重新安排和批量完成與 提交 相關的I / O操作時可能實現的更高性能之間的平衡。

默認設置為1。在每次事務提交時，日志都會寫入并刷新到磁盤。這種方式即使系統崩潰也不會丟失任何數據，但是因為每次提交都寫入磁盤，IO的性能較差。

設置為0時，每秒寫入日志并將其刷新到磁盤一次。也就是說設置為0時是(大約)每秒刷新寫入到磁盤中的，當系統崩潰，會丟失1秒鐘的數據。

設置為2時，在每次事務提交后寫入日志，然后每秒再刷新一次磁盤。每次提交都僅寫入到os buffer，然后是每秒調用fsync()將os buffer中的日志寫入到log file on disk。

日志刷新頻率由innodb_flush_log_at_timeout控制，允許您將日志刷新頻率設置為N秒（其中N為1 ... 2700，默認值為1）。但是，任何mysqld進程崩潰都可以消除最多N秒的事務。

innodb_flush_log_at_timeout很多人誤以為是控制innodb_flush_log_at_trx_commit值為0和2時的1秒頻率，實際上并非如此。

以下四種方式將innodb日志緩沖區的日志刷新到磁盤

1、每秒一次執行刷新Innodb_log_buffer到重做日志文件。即使某個事務還沒有提交，Innodb存儲引擎仍然每秒會將重做日志緩存刷新到重做日志文件。

2、每個事務提交時會將重做日志刷新到重做日志文件。

3、當重做日志緩存可用空間少于一半時，重做日志緩存被刷新到重做日志文件

4、當有checkpoint時，checkpoint在一定程度上代表了刷到磁盤時日志所處的LSN位置

https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

重做日志是在崩潰恢復期間用于糾正由未完成事務寫入的數據的基于磁盤的數據結構。

By default, the redo log is physically represented on disk as a set of files, named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion. 

默認情況下，重做日志在磁盤上物理表示為一組文件，名為ib_logfile0和ib_logfile1。 MySQL以循環方式寫入重做日志文件。

備注：innodb_log_files_in_group 確定ib_logfile文件個數，命名從 ib_logfile0 開始。如果最后1個 ib_logfile 被寫滿，而第一個ib_logfile中所有記錄的事務對數據的變更已經被持久化到磁盤中，將清空并重用之。

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_redo_log

redo

The data, in units of records, recorded in the redo log when DML statements make changes to InnoDB tables. It is used during crash recovery to correct data written by incomplete transactions. The ever-increasing LSN value represents the cumulative amount of redo data that has passed through the redo log.

當DML語句對InnoDB表進行更改時，以記錄為單位的數據記錄在重做日志中。 它在崩潰恢復期間用于更正由未完成的事務寫入的數據。 不斷增加的LSN值表示通過重做日志的重做數據的累積量。

redo log

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

在崩潰恢復期間使用的基于磁盤的數據結構，用于糾正由未完成的事務寫入的數據。 在正常操作期間，它編碼更改InnoDB表數據的請求，這些數據來自SQL語句或通過NoSQL接口的低級API調用。 在意外關閉之前未完成更新數據文件的修改會自動重播。

重做日志在物理上表示為一組文件，通常名為ib_logfile0和ib_logfile1。 重做日志中的數據根據受影響的記錄進行編碼; 這些數據統稱為重做。 數據通過重做日志的傳遞由不斷增加的LSN值表示。 在MySQL 5.6.3中，重做日志的最大大小的原始4GB限制被提升到512GB。

crash

MySQL uses the term “crash” to refer generally to any unexpected shutdown operation where the server cannot do its normal cleanup. For example, a crash could happen due to a hardware fault on the database server machine or storage device; a power failure; a potential data mismatch that causes the MySQL server to halt; a fast shutdown initiated by the DBA; or many other reasons. The robust, automatic crash recovery for InnoDB tables ensures that data is made consistent when the server is restarted, without any extra work for the DBA.

MySQL使用術語“崩潰”來指代服務器無法正常清理的任何意外關閉操作。 例如，由于數據庫服務器計算機或存儲設備上的硬件故障，可能會發生崩潰; 停電; 潛在的數據不匹配導致MySQL服務器停止; 由DBA發起的快速關閉; 或許多其他原因。 InnoDB表的強大自動崩潰恢復功能可確保在重新啟動服務器時使數據保持一致，而無需為DBA執行任何額外工作。

crash recovery

The cleanup activities that occur when MySQL is started again after a crash. For InnoDB tables, changes from incomplete transactions are replayed using data from the redo log. Changes that were committed before the crash, but not yet written into the data files, are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation.

During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

崩潰后再次啟動MySQL時發生的清理活動。 對于InnoDB表，使用重做日志中的數據重放未完成事務的更改。 在崩潰之前提交但尚未寫入數據文件的更改將從doublewrite緩沖區重建。 當數據庫正常關閉時，在清除操作期間執行此類活動。

在正常操作期間，提交的數據可以在寫入數據文件之前存儲在更改緩沖區中一段時間。 在保持數據文件最新之間總是需要權衡，這會在正常操作期間引入性能開銷，并緩沖數據，這會使關閉和崩潰恢復花費更長時間。

備注：CrashSafe指MySQL服務器宕機重啟后能夠保證：所有已經提交的事務的數據仍然存在；所有沒有提交的事務的數據自動回滾。Innodb通過Redo Log和Undo Log可以保證這兩點。

log buffer

The memory area that holds data to be written to the log files that make up the redo log. It is controlled by the innodb_log_buffer_size configuration option.

保存要寫入構成重做日志的日志文件的數據的內存區域。 它由innodb_log_buffer_size配置選項控制。

log file

One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer memory area.

構成重做日志的ib_logfileN文件之一。 數據從日志緩沖區存儲區寫入這些文件。

log group

The set of files that make up the redo log, typically named ib_logfile0 and ib_logfile1. (For that reason, sometimes referred to collectively as ib_logfile.)

組成重做日志的文件集，通常名為ib_logfile0和ib_logfile1。（因此，有時統稱為ib_logfile。）

LSN

Acronym for “log sequence number”. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.

Prior to MySQL 5.6.3, the LSN was a 4-byte unsigned integer. The LSN became an 8-byte unsigned integer in MySQL 5.6.3 when the redo log file size limit increased from 4GB to 512GB, as additional bytes were required to store extra size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit rather than 32-bit variables to store and compare LSN values.

In the MySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take an incremental backup. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

“日志序列號”的縮寫。這個任意的，不斷增加的值表示與重做日志中記錄的操作相對應的時間點。 （此時間點與事務邊界無關;它可以落在一個或多個事務的中間。）它在崩潰恢復期間由InnoDB內部使用，用于管理緩沖池。

在MySQL 5.6.3之前，LSN是一個4字節的無符號整數。當重做日志文件大小限制從4GB增加到512GB時，LSN成為MySQL 5.6.3中的8字節無符號整數，因為需要額外的字節來存儲額外的大小信息。在MySQL 5.6.3或更高版本上構建的使用LSN值的應用程序應使用64位而不是32位變量來存儲和比較LSN值。

在MySQL Enterprise Backup產品中，您可以指定LSN來表示進行增量備份的時間點。相關的LSN由mysqlbackup命令的輸出顯示。一旦您擁有與完全備份時間相對應的LSN，您就可以指定該值以進行后續增量備份，其輸出包含用于下一次增量備份的另一個LSN。