MySQL系列：binlog日誌詳解（引數、操作、GTID、最佳化、故障演練）

Code技術分享發表於2023-10-30

MySql

簡介

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

binlog日誌包含有關修改資料庫內容的 SQL 語句的資訊。此資訊以描述修改的“事件”的形式儲存

作用

主從複製必須依賴於binlog日誌
備份恢復必須依賴於binlog日誌

系統引數

--log_bin

啟用binlog日誌記錄

Command-Line Format	`--log-bin=file_name`
Type	File name

binlog日誌檔案的預設位置是資料目錄

啟用binlog日誌記錄後，伺服器會將所有更改資料的語句記錄到binlog日誌中，用於備份和複製。binlog日誌是具有基本名稱和數字副檔名的檔案序列。

如果您為--log-bin 選項提供值，則該值將用作日誌序列的基本名稱。伺服器透過將數字字尾新增到基本名稱來按順序建立binlog日誌檔案。在 MySQL 5.7 中，基本名稱預設為 host_name-bin，使用主機名。建議您指定一個基本名稱，以便您可以繼續使用相同的binlog日誌檔名，而不管預設名稱的更改。

設定此選項會導致 log_bin系統變數設定為ON（或1），而不是基本名稱。binlog日誌檔案基本名稱和任何指定的路徑都可用作 log_bin_basename系統變數。

(root@localhost) [(none)]> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> select @@log_bin_basename;
+-----------------------------------+
| @@log_bin_basename                |
+-----------------------------------+
| /data/mysql/3306/binlog/mysql-bin |
+-----------------------------------+
1 row in set (0.00 sec)

--server_id

指定伺服器 ID

Command-Line Format	`--server-id=#`
System Variable	`server_id`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`0`
Minimum Value	`0`
Maximum Value	`4294967295`

在 MySQL 5.7 中， server_id如果啟用了binlog日誌記錄，則必須指定，否則不允許啟動伺服器。

(root@localhost) [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

--binlog_format

設定binlog日誌記錄格式

Command-Line Format	`--binlog-format=format`
System Variable	`binlog_format`
Scope	Global, Session
Dynamic	Yes
Type	Enumeration
Default Value	`ROW`
Valid Values	`MIXED` `STATEMENT` `ROW`

在 MySQL 5.7.7 之前，預設格式是 STATEMENT. 在 MySQL 5.7.7 及更高版本中，預設值為ROW

STATEMENT：（5.7.7以前）SBR(statement based replication)，語句模式原封不動的記錄當前DML
ROW：(5.7.7或更高預設值) RBR(ROW based replication) ：記錄資料行的變化(使用者看不懂，需要工具分析)
MIXED:（混合）MBR(mixed based replication)模式：以上兩種模式的混合

--sync-binlog（雙一標準）

控制 MySQL 伺服器將binlog日誌同步到磁碟的頻率

Command-Line Format	`--sync-binlog=#`
System Variable	`sync_binlog`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`1`
Minimum Value	`0`
Maximum Value	`4294967295`

0：禁用 MySQL 伺服器將binlog日誌同步到磁碟。相反，MySQL 伺服器依賴作業系統不時將binlog日誌重新整理到磁碟，就像它對任何其他檔案所做的那樣。此設定提供了最佳效能，但如果發生電源故障或作業系統崩潰，伺服器可能已提交尚未同步到binlog日誌的事務。
1：在提交事務之前啟用binlog日誌到磁碟的同步。這是最安全的設定，但由於磁碟寫入次數增加，可能會對效能產生負面影響。在電源故障或作業系統崩潰的情況下，binlog日誌中丟失的事務僅處於準備狀態。這允許自動恢復例程回滾事務，從而保證不會從binlog日誌中丟失事務。
N, 其中是 0 或 1 以外的值：在收集到binlog日誌提交組N後，將binlog日誌同步到磁碟。N在電源故障或作業系統崩潰的情況下，伺服器可能已經提交了尚未重新整理到binlog日誌的事務。由於磁碟寫入次數增加，此設定可能會對效能產生負面影響。較高的值會提高效能，但會增加資料丟失的風險。

--gtid-mode(gtid)

是否啟用gtid

Command-Line Format	`--gtid-mode=MODE`
System Variable	`gtid_mode`
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	`OFF`
Valid Values	OFF``OFF_PERMISSIVE``ON_PERMISSIVE``ON

gtid-mode可取值：

OFF：不支援GTID事務，生成的是匿名事務，slave節點也只能應用匿名事務
OFF_PERMISSIVE：生成的是匿名事務，slave節點可以應用匿名事務和GTID事務
ON_PERMISSIVE：生成的是GTID事務，slave節點可以應用匿名事務和GTID事務（此步驟操作完成後，master節點的binlog日誌就會變成GTID模式）
ON：支援GTID事務，生成的是GTID事務，slave節點也只能應用GTID事務

--enforce-gtid-consistency(gtid)

是否允許違反gtid一致性

Command-Line Format	`--enforce-gtid-consistency[=value]`
System Variable	`enforce_gtid_consistency`
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	`OFF`
Valid Values	OFF``ON``WARN

enforce-gtid-consistency可選值：

OFF：允許所有事務違反 GTID 一致性。
ON: 不允許任何事務違反 GTID 一致性。
WARN：允許所有事務違反 GTID 一致性，但在這種情況下會生成警告

--enforce-gtid-consistency 沒有值的設定被解釋為將值設定為 ON。該變數還具有多個值的文字別名：0=OFF=FALSE, 1=ON=TRUE, 2=WARN

--expire-logs-day（最佳化引數）

自動刪除binlog日誌檔案的天數。預設值為 0，表示“不自動刪除

Command-Line Format	`--expire-logs-days=#`
System Variable	`expire_logs_days`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`0`
Minimum Value	`0`
Maximum Value	`99`
Unit	days

--binlog_cache_size（最佳化引數）

在事務期間儲存binlog日誌更改的快取大小

Command-Line Format	`--binlog-cache-size=#`
System Variable	`binlog_cache_size`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`32768`
Minimum Value	`4096`
Maximum Value (64-bit platforms)	`18446744073709547520`
Maximum Value (32-bit platforms)	`4294963200`
Unit	bytes
Block Size	`4096`

--max_binlog_cache_size（最佳化引數）

如果一個事務需要超過這麼多位元組的記憶體，伺服器會生成一個多語句事務需要超過 'max_binlog_cache_size' 位元組的儲存錯誤。最小值為 4096。可能的最大值為 16EB（艾位元組）。最大推薦值為4GB；這是因為 MySQL 目前無法處理大於 4GB 的binlog日誌位置。

Command-Line Format	`--max-binlog-cache-size=#`
System Variable	`max_binlog_cache_size`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`18446744073709547520`
Minimum Value	`4096`
Maximum Value	`18446744073709547520`
Unit	bytes
Block Size	`4096`

--max_binlog_size（最佳化引數）

命令列格式	`--max-binlog-size=#`
系統變數	`max_binlog_size`
範圍	全球的
動態的	是的
型別	整數
預設值	`1073741824`
最小值	`4096`
最大值	`1073741824`
單元	位元組
塊大小	`4096`

如果寫入binlog日誌導致當前日誌檔案大小超過此變數的值，則伺服器輪換binlog日誌（關閉當前檔案並開啟下一個檔案）。最小值為 4096 位元組。最大值和預設值為 1GB。

事務以一個塊的形式寫入binlog日誌，因此永遠不會在多個binlog日誌之間拆分。因此，如果您有大事務，您可能會看到大於 max_binlog_size.

如果max_relay_log_size為 0，則該值也 max_binlog_size適用於中繼日誌。

sql_log_bin

此變數控制是否為當前會話啟用日誌記錄到binlog日誌（假設binlog日誌本身已啟用）。預設值為 ON。要為當前會話禁用或啟用binlog日誌記錄，請將會話 sql_log_bin變數設定為 OFF或ON。

System Variable	`sql_log_bin`
Scope	Session
Dynamic	Yes
Type	Boolean
Default Value	`ON`

日誌操作

DDL ：原封不動的記錄當前DDL(statement語句方式)
DCL ：原封不動的記錄當前DCL(statement語句方式)
DML ：只記錄已經提交的事務DML

開啟日誌

mkdir /data/mysql/3306/binlog
chown -R mysql.mysql /data/mysql/3306/binlog
vim /etc/my.cnf
server_id=1                                          
log_bin=/data/mysql/3306/binlog
binlog_format=row

檢視日誌的開啟情況

show variables like '%log_bin%';

(root@localhost) [(none)]> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /data/mysql/3306/binlog/mysql-bin       |
| log_bin_index                   | /data/mysql/3306/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

檢視一共多少個binlog

 show binary logs;

(root@localhost) [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1255 |
| mysql-bin.000002 |       217 |
| mysql-bin.000003 |       217 |
| mysql-bin.000004 |       217 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |       217 |
| mysql-bin.000007 |       217 |
| mysql-bin.000008 |       217 |
| mysql-bin.000009 |       217 |
| mysql-bin.000010 |       217 |
| mysql-bin.000011 |      3873 |
+------------------+-----------+
11 rows in set (0.00 sec)

檢視mysql正在使用的日誌檔案

show master status;

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

event檢視

show binlog events in ''

(root@localhost) [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000012 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-25 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> show binlog events in 'mysql-bin.000012';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                      |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000012 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4     |
| mysql-bin.000012 | 123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-25 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)

(root@localhost) [(none)]> create database binlogTest;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> use binlogTest;
Database changed
(root@localhost) [binlogTest]> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [binlogTest]> show binlog events in 'mysql-bin.000012';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000012 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4                              |
| mysql-bin.000012 | 123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-25                          |
| mysql-bin.000012 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:26' |
| mysql-bin.000012 | 259 | Query          |         1 |         371 | create database binlogTest                                         |
| mysql-bin.000012 | 371 | Gtid           |         1 |         436 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:27' |
| mysql-bin.000012 | 436 | Query          |         1 |         545 | use `binlogtest`; create table t1(id int)                          |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)

詳細內容檢視

mysqlbinlog --base64-output=decode-rows -vv /data/binlog/mysql-bin.0000013

gtid

介紹

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

GTID(Global Transaction ID)是對於一個已提交事務的編號，並且是一個全域性唯一的編號。GTID實際上是由UUID+TID組成的。其中UUID是一個MySQL例項的唯一標識，儲存在mysql資料目錄下的auto.cnf檔案裡。TID代表了該例項上已經提交的事務數量，並且隨著事務提交單調遞增。
例如:

a469ece3-10cd-11ed-98cd-525400d5deea:31

[root->mcode-server->/data/mysql/3306/data]# cat auto.cnf 
[auto]
server-uuid=a469ece3-10cd-11ed-98cd-525400d5deea

開啟gtid

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=on
systemctl restart mysqld

檢視gtid開啟情況

show variables like '%gtid%';

(root@localhost) [(none)]> show variables like '%gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_next                        | AUTOMATIC                                 |
| gtid_owned                       |                                           |
| gtid_purged                      | a469ece3-10cd-11ed-98cd-525400d5deea:1-31 |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

mysqlbinlog

[root->mcode-server->/data/mysql/3306/binlog]# mysqlbinlog --help

-?, --help                      # 顯示幫助資訊並退出
--base64-output=name            # binlog輸出語句的base64解碼 分為三類：預設是值auto ,僅列印base64編碼的需要的資訊，如row-based 事件和事件的描述
                                  資訊。never 僅適用於不是row-based的事件 decode-rows   配合--verbose選項一起使用解碼行事件到帶註釋的偽SQL語句
--bind-address=name             # 繫結的IP地址
--character-sets-dir=name       # 字符集檔案的目錄
-d, --database=name             # 僅列出此資料庫的條目（僅限本地日誌）
--rewrite-db=name               # 將行事件重寫為指向，以便將其應用於新資料庫
-#, --debug[=#]                 # 輸出debug資訊，用於除錯。預設值為：d:t,/tmp/mysqldump.trace
--debug-check                   # 當程式退出時列印一些除錯資訊
--debug-info                    # 當程式退出時列印除錯資訊和記憶體和CPU使用統計資訊
--default-auth=name             # 要使用的預設身份驗證客戶端外掛
-D, --disable-log-bin           # 禁用binlog日誌，若開啟--to-last-log併傳送輸出檔案到相同的mysql server。這種方式避免無限迴圈。在規避資料庫崩
                                  潰恢復資料的時候有用。注意：需要super許可權來使用此選項
-F, --force-if-open             # 若binlog非正常關閉，強制開啟binlog，預設是on可使用--skip-force-if-open關閉
-f, --force-read                # 強制讀取未知的binlog事件
-H, --hexdump                   # 使用十六進位制和ASCII碼匯出輸出的資訊
-h, --host=name                 # 獲取binlog的服務名
-i, --idempotent                # 通知伺服器使用冪等模式應用行事件
-l, --local-load=name           # 準備LOAD DATA INFILE的本地臨時檔案指定目錄
-o, --offset=#                  # 跳過前n個條目
-p, --password[=name]           # 連線到伺服器的密碼
--plugin-dir=name               # 客戶端外掛的目錄
-P, --port=#                    # 用於連線的埠，0表示預設值。埠使用的優先順序：my.cnf，$ MYSQL_TCP_PORT,/etc/services，內建預設值(3306)
--protocol=name                 # 用於連線的協議(tcp, socket, pipe, memory)
-R, --read-from-remote-server   # 從MySQL伺服器讀取binlog日誌，是read-from-remote-master = BINLOG-DUMP-NON-GTIDS的別名。
--read-from-remote-master=name  
--raw                           # 配合引數-R一起使用,輸出原始的binlog資料而不是SQL語句
-r, --result-file=name          # 輸出指定的檔案，和--raw一起使用，此時是資料檔案的字首
--secure-auth                   # 如果客戶端使用舊的（4.1.1之前的）協議，則拒絕連線到伺服器
--server-id=#                   # 提取給定id的伺服器建立的binlog條目                
--server-id-bits=#              # 設定server-id中的有效位數
--set-charset=name              # 新增'SET NAMES character_set' 到輸出
-s, --short-form                # 僅適用於常規查詢，沒有額外的資訊和row-based事件資訊。僅用於測試，不使用於生產環境。如果你想抑制
                                  base64-output，考慮使用--base64-output = never代替
-S, --socket=name               # 連線時使用的socket檔案
--ssl-mode=name                 # SSL連線模式
--ssl-ca=name                   # PEM格式的CA檔案
--ssl-capath=name               # CA目錄
--ssl-cert=name                 # PEM格式的X509證照
--ssl-cipher=name               # 要使用的SSL密碼
--ssl-key=name                  # PEM格式的X509金鑰
--ssl-crl=name                  # 證照吊銷列表
--ssl-crlpath=name              # 證照吊銷列表路徑
--tls-version=name              # 要使用的TLS版本，允許值為：tlsv1、tlsv1.1
--start-datetime=name           # binlog檔案讀取的起始時間點，可接受datetime和timestamp型別，格式2004-12-25 11:25:56
-j, --start-position=#          # 在位置等於或大於 的第一個事件處開始讀取binlog日誌 *`N`*
--stop-datetime=name            # binlog檔案讀取的結束時間點
--stop-never                    # 等待來自伺服器的更多資料，而不是在最後一個日誌結束時停止。隱式地設定--to-last-log ，但不是在最後一個日誌結
                                  束時停止而是繼續等待直到伺服器斷開連線
--stop-never-slave-server-id=#  # 從伺服器server_id使用--read-from-remote-server --stop-never。該選項不能和--connection-server-id一起使用
--connection-server-id=#        # 從伺服器server_id使用--read-from-remote-server,該選項不能和--stop-never-slave-server-id一起使用
--stop-position=#               # 
-t, --to-last-log               # 和-r一起使用，不會在請求的binlog結尾處停止，而是繼續列印，直到mysql伺服器的最後一個binlog結束。如果將輸出發
                                  送到同一個MySQL伺服器，可能會導致無休止的迴圈
-u, --user=name                 # 連線到伺服器使用者名稱
-v, --verbose                   # 重新構建偽SQL語句的行資訊輸出，-v -v會增加列型別的註釋資訊
-V, --version                   # 列印版本資訊
--open-files-limit=#            # 開啟檔案的限制，用於保留檔案描述符以供此程式使用
-c, --verify-binlog-checksum    # 驗證binlog的事件資訊
--binlog-row-event-max-size=#   # 指定基於行的binlog的大小，改值必須是256的倍數
--skip-gtids                    # 不要保留全域性事務識別符號，而是讓伺服器像執行新事務一樣執行這些事務。
--include-gtids=name            # 列印提供了全域性事務識別符號的事件
--exclude-gtids=name            # 列印所有事件，但提供全域性事務識別符號的事件除外

--base64-output=name

binlog輸出語句的base64解碼分為三類：

auto(預設) ,僅列印base64編碼的需要的資訊，如row-based 事件和事件的描述資訊。

never 僅適用於不是row-based的事件

decode-rows 配合--verbose選項一起使用解碼行事件到帶註釋的偽SQL語句

mysqlbinlog --base64-output=never mysql-bin.000012
mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000012

-d,--database=name

僅列出此資料庫的條目（僅限本地日誌）

mysqlbinlog -d binlogTest  mysql-bin.000012

-r,--result-file=name

將文字輸出寫入的檔案

mysqlbinlog mysql-bin.000012 -r ./result.sql

--start-datetime=name

binlog檔案讀取的起始時間點，可接受datetime和timestamp型別，格式2004-12-25 11:25:56

mysqlbinlog --start-datetime='2022-08-19 14:10-24'  mysql-bin.000012

--stop-datetime=name

binlog檔案讀取的結束時間點

mysqlbinlog --stop-datetime='2022-08-19 14:10-24'  mysql-bin.000012

-j,--start-position=#

在位置等於或大於的第一個事件處開始讀取binlog日誌 N

 mysqlbinlog --start-position=1410 mysql-bin.000012 --base64-output=decode-rows -v

--stop-position=#

在位置等於或大於的第一個事件處停止讀取binlog日誌 N

mysqlbinlog --start-position=1410 --stop-position=1488 mysql-bin.000012 --base64-output=decode-rows -v

-v,--verbose

重構行事件並將它們顯示為註釋的 SQL 語句。如果此選項被給出兩次（透過傳入“-vv”或“--verbose --verbose”），則輸出包括用於指示列資料型別和一些後設資料的註釋，以及行查詢日誌事件（如果已配置）。

mysqlbinlog mysql-bin.000012 --base64-output=decode-rows -vv

--include-gtids=name

僅顯示中列出的組 gtid_set

mysqlbinlog mysql-bin.000012 --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'

--exclude-gtids=name

不顯示中列出的任何組 gtid_set

mysqlbinlog mysql-bin.000012 --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'

--skip-gtids

不要在輸出轉儲檔案中包含binlog日誌檔案中的 GTID

不要保留全域性事務識別符號，而是讓伺服器像執行新事務一樣執行這些事務

開啟GTID後,MySQL恢復binlog時,重複GTID的事務不會再執行了,想恢復就得采用此引數

mysqlbinlog --skip-gtids mysql-bin.000012 >./dump.sql
mysqlbinlog --skip-gtids  --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'  mysql-bin.000012 >./dump.sql

其他操作

自動清理日誌

企業建議,至少保留兩個全備週期+1的binlog

(root@localhost) [(none)]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  7 |
+--------------------+
1 row in set (0.00 sec)

臨時生效,重啟後失效

(root@localhost) [(none)]> set global expire_logs_days=30;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> show variables like 'expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+
1 row in set (0.00 sec)

永久生效

my.cnf
expire_logs_days=15

手工清理日誌

1.清理三天前的資料

(root@localhost) [(none)]> purge binary logs before now() - interval 3 day;
Query OK, 0 rows affected (0.00 sec)

2.清理mysql-bin.0000013之前的

(root@localhost) [(none)]> purge binary logs to 'mysql-bin.000013';
Query OK, 0 rows affected (0.00 sec)

注意:不要手工 rm binlog檔案

3.reset master

(root@localhost) [db1]> reset master;
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [db1]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主從關係中，主庫執行此操作，主從環境必崩

滾動日誌

1.flush logs

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000013 |      356 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

2.重啟mysql也會自動滾動一個新的

3.日誌檔案達到指定大小時也會滾動一個新的，透過max_binlog_size引數控制，備份時,加入引數也可以自動滾動

(root@localhost) [(none)]> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|         536870912 |
+-------------------+
1 row in set (0.00 sec)

my.cnf
max_binlog_size=512M

最佳化

binlog_cache_size = 2M #為每個session 分配的記憶體，在事務過程中用來儲存binlog日誌的快取, 提高記錄bin-log的效率。沒有什麼大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以適當的調大一點。前者建議是--1M，後者建議是：即 2--4M
max_binlog_cache_size = 8M #表示的是binlog 能夠使用的最大cache 記憶體大小
max_binlog_size= 512M #指定binlog日誌檔案的大小，如果當前的日誌大小達到max_binlog_size，還會自動建立新的binlog日誌。你不能將該變數設定為大於1GB或小於4096位元組。預設值是1GB。在匯入大容量的sql檔案時，建議關閉sql_log_bin，否則硬碟扛不住，而且建議定期做刪除。
expire_logs_days = 30 #定義了mysql清除過期日誌的時間。binlog日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”。

binlog_cache_size = 2M

為每個session 分配的記憶體，在事務過程中用來儲存binlog日誌的快取, 提高記錄bin-log的效率。沒有什麼大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以適當的調大一點。前者建議是--1M，後者建議是：即 2--4M

max_binlog_cache_size = 8M

表示的是binlog 能夠使用的最大cache 記憶體大小

max_binlog_size= 1G

指定binlog日誌檔案的大小，如果當前的日誌大小達到max_binlog_size，還會自動建立新的binlog日誌。你不能將該變數設定為大於1GB或小於4096位元組。預設值是1GB。在匯入大容量的sql檔案時，建議關閉sql_log_bin，否則硬碟扛不住，而且建議定期做刪除

expire_logs_days = 30

定義了mysql清除過期日誌的時間。binlog日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”。

綜合引數

log_bin=/data/mysql/3306/binlog/mysql-bin
binlog_format=row
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=1G
expire_logs_days=30
# 雙1標準
sync_binlog=1

故障演練

建立了一個庫 db1, 匯入了表t1 ,t1表中錄入了很多資料
一個開發人員,drop database db;
沒有備份,日誌都在.怎麼恢復?
思路:找到建庫語句到刪庫之前所有的日誌,進行恢復.(開啟了GTID模式)

(root@localhost) [(none)]> drop database if exists db1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) [(none)]> create database db1 charset utf8;  
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> use db1;
Database changed
(root@localhost) [db1]> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)

(root@localhost) [db1]> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [db1]> insert into t1 values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [db1]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [db1]> update t1 set id=30 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost) [db1]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [db1]> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [db1]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [db1]> insert into t1 values(7),(8),(9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [db1]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [db1]> drop database db1;
Query OK, 1 row affected (0.01 sec)

========================
(0) drop database if exists db1 ;
(1) create database db1 charset utf8;     
(2) use db1;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database db1;
========================
drop database if exists db1 ;
create database db1 charset utf8; 
use db1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db1;
=======
執行以上語句，模擬故障場景
需求：將資料庫恢復到以下狀態（提示第9步和第13步是誤操作，其他都是正常操作）

檢視當前binlog檔案

show master status;

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 |     2126 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-41 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

檢視當前binlog events

show binlog events in 'mysql-bin.000014 '

(root@localhost) [(none)]> show binlog events in 'mysql-bin.000014';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000014 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4                              |
| mysql-bin.000014 |  123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-32                          |
| mysql-bin.000014 |  194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:33' |
| mysql-bin.000014 |  259 | Query          |         1 |         352 | drop database if exists db1                                        |
| mysql-bin.000014 |  352 | Gtid           |         1 |         417 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:34' |
| mysql-bin.000014 |  417 | Query          |         1 |         521 | create database db1 charset utf8                                   |
| mysql-bin.000014 |  521 | Gtid           |         1 |         586 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:35' |
| mysql-bin.000014 |  586 | Query          |         1 |         681 | use `db1`; create table t1(id int)                                 |
| mysql-bin.000014 |  681 | Gtid           |         1 |         746 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:36' |
| mysql-bin.000014 |  746 | Query          |         1 |         817 | BEGIN                                                              |
| mysql-bin.000014 |  817 | Table_map      |         1 |         861 | table_id: 109 (db1.t1)                                             |
| mysql-bin.000014 |  861 | Write_rows     |         1 |         911 | table_id: 109 flags: STMT_END_F                                    |
| mysql-bin.000014 |  911 | Xid            |         1 |         942 | COMMIT /* xid=66 */                                                |
| mysql-bin.000014 |  942 | Gtid           |         1 |        1007 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:37' |
| mysql-bin.000014 | 1007 | Query          |         1 |        1078 | BEGIN                                                              |
| mysql-bin.000014 | 1078 | Table_map      |         1 |        1122 | table_id: 109 (db1.t1)                                             |
| mysql-bin.000014 | 1122 | Write_rows     |         1 |        1172 | table_id: 109 flags: STMT_END_F                                    |
| mysql-bin.000014 | 1172 | Xid            |         1 |        1203 | COMMIT /* xid=67 */                                                |
| mysql-bin.000014 | 1203 | Gtid           |         1 |        1268 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:38' |
| mysql-bin.000014 | 1268 | Query          |         1 |        1339 | BEGIN                                                              |
| mysql-bin.000014 | 1339 | Table_map      |         1 |        1383 | table_id: 109 (db1.t1)                                             |
| mysql-bin.000014 | 1383 | Update_rows    |         1 |        1429 | table_id: 109 flags: STMT_END_F                                    |
| mysql-bin.000014 | 1429 | Xid            |         1 |        1460 | COMMIT /* xid=69 */                                                |
| mysql-bin.000014 | 1460 | Gtid           |         1 |        1525 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39' |
| mysql-bin.000014 | 1525 | Query          |         1 |        1596 | BEGIN                                                              |
| mysql-bin.000014 | 1596 | Table_map      |         1 |        1640 | table_id: 109 (db1.t1)                                             |
| mysql-bin.000014 | 1640 | Delete_rows    |         1 |        1680 | table_id: 109 flags: STMT_END_F                                    |
| mysql-bin.000014 | 1680 | Xid            |         1 |        1711 | COMMIT /* xid=71 */                                                |
| mysql-bin.000014 | 1711 | Gtid           |         1 |        1776 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:40' |
| mysql-bin.000014 | 1776 | Query          |         1 |        1847 | BEGIN                                                              |
| mysql-bin.000014 | 1847 | Table_map      |         1 |        1891 | table_id: 109 (db1.t1)                                             |
| mysql-bin.000014 | 1891 | Write_rows     |         1 |        1941 | table_id: 109 flags: STMT_END_F                                    |
| mysql-bin.000014 | 1941 | Xid            |         1 |        1972 | COMMIT /* xid=73 */                                                |
| mysql-bin.000014 | 1972 | Gtid           |         1 |        2037 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41' |
| mysql-bin.000014 | 2037 | Query          |         1 |        2126 | drop database db1                                                  |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
35 rows in set (0.00 sec)

檢視binlog詳細內容

mysqlbinlog --base64-output=decode-rows -vv --start-datetime='2022-08-22 09:00' --stop-datetime='2022-08-22 12:00'  -d db1  mysql-bin.000014  >/tmp/result.sql

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220819 15:57:58 server id 1  end_log_pos 123 CRC32 0xc117f37e 	Start: binlog v 4, server v 5.7.36-log created 220819 15:57:58
# Warning: this binlog is either in use or was not closed properly.
# at 194
#220822 11:13:31 server id 1  end_log_pos 259 CRC32 0xcb8bcdca 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:33'/*!*/;
# at 259
#220822 11:13:31 server id 1  end_log_pos 352 CRC32 0xd19a4e55 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138011/*!*/;
SET @@session.pseudo_thread_id=24/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop database if exists db1
/*!*/;
# at 352
#220822 11:13:47 server id 1  end_log_pos 417 CRC32 0xaf5fe918 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:34'/*!*/;
# at 417
#220822 11:13:47 server id 1  end_log_pos 521 CRC32 0x91172a9a 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138027/*!*/;
create database db1 charset utf8
/*!*/;
# at 521
#220822 11:14:07 server id 1  end_log_pos 586 CRC32 0xd73afa06 	GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:35'/*!*/;
# at 586
#220822 11:14:07 server id 1  end_log_pos 681 CRC32 0x39129c09 	Query	thread_id=24	exec_time=0	error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1661138047/*!*/;
create table t1(id int)
/*!*/;
# at 681
#220822 11:14:40 server id 1  end_log_pos 746 CRC32 0x7c89b1d8 	GTID	last_committed=3	sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:36'/*!*/;
# at 746
#220822 11:14:40 server id 1  end_log_pos 817 CRC32 0x6f5f3852 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138080/*!*/;
BEGIN
/*!*/;
# at 817
#220822 11:14:40 server id 1  end_log_pos 861 CRC32 0xd0d79c66 	Table_map: `db1`.`t1` mapped to number 109
# at 861
#220822 11:14:40 server id 1  end_log_pos 911 CRC32 0xcb1cbbdf 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `db1`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 911
#220822 11:14:40 server id 1  end_log_pos 942 CRC32 0x2ca37ce3 	Xid = 66
COMMIT/*!*/;
# at 942
#220822 11:15:00 server id 1  end_log_pos 1007 CRC32 0xd70a75bd 	GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:37'/*!*/;
# at 1007
#220822 11:15:00 server id 1  end_log_pos 1078 CRC32 0x29ea80b8 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138100/*!*/;
BEGIN
/*!*/;
# at 1078
#220822 11:15:00 server id 1  end_log_pos 1122 CRC32 0xdeb8ca92 	Table_map: `db1`.`t1` mapped to number 109
# at 1122
#220822 11:15:00 server id 1  end_log_pos 1172 CRC32 0xffa6a19b 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `db1`.`t1`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
# at 1172
#220822 11:15:00 server id 1  end_log_pos 1203 CRC32 0x7307f602 	Xid = 67
COMMIT/*!*/;
# at 1203
#220822 11:17:21 server id 1  end_log_pos 1268 CRC32 0xa8d53a45 	GTID	last_committed=5	sequence_number=6	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:38'/*!*/;
# at 1268
#220822 11:17:21 server id 1  end_log_pos 1339 CRC32 0x9156e38d 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138241/*!*/;
BEGIN
/*!*/;
# at 1339
#220822 11:17:21 server id 1  end_log_pos 1383 CRC32 0x6efec1fd 	Table_map: `db1`.`t1` mapped to number 109
# at 1383
#220822 11:17:21 server id 1  end_log_pos 1429 CRC32 0xa15c3e47 	Update_rows: table id 109 flags: STMT_END_F
### UPDATE `db1`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=30 /* INT meta=0 nullable=1 is_null=0 */
# at 1429
#220822 11:17:21 server id 1  end_log_pos 1460 CRC32 0x3beb5cd2 	Xid = 69
COMMIT/*!*/;
# at 1460
#220822 11:17:46 server id 1  end_log_pos 1525 CRC32 0xd33b8151 	GTID	last_committed=6	sequence_number=7	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39'/*!*/;
# at 1525
#220822 11:17:46 server id 1  end_log_pos 1596 CRC32 0x52fe1a6e 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138266/*!*/;
BEGIN
/*!*/;
# at 1596
#220822 11:17:46 server id 1  end_log_pos 1640 CRC32 0x28d68e80 	Table_map: `db1`.`t1` mapped to number 109
# at 1640
#220822 11:17:46 server id 1  end_log_pos 1680 CRC32 0x75166daf 	Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `db1`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1680
#220822 11:17:46 server id 1  end_log_pos 1711 CRC32 0xc2ec60fc 	Xid = 71
COMMIT/*!*/;
# at 1711
#220822 11:18:11 server id 1  end_log_pos 1776 CRC32 0xaac0949c 	GTID	last_committed=7	sequence_number=8	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:40'/*!*/;
# at 1776
#220822 11:18:11 server id 1  end_log_pos 1847 CRC32 0xb6ba1980 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138291/*!*/;
BEGIN
/*!*/;
# at 1847
#220822 11:18:11 server id 1  end_log_pos 1891 CRC32 0x5955c15b 	Table_map: `db1`.`t1` mapped to number 109
# at 1891
#220822 11:18:11 server id 1  end_log_pos 1941 CRC32 0xabb399cc 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `db1`.`t1`
### SET
###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=8 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t1`
### SET
###   @1=9 /* INT meta=0 nullable=1 is_null=0 */
# at 1941
#220822 11:18:11 server id 1  end_log_pos 1972 CRC32 0x339d37ab 	Xid = 73
COMMIT/*!*/;
# at 1972
#220822 11:18:32 server id 1  end_log_pos 2037 CRC32 0xb9a33d03 	GTID	last_committed=8	sequence_number=9	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41'/*!*/;
# at 2037
#220822 11:18:32 server id 1  end_log_pos 2126 CRC32 0xa621a531 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138312/*!*/;
drop database db1
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# at 1460
#220822 11:17:46 server id 1  end_log_pos 1525 CRC32 0xd33b8151 	GTID	last_committed=6	sequence_number=7	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39'/*!*/;
# at 1525
#220822 11:17:46 server id 1  end_log_pos 1596 CRC32 0x52fe1a6e 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138266/*!*/;
BEGIN
/*!*/;
# at 1596
#220822 11:17:46 server id 1  end_log_pos 1640 CRC32 0x28d68e80 	Table_map: `db1`.`t1` mapped to number 109
# at 1640
#220822 11:17:46 server id 1  end_log_pos 1680 CRC32 0x75166daf 	Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `db1`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1680
#220822 11:17:46 server id 1  end_log_pos 1711 CRC32 0xc2ec60fc 	Xid = 71
COMMIT/*!*/;

COMMIT/*!*/;
# at 1972
#220822 11:18:32 server id 1  end_log_pos 2037 CRC32 0xb9a33d03 	GTID	last_committed=8	sequence_number=9	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41'/*!*/;
# at 2037
#220822 11:18:32 server id 1  end_log_pos 2126 CRC32 0xa621a531 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1661138312/*!*/;
drop database db1
/*!*/;

基於position號恢復

mysqlbinlog  --start-position=259 --stop-position=1460  --skip-gtids  -d db1   /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t1.sql
mysqlbinlog  --start-position=1711 --stop-position=1972 --skip-gtids  -d db1   /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t2.sql

mysql>
set sql_log_bin=0;
source /tmp/t1.sql;
source /tmp/t2.sql;
set sql_log_bin=1;

基於gtid恢復

mysqlbinlog --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:33-38' --skip-gtids -d db1 /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t1.sql
mysqlbinlog --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:40' --skip-gtids -d db1 /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t2.sql

mysql>
set sql_log_bin=0;
source /tmp/t1.sql;
source /tmp/t2.sql;
set sql_log_bin=1;

mysql檢視binlog日誌詳解
2020-12-16
MySql
詳解MySQL慢日誌（下）選項引數篇
2017-11-16
MySql
深入理解MySQL5.7GTID系列（七）binlog_gtid_simple_recovery引數的影響總結
2018-03-12
MySql
MySQL 的日誌：binlog
2021-12-21
MySql
mysql清除binlog日誌
2016-08-22
MySql
MySQL的binlog日誌
2016-01-20
MySql
清除MySQL Binlog日誌
2014-05-07
MySql
Mysql之binlog日誌說明及利用binlog日誌恢復資料操作記錄
2016-09-25
MySql
mysql binlog日誌刪除
2013-05-22
MySql
MySQL日誌引數配置簡介
2018-11-28
MySql
MYSQL_GTID詳解
2017-07-11
MySql
mysql之 binlog維護詳細解析（開啟、binlog引數作用、mysqlbinlog解讀、binlog刪除）
2017-05-30
MySql
MySQL 8.0因關閉Gtid 引發從庫故障
2021-11-19
MySql
MySQL 日誌系統 redo log、binlog
2021-05-24
MySql
MySQL 正確刪除 binlog 日誌
2020-12-09
MySql
實時備份mysql binlog日誌
2016-08-11
MySql
[MySQL binlog]徹底解析Mixed日誌格式的binlog
2016-10-07
MySql
Mysql 5.7 Gtid內部學習(七) 總結binlog_gtid_simple_recovery引數帶來的影響
2017-12-18
MySql
mysql的日誌檔案詳解
2018-04-28
MySql
MySQL運維之binlog_gtid_simple_recovery(GTID)
2018-05-03
MySql運維
mysql的日誌引數修改的問題.
2008-07-07
MySql
mysql之 binlog維護詳細解析（開啟、binlog相關引數作用、mysqlbinlog解讀、binlog刪除）
2017-05-30
MySql
MySQL如何通過分析binlog日誌找出操作頻繁的表
2018-07-11
MySql
利用binlog日誌恢復mysql資料
2020-10-20
MySql
MySQL--binlog日誌恢復資料
2015-12-02
MySql
MySQL-配置檔案my.cnf引數最佳化詳解
2019-06-11
MySql
Mysql配置引數詳解（一）
2018-01-26
MySql
第3節：GTID模組初始化簡介和引數binlog_gtid_simple_recovery
2019-09-20
nginx引數詳解與效能最佳化(-)
2015-03-06
Nginx
mysql二進位制日誌詳解
2013-11-07
MySql
mysql二進位制日誌相關引數
2015-08-18
MySql
監聽MySQL的binlog日誌工具分析：Canal
2020-10-20
MySql
【Mysql】遠端備份binlog日誌到本地
2016-03-21
MySql
MySQL 傳統複製與 GTID 複製原理及操作詳解
2018-08-16
MySql
MySQL InnoDB常見引數詳解
2016-10-19
MySql
【MYSQL】my.cnf引數詳解
2016-03-25
MySql
如何列印完整的MYSQL帶引數SQL日誌資訊
2020-08-04
MySql
mysql二進位制日誌的引數介紹
2021-09-11
MySql