MySQL Binary Log

binary log包含用來描述資料庫改變比如表建立操作或表資料改變的事件資訊。除非使用基於行的日誌，它還可能包含潛在可能發生改變的語句(例如，delete沒有匹配的行記錄)。binary log也包含關於每個語句更新資料所花費的時間。binary log有兩個重要的目的:
.用於複製，主複製伺服器上的binary log提供了被髮往從伺服器的資料改變的記錄。主伺服器將binary log中包含的事件傳送給它的從伺服器，從伺服器執行這些事件使用相同的資料改變應用到從伺服器。

.用於需要使用binary log來執行的特定恢復操作。在一個備份還原之後，在binary log中的事件記錄了在備份之後所發生的改變，使用binary log來重新執行這些改變。這些事件從備份時間點起記錄了資料庫的更新。

當啟用binary log後會使用伺服器的效能稍稍有所下降。然而，binary log的好處就是能讓你配置複製並且對於恢復操作來說比起這稍稍的效能下降更重要。

binary log通常對於意外停機具有彈性因為只有完成的事務會被記錄或讀回。寫入binary log的語句中的密碼由伺服器重寫，不會以明文形式出現。

為了啟用binary log，使用--log-bin[=base_name]選項來啟動伺服器。如果base_name沒有指定，預設名字是pid-file選項的值(它的預設名字是主機名)後面跟著-bin。如果給定了基本名，則伺服器將檔案寫入資料目錄中，除非給定的基本名包含一個前導絕對路徑名以指定另一個目錄。建議您顯式地指定一個基本名稱，而不是使用預設的主機名。

如果在日誌名稱中提供副檔名(例如，——log-bin=base_name.extension)，副檔名將被靜默刪除並忽略。

mysqld將數字擴充套件追加到二進位制日誌基名以生成二進位制日誌檔名。每次伺服器建立一個新的日誌檔案時，這個數字都會增加，從而建立一個有序的檔案序列。伺服器每次啟動或重新整理日誌時，都會在這個系列中建立一個新檔案。噹噹前日誌的大小達到max_binlog_size時，伺服器還會自動建立一個新的二進位制日誌檔案。如果使用大事務因為一個事務是以一個片段寫入檔案而不會跨檔案寫入所以binary log檔案可能會超過max_binlog_size的大小。

為了保持對那些被使用的binary log檔案的跟蹤，mysqld也會建立一個binary log索引檔案它包含所有被使用的binary log檔案。預設情況下這裡使用基本名作為binary 日誌檔名並帶有'.index'副檔名。你可以使用--log-bin-index[=file_name]選項來改變binary log索引檔名。在mysqld正在執行時不能手動編輯這個檔案。

術語"binary log file"通常表示包含資料庫事件的單獨編號的日誌檔案。術語句"binary log"集體表示一組binary log檔案和索引檔案。

有super許可權的客戶端可以透過使用set sql_log_bin=0來對它的語句禁用binary log。

預設情況下，伺服器會記錄事件的長度以及事件本身，並使用它來驗證事件是否被正確寫入。也可以透過設定binlog_checksum系統變數讓伺服器對事件寫checksums。當從binary log加讀時，主伺服器預設會使用事件長度，但如果啟用了master_verify_checksum系統變數也會使用checksums。從伺服器IO線和也會驗證從主伺服器所接收到的事件。如果啟用了slave_sql_verify_checksum系統變數並可用可以使用從SQL執行緒使用chechsums。

伺服器評估--binlog-do-db和--binlog-ignore-db選項的方式與--replicate-do-db和--replicate-ignore-db選項。

一個複製從伺服器預設情況下不會將從複製主伺服器所接收到的任何資料修改寫入它的binary log。為了記錄這些修改，可以在啟動從伺服器時使用--log-slave-updates選項外加--log-bin選項。在鏈式複製中，當一個從屬節點也充當其他從屬節點的主節點時，將執行此操作。

可以使用reset master語句或purge binary logs來刪除所有binary log檔案。

如果你將使用複製，直到你確定沒有從伺服器仍然需要使用它們之前你將不應該刪除舊的binary log檔案。例如如果你的從伺服器同步沒有落後三天，你可以在主伺服器上執行mysqladmin flush-logs並且刪除任何超過三天的日誌。可以手動刪了日誌檔案，但最好使用purgebinary logs，它能為你安全地更新binary log索引檔案。

在一個語句或事務完成後但在任何鎖被釋放或任何提交完成前會立即寫入binary log。這可以確保日誌以提交順序被記錄。

對非事務性表的更新在執行後立即儲存在二進位制日誌中。

在未提交事務中，對事務表比如InnoDB表的所有更新(update,delete或insert)會被快取直到伺服器收到commit語句為止。在這時，mysqld會在commit執行之前將整個事務寫入binary log。

對於非事務表的修改不能被回滾。如果一個被回滾的事務包含對非事務表的修改，整個事務在最後用ROLLBACK語句記錄，以確保複製了對這些表的修改。

當處理事務的執行緒開始時，它將為binlog_cache_size分配一個buffer來快取語句。如果語句大於這個buffer，執行緒會開啟一個臨時檔案來儲存事務。當執行緒結束時臨時檔案會被刪除。

binlog_cache_use狀態變數顯示了使用這個緩衝區(可能還有一個臨時檔案)來儲存語句的事務數。binlog_cache_disk_use狀態變數顯示了這些事務中有多少事務實際上使用了臨時檔案。這兩個變數可以被用來調整binlog_cache_size的大小使其足夠大避免使用臨時檔案。

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 4194304 |
+-------------------+---------+
1 row in set (0.00 sec)

max_binlog_cache_size系統變數(預設值為4GB，這也是最大值)可以被用來限制用於快取多語句事務的總大小。如果事務大於這麼多位元組，則會失敗並回滾。最小值是4096。

如果將使用binary log和基於行日誌，併發插入比如create ... select或insert ... select語句會被轉換為正常插入。這樣做是為了確保您可以透過在備份操作期間應用日誌來重新建立表的精確副本。如果使用基於語句的日誌記錄，則將原始語句寫入日誌。

預設情況下每次寫入(sync_binlog=1)時binary log會被同步到磁碟。如果sync_binlog沒有啟用，作業系統或機器(不僅僅是MySQL伺服器)崩潰， 二進位制日誌的最後一條語句可能會丟失。為了防止這個問題，啟用sync_binlog系統變數來在每N個提交組之後同步二進位制日誌到磁碟。sync_binlog最安全的值是1(預設值)，但這也是最慢的。

例如，如果您使用InnoDB表，MySQL伺服器處理一個COMMIT語句，它會按順序將許多準備好的事務寫到二進位制日誌中，同步二進位制日誌，然後將這個事務提交到InnoDB中。如果伺服器在這兩個操作之間崩潰，事務會在重啟時由InnoDB回滾，但仍然存在於二進位制日誌中。假設——innodb_support_xa設定為1 為預設值,這個問題已經解決了，。雖然這個選項與InnoDB中XA事務的支援有關，但它也確保了二進位制日誌和InnoDB資料檔案是同步的。為了提供更大程度的安全性，MySQL伺服器還應該配置為在提交事務之前將二進位制日誌和InnoDB日誌同步到磁碟。預設情況下，InnoDB日誌是同步的，sync_binlog=1可用於同步二進位制日誌。這個選項的效果是，在崩潰後重新啟動時，在執行事務回滾之後，MySQL伺服器掃描最新的二進位制日誌檔案來收集事務xid值，並計算二進位制日誌檔案中的最後一個有效位置。然後，MySQL伺服器告訴InnoDB完成所有已成功寫入二進位制日誌的事務，並將二進位制日誌截斷到最後一個有效位置。這確保了二進位制日誌反映了InnoDB表的準確資料，因此從資料庫與主資料庫保持同步，因為它沒有收到一條回滾的語句。

如果MySQL伺服器在崩潰恢復時發現二進位制日誌比應該的短，那麼它至少缺少一個成功提交的InnoDB事務。如果sync_binlog=1和磁碟/檔案系統在被請求時執行實際的同步(有些沒有)，則不會發生這種情況，因此伺服器將列印一條錯誤訊息The binary log file_name is shorter than its expected size。在這種情況下，這個二進位制日誌不正確，應該從主資料的新快照重新啟動複製。

以下系統變數的會話值被寫入二進位制日誌，並在解析二進位制日誌時由複製從屬伺服器執行:

.sql_mode(除了NO_DIR_IN_CREATE模式不被複制)
.foreign_key_checks
.unique_checks
.character_set_client
.collation_connection
.collation_database
.collation_server
.sql_auto_is_null

binary log格式
伺服器使用幾種日誌格式來記錄二進位制日誌中的資訊。所使用的確切格式取決於所使用的MySQL版本。有三種日誌格式:
.MySQL中的複製功能最初是基於從主到從的SQL語句傳播。這稱為基於語句的日誌記錄。透過使用——binlog-format=STATEMENT啟動伺服器，可以使用這種格式。
.在基於行的日誌記錄中，主程式將事件寫入二進位制日誌，以指示各個錶行是如何受到影響的。因此，表總是使用主鍵來確保有效地標識行，這一點很重要。透過使用——binlogformat=ROW啟動伺服器，可以使它使用基於行的日誌記錄。
.還有第三個選項:混合日誌記錄。對於混合日誌記錄，預設使用基於語句的日誌記錄，但是在某些情況下，日誌記錄模式會自動切換到基於行，如下所述。透過使用選項——binlogformat=MIXED啟動mysqld，可以使MySQL顯式地使用混合日誌記錄好壞參半。日誌格式也可以由所使用的儲存引擎設定或限制。這有助於消除在使用不同儲存引擎的主從之間複製某些語句時出現的問題。

對於基於語句的複製，複製不確定語句可能會有問題。在決定給定的語句對於基於語句的複製是否安全時，MySQL決定是否可以保證使用基於語句的日誌來複制語句。如果MySQL不能做到這一點，它會將該語句標記為可能不可靠，併發出警告。

Statement may not be safe to log in statement format.

可以使用MySQL的基於行的複製來避免這些問題。

設定Binary log格式
可以在啟動MySQL伺服器時使用--binlog-format=type來顯式選項binary log格式，type支援以下取值:
.STATEMENT基於語句記錄日誌
.ROW基於行記錄記錄日誌
.MIXED使用混合格式記錄日誌

日誌格式也可以在執行時切換。設定binlog_format系統變數的全域性值，以指定更改之後連線的客戶端的日誌格式

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.26-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.26-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.26-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)

單個客戶端透過設定binlog_format會話值可以控制自己語句的日誌格式:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

每個MySQL伺服器都可以設定自己的並且只有自己的二進位制日誌格式(無論binlog_format是用全域性還是會話範圍設定的)。這意味著更改複製主伺服器上的日誌格式並不會導致從伺服器更改日誌格式以匹配。(使用語句模式時，不復制binlog_format系統變數;當使用混合或行日誌記錄模式時，它被複制，但被從伺服器忽略)。在複製正在進行時更改主伺服器上的二進位制日誌格式，或者不更改從伺服器上的日誌格式，都可能導致複製失敗，出現錯誤比如:

Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT= STATEMENT.'

為了修改全域性或會話級binlog_format值，必須要有super許可權。客戶端可能希望在每個會話的基礎上設定二進位制日誌記錄的原因有幾個:
.對資料庫進行許多小更改的會話可能希望使用基於行的日誌記錄
.執行與WHERE子句中的許多行匹配的更新的會話可能希望使用基於語句的日誌記錄，因為記錄幾個語句比記錄許多行更有效
.有些語句需要在主伺服器上執行大量時間，但是隻修改了幾行。因此，使用基於行的日誌記錄來複制它們可能是有益的

在出現以下情況時您無法在執行時切換複製格式時：
.從儲存的函式或觸發器中
.如果啟用了NDB儲存引擎
.如果會話當前處於基於行的複製模式，並且有開啟的臨時表

嘗試在這些情況中切換格式會導致錯誤.

如果當前正在使用InnoDB表並且事務隔離級別為read committed或read uncommitted時，只能使用基於行的日誌記錄。它可以修改日誌格式為基於語句的日誌，但是在執行時這樣做會導致很快就會出錯因為InnoDB不能再執行插入。

當存在任何臨時表時，不建議在執行時切換複製格式，因為僅在使用基於語句的複製時才記錄臨時表，而使用基於行的複製時則不記錄臨時表。對於混合複製，通常會記錄臨時表;除非使用使用者定義函式(udf)和UUID()函式。

將二進位制日誌格式設定為ROW後，使用基於行的格式將許多更改寫入二進位制日誌。但是，有些更改仍然使用基於語句的格式。例如包括所有DDL(資料定義語言)語句，如CREATE TABLE、ALTER TABLE或DROP TABLE。

對於能夠進行基於行的複製的伺服器，可以使用——binlog-row-event-max-size選項。行以塊的形式儲存到二進位制日誌中，塊的大小(以位元組為單位)不超過此選項的值。該值必須是256的倍數。預設值是8192。

在使用基於語句的日誌記錄進行復制時，如果語句設計為資料修改是不確定的，則主從上的資料可能會有所不同;也就是說，它由查詢最佳化器決定。通常，即使在複製之外，這也不是一個好的實踐。

Mixed Binary Logging Format
當以混合日誌格式執行時，伺服器在以下條件下自動從基於語句的日誌切換到基於行的日誌:
.當一個函式包含UUID()。
.當一個或多個表使用AUTO_INCREMENT列被更新並且有一個觸發器或儲存函式被呼叫時，像其它不安全語句一樣，如果binlog_format=STATEMENT會生成警告。
.當檢視主體需要基於行的複製時，建立檢視的語句也會使用它。例如，當建立檢視的語句使用UUID()函式時，就會發生這種情況
.當涉及到對UDF的呼叫時
.如果一個語句是按行記錄的，並且執行該語句的會話有任何臨時表，則按行記錄將用於所有後續語句(訪問臨時表的語句除外)，直到該會話使用的所有臨時表都被刪除為止。不管是否實際記錄了任何臨時表，his都是正確的。無法使用基於行的格式記錄臨時表;因此，一旦使用了基於行的日誌記錄，使用該表的所有後續語句都是不安全的。伺服器透過將會話期間執行的所有語句視為不安全的，直到會話不再持有任何臨時表，從而近似於這種情況。
.使用FOUND_ROWS()或ROW_COUNT()時
.當使用USER()、CURRENT_USER()或CURRENT_USER時
.當一個語句引用一個或多個系統變數時

例外:以下系統變數在與會話範圍(僅)一起使用時，不會導致日誌格式切換

 auto_increment_increment
 auto_increment_offset
 character_set_client
 character_set_connection
 character_set_database
 character_set_server
 collation_connection
 collation_database
 collation_server
 foreign_key_checks
 identity
 last_insert_id
 lc_time_names
 pseudo_thread_id
 sql_auto_is_null
 time_zone
 timestamp
 unique_checks

.當其中一個表是mysql資料庫中的日誌表時
.使用LOAD_FILE()函式時

注意:
如果您試圖使用基於語句的日誌記錄來執行應該使用基於行的日誌記錄的語句，則會生成一個警告。該警告在客戶機中(在SHOW WARNINGS的輸出中)和透過mysqld錯誤日誌顯示。每次執行這樣的語句時，都會向SHOW WARNINGS表新增一個警告。但是，只有為每個客戶端會話生成警告的第一個語句被寫入錯誤日誌，以防止日誌氾濫。

除了上面的決策之外，各個引擎還可以確定在更新表中的資訊時使用的日誌格式。單個引擎的日誌功能可以定義如下:
.如果一個引擎支援基於行的日誌記錄，則該引擎被認為是支援行日誌記錄的。
.如果一個引擎支援基於語句的日誌記錄，那麼該引擎就被稱為支援語句日誌記錄

給定的儲存引擎可以支援日誌格式中的一種或兩種。下表列出了每個引擎支援的格式:

儲存引擎                           支援基於行日誌記錄                支援基於語句日誌記錄
ARCHIVE                            Yes                               Yes
BLACKHOLE                          Yes                               Yes
CSV                                Yes                               Yes
EXAMPLE                            Yes                               NO
FEDERATED                          Yes                               Yes
HEAP                               Yes                               Yes
InnoDB                             Yes                               當事務隔離級別為REPEATABLE READ或SERIALIZABLE時為Yes
                                                                     否則為No
MyISAM                             Yes                               Yes
MERGE                              Yes                               Yes
NDB                                Yes                               No

要記錄語句和使用的日誌模式是根據語句的型別(安全的、不安全的或二進位制注入的)、二進位制日誌格式(語句、行或混合的)和儲存引擎的日誌功能(語句支援、行支援、兩者都支援或兩者都不支援)來確定的。(二進位制注入指的是記錄必須使用行格式記錄的更改。)

語句可能被記錄，也可能沒有警告;失敗的語句不會被記錄，但會在日誌中生成錯誤。這在下面的決策表中顯示，其中SLC表示“支援語句日誌記錄”，RLC表示“支援行日誌記錄”。

當確定產生一個警告時，就會產生一個標準的MySQL警告(可以使用SHOW WARNINGS)。資訊也被寫入mysqld錯誤日誌。對於每個客戶端連線的每個錯誤例項，只記錄一個錯誤，以防止日誌氾濫。日誌訊息包括嘗試的SQL語句。

如果從伺服器啟動時使用log_error_verbosity設定來顯示警告，從伺服器會列印訊息到錯誤日誌中來提供狀態資訊，比如例如二進位制日誌和中繼日誌座標，它在何處開始工作，何時切換到另一箇中繼日誌，何時在斷開連線後重新連線，對於基於語句的日誌記錄不安全的語句，等等。

改變mysql資料庫表的日誌格式
mysql資料庫授權表中的內容可以直接(使用insert或delete)或間接(使用grant或create user)修改。影響mysql資料庫表的語句會使用以下規則寫入binary log:
.根據設定的binlog_format系統變數對mysql資料庫表進行資料修改的維護語句直接被記錄。這與語句有關比如insert,update,delete,replace,do,load data infile,select和truncate table。

.對mysql資料庫進行修改的語句會間接地作為語句被記錄而不管binlog_format的設定。這與語句有關比如grant,revoke,set password,rename user,create(除了create table ... select之外的所有形式),alter(所有形式)和drop(所有形式)。

create table ... select它是由資料定義和資料維護語句組成的。create table部分使用語句格式被記錄而select部分根據binlog_format設定情況來記錄。