怎查看binlog
binlog本身是一类二进制文件。二进制文件更省空间,写入速度更快,是无法直接打开来查看的。
因此mysql提供了命令mysqlbinlog进行查看。
一般的statement格式的二进制文件,用下面命令就可以
mysqlbinlog mysql-bin.000001
如果是row格式,加上-v或者-vv参数就行,如
mysqlbinlog -vv mysql-bin.000001
1.使用show binlog events
a.获取binlog文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 1288 |
| mysql-bin.000006 | 120 |
+------------------+-----------+
mysql>
b.查看当前正在写入的binlog文件
mysql> show master statusG;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 120
c.查看指定binlog文件的内容语法:
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
1.演示查看一个二进制文件中全部事务
注:binlog文件实际上是由一连串的event组成的一个组,即事务组。
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000005' G
*************************** 1. row *************************** 一个row代表一个事务组
Log_name: mysql-bin.000005
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.29-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000005
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 194
Info: BEGIN
2.演示对一个二进制文件的事务做过滤查询
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000005' FROM 194 LIMIT 2 G;
*************************** 1. row ***************************
Log_name: mysql-bin.000005
Pos: 194
Event_type: Query
Server_id: 1
End_log_pos: 319
Info: use `tpcc`; UPDATE warehouse SET w_ytd = w_ytd + 3232 WHERE w_id = 1
*************************** 2. row ***************************
Log_name: mysql-bin.000005
Pos: 319
Event_type: Query
Server_id: 1
End_log_pos: 458
Info: use `tpcc`; UPDATE district SET d_ytd = d_ytd + 3232 WHERE d_w_id = 1 AND d_id = 3
2 rows in set (0.00 sec)
mysql>