通过binlog恢复mysql数据

最新推荐文章于 2023-07-30 18:18:57 发布
最新推荐文章于 2023-07-30 18:18:57 发布
阅读量2.7k 收藏 10
点赞数 2
分类专栏： # 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47055136/article/details/131283567
版权

通过binlog恢复mysql数据

基本流程

enter image description here

一、确定要恢复的时间点

确定要恢复到的时间点，可以使用以下命令查看binlog相关信息；或者根据误操作的时间来决定要恢复的时间点。

# 查询 BINLOG 格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查看 master 正在写入的 BINLOG 信息
show master status\G;

# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql-bin.000034' limit 9000,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql-bin.000034' from 1742635 limit 10;

标准操作为：根据时间确定位置信息
例：

 mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2023-04-03 14:00:00" \
 --database beifen  mysql-bin.000002 | less

逐一分析找到对应的start-position和stop-position

二、临时库准备

主库执行flush logs，然后将相关binlog和最近一份全量备份文件拷贝至临时库,并在临时库导入最近一次全量备份文件。

在执行数据恢复前，如果操作的是生产环境，会有如下的建议：
使用 flush logs 命令，替换当前主库中正在使用的 binlog 文件，好处如下：

  1. 可将误删操作，定位在一个 BINLOG 文件中，便于之后的数据分析和恢复。
  2. 避免操作正在被使用的 BINLOG 文件，防止发生意外情况。

数据的恢复不要在生产库中执行，先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。

三、提取sql

根据步骤一确定的位置导出 SQL 文件，相关命令：

  1. 根据position
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "xxxxx" --stop-position "xxxxx" \
 --database=beifen  binlog_file \
 > /HLW/JSZX/HLWdata/xxxx.sql

其中， start_position 和 stop_position 是在步骤1中记录的binlog位置，binlog_file 是在步骤1中记录的binlog文件名，/HLW/JSZX/HLWdata/xxxx.sql是保存SQL语句的文件路径。

  1. 根据时间
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime="2023-04-03 17:00:00" --stop-datetime="2023-04-03 19:00:00" \
 --database=beifen  mysql-bin.000002 \
 > /HLW/JSZX/HLWdata/xxxx.sql
  1. 其它查看方式
  • less & more & grep
# less
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2023-04-03 14:00:00" \
 --database sync_test  mysql-bin.000034 | less
# more grep
mysqlbinlog --no-defaults --database=jiangbei_construction \
--start-datetime="2023-04-03 14:00:00" \
/var/lib/mysql/mysql-bin.000044 |grep jb_purchase_enroll |more
  • mysqlbinlog 常用参数说明：
    • –database 仅仅列出配置的数据库信息
    • –no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-- character-set=utf8 指令
    • –offset 跳过 log 中 N 个条目
    • –verbose 将日志信息重建为原始的 SQL 陈述。
      • -v 仅仅解释行信息
      • -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来
    • –start-datetime 显示从指定的时间或之后的时间的事件。
      • 接收 DATETIME 或者 TIMESTRAMP 格式。
    • –base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。
      • AUTO 默认参数，自动显示 BINLOG 中的必要的语句
      • NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。
      • DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。

四、执行SQL语句

将步骤3中保存的SQL语句文件复制到恢复后的MariaDB服务器上，并使用以下命令将SQL语句导入到数据库中：

mysql -u root -p < /path/to/output/file.sql

五、实际演练

演练环境
  • IP：10.3.1.14
  • 数据库版本：mysql 5.7.37
  • 数据库名：beifen
  • 表名：test
  • 初始化数据：
mysql> select * from test;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  2 | 李四   |   18 |   1.00 |
|  3 | 王五   |   18 |   1.00 |
+----+--------+------+--------+
2 rows in set (0.02 sec)
  • 上一次备份
[root@rccccc JSZX]# /HLW/JSZX/mysql/bin/mysqldump  -uroot -p -S /HLW/JSZX/mysql/mysql.sock  beifen test > t_test.sql
[root@rccccc JSZX]# ls -l |grep t_test.sql
-rw-r--r-- 1 root  root  2009 Apr  3 14:38 t_test.sql
模拟数据变动
写入两条数据（时间点一）
mysql> insert into test(id,name,age,salary) values(4,'阿瑟',19,2);
Query OK, 1 row affected (0.04 sec)
mysql> insert into test(id,name,age,salary) values(5,'赵六',20,3);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  2 | 李四      |   18 |   1.00 |
|  3 | 王五      |   18 |   1.00 |
|  4 | 阿瑟东    |   19 |   2.00 |
|  5 | 赵六      |   20 |   3.00 |
+----+-----------+------+--------+
更新表某字段值（时间点二）
mysql> update test set age=99 where salary <>9;
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select * from test;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  2 | 李四      |   99 |   1.00 |
|  3 | 王五      |   99 |   1.00 |
|  4 | 阿瑟东    |   99 |   2.00 |
|  5 | 赵六      |   99 |   3.00 |
+----+-----------+------+--------+
4 rows in set (0.01 sec)
删除数据（时间点三）
mysql> delete from test where id=2;
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  3 | 王五      |   99 |   1.00 |
|  4 | 阿瑟东    |   99 |   2.00 |
|  5 | 赵六      |   99 |   3.00 |
+----+-----------+------+--------+
3 rows in set (0.00 sec)
按需恢复
  1. 查看当前binlog信息
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1777 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  1. 查看日志事件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                          |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                                         |
| mysql-bin.000002 |  123 | Previous_gtids |         1 |         154 |                                                                               |
| mysql-bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000002 |  219 | Query          |         1 |         307 | BEGIN                                                                         |
| mysql-bin.000002 |  307 | Query          |         1 |         453 | use `beifen`; insert into test(id,name,age,salary) values(4,'阿瑟东',19,2)    |
| mysql-bin.000002 |  453 | Xid            |         1 |         484 | COMMIT /* xid=273 */                                                          |
| mysql-bin.000002 |  484 | Anonymous_Gtid |         1 |         549 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000002 |  549 | Query          |         1 |         637 | BEGIN                                                                         |
| mysql-bin.000002 |  637 | Query          |         1 |         780 | use `beifen`; insert into test(id,name,age,salary) values(5,'赵六',20,3)      |
| mysql-bin.000002 |  780 | Xid            |         1 |         811 | COMMIT /* xid=274 */                                                          |
| mysql-bin.000002 |  811 | Anonymous_Gtid |         1 |         876 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000002 |  876 | Query          |         1 |         964 | BEGIN                                                                         |
| mysql-bin.000002 |  964 | Query          |         1 |        1088 | use `beifen`; update  test set age='99' where age = '5'                       |
| mysql-bin.000002 | 1088 | Query          |         1 |        1177 | COMMIT                                                                        |
| mysql-bin.000002 | 1177 | Anonymous_Gtid |         1 |        1242 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000002 | 1242 | Query          |         1 |        1330 | BEGIN                                                                         |
| mysql-bin.000002 | 1330 | Query          |         1 |        1452 | use `beifen`; update test set age=99 where salary <>9                         |
| mysql-bin.000002 | 1452 | Xid            |         1 |        1483 | COMMIT /* xid=284 */                                                          |
| mysql-bin.000002 | 1483 | Anonymous_Gtid |         1 |        1548 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000002 | 1548 | Query          |         1 |        1636 | BEGIN                                                                         |
| mysql-bin.000002 | 1636 | Query          |         1 |        1746 | use `beifen`; delete from test where id=2                                     |
| mysql-bin.000002 | 1746 | Xid            |         1 |        1777 | COMMIT /* xid=286 */                                                          |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

可以很明显的看到相关操作记录。

一、恢复到【写入两条数据（时间点一）】

根据日志事件，可以得到该时间点的 start-position=154 stop-position=811

  1. 导出对应binlog日志
/HLW/JSZX/mysql/bin/mysqlbinlog   /HLW/JSZX/HLWdata/mysql-bin.000002 --start-position 154 --stop-position 811 > t_s_test.sql
  1. 恢复定期全量备份
mysql -u root -p beifen < t_test.sql
  1. 通过 BINLOG导出的sql进行恢复数据到【写入两条数据（时间点一）】
mysql -u root -p beifen < t_s_test.sql
  1. 登录数据库，验证数据是否按需恢复
mysql> select * from test;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  2 | 李四      |   18 |   1.00 |
|  3 | 王五      |   18 |   1.00 |
|  4 | 阿瑟东    |   19 |   2.00 |
|  5 | 赵六      |   20 |   3.00 |
+----+-----------+------+--------+
4 rows in set (0.00 sec)
二、恢复到【更新表某字段值（时间点二）】

根据日志事件，可以得到该时间点的 start-position=811 stop-position=1483

  1. 导出对应binlog日志
/HLW/JSZX/mysql/bin/mysqlbinlog  --no-defaults --database=beifen  /HLW/JSZX/HLWdata/mysql-bin.000002 --start-position 811 --stop-position 1483 > t_s2_test.sql
  1. 通过BINLOG导出的sql进行恢复数据到【更新表某字段值（时间点二）】
mysql -u root -p beifen < t_s2_test.sql
  1. 登录数据库，验证数据是否按需恢复
mysql> select * from test;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  2 | 李四      |   99 |   1.00 |
|  3 | 王五      |   99 |   1.00 |
|  4 | 阿瑟东    |   99 |   2.00 |
|  5 | 赵六      |   99 |   3.00 |
+----+-----------+------+--------+
4 rows in set (0.01 sec)
总结

恢复主要有两个步骤：

  1. 在临时库中，恢复定期执行的全量备份数据；
  2. 然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据.

衍生分析：

  1. binlog单个存储不要太大，会影响分析效率；
  2. 在全量备份定时任务脚本中，最好加上 flush logs，按天存储binlog日志，方便应急恢复。
西原一点红
关注
  • 2
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用mysqlbinlog恢复指定表
DN金猿的博客
06-29 33
如果从上次备份刷新binlog，到发现表被删掉的过程中产生了多个binlog。则要按照binlog产生的顺序进行恢复，那么恢复的次序应该是按照binglog的产生的序号，从小到大依次恢复。从整库备份的sql文件中导出某个表的sql语句时，vim查找到表的第一条INSERT语句后，按上下换行键计数INSERT语句的条数，然后按n yy复制，退出vim后，假如从上次备份，到发现表被删除，共有两个binlog文件，分别是test-150-bin.000002，test-150-bin.000003 ，
mysql如何利用binlog进行数据恢复详解
12-16
总之，MySQL的binlog是数据安全的重要保障，通过正确配置和理解binlog格式，我们可以有效地应对数据误操作，实现数据恢复，同时保障主从复制的准确性和可靠性。在实际操作中，应结合定期备份和监控策略，确保数据库...
mysqlbinlog如何恢复误删的数据
爪哇人的博客
12-04 5922
1 产生背景 在我们平时的开发过程中，不可避免的会有遇到过手快误删数据的情况。那么误删了数据如何恢复呢？这个就要用到我们的mysql的binlog日志来讲数据进行恢复了。 于是我们就基于这个环境背景来模拟下误删了数据库如何恢复吧 说明：在linux环境下进行演示 2 演示流程 2.1 配置开启binlog（如已开启可忽而） 2.1.1 在我们的Linux服务器上连接上我们的mysql数据库(mysql -h主机名称 -u用户 -p密码) 2.1.2连接上我们的mysql之后，我们先执行命..
MySQL——通过binlog恢复数据
热门推荐
醉酒的戈多的专栏
04-07 4万+
二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
MySQL使用binlog恢复数据
安静的技术宅
07-30 4427
MySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。复制: 从库通过拉取主库的binlog实现主从数据一致恢复: 通过重放binlog恢复数据丢失或误操作情况MySQL的Binlog是数据库的重要组成部分，它记录了数据库的更改操作，并为数据复制和数据恢复提供了有力支持。通过了解Binlog的原理和使用技巧，我们可以更好地利用这一特性，保障数据库的稳健运行，并在必要时实现高级的数据恢复。然而，进行数据恢复时，请务必谨慎操作，以免造成不可逆的损失。
MySQL使用binlog日志恢复数据
Felix的博客
09-14 1万+
MySQL使用binlog日志恢复数据
Mysql 通过 binlog日志 恢复数据（数据搞丢看过来）
Jokers_lin的博客
08-21 8873
选中区间 只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作（见案例7.6偏差） 文章仅供参考，问题也比较多，还有几个情况没考虑。 比如 日志不单单是这个表的日志，还夹杂着其他表，日志内按照执行时间顺序记录，如果区间中包含其他表操作，那么其他表也会被重新执行，会造成脏数据。
如何使用mysql binlog 恢复数据
Trouvailless的博客
04-26 1万+
如果想通过 mysql 的 binlog 恢复数据，首先要开启 binlog 。这里搭建一个测试的环境，了解一下 mysql binlog 是如何恢复数据库的。原理比较简单，binlog 会存储mysql中变化的数据，比如你创建了一个数据库，写入了一些数据，这些都会存储在 mysql 的 binlog 中。 需要恢复的时候就找到，两个位置，一个起始位置，一个结束的位置。结束的位置，一半是数据被破坏或者删除前的位置。mysql 8 默认已经开启了 binlog mysql> show variab
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
MySQL数据库的binlog日志是数据安全的重要保障，它记录了所有的DDL（Data Definition Language，如CREATE、ALTER、DROP等）和DML（Data Manipulation Language，如INSERT、UPDATE、DELETE等）语句，但不包括SELECT...
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
通过binlog日志进行mysql数据恢复
最新发布
09-21
通过binlog日志进行mysql数据恢复
Linux上通过binlog文件恢复mysql数据库详细步骤
09-10
本文将详细阐述如何通过binlog文件恢复MySQL数据库的步骤。 首先，理解binlog的含义与作用至关重要。MySQL的binlog是一种记录所有改变数据库状态的非归档日志，包括插入、更新和删除操作。它不仅用于主从同步，确保...
mysql误删数据后，从binlog中进行恢复删除数据（拯救手残，不跑路）
mu_yu_xi的博客
03-28 1万+
通过mysql的binlog日志，对误删数据进行恢复
MySQL通过Binlog恢复数据
撸码社区的博客
04-18 1万+
Binlog简介 开启Binlog 使用Binlog恢复数据
mysqlbinlog恢复表数据
fengchengwu2012的博客
01-01 5841
2021新年的第一天第一篇文章，2020年最后一天是不安宁的一天，生产数据误删除，在项目经理的夺命call中恢复数据，现在回想真是惊心动魄，此时就做下数据库数据恢复。 1、查看binlog日志是否开启，如果没开启那就尴尬了，下面的或就不用进行了 show variables like 'log_%'; 如果设置，下次记得在/etc/my.cnf中配置 log-bin=mysql-bin 2、查看所有二进制日志 show master logs...
MySQL binlog日志恢复数据详细操作步骤
巴啦啦臭魔仙的博客
01-07 8248
因为后台管理人员刚误删就反馈给我，00038 是最新的，所以确定我需要恢复的数据在 mysql-bin.000038 文件内。PS：我是直接通过导出的 sql 文件进行恢复的，数据量少可以这样操作，数据量大还是需要根据 pos 点或者时间区间进行恢复。可以在终端工具中执行如下命令查看该文件内的内容，但是内容很多的情况下查看或者查询起来并不方便。如果为 ON 则为开启状态，如果开启了就可以继续操作了，如果没开启就GG。2、可以执行如下命令，查看文件最后一个操作事件Position结束点的值。
MySQL利用bin log恢复数据
CoderJia的学习之路
07-01 1545
本文尝试通过bin log恢复误删除的数据，删库不再跑了
Mysql 通过binlog日志恢复数据
hzp666的博客
07-08 1万+
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 =
mysql如何通过binlog恢复表数据
06-09
MySQL通过binlog可以进行数据恢复，具体的步骤如下： 1. 首先需要确认你要恢复数据的时间点，例如你要恢复的时间点是2021年9月1日凌晨3点。 2. 打开MySQL的binlog功能，可以在MySQL的配置文件中设置或者直接在命令行中使用`--log-bin`参数。 3. 通过`mysqlbinlog`命令将binlog文件转换成SQL语句，例如： ``` mysqlbinlog binlog.000001 --start-datetime="2021-09-01 03:00:00" --stop-datetime="2021-09-01 03:01:00" > binlog.sql ``` 这个命令会将binlog.000001文件中2021年9月1日凌晨3点至3点1分的操作记录转换成SQL语句，并保存到binlog.sql文件中。 4. 在MySQL中执行binlog.sql文件中的SQL语句，即可恢复数据。 需要注意的是，这种方法只能用于恢复数据，无法恢复表结构。如果需要恢复整个数据库，可以使用mysqldump备份和恢复。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值