mysql關於ib_logfile事務日誌和binary log二進位制日誌的區別

總結

1、ib_logfile類似oracle的online redo log，包含commit和uncommit的資料

2、binary log類似oracle的online redo log和archive redo log,但是隻有commit的資料

statement 格式的 binlog，最後會有 COMMIT；
row 格式的 binlog，最後會有一個 XID event

3、為什麼MySQL有binlog，還要redo log？因為MySQL是多儲存引擎的，不管使用那種儲存引擎，都會有binlog，而不一定有redo log。而redo log 事務日誌ib_logfile檔案是InnoDB儲存引擎產生的

4、ib_logfile是迴圈使用，binary log不是迴圈使用，在寫滿或者重啟之後，會生成新的binary log檔案

5、兩種日誌記錄的內容差不多類似，都是事務對應DML、DDL的資訊，只是作用不同，內容可能重複，比如一個DML記錄在了ib_logfile也記錄在了binary log

6、ib_logfile作為異常當機後啟動時恢復使用

7、binary log作為資料恢復使用，主從複製搭建使用

8、兩種日誌寫入磁碟的觸發點不同，二進位制日誌只在事務提交完成後進行一次寫入，重做日誌在事務提交會寫入每隔1秒也會寫入。MySQL為了保證master和slave的資料一致性，就必須保證binlog和InnoDB redo日誌的一致性（因為備庫透過二進位制日誌重放主庫提交的事務，如果主庫commit之前就寫入binlog，一旦主庫crash，再次啟動時會回滾事務。但此時從庫已經執行，則會造成主備資料不一致）。所以必須保證二進位制日誌只在事務提交完成後進行一次寫入

9、在主從複製結構中，要保證事務的永續性和一致性，對兩種日誌的相關變數設定為如下最為妥當：sync_binlog=1(即每提交一次事務同步寫到磁碟中)；innodb_flush_log_at_trx_commit=1(即每提交一次事務都寫到磁碟中)。這兩項變數的設定保證了：每次提交事務都寫入二進位制日誌和事務日誌，並在提交時將它們重新整理到磁碟中

10、innodb中，表資料刷盤的規則只有一個：checkpoint。但是觸發checkpoint的情況卻有幾種（1.重用redo log檔案；2.髒頁達到一定比例）

11、ib_logfile作為redo log記錄的是“做了什麼改動”，是物理日誌，記錄的是"在某個資料頁上做了什麼修改"；

       binary log記錄的是這個語句的原始邏輯，分兩種模式，statement格式記錄的是sql語句，row格式記錄的是行的內容，記錄更新前和更新後的兩條資料。

使用下面的方法檢視ib_logfile裡的內容

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

使用下面兩種方法檢視binary log的內容

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];
選項解析：
IN 'log_name'   指定要查詢的binlog檔名(不指定就是第一個binlog檔案)
FROM pos        指定從哪個pos起始點開始查起(不指定就是從整個檔案首個pos點開始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count       查詢總條數(不指定就是所有行)

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，構成重做日誌。 有時也稱為日誌組。 這些檔案記錄了嘗試更改InnoDB表中資料的語句。 在崩潰後啟動時，會自動重播這些語句以更正由不完整事務寫入的資料。

此資料不能用於手動恢復; 對於該型別的操作，請使用二進位制日誌。

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.

二進位制日誌包含描述資料庫更改的“事件”，例如表建立操作或對錶資料的更改。 它還包含可能已進行更改的語句的事件（例如，不匹配任何行的DELETE），除非使用基於行的日誌記錄。 二進位制日誌還包含有關每個語句獲取更新資料的時間長度的資訊。 

二進位制日誌有兩個重要目的：

用於複製

某些資料恢復操作需要使用二進位制日誌。備份恢復後，將重新執行備份後記錄的二進位制日誌中的事件。 這些事件使資料庫從備份點更新。

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

二進位制日誌不用於不修改資料的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.

當對緩衝池中快取的資料頁進行更改時，這些更改將在稍後的某個時間寫入資料檔案，這一過程稱為重新整理。 檢查點是已成功寫入資料檔案的最新更改（由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覆蓋重用日誌檔案之前發生 ; 日誌檔案以迴圈方式使用。 通常發生寫入密集型工作負載。

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.

將發生在記憶體區域或臨時磁碟儲存區域中緩衝的 更改寫入資料庫檔案 。 定期重新整理的InnoDB儲存結構包括重做日誌，撤消日誌和緩衝池。

重新整理可能是因為 記憶體區域已滿並且系統需要釋放一些空間 ，因為提交操作意味著可以最終確定事務的更改，或者因為慢速關閉操作意味著應該最終完成所有未完成的工作。 當一次重新整理所有緩衝資料並不重要時，InnoDB可以使用一種稱為 模糊檢查點 的技術來重新整理小批次頁面以分散I / O開銷。