mysql binlog詳細介紹

Mysql binlog詳解

1、 mysql binlog的型別：

mysql的binlog按照生成方式，可以分為三種，分別是：

1）基於記錄的複製RBR(Row Based Replication) 或Row：

優點：binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程，或function，以及trigger的呼叫和觸發無法被正確複製的問題。

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

2）基於語句的複製，簡稱SBR(Statement Based Replication) 或Statement：

相比row能提高效能，減少日誌量。但是這個是取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量會小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該根據應用的實際情況，考慮其所產生的日誌量會增加多少，以及帶來的IO效能問題。

優點：可以對任何語句都能正確工作，不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能，一些語句的效率更高。例如，一個更新GB的資料的查詢僅需要幾十個位元組的二進位制日誌。

缺點：就是二進位制日誌可能會很大，而且不直觀，所以，你不能使用mysqlbinlog來檢視二進位制日誌。而且由於記錄的只是執行語句，為了這些語句能在slave上正確執行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同的結果。另外mysql 的複製,像一些特定函式功能，slave可與master上要保持一致會有很多相關問題(如sleep()函式， last_insert_id()，以及user-defined functions(udf)會出現問題)。此外，儲存過程和觸發器也是一個問題。另外一個問題就是基於語句的複製必須是序列化的。這要求大量特殊的程式碼，配置，例如InnoDB的next-key鎖等。並不是所有的儲存引擎都支援基於語句的複製。

使用以下函式的語句也無法被複制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

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

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

3）混合方式MBR(Mixed Based Replication)：

由於兩種方式不能對所有情況都能很好的處理，所以，MySQL 5.1以上支援在基於語句的複製和基於記錄的複製之前動態交換。可以透過設定session變數binlog_format來進行控制。

2、Binlog日誌格式選擇

Mysql預設是使用Statement日誌格式，推薦使用MIXED.

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

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

3、Binlog相關引數

如以下：

binlog_format = MIXED   //binlog日誌格式，可以選擇為mixed，statement，row

log_bin =目錄/mysql-bin.log    //binlog日誌名

expire_logs_days = 7                //binlog過期清理時間

max_binlog_size = 100m                    //binlog每個日誌檔案大小

binlog-do-db = 需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可

binlog-ignore-db = 不需要備份的資料庫苦命，如果備份多個資料庫，重複設定這個選項即可

4、binlog相關檔案

mysql-bin.index：

用於跟蹤磁碟上存在哪些二進位制日誌檔案。MySQL用它來定位二進位制日誌檔案。

mysql-relay-bin.index：

該檔案的功能與mysql-bin.index類似，但是它是針對中繼日誌，而不是二進位制日誌。

master.info：

儲存master的相關資訊。不要刪除它，否則，slave重啟後不能連線master。

relay-log.info：

包含slave中當前二進位制日誌和中繼日誌的資訊。

5、binlog日誌內容解析

1)在mysql命令介面中檢視時：

如果是statement模式：

mysql> show binlog events in 'mysql-bin.000021'\G;

擷取部分查詢結果：

*************************** 20. row ***************************

Log_name: mysql-bin.000021  -----------------------> 查詢的binlog日誌檔名

Pos: 11197 ------------------------------------------------------------> pos起始點:

Event_type: Query ----------------------------------------------> 事件型別：Query

Server_id: 1 -------------------------------------------> 標識是由哪臺伺服器執行的

End_log_pos: 11308 ------------------> pos結束點:11308(即：下行的pos起始點)

Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5')

---> 執行的sql語句

*************************** 21. row ***************************

Log_name: mysql-bin.000021

Pos: 11308 -----------------------------> pos起始點:11308(即：上行的pos結束點)

Event_type: Query

Server_id: 1

End_log_pos: 11417

Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */

*************************** 22. row ***************************

Log_name: mysql-bin.000021

如果是row模式：

mysql> show binlog events in 'mysql-bin.000005'\G;

擷取部分查詢結果：

*************************** 2. row ***************************

Log_name: mysql-bin.000005

Pos: 120

Event_type: Query

Server_id: 3

End_log_pos: 191

Info: BEGIN

*************************** 3. row ***************************

Log_name: mysql-bin.000005

Pos: 191

Event_type: Table_map

Server_id: 3

End_log_pos: 234

Info: table_id: 87 (lxm.t)    ---> 這裡看不到執行的sql語句，只能看到表名

*************************** 4. row ***************************

Log_name: mysql-bin.000005

Pos: 234

Event_type: Update_rows

Server_id: 3

End_log_pos: 280

Info: table_id: 87 flags: STMT_END_F

*************************** 5. row ***************************

Log_name: mysql-bin.000005

Pos: 280

Event_type: Xid

Server_id: 3

End_log_pos: 311

Info: COMMIT /* xid=249 */

*************************** 6. row ***************************

Log_name: mysql-bin.000005

      

2)用mysqlbinlog工具檢視時：

如果是statement模式：

# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013

  下面擷取一個片段分析：

.............................................................................................................

# at 552

#131128 17:50:46 server id 1  end_log_pos 665   Query   thread_id=11    exec_time=0     error_code=0        ---->執行時間:17:50:46；pos點:665

  SET TIMESTAMP=1385632246/*!*/;

  update zyyshop.stu set name='李四' where id=4        ---->執行的SQL

  /*!*/;

# at 665

#131128 17:50:46 server id 1  end_log_pos 692   Xid = 1454 ---->執行時間:17:50:46；pos點:692

.............................................................................................................

注: server id 1         資料庫主機的服務號；

    end_log_pos 665  pos點

    thread_id=11      執行緒號

如果是row模式：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#161022 15:59:31 server id 3  end_log_pos 120 CRC32 0x45d9e7a2  Start: binlog v 4, server v 5.6.24-log created 161022 15:59:31

BINLOG '

YxwLWA8DAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaLn

2UU=

'/*!*/;

# at 120

#161022 16:00:57 server id 3  end_log_pos 191 CRC32 0x37e11f27  Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1477123257/*!*/;

SET @@session.pseudo_thread_id=1/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1073741824/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 191

#161022 16:00:57 server id 3  end_log_pos 234 CRC32 0xe2ba303b  Table_map: `lxm`.`t` mapped to number 87

# at 234

#161022 16:00:57 server id 3  end_log_pos 280 CRC32 0xdae765d4  Update_rows: table id 87 flags: STMT_END_F

.............................................................................................................

是看不到明文的DML語句。

6、常用binlog日誌操作命令

@檢視所有binlog日誌列表

mysql> show master logs;或者show binary logs;

@檢視master狀態，即最後(最新)一個binlog日誌的編號名稱，及其最後一個操作事件pos結束點(Position)值

mysql> show master status;

@重新整理log日誌，自此刻開始產生一個新編號的binlog日誌檔案

mysql> flush logs;

注：每當mysqld服務重啟時，會自動執行此命令，重新整理binlog日誌；在mysqldump備份資料時加 -F 選項也會重新整理binlog日誌；

@重置(清空)所有binlog日誌

mysql> reset master;

7、如何檢視binlog日誌的內容

1)在mysql命令介面中檢視：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這條語句可以將指定的binlog日誌檔案，分成有效事件行的方式返回，並可使用limit指定pos點的起始偏移，查詢條數；

選項解析：

IN 'log_name'   指定要查詢的binlog檔名(不指定就是第一個binlog檔案)

FROM pos   指定從哪個pos起始點開始查起(不指定就是從整個檔案首個pos點開始算)

LIMIT [offset,]   偏移量(不指定就是0)

row_count       查詢總條數(不指定就是所有行)

示例：

A.查詢第一個(最早)的binlog日誌：

    mysql> show binlog events\G;

B.指定查詢 mysql-bin.000021 這個檔案：

    mysql> show binlog events in 'mysql-bin.000021'\G;

C.指定查詢 mysql-bin.000021 這個檔案，從pos點:8224開始查起：

    mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

D.指定查詢 mysql-bin.000021 這個檔案，從pos點:8224開始查起，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

E.指定查詢 mysql-bin.000021 這個檔案，從pos點:8224開始查起，偏移2行，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

2) 使用mysqlbinlog：

binlog是二進位制檔案，普通檔案檢視器cat、more、vi等都無法開啟，必須使用自帶的 mysqlbinlog 命令檢視。binlog日誌與資料庫檔案在同目錄中。

在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “--no-defaults”選項。

a)如果是ROW模式的二進位制日誌檔案，為了檢視mysql具體執行了什麼樣的sql語句，需要使用-v（--verbose）選項，該選項會將行事件重構成被註釋掉的偽SQL語句，如果想看到更詳細的資訊可以將該選項給兩次如-vv，這樣可以包含一些資料型別和元資訊的註釋內容。例如：

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

b)mysqlbinlog和可以透過--read-from-remote-server選項從遠端伺服器讀取二進位制日誌檔案，這時需要一些而外的連線引數，如--host，--password ，--port，--user，--socket，--protocol等，這些引數僅在指定了--read-from-remote-server後有效。

c)無論是本地二進位制日誌檔案還是遠端伺服器上的二進位制日誌檔案，無論是行模式、語句模式還是混合模式的二進位制日誌檔案，被mysqlbinlog工具解析後都可直接應用與MySQL Server進行基於時間點、位置或資料庫的恢復。

常見引數有:

1)   --database=db_name, -d db_name

該引數使mysqlbinlog僅從本地二進位制日誌中輸出指定的db_name被use命令選作預設資料庫時產生的日誌事件。行為類似於mysqld的--binlog-do-db命令。若該引數指定了多次那麼只有最後一次指定的內容有效。引數具體的影響依賴於二進位制日誌格式，只有在使用行模式的日誌格式時該引數才能保證一致性。基於語句或混合模式的二進位制日誌格式中因為可能存在跨庫的更新導致--database參數列現不同的行為，從而不能保證資料一致性。例如：

mysqlbinlog  mysql-bin.000001  -d testDB | mysql -uusername -p

2)   --force-read, -f

使用了該引數後mysqlbinlog工具在讀取到不能識別的日誌事件時會列印出warning，忽略事件並繼續執行，沒有此引數的情況下mysqlbinlog會停止。

mysqlbinlog  mysql-bin.000001  -d testDB -f | mysql -uusername -p

3)   --no-defaults

阻止mysqlbinlog工具從任何配置檔案讀取引數，.mylogin.cnf除外（以便於安全的儲存密碼）

mysqlbinlog mysql-bin.000001 -d testDB -f --no-defaults| mysql -uusername -p

4) --start-datetime=datetime和--stop-datetime=datetime

這兩個引數用於指定恢復開始時間點和結束時間點，可以一起或單獨給出，也可與--start-position，--stop-position混用。

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p

5) --start-position=N, -j N和--stop-position=N

上邊一組引數用於指定恢復開始位置和結束位置，可以一起或單獨給出也可與--start-datetime，--stop-datetime混用

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

d)如果需要還原的二進位制日誌檔案不止一個，安全的方式是多個二進位制檔案同時執行。

mysqlbinlog  mysql-bin.000001  mysql-bin.000002 mysql-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或

mysqlbinlog  mysql-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

當多個二進位制日誌檔案同時執行時，--start-position和--stop-position分別只應用於第一個列出的二進位制日誌檔案和最後一個列出的二進位制日誌檔案

     當然也可以先將多個二進位制日誌檔案的輸出導到同一個.sql檔案最後在執行該.sql檔案（適用於日誌量不多的情況）。

8、binlog的應用：

可以用binlog來恢復誤操作的資料。

案例：

1)全備份

mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql

備份時使用-F選項，意味著備份工作剛開始時就會重新整理log日誌，產生新的binlog日誌來記錄備份之後的資料庫的“增刪改”操作。

2)備份之後，業務對資料庫進行了大量的增刪改查操作。然後資料庫有張表被誤刪除了。此刻立即檢視最後一個binlog日誌，記錄下關鍵的pos點，即是在哪個點上的操作導致了資料庫的破壞。然後flush logs，讓mysql重新開始新的binlog日誌記錄檔案。從理論上講，此時舊的binlog日誌是不會被繼續寫入了。此時，備份舊的binlog日誌。

3)讀取舊的binlog日誌，分析問題。

方式一，用mysqlbinlog命令來讀取binlog日誌：

     mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023

方式二，在mysql伺服器中檢視：

     mysql> show binlog events in 'mysql-bin.000023';

在輸出中找到誤刪除表的確切pos點。

4)首先用全備份進行恢復：

mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;

5)從binlog日誌中恢復資料：

mysqlbinlog mysql-bin.0000xx | mysql -u使用者名稱 -p密碼 資料庫名

總結：所謂恢復，就是讓mysql將儲存在binlog日誌中指定段落區間的sql語句逐個重新執行一次而已。