背景

binlog_row_image這個參數(shù)是MySQL5.6新增的參數(shù)，默認值是FULL，在5.7版本默認值也是FULL，但今天我看到有客戶的 MySQL5.7版本參數(shù)模板采用的是MINIMAL而不是FULL，我對這個修改表示疑惑。

一般來說，對一個參數(shù)默認值作出修改，我們都應(yīng)該考慮清楚影響范圍，所以我準備做一次測試，并得出結(jié)論哪個參數(shù)值才是最佳設(shè)置。

術(shù)語解釋

前提：binlog格式必須為row格式或者mixed格式，不可以是statement格式。名稱解釋:before image：前鏡像，即數(shù)據(jù)庫表中修改前的內(nèi)容。after image：后鏡像，即數(shù)據(jù)庫表中修改后的內(nèi)容。---

binlog_row_image三種設(shè)置及異同

binlog_row_image參數(shù)可以設(shè)置三個合法值: FULL、MINIMAL、NOBLOB三個不同值的作用如下：

FULL: Log all columns in both the before image and the after image.binlog日志記錄所有前鏡像和后鏡像。MINIMAL: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.binlog日志的前鏡像只記錄唯一識別列(唯一索引列、主鍵列)，后鏡像只記錄修改列。noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.binlog記錄所有的列，就像full格式一樣。但對于BLOB或TEXT格式的列，如果他不是唯一識別列(唯一索引列、主鍵列)，或者沒有修改，那就不記錄。---

For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

官方提到:如果沒有唯一識別列(唯一索引列、主鍵列)，例如只有普通key，那么MINIMAL格式的前鏡像也會記錄所有所有列，但后鏡像依然只記錄修改列。

分析

1. 這個參數(shù)如果設(shè)置成MINIMAL格式，可以節(jié)省不少磁盤空間，節(jié)省一定的io。但由于前鏡像不記錄修改列，只在后鏡像記錄修改列，如果數(shù)據(jù)出現(xiàn)誤操作，必然不能通過flashback或binlog2SQL等快速閃回工具恢復數(shù)據(jù)，因為不能通過BinLog生成反向SQL了。

節(jié)省磁盤空間: 高數(shù)據(jù)安全性: 低

2. 這個參數(shù)如果設(shè)置成NOBLOB格式，在表中TEXT和BLOB等大字段如果不修改，就不記錄前后鏡像了，其他小字段的列的修改依然記錄前后鏡像，一般大字段消耗的磁盤空間是非常大的，可以節(jié)省不少磁盤空間。而如果表沒有大字段，NOBLOB和FULL格式并沒有區(qū)別，如果數(shù)據(jù)出現(xiàn)誤操作，可以通過flashback或BinLog2SQL等快速閃回工具恢復數(shù)據(jù)。

節(jié)省磁盤空間: 中數(shù)據(jù)安全性: 中

3. 這個參數(shù)如果設(shè)置成FULL格式，這是MySQL5.6和MySQL5.7的默認設(shè)置，binlog記錄所有數(shù)據(jù)的前后鏡像，如果數(shù)據(jù)出現(xiàn)誤操作，可以能通過flashback或binlog2sql等快速閃回工具恢復數(shù)據(jù)。在數(shù)據(jù)列比較大的情況下，在大量的update、delete操作時，binlog盤增長會很快，比較容易出現(xiàn)“binlog盤快滿”的監(jiān)控告警。

節(jié)省磁盤空間: 低數(shù)據(jù)安全性: 高

測試過程如下:

1 測試binlog_row_image=MINIMAL

1.1 測試沒有主鍵沒有唯一索引

看是否前鏡像全保留

日志如下：

binlog_row_image=MINIMAL，沒有主鍵沒有唯一索引，確實前鏡像全保留，后鏡像只有修改列

1.2 測試有主鍵

看是否前鏡像只有主鍵列，后鏡像只有修改列

日志如下：

確實前鏡像只有主鍵列，后鏡像只有修改列。就這個原因，導致不能閃回數(shù)據(jù)，安全性考慮不應(yīng)該使用binlog_row_image=MINIMAL。

2 測試 binlog_row_image=noblob

2.1 測試沒有主鍵沒有唯一索引

由于沒有主鍵沒有唯一索引，所以前鏡像是全保留，因為TEXT/blob是修改列，所以后鏡像的TEXT/blob列也被保留了。整體和FULL格式一致。

日志如下：

2.2 測試有主鍵

有主鍵，修改列依然是TEXT/blob列，由于有主鍵了，所以前鏡像不會強迫包含所有列，但前鏡像的的TEXT列被忽略、不包含,后鏡像的TEXT列由于是修改列，所以包含。

日志如下：

實驗證明binlog_row_image=noblob這個格式，依然存在缺失前鏡像的問題，導致某些場景無法閃回，所以也不推薦設(shè)置。

2.3 測試有主鍵，修改列也不是TEXT列

日志如下：

前鏡像和后鏡像包含除TEXT/BLOB列之外的所有列

結(jié)論

大多數(shù)客戶生產(chǎn)的安全性大于一切，在硬盤白菜價的今天，不提倡設(shè)置binlog_row_image=MINIMAL參數(shù)，應(yīng)該繼續(xù)使用默認值binlog_row_image=FULL格式。

總結(jié)

以上是生活随笔為你收集整理的binlog工具_MySQL5.6新增的参数binlog_row_image到底怎么设置-爱可生的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。