Mysql 通过 binlog日志 恢复数据（数据搞丢看过来）

已于 2022-08-21 12:41:32 修改
阅读量1.1w 收藏 49
点赞数 11
CC 4.0 BY-SA版权
分类专栏： 数据库 开发及业务相关问题 文章标签： mysql 数据库 sql
于 2022-08-21 12:40:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Jokers_lin/article/details/126449539
本文详细介绍了如何通过MySQL binlog恢复误删数据库和表的数据，包括锁定表、定位binlog日志、选择恢复范围并执行回滚命令。特别关注了处理误操作后的数据恢复策略和可能遇到的问题.

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.查看binlog是否开启

#查看binlog是否开启
show variables like '%log_bin%';

 2.锁表,防止数据被污染（可根据需求，不阻塞业务的情况）

#锁表,防止数据被污染
lock tables 表名 read;

3.查询最新的binlog 获取binlog日志名称 下一步需要用到

show master status;

4.查看binlog 日志 

show binlog events in 'binlog.000067';

5.确定恢复范围后，执行如下命令，回滚到结束时数据状态 

mysqlbinlog  --start-position='起始Pos' --stop-position='结束Pos' D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

6.例子（数据库误删后恢复）

6.1因为是非重要步骤，仅为突出效果，为了方便操作我就不再命令行中创建数据库了，直接通过navicat工具创建

 6.2刷新后可见到刚刚创建的数据库log-test

 6.3由于没有数据，我们添加点假数据，还是为了节约时间，我们用老办法，navicat可视化创建一张user表及添加多条测试数据进去

6.4数据准备好了，我们来模仿误操作的情况，执行dorp命令删除掉该数据库（删库执行有风险-千万注意执行环境）

drop database log-test;

由于含有非法字符，所以必须使用` `符号

 刷新navicat工具，发现数据库已被删除

到目前为止删库已经操作结束，接下来就是如何通过binlog日志恢复回来了

6.5 查询最新的binlog 获取binlog日志名称 

SHOW MASTER status;

得到当前最新binlog日志为binlog.000067 （该文件为屋里文件，可在mysql安装目录下查看）

 物理文件如图所示

6.6查看该binlog 日志 

注意图中日志细节

由此我们要想恢复数据库，也就是必须回滚到执行 删除数据库前

由此我们可以得到 起始的pos 和 结束的Pos

6.7已经确定恢复范围，执行命令，回滚到结束时数据状态 

mysqlbinlog --no-defaults --start-position=5847 --stop-position=6974 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

--start-position=起始的pos

--stop-position=结束的Pos

D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 binlog所处位置的路径

这个命令需要在mysql 命令行中执行，我们进入到mysql 安装目录的 bin 目录

发现mysqlbinlog.exe 可执行文件

执行命令 并且输入 mysql 密码

 到此数据库已经恢复成功，我们还是为了方便使用navicat刷新查看 

数据库及其表数据 均已经恢复好了

7.例子（数据库中表数据 误操作后恢复）

7.1我们还是按照这张表操作，增加数据及age字段

 执行update操作，模拟误操作导致真实数据被覆盖 （忘记加where条件，导致影响行数超出预期）

 7.2模拟操作失误，将所有用户年龄都改为了同一个值

目前数据库数据内容如下

 到目前为止误操作导致表数据异常已经操作结束，接下来就是如何通过binlog日志恢复回来了

7.3老规矩先查询最新的binlog 获取binlog日志名称

7.4然后查看该binlog 日志 

注意日志细节

我们目前的预期结果是，恢复到误操作user 表 age字段前，也就是误操作产生的日志记录的上一条COMMIT

那么我们可以由此获取到的起始的pos 和 结束的Pos

7.5通过mysql命令行我们可以执行命令 

mysqlbinlog --no-defaults --start-position=17639 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

我们可以看出当前只是恢复了最后一条数据，显然这样恢复是达不到预期结果的

7.6预期结果出现偏差（小结） 

照目前的表现证明，在现有的数据基础上，只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作，

按照这个逻辑的话，也就是我们还要再此基础上在找到上两次操作，首次添加第一条（张三）数据age的起始Pos - 然后到误操作前一条的结束Pos（王五），扩大区间范围，即可恢复成功。

不过在不知道自己操作了几步的情况下，很难找到对应的起始Pos 到 结束Pos， 这个时候就需要前面提到的，发现操作失误后，赶紧进行锁表操作，防止进一步弄脏数据

我们就简单粗暴点，换种思路，也就是我们摘除掉最后一次的误操作日志，Pos从创建表后首次操作开始 到 误操作前

mysqlbinlog --no-defaults --start-position=5847 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

 7.7 执行报异常

执行后出现异常，无法创建数据库，因为数据库存在！！！ 这个时候如果日志区间中包含创建数据库，必须要先删除掉数据库才可。 包含数据库 与 表 均如此会抛出异常

7.8重新确定区间 

我们跳过创库 -跳过创表等操作后 开始

mysqlbinlog --no-defaults --start-position=6283 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

--start-position=6283 创建表后首操作

--stop-position=18038 误操作前一条操作

我们查看表，数据已经恢复到误操作前。

 8.总结:

选中区间 只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作（见案例7.6偏差）

文章仅供参考，问题也比较多，还有几个情况没考虑。

比如 日志不单单是这个表的日志，还夹杂着其他表，日志内按照执行时间顺序记录，如果区间中包含其他表操作，那么其他表也会被重新执行，会造成脏数据。

有新的思路 或者 解决这个问题的方式 以及 产生的影响未考虑到的，欢迎评论区一起交流。

mysql通过binlog做数据恢复
weixin_49702090的博客
09-20 1388
mysql通过binlog做数据恢复
基于binlog恢复MySQL数据
m0_56188609的博客
01-16 1121
binlog日志，每当我们重启MySQL一次，会自动生成一个binlog文件，当然，我们也可以手动的来刷新binlog文件，通过 flush logs，同样会新创建一个binlog文件。实际上当服务器在重启时，也会调用flush logs操作。记录了数据库整个生命周期数据的整个操作。
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
本文主要通过实际操作说明对binlog日志恢复MySQL数据库删除数据的方法进行说明，内容主要包括MySQL数据库binlog日志查询、恢复等相关介绍，希望对大家在工作及生活中遇到的此类问题有所帮助，如需操作指导，可留言！
MYSQL之binlog恢复数据
yeYingXuan的博客
07-31 3354
可以看到有一条新增的sql和一条删除的sql，新增的时间点是 240720 8:06:13，结束的时间点是240720 8:06:13，只要我们恢复的时间段包含了这个时间段即可。对某一条执行了错误的更新/删除操作：这种恢复是最简单的，我们直接在binlog找到这条数据的操作记录，找到原数据的值，然后执行新增/更新操作即可。由上得知，我们的binlog文件名叫binlog.000012，位置在/var/lib/mysql目录下。既然他会记录sql，那我们是不是把需要恢复数据的sql拿出来就好了？
MySQL中根据binlog日志进行恢复
最新发布
小强不是坑
04-22 1149
MySQL中根据binlog日志进行恢复
Mysql 通过binlog日志恢复数据
StrawberryMuMu的博客
11-06 893
Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据 1，登录mysql查看binlog日志的状态，输入show variables like ‘%log_bin%’;查看binlog为off关闭状态 2，开启mysql binlog日志，进入mysql配置文件(vi /etc/my.cnf) 在mysqld区域内添加如下内容，①server-.
MySQL误删除 binlog 还原 恢复已删除数据 实战 超详细
id_is_null的博客
09-14 5041
MySQL 通过 binlog 恢复已删除数据 超详细
mysql.binlog数据恢复
12-14
这是一个eclipse安装插件，在oxygen下测试是可用的。有问题可以是下面反馈^_^ 操作：1、获取binlog文件 2、选中要处理的部分 3、选择菜单“jhh->binlog->”下对应的操作 4、会在相同目录下生成sql文件（文件名相同，加了sql后缀）
使用 MYSQLBINLOG 来恢复数据
weixin_33922672的博客
04-05 202
       BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储罢了。1、配置文件里要写的东西：[mysqld]log-bin=mysql-bin(名字可以改成自己的，如果不改名字的话，默认是以主机名字命名)重新启动MSYQL服务。二进制文件里面的东西显示的就是执行所有语句的详细记录，当然一些语句不被记录在内，要了解详细的，见手册页。2、查看自...
MySQL使用binlog日志恢复数据
Felix的博客
09-14 1万+
MySQL使用binlog日志恢复数据
MySQL——通过binlog恢复数据
热门推荐
醉酒的戈多的专栏
04-07 4万+
二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
MySQL使用binlog恢复数据
安静的技术宅
07-30 5047
MySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。复制: 从库通过拉取主库的binlog实现主从数据一致恢复: 通过重放binlog恢复数据丢失或误操作情况MySQL的Binlog是数据库的重要组成部分，它记录了数据库的更改操作，并为数据复制和数据恢复提供了有力支持。通过了解Binlog的原理和使用技巧，我们可以更好地利用这一特性，保障数据库的稳健运行，并在必要时实现高级的数据恢复。然而，进行数据恢复时，请务必谨慎操作，以免造成不可逆的损失。
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
5. **查看binlog事件并恢复数据**：通过`show binlog events in 'mysql-bin.000002'`查看binlog文件中的事件，然后使用`mysqlbinlog`工具指定开始和结束位置恢复特定数据，例如： ``` mysqlbinlog --start-...
MySQL—使用binlog日志恢复数据
dghfttgv的博客
02-27 6279
在 MySQL 中，使用二进制日志（binlog）恢复数据是一种常见的用于故障恢复或数据找回的方法。my.cnfmy.inilog-binValueONxxxxxx例如，可以解析这个日志文件。常用的选项包括和来指定时间范围，和来指定日志位置范围。例如，只恢复某个时间段内的操作，可以使用。5.：将解析后的 SQL 语句应用到目标数据库中，可以将解析结果通过管道直接输入到mysql客户端来执行。假设用户名是root，密码是123456，要恢复这个日志文件中的数据，可以执行。
MySQL通过binlog恢复数据
Lucius_的博客
01-24 2315
在MySQL中，如果不小心删除了数据，可以利用二进制日志（binlog）来恢复数据。
mysql误删后使用binlog恢复数据
m0_70208894的博客
05-22 2456
（这里我们是要测试binlog恢复数据的使用，所以就日志文件都放到一个全新binlog文件中方便查询使用，如果是实际恢复数据的话，就要一个一个的在这些binlog文件中找自己要的那部分文件了。数据恢复：通过恢复误删操作之后的操作记录，可以将误删的数据重新插入到数据库中，还原到误删之前的状态。这将重新执行误删操作之后的操作，从而还原到误删前的数据状态。假设我们的数据库是7天一备份，然后binlog的过期时间是大于7天的，那么通过备份的数据库+binlog文件就能够恢复数据库到达7天内的任意一个时间点的状态。
MySQL利用bin log恢复数据
CoderJia的学习之路
07-01 1738
本文尝试通过bin log恢复误删除的数据，删库不再跑了
navicat 1146 ‘mysql,user’ dont exist
03-09
<think>好的，我现在需要解决用户在使用Navicat连接MySQL时遇到的错误1146，具体是“Table 'mysql.user' doesn't exist”。首先，我得分析这个错误的原因。根据引用[1]中的信息，这个错误表明MySQL的系统表'mysql.user'不存在，这通常发生在数据库升级失败、意外删除系统表或安装不完整的情况下。 接下来，我需要考虑可能的解决方案。首先想到的是检查MySQL的系统表是否存在。用户可能需要通过命令行登录MySQL，然后执行`SHOW TABLES FROM mysql LIKE 'user';`来确认表是否存在。如果不存在，可能需要恢复或重建这些系统表。 然后，回忆到MySQL提供了mysql_upgrade工具，用于检查和修复系统表。在引用[2]中提到，这个工具可以处理类似的问题。用户可能需要运行`mysql_upgrade -u root -p --force`来强制重建缺失的表。需要注意的是，这需要管理员权限，并且可能需要停止MySQL服务后再操作。 另外，权限问题也可能导致这个错误。用户需要确保在恢复过程中使用正确的root账户和密码。如果用户忘记了密码，可能需要通过安全模式启动MySQL来重置密码，这涉及修改配置文件并重启服务。 还有，数据目录的权限不正确也可能引发问题。需要检查MySQL的数据目录（通常是/var/lib/mysql）的所有者和权限是否正确，确保MySQL进程有读写权限。 最后，如果以上方法都无法解决问题，可能需要考虑备份数据后重新安装MySQL，或者从备份中恢复系统数据库。但这是最后的手段，因为会导致数据丢失风险。 总结步骤应该是：检查系统表是否存在→尝试用mysql_upgrade修复→检查权限和数据目录→必要时重装或恢复备份。同时提醒用户操作前备份数据，避免进一步损失。</think>### 解决Navicat连接MySQL错误1146：'mysql.user'表不存在 #### 问题原因分析 错误`ERROR 1146 (42S02): Table 'mysql.user' doesn't exist`表明MySQL系统数据库（`mysql`库）中的核心表`user`丢失。常见原因包括： 1. **MySQL升级失败**：升级过程中意外中断导致系统表损坏或缺失[^1]； 2. **手动误删系统表**：如误执行`DROP TABLE mysql.user`； 3. **安装不完整**：MySQL初始化流程未正确完成。 --- #### 分步解决方案 ##### 1. **验证系统表状态** 通过命令行登录MySQL并检查`mysql.user`是否存在： ```bash mysql -u root -p ``` 执行查询： ```sql SHOW TABLES FROM mysql LIKE 'user'; ``` * **若返回空**：需重建系统表； * **若存在但报权限错误**：检查用户权限或文件权限。 --- ##### 2. **使用`mysql_upgrade`修复系统表 MySQL提供工具`mysql_upgrade`自动修复系统表： ```bash # 停止MySQL服务 systemctl stop mysql # 强制重建系统表（需root权限） mysql_upgrade -u root -p --force # 重启MySQL systemctl start mysql ``` * **关键参数**：`--force`强制覆盖修复[^2]； * **注意**：若提示`mysql_upgrade`不存在，可能需要指定完整路径（如`/usr/bin/mysql_upgrade`）。 --- ##### 3. **手动恢复系统表（备用方案）** 若`mysql_upgrade`无效，尝试从其他正常实例复制系统表： ```bash # 备份原数据 cp -r /var/lib/mysql/mysql /var/lib/mysql/mysql_bak # 从正常MySQL服务器复制mysql库文件 scp -r root@正常服务器IP:/var/lib/mysql/mysql /var/lib/mysql/ ``` * **风险提示**：需确保MySQL版本一致，否则可能引发兼容性问题。 --- ##### 4. **检查数据目录权限 确认MySQL数据目录权限正确（默认路径`/var/lib/mysql`）： ```bash chown -R mysql:mysql /var/lib/mysql chmod -R 755 /var/lib/mysql ``` --- ##### 5. **彻底重装MySQL（最后手段） 若系统表严重损坏： ```bash # 备份数据 mysqldump -u root -p --all-databases > all_dbs.sql # 卸载MySQL apt purge mysql-server-* # Debian/Ubuntu yum remove mysql-server # CentOS/RHEL # 重新安装并恢复数据 apt install mysql-server mysql -u root -p < all_dbs.sql ``` --- #### 引用说明 [^1]: 该错误直接关联MySQL系统表完整性，常见于升级异常或误操作。 : `mysql_upgrade`是官方推荐的系统表修复工具，支持版本兼容性检查。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值