MySQL二進位制日誌

二進位制日誌簡介

二進位制日誌記錄資料庫的變化，如建立表操作，表資料增刪修改，以及可能會造成資料修改的事件（如沒有匹配行的DELETE語句，除非使用ROW-base日誌格式），及更新資料消耗的時長。二進位制檔案不記錄SELECT、SHOW等不會修改資料的語句。

二進位制日誌的兩個重要作用：

• 用於複製，主庫提供資料更改的日誌記錄將被髮送到從庫，從庫執行同樣的操作保證主從資料一致。
• 根據二進位制日誌進行資料恢復，在備份恢復後，可從備份點重新執行二進位制日誌恢復到最新時間。

切換二進位制日誌檔案：

• 啟動MySQL伺服器
• 使用flush logs重新整理日誌
• 當前二進位制日誌檔案大小大於max_binlog_size，當事務比較大時，同一事務只能寫入到同一個檔案中，不能分割檔案寫入，因此可能出現二進位制日誌檔案比max_binlog_size大。

二進位制日誌格式

• STATEMENT
  預設的MySQL的二進位制日誌格式，基於此複製稱為statement-based replication(SBR)

• ROW
  基於此複製稱為row-based replication(RBR)

• MIXED
  基於此複製稱為mixed-based replication(MBR)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.04 sec)

修改binlog_format需要SUPER許可權
mysql> SET GLOBAL binlog_format = 'ROW';

除了手動切換日誌格式，Slave也可能自動切換。當主庫使用ROW格式時，備庫使用STATEMENT或MIXED，
備庫將臨時對該事務切換到ROW格式，複製完成將重新切換回之前格式。

會話修改二進位制日誌格式的情況：

• 會話中大量更新較小的語句可能使用基於ROW的記錄
• 執行匹配許多行的WHERE子句的更新適合使用基於STATEMENT的記錄
• 更新語句執行時間較長，但只有少數行記錄被更新適合使用基於ROW的記錄

下列情況將不可執行時修改二進位制日誌格式，如修改將報錯：

• 在儲存函式或者觸發器中修改
• NDB儲存引擎啟用
• 當前會話使用 RBR 模式，並且已開啟了臨時表

當使用InnoDB表且事務隔離級別是READ COMMITTED 或 READ UNCOMMITTED時，只能使用ROW格式。
如果將執行時修改日誌格式為STATEMENT，將會報錯因為InnoDB表不能插入操作。
當存在temporary表時，執行時不建議修改複製格式，因為臨時表只會當SBR是才會寫入日誌，RBR不會
寫入，MBR也會寫入。
當二進位制格式為ROW時，記錄大量修改的語句仍可使用STATEMENT格式,如DDL語句CREATE TABLE、ALTER
TABLE、DROP TABLE。

當使用MIXED，下列情況將會自動從STATEMENT切換ROW:

• 函式中包含UUID()
• 表中AUTO_INCREMENT列被更新，並在觸發器或儲存函式中呼叫。
• 當檢視建立需要依賴於RBR，如建立檢視語句包含UUID()函式
• 呼叫UDF
• 非事務表執行INSERT DELAYED語句
• 如果一個會話存在臨時表且語句使用ROW格式，則後續執行語句都將使用ROW(除訪問臨時表)，除非該會話中的臨時表被刪除。
• 當FOUND_ROWS() 或 ROW_COUNT()函式使用
• 當USER(), CURRENT_USER() 或 CURRENT_USER使用
• 當語句是指一個或多個系統變數
• 當LOAD_FILE()函式使用

mysql庫的日誌格式

• 直接修改mysql資料庫表中資料的語句的日誌格式根據binlog_format設定值，如INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, TRUNCATE TABLE。
• 直接修改mysql資料庫表中資料的語句的日誌格式使用STATEMENT而忽略binlog_format設定的值。如GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms)。

CREATE TABLE ... SELECT是DDL與DML的組合。CREATE TABLE使用STATEMENT格式而SELECT部分根據binlog_format設定的值

兩者模式的優缺點

SBR 的優點：

• 從3.23已開始存在，歷史悠久，技能成熟
• binlog檔案較小，當DELETE或UPDATE影響多行，日誌檔案佔用較小的儲存空間。
• binlog中包含了所有資料庫修改資訊，可以據此來稽核資料庫
• 主從版本可以不一樣，從伺服器版本可以比主伺服器版本高

SBR 的缺點：

• SBR中不安全的語句
  並非所有更新資料的語句都會在SBR中複製，尤其是包含不確定操作的時候。
  如下列DML語句：
  • 呼叫具有不確定因素的 UDF 或儲存過程，如程式的返回值取決於多種因素而不僅僅包含入參
  • DELETE或UPDATE語句使用LIMIT但不使用ORDER BY也是不確定語句
  • 運用以下函式的語句也不能基於SBR複製：
    • LOAD_FILE()
    • UUID(), UUID_SHORT()
    • USER()
    • FOUND_ROWS()
    • SYSDATE() (除非主從啟動時均指定了 --sysdate-is-now 選項)
    • GET_LOCK()
    • IS_FREE_LOCK()
    • IS_USED_LOCK()
    • MASTER_POS_WAIT()
    • RAND()
    • RELEASE_LOCK()
    • SLEEP()
    • VERSION()

當出現如下警告時，語句不能根據SBR正確複製

 [Warning] Statement is not safe to log in statement format.

• INSERT ... SELECT 會產生比 RBR 更多的行級鎖
• UPDATE語句全表掃描(WHERE子句沒有使用索引)，比 RBR 產生更多的行級鎖
• 對於有 AUTO_INCREMENT 列的InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
• 對於複雜的語句，會在從伺服器上重新執行，而 RBR 模式下，只會對那個發生變化的記錄產生影響
• 如果從庫產生錯誤，尤其是執行復雜語句時，SBR經過一段時間逐漸會增大主從資料誤差範圍。
• 儲存函式在被呼叫的同時也會執行一次 NOW() 函式
• 確定性的 UDF 也必須在從伺服器上執行
• 表定義必須主從庫保持一致才行，否則可能會導致複製出錯

RBR 的優點：

• 所有改變都可以被複制，這對複製來說是最安全的形式
• 和其他大多數資料庫系統的複製原理類似
• 多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多
  複製以下幾種語句時的行鎖減少，可提高併發性：
  • INSERT ... SELECT
  • 包含 AUTO_INCREMENT 欄位的 INSERT
  • WHERE子句不包含索引列或者修改很少條記錄的 UPDATE 或 DELETE 語句
• 從庫執行 INSERT，UPDATE，DELETE 語句需要較少鎖資源

RBR 的缺點：

• RBR容易記錄更多的資料
• 二進位制日誌被鎖定更長時間來寫入資料，可能導致併發問題
• UDFs產生大量BLOB值會導致複製變慢
• 不能檢視執行過什麼語句，也不能看到從庫接收執行什麼語句
  可使用mysqlbinlog指定--base64-output=DECODE-ROWS 和 --verbose檢視執行語句
• 對於MyISAM表，從庫基於RBR重新執行INSERT語句時鎖粒度較大。因此使用RBR不支援對MyISAM表的併發插入

整理自網路