MySQL使用二进制日志恢复数据

最新推荐文章于 2021-02-23 20:46:46 发布
最新推荐文章于 2021-02-23 20:46:46 发布
阅读量129 收藏
点赞数
分类专栏： 服务器相关 文章标签： MySQL SQL Server thread F# SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gwikimo/article/details/83558038
版权

转载自：

http://blog.csdn.net/liangCK/archive/2009/04/27/4129920.aspx

http://blog.chinaunix.net/u1/54130/showart_1912663.html

不错的东西，学习了下，做个记录。

mysqlbinlog 工具的使用 , 大家可以看 MySQL 的帮助手册。里面有详细的用，在这个例子中，重点是 --start-position 参数和 --stop-position 参数的使用。

 

· --start-position=N

从二进制日志中第 1 个位置等于 N 参量时的事件开始读。

· --stop-position=N

从二进制日志中第 1 个位置等于和大于 N 参量时的事件起停止读。

 

OK ，现在开始，要启动二进制日志记录，要先在 my.cnf / my.ini 文件的 mysqld 里添加

log-bin= 日志名

在这里，偶是的设置是 log-bin=liangck

然后再启动 mysql 服务，因为偶是用 windows 系统，所以执行 net start mysql 命令即可。

 

然后在一测试数据库里，创建一个表，并添加记录。

mysql>  create  table  test( id int  auto_increment not  null  primary  key , val int , data varchar ( 20));

 

mysql>  insert  into  test( val, data)  values ( 10, 'liang' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  insert  into  test( val, data)  values ( 20, 'jia' );

Query OK,  1 row affected  ( 0.08 sec)

 

mysql>  insert  into  test( val, data)  values ( 30, 'hui' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  flush logs;    -- 产生第二个日志文件

Query OK,  0 rows  affected  ( 0.09 sec)

 

mysql>  insert  into  test( val, data)  values ( 40, 'aaa' );

Query OK,  1 row affected  ( 0.05 sec)

 

mysql>  insert  into  test( val, data)  values ( 50, 'bbb' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  insert  into  test( val, data)  values ( 60, 'ccc' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  delete  from  test where  id between  4 and  5;   -- 删除记录

Query OK,  2 rows  affected  ( 0.05 sec)

 

mysql>  insert  into  test( val, data)  values ( 70, 'ddd' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  flush logs;           -- 产生第三个文件文件

Query OK,  0 rows  affected  ( 0.11 sec)

 

mysql>  insert  into  test( val, data)  values ( 80, 'dddd' );

Query OK,  1 row affected  ( 0.05 sec)

 

mysql>  insert  into  test( val, data)  values ( 90, 'eeee' );

Query OK,  1 row affected  ( 0.03 sec)

 

mysql>  drop  table  test;        -- 删除表

Query OK,  0 row affected  ( 0.05 sec)

 

――――――――――――――――――――――――――――――――――

OK ，现在测试数据已经建好了，要求是什么呢？

就是将 test 表的数据全部恢复出来。

 

先用 mysqlbinlog 工具将日志文件生成 txt 文件出来分析。

 

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000001 > G:\001.txt

 

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000002 > G:\002.txt

 

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000003 > G:\003.txt

 

通过这三个命令，可以在 G 盘下生成 3 个文件，里面分别记录了日志文件的内容，也就是用户操作的步骤。

因为我们需要重做第一个日志文件的所有操作，所以这里只需要将第一个日志文件全恢复就行了。

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000001 | mysql -uroot –p

 

Ok, 接着，我们需要分析的是第二个日志文件。为什么要分析它呢，因为它中途执行了一个操作是 DELETE ，因为我们要做的是恢复全部数据，也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。

我们先打开 002.txt 文件来分析一下。

 

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#090427 15:27:56 server id 1   end_log_pos 106 Start: binlog v 4, server v 5.1.32-community-log created 090427 15:27:56

BINLOG '

fF71SQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMyLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

'/*!*/;

# at 106

#090427 15:28:37 server id 1   end_log_pos 176 Query   thread_id=1    exec_time=0    error_code=0

use mytest/*!*/;

SET TIMESTAMP=1240817317/*!*/;

SET @@session.pseudo_thread_id=1/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@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=28/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 176

#090427 15:28:37 server id 1   end_log_pos 204 Intvar

SET INSERT_ID=4/*!*/;

# at 204

#090427 15:28:37 server id 1   end_log_pos 312 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817317/*!*/;

insert into test(val,data) values(40,'aaa')

/*!*/;

# at 312

#090427 15:28:37 server id 1   end_log_pos 339 Xid = 12

COMMIT/*!*/;

# at 339

#090427 15:28:46 server id 1   end_log_pos 409 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817326/*!*/;

BEGIN

/*!*/;

# at 409

#090427 15:28:46 server id 1   end_log_pos 437 Intvar

SET INSERT_ID=5/*!*/;

# at 437

#090427 15:28:46 server id 1   end_log_pos 545 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817326/*!*/;

insert into test(val,data) values(50,'bbb')

/*!*/;

# at 545

#090427 15:28:46 server id 1   end_log_pos 572 Xid = 13

COMMIT/*!*/;

# at 572

#090427 15:29:35 server id 1   end_log_pos 642 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817375/*!*/;

BEGIN

/*!*/;

# at 642

#090427 15:29:35 server id 1   end_log_pos 670 Intvar

SET INSERT_ID=6/*!*/;

# at 670

#090427 15:29:35 server id 1   end_log_pos 778 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817375/*!*/;

insert into test(val,data) values(60,'ccc')

/*!*/;

# at 778

#090427 15:29:35 server id 1   end_log_pos 805 Xid = 14

COMMIT/*!*/;

# at 805

#090427 15:30:21 server id 1   end_log_pos 875 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817421/*!*/;

BEGIN

/*!*/;

# at 875

#090427 15:30:21 server id 1   end_log_pos 981 Query   thread_id=1    exec_time=0    error_code=0

SET TIMESTAMP=1240817421/*!*/;

delete from test where id between 4 and 5

/*!*/;

# at 981

#090427 15:30:21 server id 1   end_log_pos 1008     Xid = 15

COMMIT/*!*/;

# at 1008

#090427 15:30:34 server id 1   end_log_pos 1078     Query   thread_id=1    exec_time=0     error_code=0

SET TIMESTAMP=1240817434/*!*/;

BEGIN

/*!*/;

# at 1078

#090427 15:30:34 server id 1   end_log_pos 1106     Intvar

SET INSERT_ID=7/*!*/;

# at 1106

#090427 15:30:34 server id 1   end_log_pos 1214     Query   thread_id=1    exec_time=0     error_code=0

SET TIMESTAMP=1240817434/*!*/;

insert into test(val,data) values(70,'ddd')

/*!*/;

# at 1214

#090427 15:30:34 server id 1   end_log_pos 1241     Xid = 16

COMMIT/*!*/;

# at 1241

#090427 15:30:41 server id 1   end_log_pos 1282     Rotate to liangck.000003   pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

―――――――――――――――――――――――――――――――――――――

 

在这个文件中，我们可以看到 DELETE 的操作的起始位置是 875 ，终止位置是 1008. 那么我们只要重做第二个日志文件的开头到 875 的操作，然后再从 1008 到末尾的操作，我们就可以把数据给恢复回来，而不会 DELETE 数据。所以执行两个命令：

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000002 --stop-pos=875 | mysql -uroot -p

 

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000002 --start-pos=1008 | mysql -uroot -p mytest

 

OK, 现在第二个日志文件的数据了。

第三个日志文件也是同理，只要找到 DROP TABLE 的位置，就可以了。

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000003 --stop-pos=574 | mysql -uroot –p

 

现在我们再查一下数据看看 :

mysql> select * from test;

+----+------+-------+

| id | val   | data   |

+----+------+-------+

|   1 |    10 | liang |

|   2 |    20 | jia    |

|   3 |    30 | hui    |

|   4 |    40 | aaa    |

|   5 |    50 | bbb    |

|   6 |    60 | ccc    |

|   7 |    70 | ddd    |

|   8 |    80 | dddd   |

|   9 |    90 | eeee   |

+----+------+-------+

9 rows in set (0.00 sec)

 

可以看到，全部数据都回来了。

 

gwikimo
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL使用二进制(binlog)文件恢复数据
@日月空@的博客
10-31 839
概述 项目运行过程中总会遇到一些意外，比如误操作把数据删了、表删了，甚至是把数据库删了的情况。这时为了保障用户不受影响，应该快速的恢复数据；如果没有备份数据库怎么办，别慌，我们还有二进制(binlog)日志文件。 二进制(binlog)日志也可叫作变更日志，是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，但是不包含数据记录查询操作。语句以事件保存，它描述了数据的更改过程。 环境准备 创建一个用于测试的库 create database myte
MySQL二进制日志管理和数据恢复
夏至ゞ的博客
12-12 499
MySQL二进制日志管理，以及使用二进制日志恢复数据。
mysqlbinlog工具基于日志恢复详细解释
csdn426523的博客
05-17 223
如果每天都会生成大量的二进制日志，这些日志长时间不清理的话，将会对磁盘空间带来很大的浪费，所以定期清理日志是DBA维护mysql的一个重要工作 1)RESET MASTER 在上面查看日志存放的文件夹中，二进制日志...
mysql mysqlbinlog: unknown variable 'default-character-set=gbk'解决方法
Mr_Melody的专栏
05-23 4702
mysql   [root@localhost data]# /Data/apps/mysql/bin/mysqlbinlog mysql-bin.000004 > yhs_binlog.log /Data/apps/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=gbk'
mysql使用binlog恢复使用delete误删的数据
u013803262的专栏
06-11 2万+
在我们平时使用MySQL数据库的时候,可能会出现delete from table_name 忘记写条件语句而导致删除了整个表的数据。 这个时候就很棘手了。如果公司的数据库都有开启数据备份那自然是没有问题。source   一下备份下来的sql文件就好。 但是如果没有备份数据呢? 办法还是有的。 答案就是bin log。 MySQL带了一个 bin log 的功能， 但是默认是
Mysql binlog数据恢复(使用mysqlbinlog_flashback逆向生成SQL语句)
NoBug Blog
02-23 2081
这里有个建议，由于mysql默认是不开启binlog的，如果是线上数据库，或者比较重要的数据库，建议还是将binlog开启，而且格式设为ROW类型的。 防止出现问题时，也有恢复的退路，养成定期备份数据库的习惯。 通过命令，查看是否开启了binlog。 show variables like 'log_%'; 数据恢复方法： 1.用最近的全量备份，然后在加上全量备份时间点后的binlog日志，然后先还原全量备份，在通过命令把binlog执行掉，这样数据就能还原了，这种方法，不限binlog的日志格式，RO
mysql 利用binlog增量备份，还原实例（日志备份数据库）
keke_Xin的专栏
11-10 636
一，什么是增量备份 增量备份，就是将新增加的数据进行备份。假如你一个数据库，有10G的数据，每天会增加10M的数据，数据库每天都要备份一次，这么多数据是不是都要备份呢？还是只要备份增加的数据呢，很显然，我只要备份增加的数据。这样减少服务器的负担。 二，启用binlog vi my.cnf log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-b...
详解如何通过Mysql的二进制日志恢复数据库数据
09-09
MySQL的二进制日志（Binary Log）是数据库系统中用于记录所有更改数据的SQL语句的一种机制，它对于...了解如何使用二进制日志恢复数据是每个DBA和系统管理员必备的技能，但更重要的是采取预防措施，确保数据安全无虞。
mysql二进制日志文件恢复数据库
12-15
 mysql二进制日志文件用来记录所有用户对数据库操作，即记录用户对数据库操作的sql语句。如果有此文件，当数据库发生意外时，可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作，再和数据库备份配合...
mysql-binlog二进制日志恢复
04-18
MySQL 二进制日志恢复 MySQL 的二进制日志（binlog）是 MySQL server 的一个重要组件，记录...MySQL 二进制日志恢复是指从二进制日志中恢复数据的过程。使用 mysqlbinlog 工具可以轻松地从二进制日志文件中恢复数据。
MySQL二进制日志：数据恢复的瑞士军刀
07-17
- **可靠性**：MySQL提供了多种机制来确保数据的完整性和可靠性，包括事务支持、备份和恢复功能。 - **易于使用**：MySQL提供了简单直观的界面和丰富的文档，便于用户学习和使用。 - **可扩展性**：MySQL支持从小型...
mysqlbinlog——日志恢复数据库文件
热门推荐
martingpf的博客
09-17 2万+
系统环境：Centos7.2 架构：Mysql主从 数据库：Mysql5.7.9 说明：公司正式环境，不小心drop一张表，由于开始了binlog日志，所以我们将采用最近一次全备 + mysql-binlog日志的方式进行恢复，我的数据备份机制为“8小时/天”，每天3次，下面来说说具体操作步骤。   一、思路 1.1 记下出问题的时间点，我是2018.09.11 12:59:00左右...
mysqlbinlog 恢复数据的四种方法
weixin_33937499的博客
09-30 405
mysqlbinlog 命令作用：mysqlbinlog 是用来解析mysql的binlog日志的。[root@MySQL data]# file mysql-bin.000001mysql-bin.000001: MySQL replication log[root@MySQL data]#mysqlbinlog日志无法直接用cat 查看，需要用mysqlbinlog命令...
mysql通过binlog恢复数据的几种情况
飞龙在天的博客
07-13 6775
binlog恢复数据的几种情况 1.数据库丢失或者drop 1）恢复系统自动备份 2）用binlog恢复系统备份时间到此刻的的数据（drop操作需要binlog删除drop记录） 2.误操作update，delete 1）恢复系统自动备份 2）在binlog日志中删除误操作的操作语句，执行binlog恢复其他的数据 或者 在binlog中找到误操作的记录，用脚本反生成sql，进行恢
一个简单的binlog恢复测试
weixin_30596023的博客
03-29 288
日常的数据备份及恢复测试，是DBA工作重中之重的事情，所以要做好备份及测试，日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份，无论那一种，几乎都少不了对binlog的备份，说明了binlog在数据恢复中的重要性，下面做个小测试，是工作中不少运维或者新人DBA容易犯的错。 创建一个测试表tb1： <test>(root@loc...
使用 MYSQLBINLOG 来恢复数据
twosecond
08-04 484
今天在家里做了一下试验，终于搞明白了以前做复制的时候没有搞明白的问题。原来BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储罢了。1、配置文件里要写的东西：[mysqld]log-bin=yueliangdao_binglog(名字可以改成自己的，如果不改名字的话，默认是以主机名字命名)重新启动MSYQL服务。二进制文件里面的东西显示的就是执行
2024年欧洲铝桁架市场主要企业市场占有率及排名.docx
07-17
2024年欧洲铝桁架市场主要企业市场占有率及排名.docx
torchaudio-0.13.1+cpu-cp39-cp39-win_amd64.whl
最新发布
07-17
torchaudio软件包，直接下载下来，通过命令窗口输入：pip install torchaudio-xxx.whl安装就行，再也不怕pip安装timeout了
出入库存盘点表-图表分析-分类查询-Excel模板
07-17
【作品名称】：出入库存盘点表-图表分析-分类查询-Excel模板 【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
mysql二进制文件恢复数据
08-24
5. 将需要恢复的二进制日志文件复制到新的数据目录中： ``` sudo cp /path/to/binlog.00000* /var/lib/mysql_new/ ``` 6. 修改新数据目录的权限，确保MySQL用户可以访问： ``` sudo chown -R mysql:mysql /var/lib/...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值