MYSQL Binlog恢复误删数据库详解
对表误删或执行缺少条件的修改 SQL 导致修改了表内其他数据时，我们需要想办法将数据恢复回来。
先创建两个测试表 table_1
CREATE TABLE `table_1` ( `id` int(0) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
首先要确保开启了BINLOG日志
-- 查看日志状态 show variables like '%log_bin%';
之前没有开启 Binlog 日志的话下面的方法就不能继续操作了，开启日志的方法网上有很多，修改一下配置文件并重启 MYSQL 服务。
模拟删除
正常情况下，我们需要从线上正在使用的日志中查找信息，要考虑的因素会很多。
为了方便测试，这里重启了一个新的日志，后面的所有操作都会记录到新的日志文件中。
flush logs
-- 插入table1一些数据
insert into table_1(name) values ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10')
-- 模拟删除表信息table1 delete from table_1 where id >5; --然后修改table1的内容 update table_1 set name= Concat(name,'1') ;
修复步骤
我们需要删除的信息，并保留删除之后修改过的信息
-- 查看日志内容 show master status; show binlog events in 'binlog.000023';
从上面的查询语句可以查询 binlog.000023 中记录的所有操作。
如图，我们在 993 的位置执行了删除表的操作，我们要把数据还原到这个位置之前
每一个事务从 Gtid 开始，Xid 结束。
有的时候，我们不确定这个位置在什么地方，但是我们有时间范围，可以根据时间范围缩小所有区间，这里需要用到 binlog 命令
以下是 Windows CMD 的命令实例：
mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000021 --start-datetime="2022-11-17 13:00:00" --stop-datetime="2022-11-17 14:00:00"
控制台打印出来的结果和SQL查询出来的结果大致上是一样的。
找到我们要还原的位置之后，执行还原命令。
以下是 Windows CMD 的命令实例：
mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000023 --stop-position=993 --database=dgcat | F:\Mysql\mysql-8.0.27-winx64\bin\mysql -uroot -p*****
执行后发现报错：
反馈 table_1表中已经有了主键 1 ，不能插入。是因为我们只指定了数据恢复的结束位置，而当前日志又包含了 table_1 的所有行插入记录，当恢复表时，他会重新插入这些行，导致和表内已存在的信息主键冲突。
因为我的 binlog 日志记录了 table_1 的所有操作记录。所以这里我选择将 table_1 清空，用日志恢复所有数据。
truncate table table_1
正常情况下，我们最好能确认下来数据恢复的开始位置。
再次执行恢复命令后，表内信息如下：
现在已经恢复了我们删除之前的状态，但是我们在删除之后还做了修改操作，我们还需要把修改操作也还原一下：
相当于跳过 delete 操作，1100 位置即是我们修改操作的开始位置，直至 1478 整个操作结束。
mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000023 --start-position=1100 --stop-position=1478 --database=dgcat | F:\Mysql\mysql-8.0.27-winx64\bin\mysql -uroot -pzdm3713261995!!!
执行结束后我们在查询表内信息 ，删除的数据恢复了，而且我们修改过的记录也在。
到此这篇关于MYSQL Binlog恢复误删数据库详解的文章就介绍到这了,更多相关MYSQL Binlog恢复数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
相关推荐
-
mysql数据库日志binlog保存时效问题(expire_logs_days)
目录 一.设置语法 1.方法一 2.方法二 二.过期删除策略 最后 一.设置语法 1.方法一 编辑/etc/my.cnf文件,在[mysqld]节点中增加如下两行 max_binlog_size = 500M expire_logs_days = 15 max_binlog_size:bin log日志每达到设定大小后,会使用新的bin log日志.如mysql-bin.000002达到500M后,创建并使用mysql-bin.000003文件作为日志记录. expire_logs_days:保
-
一步步教你如何使用mysql binlog恢复数据
如果想通过 mysql 的 binlog 恢复数据,首先要开启 binlog .这里搭建一个测试的环境,了解一下 mysql binlog 是如何恢复数据库的.原理比较简单,binlog 会存储mysql中变化的数据,比如你创建了一个数据库,写入了一些数据,这些都会存储在 mysql 的 binlog 中. 需要恢复的时候就找到,两个位置,一个起始位置,一个结束的位置.结束的位置,一半是数据被破坏或者删除前的位置.mysql 8 默认已经开启了 binlog mysql> show variab
-
mysql日志文件General_log和Binlog开启及详解
目录 背景: General_log 详解 1.介绍 2.开启数据库general_log步骤 Binlog 详解 1.介绍 2.开启binlog日志 3.常用binlog日志操作命令 4.mysqlbinlog命令使用 5.binlog的三种工作模式 总结 背景: 周末归纳下mysql的日志文件,其中general_log在mysql入侵中已经用到过,binlog即将会用到.注:mysql版本为5.7.20 General_log 详解 1.介绍 开启 general log 将所有到达MyS
-
MySQL如何查询Binlog 生成时间
目录 前言 脚本介绍 使用案例 1. 查询 binlog index 文件 2. 使用脚本查询时间 前言 本篇文章介绍如何查询 Binlog 的生成时间.云上 RDS 有日志管理,但是自建实例没有,该脚本可用于自建实例闪回定位 Binlog 文件. 脚本介绍 直接上代码吧- 通过读取 Binlog FORMAT_DESCRIPTION_EVENT header 时间戳来实现读取 Binlog 生产时间. # -*- coding: utf-8 -*- import os import sys i
-
如何利用MySQL的binlog恢复误删数据库详解
目录 1 查看当前数据库内容并备份数据库 2 开启bin_log功能 3 模拟误操作(插入3条数据,删除数据库) 4 数据恢复 5 总结 1 查看当前数据库内容并备份数据库 查看数据库信息: 备份数据库: [root@localhost ~]# mysqldump -u root -p t > /mnt/t.sql Enter password: [root@localhost ~]# ll /mnt/t.sql -rw-r--r-- 1 root root 1771 Aug 25 11:56
-
MySQL binlog 远程备份方法详解
以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将
-
mysql use命令选择数据库详解
连接到MySQL服务器后,则需要选择特定的数据库的来工作.这是因为可能有多个数据库可使用在MySQL服务器上. use命令格式: use <数据库名>; 如果我们想要切换到test数据库,那我们可以使用如下命令: mysql> USE test; Database changed 现在,我们已经选择 test 数据库,后续所有操作将在 test 数据库上执行. 注意: 所有的数据库名,表名,表中的字段名称是区分大小写的.所以,我们必须使用适当的名称,在给定任何SQL命令. 另外,use命
-
mysql数据库详解(基于ubuntu 14.0.4 LTS 64位)
1.mysql数据库的组成与相关概念 首先明白,mysql是关系型数据库,和非关系型数据库中最大的不同就是表的概念不一样. +整个mysql环境可以理解成一个最大的数据库:A +用mysql创建的数据库B是属于A的,是数据的仓库,相当于系统中的文件夹 +数据表C:是存放数据的具体场所,相当于系统中的文件,一个数据库B中包含若干个数据表C(注意此处的数据库B和A不一样) +记录D:数据表中的一行称为一个记录,因此,我们在创建数据表时,一定要创建一个id列,用于标识"这是第几条记录",id
-
MySQL使用Xtrabackup备份流程详解
01 背景 Xtrabackup是Percona公司开发的一款开源的MySQL热备份工具,之前的工作中也是经常使用,但是也仅仅是停留在使用的阶段,对于这个工具的细节,并没有做过多的研究,今天细细看了一下过程,还是有点收获的,写下来记录一下,有不对的地方,还请指正. Xtrabackup工具能够备份InnoDB,XtraDB和MyISAM的表,它支持Percona Server的所有版本,而且兼容MySQL,也兼容MariaDB,还支持一些高级的特性,例如流方式备份.压缩.加密.以及增量备份等等.
-
zabbix监控MySQL主从状态的方法详解
搭建MySQL主从后,很多时候不知道从的状态是否ok,有时候出现异常不能及时知道,这里通过shell脚本结合zabbix实现监控并告警 一般情况下,在MySQL的从上查看从的运行状态是通过Slave_IO_Running线程和Slave_SQL_Running线程是否ok,通过命令"show slave status\G;"即可查看.所以这里根据这两个值进行判断. agent端脚本编写及配置 说明:所有zabbix相关的脚本我都放在了/etc/zabbix/script/ 目录里面,下
-
mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解
第一步 安装 1.安装MySQL 2.安装Python3 [root@localhost /]#yum install python3 3.下载binlog2sql文件到本地(文件在百度云盘) [root@localhost /]#mkdir tools [root@localhost /]#cd tools [root@localhost tools]# ll total 317440 -rw-r--r--. 1 root root 317440 Sep 21 23:55 binlog2sql
-
MySQL 各个索引的使用详解
一. 慢查询日志 1.1 MySQL的日志类型 日志用于记录数据库的运行情况,以及用户对数据库执行的各类操作.当数据库发生故障时,可以根据日志分析和解决问题,从而对数据库进行恢复. 1.2 认识慢查询日志 慢查询日志用于记录MySQL数据库中响应时间超过指定阈值的语句.慢查询日志通常也被称之为慢日志,因为它不仅仅只针对SELECT语句,像INSERT.UPDATE.DELETE等语句,只要响应时间超过所设定阈值都会记录在慢查询日志中. 1.3 如何开启慢查询日志命令 慢查询日志可以通过命令临时设
-
MYSQL大量写入问题优化详解
摘要:大家提到Mysql的性能优化都是注重于优化sql以及索引来提升查询性能,大多数产品或者网站面临的更多的高并发数据读取问题.然而在大量写入数据场景该如何优化呢? 今天这里主要给大家介绍,在有大量写入的场景,进行优化的方案. 总的来说MYSQL数据库写入性能主要受限于数据库自身的配置,以及操作系统的性能,磁盘IO的性能.主要的优化手段包括以下几点: 1.调整数据库参数 (1) innodb_flush_log_at_trx_commit 默认为1,这是数据库的事务提交设置参数,可选值如下: 0
-
MySQL导入与导出备份详解
目录 一.MySQL备份类型详解 (一)按照备份对数据库的影响分类 (二)按照备份的文件进行分类 (三)按照备份的方式进行分类 二.MySQL数据库导出 (一)mysqldump导出数据 (二)mysqlhotcopy裸文件备份 (三)mysqldump与mysqlhotcopy比较 三.MySQL数据库导入 总结 一.MySQL备份类型详解 对于现代互联网公司而言,存储在服务器数据库中的数据,逐步成为企业和公司的命脉,对企业和公司的生存发展具有十分重大的影响.为了保证数据库数据存储安全,确保不