總結

1、binary log記錄的是已經提交commit 的各種DML和DDL語句，類似oracle的redo log(包含online redo log和archive redo log)中已經commit提交的資料

statement 格式的 binlog，最後會有 COMMIT；

row 格式的 binlog，最後會有一個 XID event；

2、binary log用於主從複製和基於時間點的恢復

3、在MySQL 5.7.3及其以後的版本中，若想開啟二進位制日誌，除了log-bin引數外還必須加上server_id引數。否則會有如下報錯

[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id.

4、purge binary logs時，磁碟上的物理檔案也一併刪除了

5、binary log日誌格式，可以簡單認為就是執行過的事務中的sql語句。但又不完全是sql語句這麼簡單，而是執行的sql語句（增刪改）及反向的資訊，比如delete對應著delete本身和其反向的insert。使用mysqlbinlog解析binlog可以看到

6、binary log產生時間，事務提交的時候，一次性將事務中的sql語句（一個事物可能對應多個sql語句）按照一定的格式記錄到binlog中

7、binlog的保持時間由引數expire_logs_days配置，也就是說對於非活動的日誌檔案，在生成時間超過expire_logs_days配置的天數之後，會被自動刪除。

8、檢視binary log的內容可以使用以下兩種方式

mysqlbinlog mysql-bin.000002

mysql> show binlog events in 'mysql-bin.000002';

mysql> show binlog events in 'mysql-bin.00002' from 504769752 limit 30,30;
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
選項解析：
IN 'log_name'   指定要查詢的binlog檔名(不指定就是第一個binlog檔案)
FROM pos        指定從哪個pos起始點開始查起(不指定就是從整個檔案首個pos點開始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count       查詢總條數(不指定就是所有行)

涉及引數

log_bin ：是否開啟binary log

log_bin_basename ：binary log檔案的存放路徑和名稱

log_bin_index ：binary log索引檔案的存放路徑和名稱

expire_logs_days ：自動刪除幾天以前的binary log檔案

innodb_page_size ：InnoDB表空間的頁面大小，預設16K

sync_binlog ：MySQL伺服器將二進位制日誌同步到磁碟的頻率，即寫日誌的同時是否寫入磁碟，此處寫磁碟不是寫表的資料檔案，而是寫mysql-bin.00000X日誌檔案

By default is set to 0, meaning that there is no delay

When sync_binlog is set to a value n greater than 1, the delay is applied after every n binary log commit groups.

binlog_group_commit_sync_delay ：二進位制日誌提交後需要等待多少微秒才能把二進位制日誌檔案同步到磁碟，5.7.5預設是0，表示立即寫

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_binary_log

binary log

A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.

You can examine the contents of the binary log, or replay those statements during replication or recovery, by using the mysqlbinlog command. For full information about the binary log, see Section 5.4.4, “The Binary Log”. For MySQL configuration options related to the binary log, see Section 16.1.6.4, “Binary Logging Options and Variables”.

For the MySQL Enterprise Backup product, the file name of the binary log and the current position within the file are important details. To record this information for the master server when taking a backup in a replication context, you can specify the --slave-info option.

Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.

包含嘗試更改表資料的所有語句的記錄的檔案。可以重播這些語句，以便在複製方案中使從屬伺服器保持最新，或者在從備份還原表資料後使資料庫保持最新。可以開啟和關閉二進位制日誌記錄功能，但Oracle建議在使用複製或執行備份時始終啟用它。

您可以使用mysqlbinlog命令檢查二進位制日誌的內容，或在複製或恢復期間重播這些語句。有關二進位制日誌的完整資訊，請參見第5.4.4節“二進位制日誌”。有關二進位制日誌的MySQL配置選項，請參見第16.1.6.4節“二進位制日誌記錄選項和變數”。

對於MySQL Enterprise Backup產品，二進位制日誌的檔名和檔案中的當前位置是重要的詳細資訊。要在複製上下文中進行備份時為主伺服器記錄此資訊，可以指定--slave-info選項。

在MySQL 5.0之前，可以使用類似的功能，稱為更新日誌。在MySQL 5.0及更高版本中，二進位制日誌替換了更新日誌

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

binary log

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication

Certain data recovery operations require use of the binary log.After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

二進位制日誌包含描述資料庫更改的“事件”，例如表建立操作或對錶資料的更改。它還包含可能已進行更改的語句的事件（例如，不匹配任何行的DELETE），除非使用基於行的日誌記錄。二進位制日誌還包含有關每個語句獲取更新資料的時間長度的資訊。

兩個作用：

　　1，用於複製，在主從複製中，從庫利用主庫上的binlog進行重播，實現主從同步。

　　2，用於資料庫的基於時間點的還原。

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

二進位制日誌不用於不修改資料的SELECT或SHOW等語句

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

執行啟用了二進位制日誌記錄的伺服器會使效能稍慢。但是，二進位制日誌使您能夠設定複製和還原操作的好處通常會超過這種較小的效能下降。

The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files

在當前日誌的大小達到max_binlog_size後，伺服器還會自動建立新的二進位制日誌檔案。如果您使用大型事務，則二進位制日誌檔案可能會變得比max_binlog_size大，因為事務是以一個部分寫入檔案，而不是在檔案之間分割

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same base name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

為了跟蹤已使用的二進位制日誌檔案，mysqld還建立了一個二進位制日誌索引檔案，其中包含所有使用的二進位制日誌檔案的名稱。預設情況下，它具有與二進位制日誌檔案相同的基本名稱，副檔名為“.index”。您可以使用--log-bin-index [= file_name]選項更改二進位制日誌索引檔案的名稱。在mysqld執行時，您不應手動編輯此檔案; 這樣做會讓mysqld感到困惑。

Binary Logging Formats

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

MySQL中的複製功能最初基於從主裝置到從裝置的SQL語句傳播。這稱為基於語句的日誌記錄。您可以透過使用--binlog-format = STATEMENT啟動伺服器來使用此格式。

在基於行的日誌記錄中，主伺服器將事件寫入二進位制日誌，以指示各個錶行的影響方式。因此，重要的是表始終使用主鍵來確保可以有效地識別行。您可以透過使用--binlog-format = ROW啟動伺服器來使用基於行的日誌記錄。

第三種選擇也是可用的：混合日誌記錄。對於混合日誌記錄，預設情況下使用基於語句的日誌記錄，但在某些情況下，日誌記錄模式會自動切換到基於行的日誌記錄，如下所述。您可以透過使用選項--binlog-format = MIXED啟動mysqld，使MySQL顯式使用混合日誌記錄。

實驗過程

[root@mydb ~]# cat /etc/my.cnf |grep log-bin -A1

log-bin=mysql-bin

server-id=1

mysql> show variables like '%log_bin%';

+---------------------------------+--------------------------------+

| Variable_name | Value |

+---------------------------------+--------------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/mysql-bin |

| log_bin_index | /var/lib/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+--------------------------------+

檢視當前伺服器所有的二進位制日誌檔案

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207597 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207597 |

+------------------+-----------+

檢視當前二進位制日誌檔案狀態

mysql> show master status

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> flush logs;

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 154 |

+------------------+-----------+

mysql> flush binary logs;

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

刪除某個日誌之前的所有日誌

mysql> purge binary logs to 'mysql-bin.000003';

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

[root@mydb ~]# ll /var/lib/mysql|grep mysql-bin

-rw-r----- 1 mysql mysql 177 Sep 29 17:17 mysql-bin.000003

-rw-r----- 1 mysql mysql 154 Sep 30 08:52 mysql-bin.000004

刪除某個時間點之前的binary log

mysql> purge binary logs before '2018-09-30 12:00:00';

刪除所有的binary log

mysql> reset master;

自動刪除binary log

mysql> set global expire_logs_days=7;

設定expire_logs_days引數，設定自動清理，其預設值為0,表示不啟用過期自動刪除功能，如果啟用了自動清理功能，表示超出此天數的二進位制日誌檔案將被自動刪除

檢視binary log的內容

mysql> show binlog events in 'mysql-bin.000002';

mysql關於二進位制日誌binary log的總結

相關文章