在認識binlog日志三種模式前,先了解一下解析binlog日志的指令工mysqlbinlog。mysqlbinlog工具的作用是解析mysql的二進制binlog日志内容,把二進制日志解析成可以在MySQL資料庫裡執行的SQL語句。binlog日志原始資料是以二進制形式存在的,需要使用mysqlbinlog工具轉換成SQL語句形式。
mysql的binlog日志作用是用來記錄mysql内部增删改等對mysql資料庫有更新内容的記錄(對資料庫進行改動的操作),對資料庫查詢的語句如show,select開頭的語句,不會被binlog日志記錄,主要用于資料庫的主從複制與及增量恢複。
案例:
在對資料庫進行定時備份時,隻能備份到某個時間點,假如在淩晨0點進行全備了,但是在中午12點出現故障需要恢複資料,使用0點的全備隻能恢複到0點時刻的資料,難道0點到12點的資料隻能丢失了嗎?
這時就是展現binlog日志重要性的時候了,需要對binlog日志進行定時推送(一分鐘一次或五分鐘一次,時間頻率視業務場景而定)完成增量備份。當出現故障時,可以使用定時備份和增量備份恢複到故障點時刻的資料。具體的恢複方案,這裡不做簡述,後面再寫文章來講解。
binlog日志三種模式
ROW Level
記錄的方式是行,即如果批量修改資料,記錄的不是批量修改的SQL語句事件,而是每條記錄被更改的SQL語句,是以,ROW模式的binlog日志檔案會變得很“重”。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYTMfhHLlN3XnxCM38FdsYkRGZkRG9lcvx2bjxyMx8VZ6l2csIWewVTTpJWNFFXN1IlMwVzbXlVN1EGc1klNhVTQClGVF5UMR9Fd4VGdsATNfd3bkFGazxycykFaKdkYzZUbapXNXlleSdVY2pESa9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnLwYTOxMTMyUTMxETOwEjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
優點:row level的binlog日志内容會非常清楚的記錄下每一行資料被修改的細節。而且不會出現某些特定情況下存儲過程或function,以及trigger的調用和觸發器無法被正确複制的問題。
缺點:row level下,所有執行的語句當記錄到日志中的時候,都以每行記錄的修改來記錄,這樣可能會産生大量的日志内容,産生的binlog日志量是驚人的。批量修改幾百萬條資料,那麼記錄幾百萬行……
Statement level(預設)
記錄每一條修改資料的SQL語句(批量修改時,記錄的不是單條SQL語句,而是批量修改的SQL語句事件)。看上面的圖解可以很好的了解row level和statement level兩種模式的差別。
優點:statement模式記錄的更改的SQ語句事件,并非每條更改記錄,是以大大減少了binlog日志量,節約磁盤IO,提高性能。
缺點:statement level下對一些特殊功能的複制效果不是很好,比如:函數、存儲過程的複制。由于row level是基于每一行的變化來記錄的,是以不會出現類似問題
Mixed
實際上就是前兩種模式的結合。在Mixed模式下,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式,也就是在Statement和Row之間選擇一種。
企業場景如何選擇binlog的模式
1、 如果生産中使用MySQL的特殊功能相對少(存儲過程、觸發器、函數)。選擇預設的語句模式,Statement Level。
2、 如果生産中使用MySQL的特殊功能較多的,可以選擇Mixed模式。
3、 如果生産中使用MySQL的特殊功能較多,又希望資料最大化一緻,此時最好Row level模式;但是要注意,該模式的binlog非常“沉重”。
檢視binlog模式
mysql> show global variables like "%binlog_format%";
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
配置binlog日志模式
vim my.cnf(在[mysqld]子產品中配置)
log-bin = /data/3306/mysql-bin
binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
不重新開機,使配置在msyql中生效
SET global binlog_format='STATEMENT';
以上内容來源于網際網路。