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

目录
  • 前言
  • 方法如下
  • 总结

前言

MySQL的binlog日志是MySQL日志中非常重要的一种日志,记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。

定期备份固然可以在服务器发生宕机的时候快速的恢复数据,但传统的全量备份不可能做到实时,所以在发生宕机的时候,也会损伤一部分数据,如果这个时候开启了binlog日志,那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复

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-id = 1(单个节点id) ②log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql库文件所在位置一样) ③expire_logs_days = 10(表示此日志保存时间为10天),重启mysqld,再次查看binlog日志开启状态为ON

3,Binlog日志包括两类文件;第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件

4,查看所有binlog日志文件列表:show master logs;

5,查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:show master status;

6,Flush logs 刷新日志,此刻开始产生一个新编号的binlog文件,例如:

每当mysqld服务重启时,会自动执行刷新binlog日志命令,mysqldump备份数据时加-F选项也会刷新binlog日志

7,清空所有binlog日志命令:reset master;

8,查看binlog文件内容,使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的)

9,上面的方法读取内容较多不易观察,以下命令更为方便查看命令:show binlog events in ‘mysql-bin.000003’;

10,指定查询,从pos点406开始查询,如下:

11,指定查询,从pos点154开始查询,中间跳过2行,查询4条数据,如图:

12,利用binlog日志恢复mysql数据,例如,现有一张数据表如图:此表位于hello数据库

现在将此数据库备份到本地(模拟每周的备份情况),备份命令如下

可以在数据备份之前或者之后执行flush logs重新生成一个binlog日志用来记录备份之后的所有增删改操作(重新生成日志更好找pos点),由于业务需求,现在对表进行插入

数据,修改数据,如图新增了两条数据id 为5和6,修改后数据如图,

6的年龄修改成了30

13,由于操作失误,误删除了数据库,所有数据都不见了,此时可以通过binlog日志恢复数据,由于之前有做了数据库备份,所以可以先将备份的数据导入进去,剩下缺少的就是备份之后操作所产生的内容(备份之后执行了插入内容以及更改内容),先恢复备份的数据:创建库hello并选择库(use hello),通过命令source /root/hello.sql将数据库内容导入,然后执行

14,恢复的内容如下所示:

15,上面恢复的数据只是截止到备份时间的数据,剩下缺少的数据可以通过binlog日志来恢复,由于我们备份数据库之前重新创建了mysql-bin.000006日志,所以备份后的所有操作都保存在这个日志中,可以先备份下这个日志文件,cp mysql-bin.000006 /root/ 接着执行flush logs 刷新日志,重新创建了一个binlog日志7

16,重新创建一个日志7的目的:接下来所有操作的数据都会写入到日志7中,日志6中不会在写入任何数据(方便根据日志6的内容恢复数据,因为日志6的数据就是备份之后到删库之前的所有操作日志,重建日志7不会有过多的数据影响恢复)

17,登录mysql 查看日志6: show binlog events in ‘mysql-bin.000006’;结果如下:

查看日志发现,在备份数据后首先执行的是插入数据操作(在一个事务内),此时可以通过指定pos结束点恢复(部分恢复),如图,pos结束点位435(事务已提交表示结束),执行命令如下, /usr/bin/mysqlbinlog --stop-position=435 --database=hello /var/lib/mysql/mysql-bin.000006 | /usr/bin/mysql -uroot -p密码 -v hello (其中整个命令的含义是通过mysqlbinlog读取日志内容并通过管道传给mysql命令,-v表示执行此mysql命令)执行后查询表发现如下:

备份之后插入的数据都出现了,目前还差一次修改的数据没有找回来,接着看日志6如图,

发现执行更新操作的事务区间为573到718,所以可以执行以下命令来恢复这段数据,/usr/bin/mysqlbinlog --start-position=573 --stop-position=718 --database=hello /var/lib/mysql/mysql-bin.000006 | /usr/bin/mysql -uroot -p密码 -v hello注意其中的符号都是英文状态下(只恢复这段事务区间的数据也就是更新的数据),恢复结果如下:

更改的数据回来了,也可以最开始的时候直接通过最终的pos结束点718来恢复

18,还有一种通过时间来恢复,还是先备份当前数据库(备份之前先flush logs创建一个新的binlog日志文件9),然后修改数据插入数据后如图,

修改后将当前数据库删除,(删除库之后再执行一下flush logs创建一个新的日志文件,这样新的操作都写入到这个文件中,上个日志文件只用来恢复数据,不会有其余数据混入)现在我们通过时间点来恢复从备份数据后到删除数据库这期间所有操作的数据,首先通过mysqlbinlog来了查看日志文件9如图所示,

19,执行命令:/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:57:55" --stop-datetime="2018-04-27 20:58:18" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello 更改的数据得到了恢复

20,插入数据的日志时间如图:

执行命令/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:58:18" --stop-datetime="2018-04-27 20:58:35" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello 插入的数据得到了恢复,如图,

总结

到此这篇关于Mysql如何通过binlog日志恢复数据的文章就介绍到这了,更多相关Mysql binlog日志恢复数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql的Binlog数据恢复:不小心删除数据库详解

    Mysql的Bin log数据恢复:不小心删除数据库 前言:因为不小心删除了测试机器上Mysql的一整个数据库Schema,因为是测试机所以没有做备份,现在通过MySQL的Bin log方式恢复到删除以前的数据库. 当然做Bin log的数据恢复前提是已经打开Bin log的功能,如果又没做数据备份,又没打开Bin log日志,那你就可能需要考虑快照等其它方式从系统的角度去恢复. Bin log 常用于数据增量备份和恢复,以及数据库主从复制.如果没有开启,可以通过如下方式打开: 1.打开mysq

  • mysql如何利用binlog进行数据恢复详解

    前言 最近线上误操作了一个数据,由于是直接修改的数据库,所有唯一的恢复方式就在mysql的binlog.binlog使用的是ROW模式,即受影响的每条记录都会生成一个sql.同时利用了binlog2sql项目. MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与master同步. binlog基本配置和格式 binlog基本配置 binlog需要在mysql

  • MySQL中的binlog相关命令和恢复技巧

    操作命令: 复制代码 代码如下: show binlog events in 'mysql-bin.000016' limit 10; reset master 删除所有的二进制日志flush logs  产生一个新的binlog日志文件 show master logs; 或者 show binary logs; 查看二进制文件列表和文件大小 复制代码 代码如下: ./mysqlbinlog --start-datetime="2012-05-21 15:30:00" --stop-

  • MySQL使用binlog日志做数据恢复的实现

    MySQL的binlog日志是MySQL日志中非常重要的一种日志,记录了数据库所有的DML操作.通过binlog日志我们可以进行数据库的读写分离.数据增量备份以及服务器宕机时的数据恢复. 定期备份固然可以在服务器发生宕机的时候快速的恢复数据,但传统的全量备份不可能做到实时,所以在发生宕机的时候,也会损伤一部分数据,如果这个时候开启了binlog日志,那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复.如果了解Redis的朋友,可能想到了,Redis有两种持久化方式,分别是AOF和

  • 教你自动恢复MySQL数据库的日志文件(binlog)

    如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据."mysqlbinlog:用于处理二进制日志文件的实用工具". 要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名.一般可以从选项文件(即my.cnf or my.ini,取决于你的系统)中找到路径.如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出.启用二进制日志的选项为 --log-b

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

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

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

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

  • MySQL通配符与正则表达式搜过滤数据详解

    目录 通配符过滤 通配符 % 通配符 _ 技巧 正则表达式过滤 字符匹配 其他字符 对比 正则表达式测试 总结 通配符过滤 通配符:用来匹配值的一部分特殊字符. 通配符可以在搜索模式中任意位置使用,并且可以使用多个通配符. 搜索模式:由字面值.通配符或者两者组合构成的搜索条件. 语法(使用LIKE操作符): SELECT [column_name] FROM [table_name] WHERE [column_name] LIKE '[string]'; 通配符 % ‘%’ 表示任何字符出现任

  • MySQL单表ibd文件恢复方法详解

    前言: 随着innodb的普及,innobackup也成为了主流备份方式.物理备份对于新建slave,全库恢复的需求都能从容应对. 但当面临单表数据误删,或者单表误drop的情况,如果使用物理全备进行恢复呢? 下文将进行详细分析. 恢复过程中需要用到的工具,percona data recover tool : https://launchpad.net/percona-innodb-recovery-tool 情况一:误删部分数据,需要用最近一次备份覆盖 来自同一台机器的ibd恢复覆盖,且备份

  • MongoDB 导出导入备份恢复数据详解及实例

    数据库备份和数据恢复的重要性,我想大家都知道,这里就举例说明如何操作数据备份,数据恢复的实例: 创建测试数据 创建db:testdb,collection:user,插入10条记录 mongo MongoDB shell version: 3.0.2 connecting to: test > use testdb switched to db testdb > db.user.insert({id:1,name:"用户1"}); WriteResult({ "n

  • MySQL数据库使用mysqldump导出数据详解

    mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有

  • Mysql数据库之Binlog日志使用总结(必看篇)

    binlog二进制日志对于mysql数据库的重要性有多大,在此就不多说了.下面根据本人的日常操作经历,并结合网上参考资料,对binlog日志使用做一梳理: 一.binlog日志介绍 1)什么是binlog binlog日志用于记录所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句.语句以"事件"的形式保存,它描述数据更改. 2)binlog作用 因为有了数据更新的binlog,所以可以用于实时备份,与master/slave主从复制结合. 3)和b

  • mysql 正确清理binlog日志的两种方法

    mysq 正确清理binlog日志 前言: MySQL中的binlog日志记录了数据库中数据的变动,便于对数据的基于时间点和基于位置的恢复,但是binlog也会日渐增大,占用很大的磁盘空间,因此,要对binlog使用正确安全的方法清理掉一部分没用的日志. [方法一]手动清理binlog 清理前的准备: ① 查看主库和从库正在使用的binlog是哪个文件 show master status\G show slave status\G ② 在删除binlog日志之前,首先对binlog日志备份,以

  • MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析

    (一)概述 在日常MySQL数据库运维过程中,可能会遇到用户误删除数据,常见的误删除数据操作有: 用户执行delete,因为条件不对,删除了不应该删除的数据(DML操作): 用户执行update,因为条件不对,更新数据出错(DML操作): 用户误删除表drop table(DDL操作): 用户误清空表truncate(DDL操作): 用户删除数据库drop database,跑路(DDL操作) -等 这些情况虽然不会经常遇到,但是遇到了,我们需要有能力将其恢复,下面讲述如何恢复. (二)恢复原理

  • MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将

随机推荐