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 ? 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 ? 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 ? 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設置情況來記錄。

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。