mysql误删后使用binlog恢复数据

1 预期效果

使用 binlog 恢复数据的预期效果是将误删的数据还原到误删之前的状态，以减少或消除数据丢失的影响。通过正确解析和执行 binlog 中的操作记录，可以重新执行误删操作之后的插入、更新或删除操作，从而恢复被误删的数据。

1. 数据恢复：通过恢复误删操作之后的操作记录，可以将误删的数据重新插入到数据库中，还原到误删之前的状态。这意味着恢复后的数据库将包含被误删的数据，以及误删之后的其他操作。
2. 数据一致性：如果只选择了误删操作之后的操作记录进行恢复，而忽略了其他更改操作，可以确保恢复后的数据保持一致性。这意味着只有被误删的数据会恢复，而其他更改操作不会被重新执行。
3. 最小化数据丢失：使用 binlog 恢复数据可以最小化数据丢失的影响。通过恢复误删操作之后的操作记录，可以尽可能地还原被误删的数据，而无需依赖数据库备份或其他手段。

需要注意的是，预期效果可能受到以下因素的影响：

• 其他更改操作：如果误删操作之后进行了其他更改操作，恢复过程可能会导致这些操作被重新执行，可能会引起数据不一致或冲突。因此，在执行恢复操作之前，应仔细分析 binlog 文件，并选择适当的操作记录进行恢复。
• 数据库状态和依赖项：误删操作可能依赖于特定的数据库状态或外部数据。在执行恢复操作之前，应确保数据库的环境和依赖项与误删操作发生时相同，以确保恢复的数据能够正确关联和使用。
• 恢复操作的正确性：正确解析和执行 binlog 中的操作记录是关键。在执行恢复操作之前，应仔细验证和测试恢复过程，确保操作记录的准确性和正确性。

2实现原理

binlog记录了数据库中的所有更改操作，以便在需要时进行数据恢复、主从复制和数据审计等操作。通过解析和分析binlog，可以还原数据库中的数据更改历史，并进行相应的操作，例如数据恢复或主从复制等

下面是使用 binlog 恢复数据的一般原理：

1. 确认误删的时间点：首先，需要确定误删操作发生的时间点。这将帮助你确定要恢复的数据范围，以便从 binlog 中提取相应的操作记录。
2. 导出 binlog 文件：找到包含误删操作的 binlog 文件。这通常是通过查看 MySQL 数据库的配置文件（如 my.cnf 或 my.ini）中的 binlog 相关配置参数来确定。将该 binlog 文件复制到安全的位置，以便进行恢复操作。
3. 解析 binlog 文件：使用 mysqlbinlog 工具来解析 binlog 文件，并将其转换为可读的 SQL 语句。例如，可以执行以下命令：mysqlbinlog binlog-file > output.sq 其中 binlog-file 是实际的 binlog 文件名，output.sql 是输出的 SQL 文件，包含了所有的操作记录。
4. 过滤和恢复操作：在生成的 SQL 文件中，可以根据误删操作发生的时间点，选择需要恢复的操作记录。可以手动编辑 SQL 文件，删除不需要的操作记录，只保留误删操作之后的操作语句。确保只包含了需要恢复数据的操作。
5. 执行恢复操作：使用数据库客户端连接到 MySQL 数据库，并执行编辑后的 SQL 文件，将其中的操作语句逐个重新执行。这将重新执行误删操作之后的操作，从而还原到误删前的数据状态。

需要注意的是，使用 binlog 恢复数据存在一些限制和风险，包括：

• 误删操作之后的其他修改：如果误删操作之后的时间段内进行了其他更改操作，这些操作也将被重新执行，可能会导致数据不一致或冲突。在恢复数据之前，应仔细分析 binlog 文件，确保只恢复必要的操作。
• 依赖外部数据和状态：如果误删操作涉及到外部数据或依赖于特定的数据库状态，恢复过程可能会受到影响。在执行恢复操作之前，确保数据库的环境和依赖项与误删操作发生时相同。
• 数据库备份和恢复策略：为避免数据丢失和误删除的影响，建议实施定期的数据库备份和恢复策略，并测试和验证备份的可用性和完整性。

3实际操作

3.1 查看自己的binlog日志是否打开

在黑窗口中输入命令查看show variables like 'log_bin%' ; ，一般都是默认打开的

1. **log_bin**变量被设置为 ON，表示二进制日志功能已经启用。
2. **log_bin_basename**显示二进制日志文件的路径和文件名前缀。
3. **log_bin_index**显示二进制日志索引文件的路径。
4. 其他一些与二进制日志相关的配置项的值。**sql_log_bin是 MySQL 中一个非常有用的系统变量,它控制当前会话是否将执行的 SQL 语句记录到二进制日志中。可以通过SET sql_log_bin = 1**;修改成ON

3.2 查看binlog文件

通过上一步查询的log_bin_basename得到的路径打开存储binlog文件的文件夹

可以看到已经有很多log文件了

（这里我们是要测试binlog恢复数据的使用，所以就日志文件都放到一个全新binlog文件中方便查询使用，如果是实际恢复数据的话，就要一个一个的在这些binlog文件中找自己要的那部分文件了。）

3.3 模拟数据库

在数据库中进行 flush logs 命令可以新创一个binlog文件，接下来的操作也就会放到新的文件中了。此时再进入到上面这个文件夹中就会看到又多了一个文件叫做LAPTOP-595LBSCH-bin.000092

假设我们的数据库是7天一备份，然后binlog的过期时间是大于7天的，那么通过备份的数据库+binlog文件就能够恢复数据库到达7天内的任意一个时间点的状态。，下面是一个模拟备份的行为

之后我们进行一些操作，模拟正常数据库操作

​
​
​
添加一条数据：
INSERT INTO `user` (`id`, `name`) VALUES (6, '老六');
将小二改成张三丰：
UPDATE `user` SET `name` = '张三丰' WHERE `id` = 1;
将王五改成王伟：
UPDATE `user` SET `name` = '王伟' WHERE `name` = '王五';
删除整个表：
DROP TABLE `user`;

​

​

​

经过这些操作之后！

3.4 恢复操作实战

现在的处境就是整个表都被删除了，我们想要实现将数据库改成王五刚被改成王伟的数据库的模样

我们要做的就是将上次备份的数据库恢复，然后从上次备份的时间点 - > 到王五刚被改成王伟的时间点 中的binlog操作都找到

3.41、我们在binlog所在的文件夹位置打开黑窗口，然后运行，(注意LAPTOP-595LBSCH-bin.000092是因为测试时候知道刚才的操作一定就在这个文件中，如果不知道就需要逐个打开多个binlog文件然后自己找你想要的那个时间点，)

mysqlbinlog -v --set-charset=utf8mb4 LAPTOP-595LBSCH-bin.000092 > output.txt

之后通过打开这个output.txt文件可能有部分乱码（乱码自己解决，如果实在解决不了只能猜了。），比如找到这一部分，意思就是将王五改成王五的操作，他们的执行行数在1109另一种办法就是在mysql中使用show binlog events in 'LAPTOP-595LBSCH-bin.000092';来查看binlog中的日志，

我们可以看到有4个数据，有写入数据，删除更新数据等，还有最后一个是drop table。

经过这些我们已经得到了想要的信息，数据库上次备份后的binlog开始时间应该是317也就是备份后的第一条ddl语句的begin时间，然后我们想要恢复到的时间是1109，日志文件的名字叫做LAPTOP-595LBSCH-bin.000092也就是更新王五那步操作的commit行，之后就是将这个时间段内binlog记录的操作都输入到备份的数据库中

下面这部操作是在不登陆mysql的黑窗口运行的， | mysql -uroot -p<数据库密码>的意思就是将前面步骤操作的结果输入到后面的命令中

mysqlbinlog --no-defaults --start-position=317 --stop-position=1109 LAPTOP-595LBSCH-bin.000092 | mysql -uroot -p<数据库密码>

此时打开数据库就会发现，数据库已经成功恢复到了删表之前的状态了。