簡介

binlog是mysql server層存儲的二進制日志肛根，主要用于記錄更新操作：

• 所有數(shù)據(jù)庫表結構變更DDL（例如create摔认、alter顽照、drop）狼荞；
• 表數(shù)據(jù)修改DML（insert苞也、update、delete）粘秆。

日志類型

binlog日志包含：

• 二進制日志索引文件（文件后綴名=.index）如迟，是個普通文件，文件內(nèi)容如下：
  image

• 二進制日志文件（文件后綴名=.00000*），用于記錄更新操作殷勘。

寫入流程

當事務表存儲引擎時此再，所有未提交的二進制日志會被記錄到一個緩存（通過binlog_cache_size配置緩存大小，默認32K）中去玲销，等待事務提交時直接將緩存中的二進制日志刷新到磁盤二進制文件输拇。

binlog_cache_size是基于會話的，因此設置的大小一定要消息贤斜〔叻停可以通過binlog_cache_use(寫入緩存的次數(shù))、binlog_cache_disk_use(寫入臨時文件的次數(shù))瘩绒，判斷當前的設置是否合適猴抹，太小會寫到臨時文件影響性能，太大會造成內(nèi)存浪費锁荔。

默認的情況下蟀给，二進制日志并不是在每次寫的時候都刷新到磁盤中。因此阳堕，當數(shù)據(jù)庫發(fā)生宕機的時候跋理，就會可能造成數(shù)據(jù)的丟失，這會給恢復和復制帶來問題恬总。因此可以呀通過sync_binlog=N（默認是0）設置寫入N次就同步一次到磁盤前普。

不過即使將sync_binlog設置成1，也會出現(xiàn)問題壹堰。當使用InnoDB存儲引擎時汁政，在一個事務發(fā)出COMMIT操作之前，先將二進制日志寫入磁盤缀旁，但如果此時提交動作還沒有在InnoDB存儲redo log寫入记劈，并且此時數(shù)據(jù)庫發(fā)生了宕機。下次啟動時并巍，該事務會被回滾掉目木，但是二進制日志已經(jīng)記錄了該信息，就會造成復制時數(shù)據(jù)不一致懊渡。因此刽射，需要保證binlog與redo log分布式事務的一致性，可以使用XA事務保證一致性剃执，通過innodb_support_xa=ON（默認開啟）開啟誓禁。

日志格式

binlog格式有三種：row，statement肾档，mixed摹恰。

可通過show variables like '%binlog_format%' 查看當前binlog日志格式辫继。

• row：非常清晰的記錄下每行數(shù)據(jù)的修改細節(jié)，不需要記錄上下文相關信息俗慈。

  • 優(yōu)點：不會出現(xiàn)某些特定情況下的存儲過程姑宽、函數(shù)、觸發(fā)器的調用和觸發(fā)無法被正確復制的問題闺阱。
  • 缺點：會產(chǎn)生大量的日志炮车，尤其是alter table會記錄所有行的變更細節(jié)。

• statement：記錄更新操作的sql語句酣溃。

  • 優(yōu)點：相對row格式瘦穆，減少了日志，節(jié)約IO赊豌，提高性能扛或。
  • 缺點：為了保證sql語句能在slave上正確執(zhí)行，必須記錄上下文信息亿絮，以保證所有語句能在slave得到和在master端執(zhí)行時候相同的結果告喊；另外麸拄，主從復制時派昧，存在部分函數(shù)（如sleep）及存儲過程在slave上會出現(xiàn)與master結果不一致的情況。

• mixed：混合格式拢切，實際上是row與statement的結合蒂萎。mysql會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在statement和row之間選擇一種淮椰。

  新版本的mysql對row格式也被做了優(yōu)化五慈，并不是所有的修改都會以row格式來記錄，像遇到表結構變更的時候就會以statement格式來記錄主穗，如果sql語句確實就是update或者delete等修改數(shù)據(jù)的語句泻拦，那么還是會記錄所有行的變更；因此忽媒，現(xiàn)在一般使用row格式即可争拐。

對于row格式來說，mysql5.6新增binlog_row_image減少了DML操作產(chǎn)生的日志量：

• 舊版邏輯：當表含有blob或者text類型的字段晦雨，update的時候架曹，即使不更新這些字段，也會被記錄到binlog中闹瞧，會導致日志龐大绑雄。
• 5.6新版邏輯：binlog_row_image提供full、minimal奥邮、nolob三個可選值万牺。
  • full：記錄所有字段信息罗珍，跟舊版一樣。
  • minimal：只記錄被修改的列信息杏愤。
  • nolob：記錄除了blob和text以外的所有字段靡砌。

相關命令及配置

開啟binlog

首先查看mysql binlog配置情況 show variables like '%log_bin%';

image

查看binlog當前格式

show variables like '%binlog_format%';

image

查看所有binlog日志列表

show master logs;

image

最新binlog日志

show master status;

image

查看某個binlog日志內(nèi)容

show binlog events in 'binlog.000003' from 3 limit 2;

image

另外一種方式是使用mysqlbinlog工具厕宗，可以看到更加詳細的信息：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/binlog.000003