MySql恢复数据方法梳理讲解

目录
  • 一、前言
  • 二、步骤
  • 三、其它笔记

一、前言

mysql具有binlog功能，可以记录对表进行过的操作、执行过的sql语句；

以下主要是总结下binlog的开启方法、使用方法等；

如果误删数据库表中的某些数据、或者误删整个表、误修改表结构等，就可以使用这个方法进行恢复。

二、步骤

1.如果想使用MySql数据恢复功能，首先需要修改Mysql配置文件，开启binlog功能。（有些版本binlog默认是不开启的，因此需要手动修改配置文件开启）

(1)windows环境

配置文件名称为my.ini,可以安装软件everything搜索这个文件。路径样例如下：

C:ProgramDataMySQLMySQL Server 5.7my.ini

(2)linux环境

配置文件名称为my.cnf,可以使用find命令搜索文件所在位置：

sudo find / -name my.cnf

位置样例如下：

/etc/my.cnf

2.找到配置文件后(my.ini/my.cnf)，新增以下配置即可(当然先看看配置文件是否已配置了这行，不要重复了)：

log-bin=mysqlbinlog
binlog-format=ROW

或者也可以指定binlog的位置（下方是linux下的路径）：

log-bin=/data/mysql/log/mysqlbinlog
binlog-format=ROW

注意，指定binlog在某个文件夹下时，必须自己创建文件夹，否则重启mysql会报错。

3.配置完成后，重启mysql。

4.现在，对mysql的数据库、表、数据等操作、执行增删改sql，都会记录到binlog中。（搜索mysqlbinlog就可以找到这个日志文件）

5.进行测试，新建一个表binlog_test，列随意，然后写几行数据，然后清空表数据。（准备使用binlog还原数据）

6.查看binlog的命令如下：

mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysqlbinlog.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status； #查看当前正在写入的binlog文件

7.首先看当前使用的是哪个binlog:

show master status;

本人执行后，发现当前使用的binlog是：mysqlbinlog.000004

8.然后查看这个binlog的内容：

show binlog events in 'mysqlbinlog.000004';

执行后，可以从查询结果的Info列中，找到刚才创建binlog_test表的sql（对应的Event_type是Query）;

可以看到刚才清空表数据的记录行，Event_type是Delete_rows；

在上一些，可以看到创建表后、新增数据的Event_type是Write_rows；

9.分析这个binlog，我们的目标是还原刚才被删除的数据，因此找到create table语句后的第一个Event_type是Query的行，它的Pos为460；

再找到Event_type是Delete_rows上面最接近的、Info中是COMMIT的行，它的Event_type是Xid，End_log_pos是1199。

（每一行的End_log_pos对应下一行的Pos）

10.现在找到了一个范围，从460到1199之间的执行日志，就是给表binlog_test增加数据的日志，因此只要重复执行这之间的操作，就可以还原数据了。

11-1.windows下，找到mysqlbinlog.exe的位置；

linux下，找到mysqlbinlog的位置（这个是mysql的bin目录下的一个可执行文件）；

然后执行以下命令，即可还原数据：

mysqlbinlog --no-defaults --start-position=460 --stop-position=1199 "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test | mysql -uroot -proot test

其中，指定了开始位置为460，结束位置为1199，命令执行后就会读取日志中指定范围内的命令并重新执行。

其它命令的含义为：

"-d test"的意思是，指定数据库为test（binlog_test表在test数据库中）
"|"是管道符
"mysql -uroot -proot test"是mysql的账号密码、登陆目标数据库
"-uroot"：账号是root
"-proot"：密码是root
"test"：数据库是test

11-2.也可以把binlog日志中，指定范围内的日志导出，然后自己执行source命令还原数据，如下：

mysqlbinlog "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test  --skip-gtids  --start-position=460 --stop-position=1199 > test.sql

执行这个命令后，指定范围内的日志被导出到test.sql中；

然后登陆mysql，执行命令：

mysql> use test;
mysql> source test.sql;

这样就可以还原数据了。

三、其它笔记

1.如果使用truncate删除表的数据，binlog中也会新增几行记录，记录truncate操作，样例如下：

mysqlbinlog.000004 | 3122 | Query | 1 | 3211 | use 'test'; TRUNCATE 'binlog_test'

如果想恢复数据，就需要找到最开始插入数据的Pos、到truncate之前的End_log_pos，然后重新执行这期间的命令。

2.增删改操作会记录到binlog中，慢查询sql也可以记录到慢查询log中，不过也需要自己修改配置文件开启慢查询日志。

到此这篇关于MySql恢复数据方法梳理讲解的文章就介绍到这了,更多相关MySql恢复数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL 数据恢复的多种方法汇总

    目录 1.前言 2.直接恢复 2.1 mysqldump 备份全量恢复 2.2 xtrabackup 备份全量恢复 2.3 基于时间点恢复 3.恢复一个表 3.1 从 mysqldump 备份恢复一个表 3.2 从 xtrabackup 备份恢复一个表 4.跳过误操作SQL 4.1 使用备份文件恢复跳过 4.2 使用延迟库跳过 5. 闪回. 5.1 binlog2sql 5.2 MyFlash 1.前言 数据恢复的前提的做好备份,且开启 binlog,格式为 row.如果没有备份文件,那么删掉库

  • 教你如何通过日志文件恢复MySQL数据

    目录 前言 1.找到最新的 binlog 文件 2.找到我们想恢复数据在日志文件里的开始结束位置 2.1.使用时间范围 2.2.使用 position 范围 3.恢复 3.1 通过时间恢复 3.2 通过 position 恢复 总结 前言 本文将讲述怎么通过 MySql 的日志 binlog 文件进行数据恢复. 通过已备份数据 加上 binlog 文件恢复上次备份到删除之间的数据 1.找到最新的 binlog 文件 进入 mysql 命令行执行如下命令 mysql> show master st

  • Mysql如何通过binlog日志恢复数据详解

    Mysql如何通过binlog日志恢复数据详解

    目录 前言 方法如下 总结 前言 MySQL的binlog日志是MySQL日志中非常重要的一种日志,记录了数据库所有的DML操作.通过binlog日志我们可以进行数据库的读写分离.数据增量备份以及服务器宕机时的数据恢复. 定期备份固然可以在服务器发生宕机的时候快速的恢复数据,但传统的全量备份不可能做到实时,所以在发生宕机的时候,也会损伤一部分数据,如果这个时候开启了binlog日志,那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复 Binlog日志,即binary log,是二

  • 一文学会Mysql数据库备份与恢复

    一文学会Mysql数据库备份与恢复

    目录 数据库备份的分类 从数据库的备份策略角度,备份可分为 常见的备份方法 Mysql完全备份 完全备份分类 冷备份方法实现恢复数据库 mysqldump备份与恢复 Mysql完全恢复 Mysql 日志管理 Mysql增量备份 Mysql增量恢复 断点恢复 基于位置点恢复 基于时间点恢复 总结 数据库备份的分类 物理备份:数据库操作系统的物理文件(如数据文件,日志文件等)的备份 物理备份的方法: 冷备份(脱机备份):是在关闭数据库时候进行的 热备份(联机备份):数据库处于运行状态,依赖于数据库的

  • Mysql数据库按时间点恢复实战记录

    Mysql数据库按时间点恢复实战记录

    简介:Mysql数据库按时间点恢复实战 对于任何一家企业来讲,数据都是最宝贵的财富. 如何保护数据完整性,数据不受损坏,在发生故障时,如何保住数据,在发生误操作,黑客入侵,数据篡改等场景时,如何基于我们的备份来进行数据恢复,是每个技术人员需要关注的关键点. 阿里云致力于服务客户,为客户数据库提供连续数据保护.低成本的备份服务.它可以为多种环境的数据提供强有力的保护,以及强力恢复.在发生数据丢失.数据损坏的极端情况下,RDS管控平台具有一键还原的功能,基于客户设置的需要恢复的时间点,进行数据全方位

  • 如何利用MySQL的binlog恢复误删数据库详解

    如何利用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通过ibd文件恢复数据的详细步骤

    恢复步骤 1.创建数据库(随意创建) 2.创建数据表(备注:表结构要和要恢复的表结构一致,row_format要和ibd文件的row_format一致,否则,会提示两者不一致. 当前row_format=dynamic) 3.表的属性查看 我们使用:show table status like ‘matlab’\G,查看表的属性 备注:创建表时候的row_format和表属性的不一致,基于innodb是,要把row_format设置成dynamic时,需要修改mysql的全局配置,直接在myql

  • MySQL数据库通过Binlog恢复数据的详细步骤

    MySQL数据库通过Binlog恢复数据的详细步骤

    目录 Mysql Binlog 简介 开启Binlog 使用Binlog恢复数据 附录 总结 Mysql Binlog 简介 Mysql Binlog是二进制格式的日志文件Binlog是用来记录Mysql内部对数据库的改动(只记录对数据的修改操作),主要用于数据库的主从复制以及增量恢复 开启Binlog 查看是否打开了Binlog [ON表示已经打开 OFF表示关闭 默认关闭状态] show variables like ‘%log_bin%’; 开启Binlog [修改完以后重启服务] 方法1

  • MySQL通过binlog恢复数据

    mysql 日志文件 任何成熟软件都会有一套成熟的日志系统,当软件出现问题时,这些日志就是查询问题来源的宝库.同样,mysql也不例外,也会有一系列日志记录mysql的运行状态. mysql主要有以下几种日志: 错误日志:记录mysql运行过程中的错误信息 一般查询日志:记录mysql正在运行的语句,包括查询.修改.更新等的每条sql 慢查询日志:记录查询比较耗时的SQL语句 binlog日志:记录数据修改记录,包括创建表.数据更新等 这些日志均需要在my.cnf文件进行配置,如果不知道mysq

  • MySql恢复数据方法梳理讲解

    目录 一.前言 二.步骤 三.其它笔记 一.前言 mysql具有binlog功能,可以记录对表进行过的操作.执行过的sql语句: 以下主要是总结下binlog的开启方法.使用方法等: 如果误删数据库表中的某些数据.或者误删整个表.误修改表结构等,就可以使用这个方法进行恢复. 二.步骤 1.如果想使用MySql数据恢复功能,首先需要修改Mysql配置文件,开启binlog功能.(有些版本binlog默认是不开启的,因此需要手动修改配置文件开启) (1)windows环境 配置文件名称为my.ini

  • MySQL检索数据操作方法梳理

    MySQL检索数据操作方法梳理

    目录 前言: 1. select语句-用于检索所有列 2. 注释的使用 前言: 本实验中所用数据库创建SQL代码以及插入数据SQL代码链接: 链接: https://pan.baidu.com/s/17dIyjZ-pCmJlcvLm5UYbmA?pwd=x5bb 提取码: x5bb 本人用来编写运行 SQL 代码的工具是 Navicat ,将 Navicat 转换为命令行模式的方法如下:鼠标右键自己新建的数据库 ,选择命令行界面 1. select语句-用于检索所有列 (1)检索单列 从 Pro

  • MySQL过滤数据操作方法梳理

    目录 前言 1. where 字句的使用 2. where 字句操作符 2.1 检查单个值 2.2 不匹配检查 2.3 范围值检查 2.4 空值检查 3. 扩展 3.1 SQL过滤与应用过滤 3.2 引号的使用 3.3 NULL的特殊性 前言 本实验中所用数据库创建SQL语句以及插入数据到数据库中的SQL语句链接: 链接: https://pan.baidu.com/s/1BnFJrLH41iTXlYKhqPninQ?pwd=u3vs 提取码: u3vs 书接上回说到,排序检索数据 1. whe

  • ktl工具实现mysql向mysql同步数据方法

    使用ktl工具实现mysql向mysql同步数据 1.新建作业步骤 2.完善作业步骤,主要是完成作业中的转换工作 3.首先要确定数据来源库,也就是数据输出库,这里是mysql 4.其次要确定数据接收库,这里也是mysql数据库 5.在作业中点击执行进行数据同步,根据数据量的不同同步时间也不一样. 6.数据同步完成后查看两个mysql数据库中同步的表数据,数据一致同步完成.

  • MySQL 两种恢复数据的方法

    一 前言 前一段时间接二连三的出现开发人员在测试环境和生产误操作导致数据库误删除/更新,对DBA而言,回滚数据着实是一件头疼的事情,凡涉及到恢复线上数据必然对应用带来一定的影响.大多数情况是开发误操作delete数据,update多数行,根据之前的操作经验,本文介绍常用的恢复方法. 二 常用的恢复方式 2.1 利用备份恢复 使用这种方式的前提必须有最近的备份集或者知道出现误操作起始的binlog 位点或者GTID,利用备份集恢复到中间的机器上,然后利用MySQL的slave 特性 START S

  • 解说mysql之binlog日志以及利用binlog日志恢复数据的方法

    众所周知,binlog日志对于mysql数据库来说是十分重要的.在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷! 废话不多说,下面是梳理的binlog日志操作解说: 一.初步了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的. ---

  • MYSQL使用.frm恢复数据表结构的实现方法

    我们都知道当我们建立数据表(innodb或myisam)时,会生成相应的文件(如:MYD,MYI,frm) 在这里,我们探讨下使用frm文件恢复 innodb和myisam类型表的结构,不过由于他们存储引擎的特性,所以恢复的方法也不一样,以下是详细的恢复过程. myisamchk "xxx.frm" myisamchk 可以试出来,库是不是 myisam 类型 1:恢复innodb类型数据表结构 我们先从test数据目录 copy一个innodb.frm文件到另外一个库(innodb)

  • mysql 找回误删表的数据方法(必看)

    有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可. 要是没有备份 可能就会比较麻烦,找回数据的成本也是非常之高的. 下面介绍下 mysqlbinlog找回备份时间点之后的数据的办法: 做个简单的实验,将mysql的表数据删除之后,然后用mysqlbinlog 找回刚才删除的表的数据. app表的创建时间和数据的插入: 2013-02-04 10:00:00 原理: mysqlbinlog 前提: mysql开启了bin log

  • Python向MySQL批量插数据的实例讲解

    背景:最近测试web项目需要多条测试数据,sql中嫌要写多条,就看了看python如何向MySQL批量插数据(pymysql库) 1.向MySQL批量插数据 import pymysql #import datetime #day = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')#参数值插入时间 db = pymysql.connect(host='服务器IP', user='账号', passwd='密码', port=端口号) c

随机推荐