mysql---二進位日誌

標籤：ast   開始   預存程序   命令   恢複   eve   minimal   res   開啟   

記錄了所有對MySQL資料庫的修改事件,包括增刪改查事件和對錶結構的修改事件

二進位日誌的開啟

#是否啟用了日誌mysql> show variables like ‘log_bin‘; +---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+[mysqld]log-bin [=DIR \ [filename]]log_bin=mysql-bin 名字可以隨便起(mysql-bin)

格式

binlog_format={STATEMENT|ROW|MIXED}

1. 語句(statement)：預設的記錄格式,基於段
2. 行(row)：定義的並非資料本身而是這一行的資料是什麼
3. 混合模式(mixed)：交替使用行和語句、由mysql伺服器自行判斷

基於段的日誌格式優點

1. 日誌記錄量相對較小,節約磁碟及網路IO
2. 只對一條記錄修改或者插入,row格式所產生的日誌量小於段產生的日誌量

缺點

1. 必須要記錄上下文資訊,保證語句在從伺服器上執行結果與主伺服器相同
2. 特定函數如UUID(),user()這樣非確定性函數還是無法複製；可能造成MySQL複製的主備伺服器資料不一致

操作

mysql> show variables like ‘binlog_format‘;mysql> set session binlog_format=statement;mysql> show binary logs; mysql> flush logs;

行日誌格式

1. 5.7版本的預設格式
2. Row格式可以避免Mysql複製中出現的主從不一致問題

同一SQL語句修改了10000條資料的情況下

• 基於段的日誌格式只會記錄這個SQL語句
• 基於行的日誌會有10000條記錄分別記錄每一行的資料修改

優點

1. 使MySQL主從複製更加安全
2. 對每一行資料的修改比基於段的複製高效

誤操作而修改了資料庫中的資料,同時又沒有備份可以恢複時,我們就可以通過分析二進位日誌,對日誌中記錄的資料修改操作做反向處理的方式來達到恢複資料的目的

缺點

1. 記錄日誌量較大；binlog_row_image=[FULL|MINIMAL|NOBLOB]

混合日誌格式

binlog_format=MIXED

特點

• 根據SQL語句由系統決定基於段和基於行的日誌格式中進行選擇
• 資料量的大小由所執行的SQL語句決定

二進位日誌格式對複製的影響基於SQL語句(statement)複製(SBR)優點

1. 產生的日誌量少,節約網路傳輸IO
2. 並不強制要求主從資料庫的表定義完全相同
3. 相比於基於行的複製方式更為靈活

缺點

1. 對於非確定性事件,無法保證主從複製資料的一致性
2. 對於預存程序,觸發器,自訂函數進行的修改也可能造成資料不一致
3. 相比於基於行的複製方式在從上執行時需要更多的行鎖

基於行的複製(PBR)優點

1. 可以應用於任何SQL的複製包括非確定函數,預存程序等
2. 可以減少資料庫鎖的使用
3. 對主從資料的一致性更加有保證

缺點

1. 要求主從資料庫的表結構相同,否則可能會中斷複製
2. 無法在從上單獨執行觸發器

查看二進位日誌資料庫會話查看

#顯示當前伺服器使用的二進位檔案及大小mysql> show binary logs#顯示主伺服器使用的二進位檔案及大小mysql> show master logs;#當前使用的二進位檔案及所處位置mysql> show master status#lush logs一般只會滾動中繼日誌和二進位日誌mysql> flush logs;#二進位日誌的記錄位置,通常為上一個事件執行結束時間的位置mysql> showmaster status;#...資料庫的增刪改查#觀察二進位位置的變化mysql> show master status;#查看所有的二進位資訊mysql> show binlog events\G;#查看指定日誌的二進位資訊mysql> show binlog events in ‘mysql-bin.000011‘; #從指定的事件位置開始mysql> show binlog events in ‘mysql-bin.000011‘ from 190;#指定位移量(不是語句，是事件)mysql> showbinlog events in ‘mysql-bin.000011‘ from 190 limit 3;

命令列mysqlbinlog

#必須在資料目錄下mysqlbinlog mysql-bin.000017#匯出此資料庫的資訊  mysqlbinlog mysql-bin.000017 > /tmp/a.sql#匯入此資料庫的資訊mysql < a.sqlmysqlbinlog -vv 日誌名

二進位日誌刪除

長時間不清理會浪費很多的磁碟空間,但是刪除後可能導致資料庫崩潰無法進行恢複.

若要刪除二進位日誌首先將其和Database Backup一份

#刪除所有的二進位日誌（不可效仿）mysql> reset master; #根據檔案或時間點來刪除二進位日誌# TO ‘log_name‘ 刪除檔案之前的所有檔案 mysql> PURGE { BINARY | MASTER } LOGS {TO ‘log_name‘ | BEFORE datetime_expr }#使用時間來刪除二進位日誌mysql> PURGEBINARY LOGS BEFORE ‘13-10-19 10:26:36‘; 

mysql---二進位日誌