mysqlbinlog命令使用

一.MySQl binlog介绍

1.1 binlog的作用

• 逻辑日志,记录的是数据库内部的所有变动（sql语句 行的改变）
  server层日志，binlog不仅仅记录innodb的变动，也记录myisam存储引擎的变动。
• innodb redo 是存储引擎层，和binlog不是一层，redo只记录innodb的变化。
• binlog不记录 select show 等查询语句,记录dml ddl等数据库变动的语句.

1. 可以查看mysql变更
2. mysql的复制架构（主从）
3. mysql备份恢复（可以通过binlog进行数据的补齐或者回滚）

1.2 清理binlog

• reset master; //删除master的binlog，线上不要使用
• purge master logs before ‘2012-03-30 17:20:00’; //删除指定日期以前的日志索引中binlog日志文件
• purge master logs to ‘binlog.000002’; //删除指定日志文件的日志索引中binlog日志文件

如果想直接删除binlog

rm binlog.000002
(不要删除当前正在使用的binlog，也就是编号最大的binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不会自动更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002
主从复制如果删掉了，容易从库报错（保证从库不再同步该binlog的数据）

1.3 binlog和redo的区别

• redo 物理逻辑日志，记录的是数据页的改变；binlog 是逻辑日志， 记录的SQL或者是行改变
• redo是innodb存储引擎层，binlog是server层
• redo时时刻刻都在写入文件，binlog是 提交事务的写入（具体写入策略跟具体参数有关）

1.4 常用参数

--start-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。
--stop-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复。
--start-position=N 从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=N 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
--base64-output=DECODE-ROWS 会显示出row模式带来的sql变更
-d 与 --database 效果相同，指定一个数据库名称。
--offset=N，-o N 跳过前N个条目。
--no-defaults 可以避免 my.cnf 里配了 [client] 某些 mysqlbinlog 没有的参数导致 mysqlbinlog 失败
--skip-gtids 不保留 GTID 事件信息，这样回放 binlog 时会跟执行新事物一样，生成新的 GTID
--include-gtids 只解析出指定的 GTID 的事务
--exclude-gtids 不解析指定的 GTID 的事务
--rewrite-db 恢复时需要重写到新的DB，使用参数--rewrite-db='ywdb->ywdbnew'，否则会写入到错误的库中。

注意:

1. 不要查看当前正在写入的binlog文件
2. 不要加--force参数强制访问
3. 如果binlog格式是行模式的,请加 -vv参数

1.5 解析指定范围的 binlog

a. 时间范围

--start-datetime、--stop-datetime 解析出指定时间范围内的 binlog，这个只适合粗略的解析，不精准，因此不要用来回放 binlog。有个小技巧：如果只能确定大概的时间范围，而且不确定在哪个 binlog 中，可以直接解析多个 binlog。比如大概在 11:20-12:00 内做了个表删除操作，但这个时间内有多个 binlog，可以这样：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. 偏移量范围

--start-position、--stop-position 解析 binlog 指定偏移量范围内的 binlog。如果同时指定了 --start-position 和 --stop-position，并且是解析多个 binlog，则 --start-position 只对第一个 binlog 生效，--stop-position 只对最后一个 binlog 生效。

这个常用场景是：已经解析过一次 binlog 并取得目标事务的起始position 后，精确的解析这一段 binlog：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows  --start-position='537' --stop-position='945' mysql-bin.000204
# at 537           "起始位置是 GTID event 前的这个 position"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes 
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/; 
...
... 
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/; 
COMMIT /*!*/; 
# at 945          "结束位置是 COMMIT event 后的这个 position"

c. GTID 范围

--include-gtids、--exclude-gtids 详细看参数解释。

1.6 回放 binlog

• 回放一定不能加 --base64-output=decode-rows 参数，因为不会解析出行格式（这是binlog真正有效的部分）；
• 回放也可以用上面指定范围的参数；
• 解析 binlog 回放到本实例，不需要修改 server id，但要注意 GTID 是否已存在；
• GTID 已经存在，回放不会报错，但也不会真正回放这些事务，可以通过 --skip-gtids 参数跳过 GTID 的限制；

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

1.7 示例

（1）基于开始/结束时间
mysqlbinlog --start-datetime='2018-07-10 00:00:00' --stop-datetime='2018-07-10 01:01:01' -d 库名 二进制文件
 
（2）基于pos值
mysqlbinlog --start-position=107 --stop-position=1000 -d 库名 二进制文件
 
（3）转换为可读文本，加 --base64-output=decode-rows 不显示行格式的内容
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv -d 库名 二进制文件

（3）解析指定 GTID 的事务
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199

（4）在输出中只显示语句(-s/--short-term)
mysqlbinlog --short-form mysqld-bin.000001
mysqlbinlog -s mysqld-bin.000001

（5）从远程服务器获取二进制日志
mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

解析并搜索zhenxing这条记

BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'

for binlog in ${BINLOG_LIST}
do 
    echo "====== ${binlog}"
    mysqlbinlog -vv ${binlog}|grep -iEw "zhenxing"
done

过滤出哪些binlog对该表做了DELETE或UPDATE

## 这里我通过已知的故障时间区间将涉及的binlog列出来做循环解析
BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'

for binlog in ${BINLOG_LIST}
do 
    echo "====== ${binlog}"
    mysqlbinlog --no-defaults --base64-output=decode-rows -vv ${binlog} | awk '/###/ {if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count) print i,"\t",count[i]}' | column -t | sort -k2nr|grep -i t_dba_info 
done

排除一些特殊操作（truncate|create|drop）的可能性

BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'

for binlog in ${BINLOG_LIST}
do 
    echo "====== ${binlog}"
    mysqlbinlog ${binlog}|egrep -iEw "truncate|create|drop"
done