MySQL的binlog二进制日志文件及内容查看

日志文件查看

查看日志的开启情况

log_bin参数设置的路径,可以找到二进制日志
select @@log_bin;
select @@log_bin_basename;
show variables like '%log_bin%';

查看一共多少个binlog

Master [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.01 sec)
Master [(none)]>flush logs;刷新一个Position

查看mysql正在使用的日志文件

show master status;
| File | Position |
+------------------+----------+
| mysql-bin.000003 | 154
file：当前MySQL正在使用的文件名
Position：最后一个事件的结束位置号

日志内容查看

event查看

Master [binlog]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 6 | 319 | create database binlog |
| mysql-bin.000003 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 384 | Query | 6 | 486 | use `binlog`; create table t1 (id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
Log_name：binlog文件名
Pos：开始的position *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
补充:
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
Master [binlog]>show binlog events in 'mysql-bin.000004' from 154 limit 5
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

binlog文件内容详细查看

mysqlbinlog /data/mysql/mysql-bin.000006
转换row格式的dml语言为可读：
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
只看某一个库的binlog日志：
mysqlbinlog -d binlog /data/binlog/mysql-bin.000003
只看某个时间段的binlog日志：
mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data
/binlog/mysql-bin.000004

基于Position号进行日志截取

核心就是找截取的起点和终点
mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp
/bin.sql