MySQL二進位日誌

標籤：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 Querythread_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‘;

MySQL二進位日誌