mysql關(guān)于redo事務(wù)日志ib_logfile的理解

總結(jié)

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

2、redo事務(wù)日志的作用就是用于crash recovery，crash recovery是數(shù)據(jù)庫重啟時(shí)自動(dòng)的行為，無需為DBA執(zhí)行任何額外工作

3、MySQL以循環(huán)方式寫入重做日志文件，如果最后1個(gè) ib_logfile 被寫滿，而第一個(gè)ib_logfile中所有記錄的事務(wù)對(duì)數(shù)據(jù)的變更已經(jīng)被持久化到磁盤中，將清空并重用之。

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

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

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

     Log sequence number：表明當(dāng)前redo log的最新LSN。

     Log flushed up to：表明當(dāng)前已經(jīng)刷新到磁盤上redo log的LSN。

     Last checkpoint at ：redo log記錄的更新已經(jīng)刷新到磁盤上的檢查點(diǎn)LSN，該LSN之前的redo log上記錄的更新已全部刷新到磁盤上，可以被覆蓋重復(fù)使用。

7、查看ib_logfile里的內(nèi)容的方法

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

相關(guān)參數(shù)

innodb_log_file_size ：每個(gè)redo log文件大小

innodb_log_files_in_group ：redo log日志組成員個(gè)數(shù)

innodb_log_group_home_dir ：redo log存放目錄

innodb_page_size ：InnoDB表空間的頁面大小，默認(rèn)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允許增加刷新之間的超時(shí)時(shí)間，以減少刷新并避免影響二進(jìn)制日志組提交的性能。 innodb_flush_log_at_timeout的默認(rèn)設(shè)置是每秒一次。

innodb_flush_log_at_trx_commit ：控制commit動(dòng)作是否刷新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

控制提交操作的嚴(yán)格ACID合規(guī)性與重新安排和批量完成與 提交 相關(guān)的I / O操作時(shí)可能實(shí)現(xiàn)的更高性能之間的平衡。

默認(rèn)設(shè)置為1。在每次事務(wù)提交時(shí)，日志都會(huì)寫入并刷新到磁盤。這種方式即使系統(tǒng)崩潰也不會(huì)丟失任何數(shù)據(jù)，但是因?yàn)槊看翁峤欢紝懭氪疟P，IO的性能較差。

設(shè)置為0時(shí)，每秒寫入日志并將其刷新到磁盤一次。也就是說設(shè)置為0時(shí)是(大約)每秒刷新寫入到磁盤中的，當(dāng)系統(tǒng)崩潰，會(huì)丟失1秒鐘的數(shù)據(jù)。

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

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

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

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

1、每秒一次執(zhí)行刷新Innodb_log_buffer到重做日志文件。即使某個(gè)事務(wù)還沒有提交，Innodb存儲(chǔ)引擎仍然每秒會(huì)將重做日志緩存刷新到重做日志文件。

2、每個(gè)事務(wù)提交時(shí)會(huì)將重做日志刷新到重做日志文件。

3、當(dāng)重做日志緩存可用空間少于一半時(shí)，重做日志緩存被刷新到重做日志文件

4、當(dāng)有checkpoint時(shí)，checkpoint在一定程度上代表了刷到磁盤時(shí)日志所處的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.

重做日志是在崩潰恢復(fù)期間用于糾正由未完成事務(wù)寫入的數(shù)據(jù)的基于磁盤的數(shù)據(jù)結(jié)構(gòu)。

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. 

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

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

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.

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

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.

在崩潰恢復(fù)期間使用的基于磁盤的數(shù)據(jù)結(jié)構(gòu)，用于糾正由未完成的事務(wù)寫入的數(shù)據(jù)。 在正常操作期間，它編碼更改InnoDB表數(shù)據(jù)的請(qǐng)求，這些數(shù)據(jù)來自SQL語句或通過NoSQL接口的低級(jí)API調(diào)用。 在意外關(guān)閉之前未完成更新數(shù)據(jù)文件的修改會(huì)自動(dòng)重播。

重做日志在物理上表示為一組文件，通常名為ib_logfile0和ib_logfile1。 重做日志中的數(shù)據(jù)根據(jù)受影響的記錄進(jìn)行編碼; 這些數(shù)據(jù)統(tǒng)稱為重做。 數(shù)據(jù)通過重做日志的傳遞由不斷增加的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使用術(shù)語“崩潰”來指代服務(wù)器無法正常清理的任何意外關(guān)閉操作。 例如，由于數(shù)據(jù)庫服務(wù)器計(jì)算機(jī)或存儲(chǔ)設(shè)備上的硬件故障，可能會(huì)發(fā)生崩潰; 停電; 潛在的數(shù)據(jù)不匹配導(dǎo)致MySQL服務(wù)器停止; 由DBA發(fā)起的快速關(guān)閉; 或許多其他原因。 InnoDB表的強(qiáng)大自動(dòng)崩潰恢復(fù)功能可確保在重新啟動(dòng)服務(wù)器時(shí)使數(shù)據(jù)保持一致，而無需為DBA執(zhí)行任何額外工作。

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.

崩潰后再次啟動(dòng)MySQL時(shí)發(fā)生的清理活動(dòng)。 對(duì)于InnoDB表，使用重做日志中的數(shù)據(jù)重放未完成事務(wù)的更改。 在崩潰之前提交但尚未寫入數(shù)據(jù)文件的更改將從doublewrite緩沖區(qū)重建。 當(dāng)數(shù)據(jù)庫正常關(guān)閉時(shí)，在清除操作期間執(zhí)行此類活動(dòng)。

在正常操作期間，提交的數(shù)據(jù)可以在寫入數(shù)據(jù)文件之前存儲(chǔ)在更改緩沖區(qū)中一段時(shí)間。 在保持?jǐn)?shù)據(jù)文件最新之間總是需要權(quán)衡，這會(huì)在正常操作期間引入性能開銷，并緩沖數(shù)據(jù)，這會(huì)使關(guān)閉和崩潰恢復(fù)花費(fèi)更長(zhǎng)時(shí)間。

備注：CrashSafe指MySQL服務(wù)器宕機(jī)重啟后能夠保證：所有已經(jīng)提交的事務(wù)的數(shù)據(jù)仍然存在；所有沒有提交的事務(wù)的數(shù)據(jù)自動(dòng)回滾。Innodb通過Redo Log和Undo Log可以保證這兩點(diǎn)。

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.

保存要寫入構(gòu)成重做日志的日志文件的數(shù)據(jù)的內(nèi)存區(qū)域。 它由innodb_log_buffer_size配置選項(xiàng)控制。

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.

構(gòu)成重做日志的ib_logfileN文件之一。 數(shù)據(jù)從日志緩沖區(qū)存儲(chǔ)區(qū)寫入這些文件。

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。（因此，有時(shí)統(tǒng)稱為ib_logfile。）

LSN

Acronym for “l(fā)og 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.

“日志序列號(hào)”的縮寫。這個(gè)任意的，不斷增加的值表示與重做日志中記錄的操作相對(duì)應(yīng)的時(shí)間點(diǎn)。 （此時(shí)間點(diǎn)與事務(wù)邊界無關(guān);它可以落在一個(gè)或多個(gè)事務(wù)的中間。）它在崩潰恢復(fù)期間由InnoDB內(nèi)部使用，用于管理緩沖池。

在MySQL 5.6.3之前，LSN是一個(gè)4字節(jié)的無符號(hào)整數(shù)。當(dāng)重做日志文件大小限制從4GB增加到512GB時(shí)，LSN成為MySQL 5.6.3中的8字節(jié)無符號(hào)整數(shù)，因?yàn)樾枰~外的字節(jié)來存儲(chǔ)額外的大小信息。在MySQL 5.6.3或更高版本上構(gòu)建的使用LSN值的應(yīng)用程序應(yīng)使用64位而不是32位變量來存儲(chǔ)和比較LSN值。

在MySQL Enterprise Backup產(chǎn)品中，您可以指定LSN來表示進(jìn)行增量備份的時(shí)間點(diǎn)。相關(guān)的LSN由mysqlbackup命令的輸出顯示。一旦您擁有與完全備份時(shí)間相對(duì)應(yīng)的LSN，您就可以指定該值以進(jìn)行后續(xù)增量備份，其輸出包含用于下一次增量備份的另一個(gè)LSN。