MySQL 5.7 - 通过 BINLOG 恢复数据

时间:2023-02-22 09:17:01

日常开发,运维中,经常会出现误删数据的情况。误删数据的类型大致可分为以下几类:

  • 使用 delete 误删行
  • 使用 drop table 或 truncate table 误删表
  • 使用 drop database 语句误删数据库
  • 使用 rm 命令误删整个 MySQL 实例。

不同的情况,都会有其优先的解决方案:

  • 针对误删行,可以通过 Flashback 工具将数据恢复
  • 针对误删表或库,一般采用通过 BINLOG 将数据恢复。
  • 而对于误删 MySQL 实例,则需要我们搭建 HA 的 MySQL 集群,并保证我们的数据跨机房,跨城市保存。

本篇主要讨论的内容是误删表或者库,会先介绍有关 BINLOG 的操作命令,然后会对误删表的这种情况进行实际的模拟。

BINLOG 常见操作命令

BINLOG 的查询方式一般分为两种,一种是进入 MySQL 控制台进行查询,另一种是通过 MySQL 提供的工具 mysqlbinlog 进行查询,两者的不同会在下面介绍。

通过 MySQL Cli 查询 BINLOG 信息

在 cli 中,常见的命令如下:

# 查询 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;

使用 show BINLOG events 的问题:

  • 使用该命令时,如果当前 binlog 文件很大,而且没有指定 limit,会引发对资源的过度消耗。因为 MySQL 客户端需要将 binlog 的全部内容处理,返回并显示出来。为了防止这种情况,mysqlbinlog 工具是一个很好的选择。

通过 mysqlbinlog 查询 BINLOG 信息

在介绍 mysqlbinlog 工具使用前,先来看下 BINLOG 文件的内容:

# 查询 BINLOG 的信息
mysqlbinlog --no-defaults mysql-bin.000034 | less
# at 141
#100309 9:28:36 server id 123 end_log_pos 245
Query thread_id=3350 exec_time=11 error_code=0
  • at 表示 offset 或者说事件开始的起始位置
  • 100309 9:28:36 server id 123 表示 server 123 开始执行事件的日期
  • end_log_pos 245 表示事件的结束位置 + 1,或者说是下一个事件的起始位置。
  • exec_time 表示在 master 上花费的时间,在 salve 上,记录的时间是从 Master 记录开始,一直到 Slave 结束完成所花费的时间。
  • rror_code=0 表示没有错误发生。

在大致了解 binlog 的内容后,mysqlbinlog 的用途有哪些?:

  • mysqlbinlog 可以作为代替 cli 读取 binlog 的工具。
  • mysqlbinlog 可以将执行过的 SQL 语句输出,用于数据的恢复或备份。

查询 BINLOG 日志:

# 查询规定时候后发生的 BINLOG 日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-datetime "2019-11-22 14:00:00" \
--database sync_test mysql-bin.000034 | less

导出 BINLOG 日志,用于分析和排查 sql 语句:

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-datetime "2019-11-22 14:00:00" \
--database sync_test \
mysql-bin.000034 > /home/mysql_backup/binlog_raw.sql

导入 BINLOG 日志

# 通过 BINLOG 进行恢复。
mysqlbinlog --start-position=1038 --stop-position=1164 \
--database=db_name mysql-bin.000034 | \
mysql -u cisco -p db_name # 通过 BINLOG 导出的 sql 进行恢复。
mysql -u cisco -p db_name < binlog_raw.sql

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 二进制数据。

MySQL Cli 和 mysqlbinlog 工具之间的比较

如果想知道当前 MySQL 中正在写入的 BINLOG 的名称,大小等基本信息时,可以通过 Cli 相关的命令来查询。

但想查询,定位,恢复 BINLOG 中具体的数据时,要通过 mysqlbinlog 工具,因为相较于 Cli 来说,mysqlbinlog 提供了 --start-datetime--stop-position 等这样更为丰富的参数供我们选择。这时 Cli 中 SHOW BINLOG EVENTS 的简要语法就变得相形见绌了。

使用 BINLOG 恢复数据

恢复的大致流程如下:

  1. 会创建数据库和表,并插入数据。
  2. 误删一条数据。
  3. 继续插入数据。
  4. 误删表。
  5. 最后将原来以及之后插入的数据进行恢复。

准备数据

准备数据库,表及数据:

# 创建临时数据库
CREATE DATABASE IF NOT EXISTS test_binlog \
default charset utf8 COLLATE utf8_general_ci;  # 创建临时表
CREATE TABLE `sync_test` (`id` int(11) \
NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, \
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 添加数据
insert into sync_test (id, name) values (null, 'xiaoa');
insert into sync_test (id, name) values (null, 'xiaob');
insert into sync_test (id, name) values (null, 'xiaoc'); # 查看添加的数据
select * from sync_test;

删除表或者数据

误删操作:

# 删除 name=xiaob 的数据
delete from sync_test where id=3 # 插入几条数据
insert into sync_test (id, name) values (null, 'xiaod');
insert into sync_test (id, name) values (null, 'xiaoe');
insert into sync_test (id, name) values (null, 'xiaof'); # 删除表
DROP TABLE sync_test;

数据的恢复

在执行数据恢复前,如果操作的是生产环境,会有如下的建议:

  • 使用 flush logs 命令,替换当前主库中正在使用的 binlog 文件,好处如下:

    • 可将误删操作,定位在一个 BINLOG 文件中,便于之后的数据分析和恢复。
    • 避免操作正在被使用的 BINLOG 文件,防止发生意外情况。
  • 数据的恢复不要在生产库中执行,先在临时库恢复,确认无误后,再倒回生产库。防止对数据的二次伤害。

通常来说,恢复主要有两个步骤:

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

使用 BINLOG 做数据恢复前:

# 查看正在使用的 Binlog 文件
show master status\G;
# 显示结果是: mysql-bin.000034 # 执行 flush logs 操作,生成新的 BINLOG
flush logs; # 查看正在使用的 Binlog 文件
show master status\G;
# 结果是:mysql-bin.000035

确定恢复数据的步骤:

这里主要是有两条误删的操作,数据行的误删和表的误删。有两种方式进行恢复。

  • 方式一:首先恢复到删除表操作之前的位置,然后再单独恢复误删的数据行。
  • 方式二:首先恢复到误删数据行的之前的位置,然后跳过误删事件再恢复数据表操作之前的位置。

这里采用方式一的方案进行演示,由于是演示,就不额外找一个临时库进行全量恢复了,直接进行操作。

查询创建表的事件位置和删除表的事件位置

#  根据时间确定位置信息
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-datetime "2019-11-22 14:00:00" \
--database test_binlog mysql-bin.000034 | less

创建表的开始位置:

MySQL 5.7 - 通过 BINLOG 恢复数据

删除表的结束位置:

MySQL 5.7 - 通过 BINLOG 恢复数据

插入 name='xiaob' 的位置:

MySQL 5.7 - 通过 BINLOG 恢复数据

# 根据位置导出 SQL 文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-position "2508132" --stop-position "2511004" \
--database test_binlog mysql-bin.000034 \
> /home/mysql_backup/test_binlog_step1.sql mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-position "2508813" --stop-position "2509187" \
--database test_binlog mysql-bin.000034 \
> /home/mysql_backup/test_binlog_step2.sql # 使用 mysql 进行恢复
mysql -u cisco -p < /home/mysql_backup/test_binlog_step1.sql
mysql -u cisco -p < /home/mysql_backup/test_binlog_step2.sql

MySQL 5.7 中无论是否打开 GTID 的配置,在每次事务开启时,都首先会出 GTID 的一个事务,用于并行复制。所以在确定导出开始事务位置时,要算上这个事件。

在使用 --stop-position 导出时,会导出在指定位置的前一个事件,所以这里要推后一个事务。

对于 DML 的语句,主要结束位置要算上 COMMIT 的位置。

总结

在文章开始时,我们熟悉了操作 BINLOG 的两种方式 CLI 和 mysqlbinlog 工具,接着介绍了其间的区别和使用场景,对于一些大型的 BINLOG 文件,使用 mysqlbinlog 会更加的方便和效率。并对 mysqlbinlog 的一些常见参数进行了介绍。

接着通过使用 mysqlbinlog 实际模拟了数据恢复的过程,并在恢复数据时,提出了一些需要注意的事项,比如 flush logs 等。

最后在恢复数据时,要注意 start-positionend-position 的一些小细节,来保证找到合适的位置。

参考

point-in-time-recovery

recovery-from-backups

backup-policy.

MySQL 5.7 - 通过 BINLOG 恢复数据的更多相关文章

  1. 不小心删除数据--利用MySQL的binlog恢复数据

    MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与maste ...

  2. 利用mysql的binlog恢复数据

    MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与maste ...

  3. mysql通过binlog恢复数据

    如果mysql不小心操作失误导致数据错误或者丢失这时候binlog起到了很大的作用 恢复有几种方式 1.按时间恢复--start-datetime   如果确定了时间点,那么按时间恢复是一个再好不过的 ...

  4. mysql利用binlog恢复数据详细例子

    模拟数据恢复的案例 有些时候脑瓜就会短路,难免会出错 场景:在生产环境中,我们搭建了mysql主从,备份操作都是在从备份数据库上 前提:有最近一天或者最近的全备 或者最近一天相关数据库的备份 最重要的 ...

  5. mysql利用binlog恢复数据

    需求:需要给开发提供一个2018年9月30号的数据,按照我们公司正常备份策略来说,直接找到对应时间的备份数据,解压导入即可,恰好这个时间节点的数据没有,只备份到2018年9月25号的,糟糕了吧 咋办呢 ...

  6. mysql binlog恢复数据实战

    在前面,我们了解了mysql binlog日志的作用以及使用方法:  http://www.php20.cn/article/237 在后面讲到了,可以通过binlog进行恢复数据,那么,具体步骤是怎 ...

  7. mysql数据库通过二进制 -【恢复数据记录】

    1.修改配置文件 vi /etc/my.cnf log-bin = binlog systemctl restart mysqld mysql -uroot -p123456 mysql> sh ...

  8. 从binlog恢复数据及Mysqlbinlog文件删除

    做了mysql主从也有一段时间了,这两天检查磁盘空间情况,发现放数据库的分区磁盘激增了40多G,一路查看下来,发现配置好主从复制以来到现在的binlog就有40多G,原来根源出在这里,查看了一下my. ...

  9. mysqdump&plus;binlog恢复数据

    备份全库 [root@db01 b]# mysqldump -uroot -poldboy123 -A > /b/full.sql Warning: Using a password on th ...

随机推荐

  1. 简单酷炫的canvas动画

    作为一个新人怀着激动而紧张的心情写了第一篇帖子还请大家多多支持,小弟在次拜谢. 驯鹿拉圣诞老人动画效果图如下 html如下: <div style="width:400px;heigh ...

  2. angular js 在ie11 下的get请求缓存问题的解决办法

    使用angularjs 1.x开发的应用在ie11 下回碰到各种怪异的情况:一般表现在:卡,慢,丑. 还有另外一种情况,就是:get请求被缓存,导致页面数据没有刷新.今天我们就来解决这个问题. 几行代 ...

  3. sgu 240 Runaway (spfa)

    题意:N点M边的无向图,边上有线性不下降的温度,给固定入口S,有E个出口.逃出去,使最大承受温度最小.输出该温度,若该温度超过H,输出-1. 羞涩的题意 显然N*H的复杂度dp[n][h]表示到达n最 ...

  4. uva 12549

    12549 - Sentry Robots Time limit: 1.000 seconds We need to guard a set of points of interest using s ...

  5. oracle数据库备份和还原

    ip导出方式:exp  demo/demo@127.0.0.1:1521/orcl file=f:\f.dmp full=y 备份:exp demo/demo@orcl file=f:\f.dmp f ...

  6. Xcode中文乱码问题

    老师给拷贝的程序用Xcode打开中文显示是乱码,而预览里面是正常显示的,Xcode默认编码UTF-8没错的,怎么办呢? 解决办法:用自带的文本编辑器打开,全选,复制,Xcode中打开文件,粘贴,ok~ ...

  7. android项目 之 记事本(12) ----- 图片的等比例缩放及给图片加入边框

    本文是自己学习所做笔记.欢迎转载,但请注明出处:http://blog.csdn.net/jesson20121020 在Android的UI开发中常常会遇到图片的缩放,就比方记事本,如今的图片都比較 ...

  8. &lbrack;cocos2dx-lua&rsqb;&amp&semi;quot&semi;Hello Lua&amp&semi;quot&semi;分析

    一年之前学的cocos2dx,那时候还是用C++编写的.但学完之后就找的一个新的方向--Unity3D开发的岗位,对我而言是一个新方向,那时候经过了几个月的每天熬夜奋战,从"0"基 ...

  9. nodejs01--什么是nodejs,nodejs的基本使用

    nodejs使用范围 -直接在cmd命令行运行,在你的电脑上直接运行 -可以搭建一个web服务器(express,koa) -一些基本的使用 -modules是如何工作的 -npm管理modules ...

  10. cocos2dx 播放gif

    起因 或许有人会说,cocos2dx中直接帧动画就行了用什么GIF. 起因是为游戏内部要用到第三方平台的头像,而第三方平台的头像大多都是用到Gif,所以才会有了这个需求 过程 查了各种文档都没找到.但 ...