MySql数据恢复Binlog实操详解Binlogbinlog操作mysqlbinlog工具实操将binlog日志转换为txt删表参考

95 阅读 0 评论 63 点赞

我是靠谱客的博主要减肥溪流，最近开发中收集的这篇文章主要介绍MySql数据恢复Binlog实操详解Binlogbinlog操作mysqlbinlog工具实操将binlog日志转换为txt删表参考，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Binlog

介绍

如其名：二进制日志类型：记录所有的除查询语句（Select、show等）外的DDL、DML语句等数据库操作语句。

二进制日志由包含“事件”的文件组成，这些文件描述了对数据库内容的修改。服务器以二进制格式写入这些文件。要以文本格式显示其内容使用 mysqlbinlog。

用途

主从复制：一主二从来举例：master节点开启binlog，master将binlog日志传递给二从（slaves）来实现主从数据同步。
数据恢复：通过mysqlbinlog可实现数据恢复，这里我们侧重将这个。

数据格式

三种binlog格式

Statement ：基于SQL语句的复制，每一条会修改数据的sql都会记录在binlog中。
Row ：不记录sql语句上下文相关信息，仅保存哪条记录被修改。
Mixed：一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

查看数据类型

show variables like 'binlog_format';

在这里插入图片描述

查看是否打开binlog

show variables like 'log_bin';

在这里插入图片描述

设置格式

格式示例在binlog操作中。

Liunx

位置

找到my.ini文件：

whereis my.ini

在这里插入图片描述

添加：

binlog_format=ROW

或者MySQL中直接输入：

set globle binlog_format='MIXED'

windows

位置

my.ini位于：C:ProgramDataMySQLMySQL Server 5.6，而不是位于：C:Program FilesMySQLMySQL Server 5.6的my-default

# Binary Logging.
# log-bin
#文件名称
log_bin=mysql-bin	
binlog-format=Statement
server-id=1

也可以配置全路径：

log_bin=D:mysqlbinlogmysql-bin

重启服务

win + r -> services.msc -> MySql56重启动服务

在这里插入图片描述

验证

show variables like 'log_%';
show variables like 'binlog_format';

在这里插入图片描述

在log_bin_basename目录下：

在这里插入图片描述

binlog操作

MySql中操作。

查看binlog文件

show binary logs;			//查看binlog文件列表
show master status;			//查看master节点上的binlog文件状态，使用哪个binlog文件
show master logs;			//查看master节点上的binlog文件列表

在这里插入图片描述

修改数据cstatus为2：

在这里插入图片描述

查看binlog事务记录

binlog存储的是MySQL的事务操作：

show binlog events;
show binlog events in 'mysql-bin.000001';

在这里插入图片描述

设置过期时间和文件大小

expire_logs_days=7
max_binlog_size=500M

show variables like 'expire_logs_days';

在这里插入图片描述

set global

通过MySQL中这是全局变量的形式修改配置参数：set global expire_logs_days=7

全局变量:服务器每次启动将为所有的全局变量赋初始值,即重启将变为无效设置。最好修改配置文件。

mysqlbinlog工具

在MySQL的安装bin目录（C:Program FilesMySQLMySQL Server 5.6bin）下有mysqlbinlog工具：

查看binlog文件

Statement

mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000001"

直接通过mysqlbinlog看：

在这里插入图片描述

通过mysql内部命令看：

show binlog events in 'mysql-bin.000001';

在这里插入图片描述

查看Row格式

update->insert->delete。

我们可以看到，对比Statement，Row要多出一行表的信息。

在这里插入图片描述

Flush logs

Mysql中执行：

flush logs;

在这里插入图片描述

mysqlbinlog文件将形成并使用新的日志文件。

mysqlbinlog工具实操

调用MySql安装的bin目录下的mysqlbinlog工具实操。

PS:加上指定的数据库：--database=cbry，截图里面第一次操作的时候默认模拟只操作了一个数据库。

小结：无论是：start-position stop-position 还是start-datetime stop-datetime，都是指定一个范围，而mysqlbinlog则是重复这个二进制binlog日志文件里面的位置(Pos)或者时间(datetime)。这里就确定了两个范围：文件、文件里的记录位置。指定后，重复操作范围内的操作，这就是mysqlbinlog的操作。

当你误删一条数据，利用二进制日志恢复的时候你需要寻找的是　创建数据的那个节点（时间）

查看当前使用binlog文件

show master status;

主节点使用了mysql-bin.000002日志文件。

在这里插入图片描述

数据库操作对呀binlog

我们将所有人的状态cstatus设置为1，再设置回2：

在这里插入图片描述

我们可以看到：

cid为1的cstatus修改为2的操作开始于位置:1187 - 1135。

在这里插入图片描述

恢复操作到 “截止时间” Pos(position 2096) 。

C:Program FilesMySQLMySQL Server 5.6bin> 
mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000002" --stop-position 2096  --database=cbry | mysql -uroot -p

在这里插入图片描述

重新查看binlog：我们发现他逆操作（重新执行mysql-bin.00000二进制日志文件中第2096个位置前面的操作）回去了。

在这里插入图片描述

范围修改

重复执行范围内的操作

修改“李四”的cstatus：2->3->4->5

在这里插入图片描述

mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000004" --start-position 199 --stop-position 367  --database=cbry | mysql -uroot -p

在这里插入图片描述

删除李四（mysqlbinlog操作为重复操作验证）

直接执行上面的语句，并没有结果，佐证了前面的：范围操作只是重复执行操作而已。

在这里插入图片描述

start

mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000004" --stop-position 367  --database=cbry | mysql -uroot -p

无效

stop

mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000004" --stop-position 367  --database=cbry | mysql -uroot -p

无效

原因

这是因为之前的插入语句并没有在mysql-bin.000004中。

我们重新清空表数据并flush logs;。再插入，并修改张三的cstatus：2->3->4->5。然后通过操作来验证结果：mysqlbinlog就是重复执行范围内的操作。

在这里插入图片描述

范围执行插入语句恢复

mysqlbinlog "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000005" --start-position 1184 --stop-position 1394  --database=cbry | mysql -uroot -p

在这里插入图片描述

start和stop不再演示，结果就是mysqlbinlog就是重复执行范围内的操作。

将binlog日志转换为txt

主要是为了获取Pos，相当于：show binlog events in 'mysql-bin.000002';在MySQL外的文本化。

mysqlbinlog --start-datetime="2021-07-02 15:00:00" --stop-datetime="2021-07-02 17:00:00" --database=cbry "C:ProgramDataMySQLMySQL Server 5.6datamysql-bin.000002" > C:UsersUserNameDesktopmysql-bin.000002.txt

datetime而不是date（虽然也阔以）：

在这里插入图片描述