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的解析器玩玩
参考：
网友评论