MySQL系列之二進(jìn)制日志Binlog學(xué)習(xí)筆記

學(xué)習(xí)準(zhǔn)備：

Mysql服務(wù)于端（5.7+版本）

Mysql客戶(hù)端軟件（workbench、sqlyog etc.）

1、什么是Binlog？

在上一篇博客的學(xué)習(xí)，我們知道了InnoDB存儲(chǔ)引擎的兩種事務(wù)日志，redo log是InnoDB特有的功能，而MySQL也是有自己的日志機(jī)制的，也即本文學(xué)習(xí)的binlog

binlog（二進(jìn)制日志）：binary log，簡(jiǎn)稱(chēng)是binlog，binlog記錄所有數(shù)據(jù)庫(kù)表結(jié)構(gòu)變更以及表數(shù)據(jù)修改，而不會(huì)記錄SELECT和SHOW這類(lèi)操作，數(shù)據(jù)保存的是二進(jìn)制數(shù)據(jù)

binlog以事件的形式保存，還包括sql執(zhí)行所需的時(shí)間等等信息，開(kāi)啟Binlog日志有以下兩個(gè)最重要的使用場(chǎng)景：

主從復(fù)制：binlog的特性可以被應(yīng)用于主從復(fù)制，主庫(kù)（master）開(kāi)啟binlog功能，從庫(kù)（salve）通過(guò)binlog的事件記錄，將數(shù)據(jù)同步到數(shù)據(jù)庫(kù)

數(shù)據(jù)恢復(fù)：binlog可以用于在數(shù)據(jù)恢復(fù)，因?yàn)閎inlog記錄了sql的變更修改操作日志，如果數(shù)一不小心刪庫(kù)了，就可以通過(guò)binlog進(jìn)行數(shù)據(jù)恢復(fù)

2、Binlog記錄模式

Binlog日志的文件名默認(rèn)是“主機(jī)名_binlog-序列號(hào)”的形式，eg：adminstrator_binlog-000001，當(dāng)然也可以在配置文件修改命名為mysqlbinlog：

[mysql] log-bin=mysqlbinlog

1

2

binlog對(duì)log event進(jìn)行記錄，文件記錄模式有STATEMENT、ROW和MIXED三種，具體含義：

Row模式：row-based replication，簡(jiǎn)稱(chēng)RBR，日志會(huì)記錄每一行數(shù)據(jù)被修改的情況，如果用于主從復(fù)制，在slave數(shù)據(jù)庫(kù)會(huì)對(duì)相同的數(shù)據(jù)進(jìn)行修改

STATEMENT模式：statement-based replication, 簡(jiǎn)稱(chēng)SBR，這種模式，沒(méi)錯(cuò)修改變更的sql都會(huì)被記錄在日志里

MIXED模式：mixed-based replication, 簡(jiǎn)稱(chēng)MB，這種模式，一般會(huì)使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用ROW模式保存binlog

在 MySQL 5.7.7 之前，默認(rèn)的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默認(rèn)值是 ROW。日志格式通過(guò) binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

3、Binlog文件結(jié)構(gòu)

binlog機(jī)制中用來(lái)表示保存修改操作的數(shù)據(jù)結(jié)構(gòu)是Log event。不同的修改操作對(duì)應(yīng)的不同的log event。比較常用的log event有：Query event、Row event、Xid event等，可以獲取一份log來(lái)看看：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #201012 11:26:13 server id 1 end_log_pos 124 CRC32 0x253ff34e Start: binlog v 4, server v 8.0.13 created 201012 11:26:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' 1cyDXw8BAAAAeAAAAHwAAAABAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADVzINfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgFO8z8l '/*!*/; # at 124 #201012 11:26:14 server id 1 end_log_pos 155 CRC32 0xf80d0acf Previous-GTIDs # [empty] # at 155 #201012 14:04:52 server id 1 end_log_pos 228 CRC32 0xe767e3f3 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1602482692499600 immediate_commit_timestamp=1602482692499600 transaction_length=183 # original_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨a¨¤??????????|ìo?à¨o?á?¨o?à??) # immediate_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨a¨¤??????????|ìo?à¨o?á?¨o?à??) /*!80001 SET @@session.original_commit_timestamp=1602482692499600*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 228 #201012 14:04:52 server id 1 end_log_pos 338 CRC32 0x8f981f33 Query thread_id=9 exec_time=0 error_code=0 Xid = 16 SET TIMESTAMP=1602482692/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; create database `test` /*!*/; # at 338 #201012 14:31:46 server id 1 end_log_pos 411 CRC32 0xc6dbb42e Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1602484306791600 immediate_commit_timestamp=1602484306791600 transaction_length=181 # original_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨a¨¤??????????|ìo?à¨o?á?¨o?à??) # immediate_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨a¨¤??????????|ìo?à¨o?á?¨o?à??) /*!80001 SET @@session.original_commit_timestamp=1602484306791600*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

畫(huà)表格表示其主要信息：

4、Binlog寫(xiě)入機(jī)制

binlog 什么時(shí)候刷新到磁盤(pán)？binlog刷數(shù)據(jù)到磁盤(pán)跟參數(shù) sync_binlog 相關(guān)

如果設(shè)置為0：則表示MySQL不控制binlog的刷新，由文件系統(tǒng)去控制日志的刷新；

如果設(shè)置為不為0的值：則表示每 sync_binlog 次事務(wù)，MySQL調(diào)用文件系統(tǒng)刷新binlog到磁盤(pán)中

然后binlog具體是怎么刷日志的？畫(huà)圖表示：

根據(jù)記錄模式和操作觸發(fā)log事件從而生成log event

將事務(wù)執(zhí)行過(guò)程的log event寫(xiě)入緩存區(qū)，緩存區(qū)是一種binlog_cache_mngr數(shù)據(jù)結(jié)構(gòu)，該結(jié)構(gòu)中有兩個(gè)緩沖區(qū)，一個(gè)是stmt_cache，用于存放不支持事務(wù)的信息；另一個(gè)是trx_cache，用于存放支持事務(wù)的信息。

事務(wù)在提交階段會(huì)將產(chǎn)生的log event寫(xiě)入到磁盤(pán)binlog文件中

5、Binlog命令操作

啟用Binlog

通過(guò)命令set global log_bin=1;啟動(dòng)，也可以在my.ini配置文件加上配置：

[mysql] #log-bin=ON binlog-format=ROW log-bin=mysqlbinlog

1

2

3

4

不能通過(guò)命令配置，只能修改my.ini配置文件

確定binlog是否啟動(dòng)

查看binlog相關(guān)參數(shù)

設(shè)置binlog_format模式

set global binlog_format='ROW';

1

binlog events命令

//等價(jià)于show master logs; show binary logs; //查看最新一個(gè)binlog日志文件名稱(chēng)和Position show master status; //查看 binlog 內(nèi)容 show binlog events; //查看 指定binlog 內(nèi)容 show binlog events in 'mysqlbinlog.000001';

1

2

3

4

5

6

7

8

mysqlbinlog 命令

// 查看指定的binlog日志文件 mysqlbinlog "文件名" // 查看指定binlog文件并保存到test.sql文件 mysqlbinlog "文件名" > "test.sql"

1

2

3

4

要到對(duì)應(yīng)目錄，不然就需要指定路徑：

查看test.sql：

使用 binlog 恢復(fù)數(shù)據(jù)

// 按指定時(shí)間恢復(fù) mysqlbinlog --start-datetime="2020-10-25 18:00:00" --stop- datetime="2020-10-26 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123

1

2

3

// 按事件位置號(hào)恢復(fù) mysqlbinlog --start-position=592 --stop-position=708 mysqlbinlog.000001 | mysql -u root -p

1

2

清除binlog文件

指定日志文件

purge binary logs to 'mysqlbinlog.000001';

1

按時(shí)間清日志

purge binary logs before '2020-10-10 00:00:00';

1

清除所有日志

reset master; //清除所有文件

1

ps：可以通過(guò)設(shè)置expire_logs_days參數(shù)來(lái)啟動(dòng)自動(dòng)清理功能。默認(rèn)值為0表示沒(méi)啟用。設(shè)置為1表示超出1天binlog文件會(huì)自動(dòng)刪除掉。

mysql> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mysqlbinlog.000001 | 519 | +--------------------+-----------+ 1 row in set (0.00 sec) mysql> show master status\G; *************************** 1. row *************************** File: mysqlbinlog.000001 Position: 519 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) mysql> show binlog events \G; ... mysql> show binlog events in 'mysqlbinlog.000001'\G; *************************** 1. row *************************** Log_name: mysqlbinlog.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 124 Info: Server ver: 8.0.13, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysqlbinlog.000001 Pos: 124 Event_type: Previous_gtids Server_id: 1 End_log_pos: 155 Info: *************************** 3. row *************************** Log_name: mysqlbinlog.000001 Pos: 155 Event_type: Anonymous_Gtid Server_id: 1 End_log_pos: 228 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 4. row *************************** Log_name: mysqlbinlog.000001 Pos: 228 Event_type: Query Server_id: 1 End_log_pos: 338 Info: create database `test` /* xid=16 */ *************************** 5. row *************************** Log_name: mysqlbinlog.000001 Pos: 338 Event_type: Anonymous_Gtid Server_id: 1 End_log_pos: 411 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 6. row *************************** Log_name: mysqlbinlog.000001 Pos: 411 Event_type: Query Server_id: 1 End_log_pos: 519 Info: drop database `test` /* xid=40 */ 6 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

6、Redo Log和Binlog區(qū)別

Redo log是InnoDB特有的功能，屬于事務(wù)日志；Binlog是MySQL的功能

Redo log不是二進(jìn)制文件，而B(niǎo)inlog是一種二進(jìn)制的日志文件

Redo log是物理日志，記錄的是數(shù)據(jù)頁(yè)更新的狀態(tài)內(nèi)容，Binlog是邏輯日志，記錄的是更新過(guò)程

Redo Log作為服務(wù)器異常宕機(jī)后事務(wù)數(shù)據(jù)自動(dòng)恢復(fù)使用，Binlog可以作為主從復(fù)制和數(shù)據(jù)恢復(fù)使用

Redo Log日志是循環(huán)寫(xiě)，日志空間大小是固定，Binlog是追加寫(xiě)入，不會(huì)覆蓋寫(xiě)

對(duì)于redo log可以參考我之前博客：MySQL系列之事務(wù)日志redo log學(xué)習(xí)筆記

附錄：參考資料

MySQL Binlog 介紹

詳細(xì)分析MySQL事務(wù)日志(redo log和undo log)

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。