MySQL——binlog

一、binlog簡介：

1、什么是binlog：

   binlog日志用于記錄所有更新了數據或者已經潛在更新了數據（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式保存，它描述數據更改。

2、binlog的記錄格式：

Mysql binlog日志有三種格式，分別為：Statement ,MiXED ,和ROW；

  （在MySQL5.7.7版本之后，把binlog_format的默認值修改成了ROW。master將修改表的event寫入binlog中，并且master將binlog信息發送到slave，slave重放binlog中event。基于ROW格式復制是最安全的復制，slave需要的行鎖更少；但是也有一些缺點，那就是基于ROW格式的復制，binlog會記錄更多的數據。并且無法在slave上看到從master上獲取的語句，因為都是event。但是在ROW格式下，可以開啟binlog_rows_query_log_events參數，這會讓binlog在記錄events同時，也記錄原始的sql語句，以方便后續的查詢；）

①：Statement：每一條會修改數據的sql都會記錄在binlog中。

優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。(相比row能節約多少性能與日志量，這個取決于應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日志量還小于Statement產生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日志，因此在考慮是否使用ROW格式日志時應該跟據應用的實際情況，其所產生的日志量會增加多少，以及帶來的IO性能問題。)

缺點：由于記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的復制,像一些特定函數功能，slave可與master上要保持一致會有很多相關問題(如sleep()函數， last_insert_id()，以及user-defined functions(udf)會出現問題).

使用以下函數的語句也無法被復制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

------同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖

②：Row: 不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。

優點： binlog中可以不記錄執行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以row level的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題

缺點: 所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日志量會很大，特別是當執行alter table之類的語句的時候，由于表結構修改，每條記錄都發生改變，那么該表每一條記錄都會記錄到日志中。

③：Mixedlevel: 是以上兩種level的混合使用，

  一般的語句修改使用statment格式保存binlog，如一些函數，statement無法完成主從復制的操作，則采用row格式保存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至于update或者delete等修改數據的語句，還是會記錄所有行的變更。

二、Binlog基本配制與格式設定

1、基本配制

Mysql BInlog日志格式可以通過mysql的my.cnf文件的屬性binlog_format指定。如以下：

binlog_format = ROW             ---binlog日志格式
log_bin =/mysql/mysql-bin.log        ---binlog日志名
expire_logs_days = 7            ---binlog過期清理時間
max_binlog_size 100m            ---binlog每個日志文件大小

2.Binlog日志格式選擇

Mysql默認是使用Statement日志格式，推薦使用ROW.

  由于一些特殊使用，可以考慮使用ROWED，如自己通過binlog日志來同步數據的修改，這樣會節省很多相關操作。對于binlog數據處理會變得非常輕松,相對mixed，解析也會很輕松(當然前提是增加的日志量所帶來的IO開銷在容忍的范圍內即可)。

3.mysqlbinlog格式選擇

mysql對于日志格式的選定原則:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那么無論如何 都采用 SBR 模式記錄。

三、binlog的相關參數：

--log_bin：
設置此參數表示啟用binlog功能，并指定路徑名稱

--log_bin_index
設置此參數是指定二進制索引文件的路徑與名稱

--binlog_do_db
此參數表示只記錄指定數據庫的二進制日志

--binlog_ignore_db
此參數表示不記錄指定的數據庫的二進制日志

--max_binlog_cache_size
此參數表示binlog使用的內存最大的尺寸

--binlog_cache_size
此參數表示binlog使用的內存大小，可以通過狀態變量binlog_cache_use和binlog_cache_disk_use來幫助測試。
    binlog_cache_use：使用二進制日志緩存的事務數量
    binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時文件來保存事務中的語句的事務數量

--max_binlog_size
Binlog最大值，最大和默認值是1GB，該設置并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進當前日志，直到事務結束

--sync_binlog
這個參數直接影響mysql的性能和完整性

--sync_binlog=0：
當事務提交后，Mysql僅僅是將binlog_cache中的數據寫入Binlog文件，但不執行fsync之類的磁盤        同步指令通知文件系統將緩存刷新到磁盤，而讓Filesystem自行決定什么時候來做同步，這個是性能最好的。

--sync_binlog=n，在進行n次事務提交以后，Mysql將執行一次fsync之類的磁盤同步指令，同志文件系統將Binlog文件緩存刷新到磁盤。
注意：Mysql中默認的設置是sync_binlog=0，即不作任何強制性的磁盤刷新指令，這時性能是最好的，但風險也是最大的。一旦系統繃Crash，在文件系統緩存中的所有Binlog信息都會丟失

四、binlog的刪除：

  binlog的刪除可以手工刪除或自動刪除

1、自動刪除binlog

通過binlog參數（expire_logs_days ）來實現mysql自動刪除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days';
mysql> set global expire_logs_days=3;

2、手工刪除binlog

mysql> reset master;       ----刪除master的binlog
mysql> reset slave;        ----刪除slave的中繼日志
mysql> purge master logs before '2017-03-30 17:20:00';   -----刪除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002';     ----刪除指定日志文件的日志索引中binlog日志文件或者直接用操作系統命令直接刪除
mysql> set sql_log_bin=1/0;    ----如果用戶有super權限，可以啟用或禁用當前會話的binlog記錄
mysql> show master logs;     ---查看master的binlog日志 
mysql> show binary logs;     ---查看master的binlog日志
mysql> show master status;   ---用于提供master二進制日志文件的狀態信息
mysql> show slave hosts;     ---顯示當前注冊的slave的列表。不以--report-host=slave_name選項為開頭的slave不會顯示在本列表中

3、binglog的查看

通過mysqlbinlog命令可以查看binlog的內容

[root@localhost ~]# mysqlbinlog  /home/mysql/binlog/binlog.000003  | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1  end_log_pos 294   Query   thread_id=3     exec_time=2     error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1  end_log_pos 388   Query   thread_id=3     exec_time=28    error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

3.1、解析binlog格式

--位置

位于文件中的位置，“at 196”說明“事件”的起點，是以第196字節開始；“end_log_pos 294”說明以第294字節結束

--時間戳

事件發生的時間戳：“120330 17:54:46”

--事件執行時間

事件執行花費的時間:"exec_time=28"

--錯誤碼

錯誤碼為：“error_code=0”

--服務器的標識

服務器的標識id：“server id 1”

注意：

1、binlog事件中的時間戳是從語句那里繼承過來的，一條語句產生多個事件，那這些事件的時間戳都是一樣的，而且都是和第一個事件一致的；

2、有時候我們會在binlog中發現執行語句和提交時間不一致的情況，是因為有些事務是自動提交的，在這個事務中只有一條語句執行了一定的時間；

innodb中rowid對binlog的影響：

1、在galera cluster上面最好不要讓一個事務更新太多的數據，可以適當控制在1萬行以內，都是么有問題的，因為galera cluster的驗證和提交都是串行    的，一個事務太大，會導致集群其他事務都等待這個事務完成造成集群假死現象；

2、在innodb中如果沒有指定主鍵，就會創建一個rowid，但是MySQL的binlog是server層，而innodb中的rowid是存儲引擎的東西，server層根本感知不到      rowid的存在；

3、rowid是innodb自己定義的一個列，只有在表中沒有定義主鍵的時候，系統才會給這個表加上這一列，但這一列只是為了存儲，構成一個聚簇索引，但不    會暴露給邏輯層，并且上層也用不到它，所以直接忽略它即可；

4、在MySQL數據庫的使用中，一定要定義主鍵，如果沒有主鍵，并且是row模式的復制，就必然會造成這樣的問題，而不像其他數據庫一樣，如果沒有主鍵，    還可以使用rowid來操作表；

5、在galera cluster中，更要定義主鍵。如果沒有定義，必然會造成故障，這不是galera cluster的問題，只是它會把這個問題放大而已；