mysql關(guān)于ib_logfile事務(wù)日志和binary log二進(jìn)制日志的區(qū)別

總結(jié)

1、ib_logfile類似oracle的online redo log，包含commit和uncommit的數(shù)據(jù)

2、binary log類似oracle的online redo log和archive redo log,但是只有commit的數(shù)據(jù)

statement 格式的 binlog，最后會(huì)有 COMMIT；
row 格式的 binlog，最后會(huì)有一個(gè) XID event

3、為什么MySQL有binlog，還要redo log？因?yàn)镸ySQL是多存儲(chǔ)引擎的，不管使用那種存儲(chǔ)引擎，都會(huì)有binlog，而不一定有redo log。而redo log 事務(wù)日志ib_logfile文件是InnoDB存儲(chǔ)引擎產(chǎn)生的

4、ib_logfile是循環(huán)使用，binary log不是循環(huán)使用，在寫滿或者重啟之后，會(huì)生成新的binary log文件

5、兩種日志記錄的內(nèi)容差不多類似，都是事務(wù)對(duì)應(yīng)DML、DDL的信息，只是作用不同，內(nèi)容可能重復(fù)，比如一個(gè)DML記錄在了ib_logfile也記錄在了binary log

6、ib_logfile作為異常宕機(jī)后啟動(dòng)時(shí)恢復(fù)使用

7、binary log作為數(shù)據(jù)恢復(fù)使用，主從復(fù)制搭建使用

8、兩種日志寫入磁盤的觸發(fā)點(diǎn)不同，二進(jìn)制日志只在事務(wù)提交完成后進(jìn)行一次寫入，重做日志在事務(wù)提交會(huì)寫入每隔1秒也會(huì)寫入。MySQL為了保證master和slave的數(shù)據(jù)一致性，就必須保證binlog和InnoDB redo日志的一致性（因?yàn)閭鋷焱ㄟ^二進(jìn)制日志重放主庫提交的事務(wù)，如果主庫commit之前就寫入binlog，一旦主庫crash，再次啟動(dòng)時(shí)會(huì)回滾事務(wù)。但此時(shí)從庫已經(jīng)執(zhí)行，則會(huì)造成主備數(shù)據(jù)不一致）。所以必須保證二進(jìn)制日志只在事務(wù)提交完成后進(jìn)行一次寫入

9、在主從復(fù)制結(jié)構(gòu)中，要保證事務(wù)的持久性和一致性，對(duì)兩種日志的相關(guān)變量設(shè)置為如下最為妥當(dāng)：sync_binlog=1(即每提交一次事務(wù)同步寫到磁盤中)；innodb_flush_log_at_trx_commit=1(即每提交一次事務(wù)都寫到磁盤中)。這兩項(xiàng)變量的設(shè)置保證了：每次提交事務(wù)都寫入二進(jìn)制日志和事務(wù)日志，并在提交時(shí)將它們刷新到磁盤中

10、innodb中，表數(shù)據(jù)刷盤的規(guī)則只有一個(gè)：checkpoint。但是觸發(fā)checkpoint的情況卻有幾種（1.重用redo log文件；2.臟頁達(dá)到一定比例）

11、ib_logfile作為redo log記錄的是“做了什么改動(dòng)”，是物理日志，記錄的是"在某個(gè)數(shù)據(jù)頁上做了什么修改"；

       binary log記錄的是這個(gè)語句的原始邏輯，分兩種模式，statement格式記錄的是sql語句，row格式記錄的是行的內(nèi)容，記錄更新前和更新后的兩條數(shù)據(jù)。

使用下面的方法查看ib_logfile里的內(nèi)容

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

使用下面兩種方法查看binary log的內(nèi)容

mysqlbinlog mysql-bin.000002

mysql> show binlog events in 'mysql-bin.000002';

mysql> show binlog events in 'mysql-bin.00002' from 504769752 limit 30,30;
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
選項(xiàng)解析：
IN 'log_name'   指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)
FROM pos        指定從哪個(gè)pos起始點(diǎn)開始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count       查詢總條數(shù)(不指定就是所有行)

ib_logfile

官方文檔https://dev.mysql.com/doc/refman/5.7/en/glossary.html

A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. Also sometimes referred to as the log group. These files record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

This data cannot be used for manual recovery; for that type of operation, use the binary log.

一組文件，通常名為ib_logfile0和ib_logfile1，構(gòu)成重做日志。 有時(shí)也稱為日志組。 這些文件記錄了嘗試更改InnoDB表中數(shù)據(jù)的語句。 在崩潰后啟動(dòng)時(shí)，會(huì)自動(dòng)重播這些語句以更正由不完整事務(wù)寫入的數(shù)據(jù)。

此數(shù)據(jù)不能用于手動(dòng)恢復(fù); 對(duì)于該類型的操作，請(qǐng)使用二進(jìn)制日志。

binary log

官方文檔https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication

Certain data recovery operations require use of the binary log.After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

二進(jìn)制日志包含描述數(shù)據(jù)庫更改的“事件”，例如表創(chuàng)建操作或?qū)Ρ頂?shù)據(jù)的更改。 它還包含可能已進(jìn)行更改的語句的事件（例如，不匹配任何行的DELETE），除非使用基于行的日志記錄。 二進(jìn)制日志還包含有關(guān)每個(gè)語句獲取更新數(shù)據(jù)的時(shí)間長度的信息。 

二進(jìn)制日志有兩個(gè)重要目的：

用于復(fù)制

某些數(shù)據(jù)恢復(fù)操作需要使用二進(jìn)制日志。備份恢復(fù)后，將重新執(zhí)行備份后記錄的二進(jìn)制日志中的事件。 這些事件使數(shù)據(jù)庫從備份點(diǎn)更新。

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

二進(jìn)制日志不用于不修改數(shù)據(jù)的SELECT或SHOW等語句

checkpoint

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

As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.

當(dāng)對(duì)緩沖池中緩存的數(shù)據(jù)頁進(jìn)行更改時(shí)，這些更改將在稍后的某個(gè)時(shí)間寫入數(shù)據(jù)文件，這一過程稱為刷新。 檢查點(diǎn)是已成功寫入數(shù)據(jù)文件的最新更改（由LSN值表示）的記錄。

sharp checkpoint

The process of flushing to disk all dirty buffer pool pages whose redo entries are contained in certain portion of the redo log. Occurs before InnoDB reuses a portion of a log file ; the log files are used in a circular fashion. Typically occurs with write-intensive workloads.

將重做條目包含在重做日志的某些部分中的所有臟緩沖池頁面刷新到磁盤的過程。 在InnoDB覆蓋重用日志文件之前發(fā)生 ; 日志文件以循環(huán)方式使用。 通常發(fā)生寫入密集型工作負(fù)載。

flush

To write changes to the database files , that had been buffered in a memory area or a temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.

Flushing can happen because a memory area becomes full and the system needs to free some space , because a commit operation means the changes from a transaction can be finalized, or because a slow shutdown operation means that all outstanding work should be finalized. When it is not critical to flush all the buffered data at once, InnoDB can use a technique called fuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

將發(fā)生在內(nèi)存區(qū)域或臨時(shí)磁盤存儲(chǔ)區(qū)域中緩沖的 更改寫入數(shù)據(jù)庫文件 。 定期刷新的InnoDB存儲(chǔ)結(jié)構(gòu)包括重做日志，撤消日志和緩沖池。

刷新可能是因?yàn)? 內(nèi)存區(qū)域已滿并且系統(tǒng)需要釋放一些空間 ，因?yàn)樘峤徊僮饕馕吨梢宰罱K確定事務(wù)的更改，或者因?yàn)槁訇P(guān)閉操作意味著應(yīng)該最終完成所有未完成的工作。 當(dāng)一次刷新所有緩沖數(shù)據(jù)并不重要時(shí)，InnoDB可以使用一種稱為 模糊檢查點(diǎn) 的技術(shù)來刷新小批量頁面以分散I / O開銷。