MySQL BinLog如何恢复误更新删除数据

来自：网络

时间：2024-08-28

阅读：

1. 前言

实际开发、生产场景中会出现，RDS 宕机时数据记录未入库导致数据丢失；

误更新、误删除操作导致记录被修改或数据丢失的情况；

对于 MySQL 我们可以通过 BinLog 找回误删除的数据。

BinLog 是 MySQL 自带的日志，偏向逻辑性的日志，记录的是对哪个表的哪一行做了更新操作，更新前后的值是什么。

2. BinLog 说明

Binary Logging 是个存储二进制文件，有两种文件类型

索引文件

文件名后缀为.index;
用于记录哪些日志文件正在被使用

日志文件

文件名后缀为.00000*;
记录数据库所有的 DDL 和 DML (除了数据查询语句)语句事件

binlog_format 三种日志格式

Statement

每一条修改数据的 sql 都会记录到 master 的 bin_log 中，slave 在复制的时候 sql 进程会解析成 master 端执行过的相同的 sql 在 slave 库上再次执行

Row

日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改

Mixed

混合模式，根据实际执行语句选择 Statement 和 Row 的一种进行执行。
比如对于修改表结构该表所有记录都会变更，此时会以 Statement 模式记录，而不是所有行记录变更都写入日志。

	优点	缺点
Statement	记录执行语句及上下文信息，不记录被语句影响到的每一条数据的信息，日志量较小	主从同步可能会存在日志上下文信息不正确导致执行结果不一致的问题
Row	记录每一条数据的变化，足够详细，不会出现主从复制数据不一致的问题	日志量较大
Mixed	混合模式，根据语句执行及 MySQL 优化策略选择一种模式记录日志，日志量可控	相对 Row 模式不够详细

通过设置配置文件 my.ini 的 log_bin 属性来开启日志功能，此时对数据库的操作会记录 binlog 日志并写入磁盘文件。

编辑 my.ini 可开启并配置 Binlog 策略

# Binary Logging.
# binlog 日志文件路径
log-bin=C:/ProgramData/MySQL/BinlogData
# binlog 日志格式
binlog_format=ROW
# binlog 过期清理时间/天
expire_logs_days=90
# binlog 日志文件大小/个
max_binlog_size=100M
# binlog 缓存大小
binlog_cache_size=4M
max_binlog_cache_size=512M

通过应用程序 mysqlbinlog.exe 可以从日志文件中读取指定时间段的数据库语句变更详细日志。

mysqlbinlog --base64-output=decode-rows -v --database=<数据库名称> --start-datetime="<起始时间>" --stop-datetime="<截至时间>" <日志文件> > <输出文件>

如下所示某时刻的变更详细日志，某数据库在 211129 16:53:31 时刻一条更新操作日志，根据该日志可清晰的指定更新前的数据，依据该日志可恢复记录数据到更新前的数据。

#211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6 	Update_rows: table id 337 flags: STMT_END_F
### UPDATE `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### SET
###   @1=2
###   @2='niaonao'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL

3. BinLog 配置是否被开启

查看是否开启 BinLog，属性 log_bin 的值为 OFF 则没开启该功能无法通过本文下面的 BigLog 方式恢复数据。

log_bin 的值为 ON 则支持恢复数据。

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.01 sec)

4. BinLog 配置怎么开启

找到 MySQL 的配置文件，配置文件路径 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini，只需要把 log-bin 开放后(去除属性前面的 # 注释)重启服务即可，不生效则确认 my.ini 配置无误多次重启后生效。

可指定文件生成路径，默认在相对路径下。此处指定 binlog 文件生成配置 C:\ProgramData\MySQL\BinlogData，日志记录格式配置为 ROW，单文件最大 100M，三个月清理历史文件。

log-bin 不指定时，默认使用的设置是 log-bin=mysql-bin；binlog_format 默认使用 STATEMENT；

# Binary Logging.
# binlog 日志文件
log-bin=C:\ProgramData\MySQL\BinlogData
# binlog 日志格式
binlog_format=ROW
# binlog 过期清理时间/天
expire_logs_days=90
# binlog 日志文件大小/个
max_binlog_size=100M
# binlog 缓存大小
binlog_cache_size=4M
max_binlog_cache_size=512M

重启服务

服务名称就是 MySQL56，可通过 WIN+R，输入 services.msc 查看服务。

MySQL BinLog如何恢复误更新删除数据

PS C:\Users\Lenovo> net stop MySQL56
MySQL56 服务正在停止.
MySQL56 服务已成功停止。

PS C:\Users\Lenovo> net start MySQL56
MySQL56 服务正在启动 .
MySQL56 服务已经启动成功。

PS C:\Users\Lenovo> mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | C:\ProgramData\MySQL\BinlogData       |
| log_bin_index                   | C:\ProgramData\MySQL\BinlogData.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)

可以通过 show variables like 'log_bin%' 看到此时配置 log_bin 已开启为 ON.

5. 误更新或删除数据

以数据库 ecrm_jd 的用户表 xxl_job_user 演示，更新一条记录，删除两条记录。

再根据 binlog 日志来追踪数据。

mysql> use ecrm_jd;
Database changed

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
+----+----------+----------------------------------+------+------------+
1 row in set (0.00 sec)

mysql> insert xxl_job_user(username,password,role) values('admin11', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin12', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin13', 'e10adc3949ba59abbe56e057f20f883e', 0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  2 | admin11  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  4 | admin13  | e10adc3949ba59abbe56e057f20f883e |    0 | NULL       |
+----+----------+----------------------------------+------+------------+
4 rows in set (0.00 sec)

mysql> update xxl_job_user set username = 'niaonao' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from xxl_job_user where id in (1,4);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  2 | niaonao  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
+----+----------+----------------------------------+------+------------+
2 rows in set (0.00 sec)

6. binlog 日志跟踪查找被删除的数据

MySQL BinLog如何恢复误更新删除数据

这里是 2021-11-29 16:56 左右修改的，在 C:\ProgramData\MySQL 下找到 <filename>.000003 日志文件。

通过应用程序 mysqlbinlog 查看 binlog。

去安装路径下找到应用程序 ~\MySQL Server 5.6\bin\mysqlbinlog.exe

WIN+R 输入 cmd 打开命令行窗口，切换到 mysqlbinlog 所在目录，执行以下命令导出脚本。

mysqlbinlog --base64-output=decode-rows -v --database=<数据库名称> --start-datetime="<起始时间>" --stop-datetime="<截至时间>" <日志文件> > <输出文件>

此处从文件 BinlogData.000003 中解析导出数据库 ecrm_jd 在 2021-11-29 16:50:00 ~ 2021-11-29 17:30:00 时间内的日志，输出到文件 binlog202111291650.sql

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqlbinlog --base64-output=decode-rows -v --database=ecrm_jd --start-datetime="2021-11-29 16:50:00" --stop-datetime="2021-11-29 17:30:00" C:\ProgramData\MySQL\BinlogData.000003 > binlog202111291650.sql

打开文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211129 15:20:44 server id 1  end_log_pos 120 CRC32 0x7b673bd6 	Start: binlog v 4, server v 5.6.21-log created 211129 15:20:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#211129 16:52:19 server id 1  end_log_pos 195 CRC32 0xeeb79b0f 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638175939/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#211129 16:52:19 server id 1  end_log_pos 263 CRC32 0xd1715424 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 263
#211129 16:52:19 server id 1  end_log_pos 439 CRC32 0x93f08743 	Write_rows: table id 337 flags: STMT_END_F
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=3
###   @2='admin12'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=4
###   @2='admin13'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=0
###   @5=NULL
# at 439
#211129 16:52:19 server id 1  end_log_pos 470 CRC32 0x48cdfe14 	Xid = 391
COMMIT/*!*/;
# at 470
#211129 16:53:31 server id 1  end_log_pos 545 CRC32 0xdc9527f7 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638176011/*!*/;
BEGIN
/*!*/;
# at 545
#211129 16:53:31 server id 1  end_log_pos 613 CRC32 0x7b2ee120 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 613
#211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6 	Update_rows: table id 337 flags: STMT_END_F
### UPDATE `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### SET
###   @1=2
###   @2='niaonao'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
# at 743
#211129 16:53:31 server id 1  end_log_pos 774 CRC32 0x0423f88b 	Xid = 397
COMMIT/*!*/;
# at 774
#211129 16:54:21 server id 1  end_log_pos 849 CRC32 0x6280ce3c 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638176061/*!*/;
BEGIN
/*!*/;
# at 849
#211129 16:54:21 server id 1  end_log_pos 917 CRC32 0xd4ee0972 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 917
#211129 16:54:21 server id 1  end_log_pos 1044 CRC32 0xcc058cef 	Delete_rows: table id 337 flags: STMT_END_F
### DELETE FROM `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=1
###   @2='admin'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### DELETE FROM `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=4
###   @2='admin13'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=0
###   @5=NULL
# at 1044
#211129 16:54:21 server id 1  end_log_pos 1075 CRC32 0x23f55056 	Xid = 401
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过程序 mysqlbinlog 恢复的日志中可以查看指定时间段内的数据库操作语句，找到误 UPDATE、DELETE 语句可以看到被删除的记录属性和属性值，依据该日志可恢复该记录。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。

SQL Server:SELECT TOP 10 * FROM T_USER ORDER BY NEWID()ORACLE:SELECT * FROM (SELECT * FROM T_USER ORDER BY DBMS_RANDOM.RANDOM()) WHERE RONUM <= 10MySQL:SELECT *

2024-10-21 20:47:50

一、limit用法在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。SELECT * FROM table LIMIT [off

2024-10-21 20:44:48

目录问题原因解决办法如何设置和查看使用Navicat Premium 12导入mysql的sql文件时，导入失败，出现如下错误：2006 - MySQL server has gone away。mysql出现ERROR : (2006, &

2024-10-21 20:43:22

今天早上启动了 phpstudy 软件中的 MySQL 8.0 数据库时，发现它启动后约过了 3 秒就自行关闭了。接着又自动启动然后再次关闭，似乎陷入了一个“开-闭-开”的循环。这导致我无

2024-10-18 23:15:41

目录

1. 每秒交易量

2. 每分钟交易量

3. 每5分钟交易量

4. 每10分钟交易量

5. 每30分钟交易量

结合 WHERE

2024-10-18 23:15:27

目录 Redis是什么？ Redis的特点依赖配置 redis的配置类（用于格式转换，处理乱码） String（字符串）特点业务场景代码使用案例 List（列表）特点业务场景代码使用案例 S

2024-10-18 23:15:18

目录 Oracle中BLOB、CLOB的读取和写入基于SQL的方式实现CLOB、BLOB的插入与读取 1. 插入大类型数据 2. 读取大类型数据并转换为字符串基于Java方式实现CLOB和B

2024-10-18 23:15:09

目录 dbeaver导出mysql数据库右键表-tools-Dump database 按默认设置总结 dbeaver导出mysql数据库右键表-tools-Dump database按默认设置只需修改输出文件夹我这里把

2024-10-18 23:15:01

目录 DBeaver如何导入excel中的大量数据步骤一：将Excel文件转换为CSV格式步骤二：在DBeaver中创建数据库表（如果尚未创建）步骤三：导入CSV文件到数据库表注意事项总结

2024-10-18 23:14:43

目录一、先清理归档日志使得数据库能够正常连接二、排查问题三、处理问题四、清理后效果最近客户单位的oracle数据库出了问题，经常出现无法连接,报错提示 ORA-00257: arc

2024-10-18 23:14:30

目录函数单行函数字符函数数学函数日期函数流程控制函数分组函数分组查询按单个字段分组在分组前进行条件筛选在分组之后进行条件筛选按多字段分

2024-10-18 23:14:18

目录 1、计算两个时间字符串之间的差值 2、字符串转换成日期/时间类型 3、计算两个日期和时间之间的差值在 SQL Server 中，计算两个时间字符串之间的差值首先将这些字符串转

2024-10-18 23:14:10

目录问题描述原因分析解决方案在Oracle数据库中，DECODE 函数是一个非常实用的条件处理函数，通常用于替代简单的 CASE WHEN 语句。它根据给定的值列表进行匹配，如果匹配成功

2024-10-18 23:14:02

目录描述实践描述删除表大批量数据，这是一个比较少的事件。但在实际的业务开发中或者数据测试也会遇到这种情况。比如定期从日志大表中删除几百万的数据记录；删除表数据的

2024-10-18 23:13:52

目录前言简介概述 DELETE 操作的基本概念常用的 DELETE 方法核心源码解读简单 DELETE 语句批量 DELETE 示例案例分析案例1：使用简单 DELETE 删除用户数据

2024-10-18 23:13:43

目录一、MySQL清空表数据三种方法 1.1 清空表数据：truncate 1.2 删除表：drop 1.3 删除/清空表数据：delete 二、使用原则三、truncate、drop、delete区别 1.操作类型

2024-10-14 19:50:11

目录一、问题描述二、解决方案 1.使用mysqldump命令备份数据库 2.创建目标数据库 3.导入数据到目标数据库三、补充知识 3.1 mysqldump命令介绍 3.2 常用备份命

2024-10-14 19:50:03

目录一、limit深分页为什么会变慢二、优化方案 2.1 通过子查询优化（覆盖索引）回顾B+树结构覆盖索引把条件转移到主键索引树 2.2 INNER JOIN 延迟关联

2024-10-14 19:49:55

目录 MySQL的TRUNCATE()函数规则如下函数示例当 X 为正数时当 X 为负数时总结 MySQL的TRUNCATE()函数TRUNCATE(X,D) 是MySQL自带的一个系统函数。其中，X是数值，D

2024-10-14 19:49:37

目录方法一：在电脑设置中查看电脑iP地址方法二：通过运行程序查看电脑IP地址方法三：通过浏览器查看电脑IP地址拓展：在自己电脑建立了MYSQL数据库，但是想在其它的电脑进行连接，

2024-10-14 19:49:29

目录概念 Pipeline 底层原理分析 Redis单个命令执行基本步骤 RTT 时间 Redis Pipeline Pipeline实际应用场景数据导入导出数据处理批量操作其他应用场

2024-10-14 19:49:13

修改配置文件1.打开Postgresql配置文件，如：C:\Program Files\PostgreSQL\14\data\postgresql.conf
2.修改如下，如果字段被#注释，则去掉#log_directory = 'log'
log_file

2024-10-14 19:49:02

目录创建分区删除分区分区性能总结说明：分区表，顾名思义，就是一张表根据规则，划分多个区，通过分区，实现一种“逻辑隔离”，这在Saas系统中是非常常见的。本文介绍如何

2024-10-14 19:48:52

目录 1.创建mysql基础配置 2.创建pvc挂载 3.创建工作负载 4.创建服务（创建服务后才能进行外部访问） 5.测试mysql是否能正常访问演示示例使用的是3.4.1，各版本有名字差异功能是

2024-10-14 19:48:36

目录安装步骤 1. 解压安装包 2. 移动解压后的文件夹 3. 创建MySQL用户 4. 设置权限 5. 配置MySQL 6. 设置环境变量 7. 初始化数据库 8. 设置系统服务 9. 设置roo

2024-10-14 19:48:11

目录解决方案 1. 检查是否输入正确的密码 2. 使用“跳过权限表”的方式重置 root 密码 2.1 停止 MySQL 服务 2.2 以跳过权限表模式启动 MySQL 2.3 重

2024-10-14 19:48:03

目录实现演示说明：实际开发中，我们在前端页面上点击了一个按钮，访问了一个接口，这时因为网络波动或者其他原因，页面上没有反应，用户可能会在短时间内再次点击一次或者用户以为没

2024-10-14 19:47:53

发生的错误好吧，当我在 laragon 应用程序中从mysql 5.1.72升级到mysql 8.0.32时，发生了这个错误。当我在 laragon 应用程序中 start all 时，出现的错误是这样的。laragon 中的

2024-09-17 21:32:55

介绍在本实验中，我们将学习和练习索引、视图、备份和恢复。这些概念对于数据库管理员来说非常重要。学习目标创建索引创建视图备份与恢复准备开始之前，我们需要准备好环境

2024-09-17 21:32:18

如何将 SQL 文件导入 MySQL 数据库导入 MySQL 数据库文件的方法：使用 MySQL 客户端打开 MySQL 客户端并连接到目标数据库。使用 SOURCE 命令，后跟要导入的 SQL 文件路径：SOUR

2024-09-17 21:32:00

2018-08-19

2020-03-22

2020-12-17

2020-03-22

2020-10-15

2021-01-28

2020-03-22

2021-01-21

2020-10-28

MySQL BinLog如何恢复误更新删除数据

目录

1. 前言

2. BinLog 说明

索引文件

日志文件

binlog_format 三种日志格式

3. BinLog 配置是否被开启

4. BinLog 配置怎么开启

5. 误更新或删除数据

6. binlog 日志跟踪查找被删除的数据

总结

热点内容

免费资源网

在线工具

扫一扫随时看

本站下载频道