MySQL二進制日志

二進制日志相關的伺服器變量：

sql_log_bin = {ON|OFF}   是否記錄二進制日志。啟用二進制記錄日志，則這項必須是ON

有時候，我們想執行的某些語句不要同步到slave上，可以執行set sql_log_bin=OFF;暫時關閉二進制記錄功能。操作完後再啟用二進制日志功能即可。

sql_log_off={ON|OFF}    用于控制是否禁止将一般查詢日志類資訊記錄進查詢日志檔案。預設為OFF，表示不禁止記錄功能。使用者可以在會話級别修改此變量的值，但其必須具有SUPER權限。作用範圍為全局和會話級别，屬動态變量。

log_bin = mysql-bin  記錄的檔案位置。通常為資料所在的目錄

binlog_format = {MIXED|row|statement}　　二進制日志的記錄格式

max_binlog_size = 1073741824   二進制日志檔案的單檔案上限 (機關:位元組) 

【超出則自動滾動日志。注意： (1) 到達最大值會自動滾動  (2) 檔案達到上限時的大小未必為指定的精确值】

max_binlog_cache_size = 18446744073709547520

max_binlog_stmt_cache_size = 18446744073709547520   # 異步寫入磁盤的日志檔案大小

sync_binlog = 0|1    設定多久同步一次二進制日志至磁盤檔案中，0表示不同步，任何正數值都表示對二進制每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進制日志同步，否則，每個事務的送出會引起二進制日志同步。

二進制日志的檢視指令：

mysqlbinlog

-j, --start-position=#：從指定的事件位置檢視

--stop-position=#：隻顯示到指定的事件位置

--start-datetime="YYYY-MM-DD hh:mm:ss"

--stop-datetime="YYYY-MM-DD hh:mm:ss"

例如：

mysqlbinlog -uroot -h 172.16.20.71 -p passwd /data/mysql/mysql-bin.000008   [通路遠端資料庫]

mysqlbinlog --start-position=515 --stop-position=616  /data/mysql/mysql-bin.000008

mysqlbinlog -uroot -proot /data/nysql/mysql-bin.000060 --start-datetime="2016-04-11 00:00:01" --stop-datetime="2016-04-11 23:59:59"

mysqlbinlog -uroot -proot --database=Mobile /data/mysql/mysql-bin.000008  --start-datetime="xxx" --stop-datetime="xxx" # 僅列出Mobile的相關二進制日志

說明：如果在備份時候報錯 “'Sanity check failed', Could not read entry at offset 1009: Error in log format or read error.” 這種情況是由于mysqlbinlog的版本和MySQL伺服器的版本不一樣導緻的，一般是因為安裝過多個版本的MySQL系統将mysqlbinlog識别成了老的版本，是以隻要用mysqlbinlog的全局路徑即可。

二進制日志事件的格式：

# at 328

#151105 16:31:40 server id 1  end_log_pos 431  Query thread_id=1  exec_time=0  error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1446712300/*!*/;

CREATE TABLE tb1  (id int, name char (30) ) 

/*!*/;

事件發生的日期和時間：151105 16:31:40

事件發生的伺服器辨別：server id 1 【在雙主模型上，可用來識别是否是自己曾發出去的日志，避免死循環】

事件的結束位置：end_log_pos 431

事件的類型：Query

事件發生時所在伺服器執行此事件的線程的ID：thread_id=1

語句的時間戳與将其寫入二進制檔案中的時間差：exec_time=0

錯誤代碼：error_code=0

事件内容：

GTID：Global Transaction ID;  全局事務ID

專屬屬性：GTID

清理二進制日志的方法：

> show binary logs;

> purge binary logs to 'binlog.000030';

本文轉自 lirulei90 51CTO部落格，原文連結：http://blog.51cto.com/lee90/1783343，如需轉載請自行聯系原作者