binlog

Mysql Binlog是二進制格式的日志文件，用來記錄Mysql內部對數據庫的改動(只記錄對數據的修改操作)，主要用于數據庫的主從復制以及增量恢復。

獲取binlog日志列表

MariaDB [examples]> show master logs;

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

| Log_name | File_size |

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

| ON.000001 | 9893 |

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

可知當前只有一個binlog文件：ON.000001

MariaDB [examples]> show binary logs;

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

| Log_name | File_size |

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

| ON.000001 | 9893 |

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

1 row in set (0.134 sec)

查看examples.prefix的表結構

MariaDB [examples]> show create table prefix \G;

*************************** 1. row ***************************

Table: prefix

Create Table: CREATE TABLE `prefix` (

`a` int(10) NOT NULL,

`b` int(10) NOT NULL,

`c` int(10) NOT NULL,

KEY `I_index` (`a`,`b`,`c`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

對全表進行更新操作

MariaDB [examples]> update prefix set b=a+101;

Query OK, 32 rows affected (0.201 sec)

Rows matched: 32 Changed: 32 Warnings: 0

可以看到有32條數據發生了更新

查看binlog(也可以在日志文件目錄通過mysqlbinlog ON.000001命令查看)

MariaDB [(none)]> show binlog events in 'ON.000001' \G;

...

...

...

*************************** 108. row ***************************

Log_name: ON.000001

Pos: 9893

Event_type: Gtid

Server_id: 1

End_log_pos: 9935

Info: BEGIN GTID 0-1-48

*************************** 109. row ***************************

Log_name: ON.000001

Pos: 9935

Event_type: Query

Server_id: 1

End_log_pos: 10031

Info: use `examples`; update prefix set b=a+101

*************************** 110. row ***************************

Log_name: ON.000001

Pos: 10031

Event_type: Xid

Server_id: 1

End_log_pos: 10062

Info: COMMIT /* xid=883 */

有很多條記錄，本次更新操作增加了這三條數據，其中第109是我們顯示執行的SQL，而第108與109條是事務的開始與結束。

關于Xid:

Binlog::GTID_EVENT:

A global transaction identifier (GTID)

開啟事務

Binlog::QUERY_EVENT:

The query event is used to send text querys right the binlog.

mysql中create，insert，update，delete的Event_type均為Query類型，初看覺得很奇怪，這里只是類型定義而已。

Binlog::XID_EVENT:

Transaction ID for 2PC, written whenever a COMMIT is expected.

提交事務

看看官方對各個字段的解釋

Log_name

The name of the file that is being listed.

Pos

The position at which the event occurs.

Event_type

An identifier that describes the event type.

Server_id

The server ID of the server on which the event originated.

End_log_pos

The position at which the next event begins, which is equal to Pos plus the size of the event.

Info

More detailed information about the event type. The format of this information depends on the event type.

redo log

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions

關于redo log，文件名前綴為ib_logfile, 嘗試用mysqlbinlog讀取

mysqlbinlog ib_logfile1

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

ERROR: File is not a binary log file.

google一圈，目前尚沒發現有可以解析該類文件的工具，只能從文檔中了解其具體結構。

后面有時間研究下源碼，寫個ib_logfile的解析器玩玩

參考：

總結

以上是生活随笔為你收集整理的mysql binlog redo_mysql的binlog与redo log的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。