mysql如何利用binlog进行数据恢复详解

 

前言：

MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个:

* 数据回复

* 主从数据库。用于slave端执行增删改，保持与master同步

如果线上误操作了一个数据或者被恶意攻击了，由于是直接修改的数据库，所有唯一的恢复方式就在mysql的binlog。binlog使用的是ROW模式，即受影响的每条记录都会生成一个sql。同时利用了binlog2sql项目。

mysqlbinlog 官方操作文档：https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

注意：

数据库最好要作定时快照备份,避免需要增量更新的数量过多

 

binlog基本配置和格式

binlog基本配置

binlog需要在mysql的配置文件的mysqld节点中进行配置：

# 日志中的Serverid
server-id = 1
# 日志路径
log_bin  = /var/log/mysql/mysql-bin.log
# 保存几天的日志
expire_logs_days = 10
# 每个binlog的大小
max_binlog_size = 1000M
#binlgo模式
binlog_format=ROW
# 默认是所有记录，可以配置哪些需要记录，哪些不记录
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

查看binlog状态

• SHOW BINARY LOGS; 查看binlog文件
• SHOW VARIABLES LIKE '%log_bin%' 查看日志状态
• SHOW MASTER STATUS 查看日志文件位置

binlog的三种格式

1.ROW

针对行记录日志，每行修改产生一条记录(即每一行数据操作都会产生独立的sql记录)。

优点：上下文信息比较全，恢复某条误操作时可以直接在日志中查找到原文信息，对于主从复制支持好。

缺点：输出非常大，如果是Alter语句将产生大量的记录

2.STATEMENT

针对sql语句的，每条语句产生一条记录

优点：产生的日志量比较小，主从版本可以不一致

缺点：主从有些语句不能支持，像自增主键和UUID这种类型的

3.MIX

结合了两种的优点，一般情况下都采用STATEMENT模式，对于不支持的语句采用ROW模式

常用操作语法：（要找到能允许mysqlbinlog 执行的位置运行）

数据操作的查看与恢复:

mysql自带的mysqlbinlog

由于binlog是二进制的，所以需要先转换成文本文件，一般可以采用Mysql自带的mysqlbinlog转换成文本。

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2020-04-29

参数说明

• --no-defaults 为了防止报错：mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
• --base64-output='decode-rows' 和-v一起使用， 进行base64解码
  其他有很多用来限定范围的参数，比如数据库，起始时间，起始位置等等。这些参数在查找误操作的时候非常有用。

• 操作选项：

  <

  Option Name	Description	Introduced
  --base64-output	Print binary log entries using base-64 encoding
  --bind-address	Use specified network interface to connect to MySQL Server
  --binlog-row-event-max-size	Binary log max event size
  --character-sets-dir	Directory where character sets are installed