尝试用binlog恢复mysql数据

kelvinlee 发布于2019-08-01 13:59 / 2085人阅读

摘要：介绍又称归档日志是专门用来记录逻辑的监控并记录你在干啥，比如有没有偷偷删表删库位于层，因此所有的存储引擎都可以使用没有固定大小，会追加写入且不会覆盖旧的开启和配置首先我们要检查下我们是否开启了输入命令，变量为表示已开启如何开启直接添加即可，

Binlog介绍：

Binlog又称归档日志是专门用来记录逻辑的 (监控并记录你在干啥，比如有没有偷偷删表删库？？)

Binlog位于server层，因此所有的存储引擎都可以使用

Binlog没有固定大小，会追加写入且不会覆盖旧的Binlog

Binlog开启和配置：

首先我们要检查下我们是否开启了Binlog
输入命令：

show variables like "%bin%";

1，变量log_bin 为 on表示已开启 (如何开启？直接my.cnf添加log_bin="log_bin_basername"即可)

2，变量log_bin_basename 为 binlog日志的文件名，还会自动接后缀.00000xx，每次重启mysql服务或者使用flush logs命令都会生成一个新的binlog文件

3，变量bin_format则表示日志记录的格式，共有三种格式，分别是statement,rows,mixed,

statement 记录的是sql语句

rows 记录的是对数据操作的上下文

mixed 则兼容statement和rows，会自动根据场景来选择使用前两者的哪一种

一般我们推荐使用mixed，因为statement存在隔离限制，如果在"rc/ru" 即读未提交/读提交的隔离级别下，会产生不可重复读，配置主从后使用statement格式（因为只是记录sql）进行复制会导致主从数据不一致，而 rows 由于记录的行数据操作的上下文，相比statement占用空间更大

接着我们先查看一下当前的isolation

输入命令：

show variables like "%isolation%";

 ps：5.7+变量名变为`tracsaction_isolation`,我这边是5.6版本的所以还是`tx_isolation`

通过图可以看到，我这边隔离级别为rc，也就是读提交

对照上面讲的，rc 不支持 statement 格式，所以我们要切换格式为rows或者mixed

可以直接通过global 变量更改，或者直接修改my.cnf，我这里直接修改my.cnf

保存，重启服务，然后重新查看变量binlog_format，ok,已变更为mixed

建立测试数据并备份：

建立一个test库，然后在test库中建立一个简单的t1表用以测试

CREATE TABLE `t1` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `num` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

然后往t1表插入三条数据

insert into t1  (num) values(1),(2),(3);

这样我们测试表和备份数据就完成了，然后用mysqldump进行备份

mysqldump -u -h -p database > /dirs/xxx.sql

接着再插入3条新数据用以测试数据恢复，由于备份中不存在这三条，所以这三条是需要恢复的数据

insert into t1  (num) values(4),(5),(6);

然后我们假装误删表。。。。

drop table t1

准备阶段到此结束~~~

开始恢复数据:

ok,现在开始进入场景，由于我误删了t1表，所以我需要恢复数据（t1应当数据有6条），但备份的数据中只有3条，所以后面插入的那3条记录遗失了，但由于我们配置了binlog，所以后面那3条的数据日志是有被记录的，因此我们只需要用Binlog来恢复那三条数据即可，下面开始数据恢复。

1，先使用备份恢复

我们先把数据恢复到备份状态

mysql -uroot -p -h127.0.0.1 test < /data/mysql/test_backup.sql

恢复成功,检查表中数据

ok，已经恢复到备份状态啦

2，使用Binlog恢复

先查询下当前使用的binlog是哪个文件

show master status;

可以看到使用的是binlog.000001文件，position累计计数到1615

接着我们先熟悉下mysqlbinlog的筛选规则

mysqlbinlog --help

如图，我们可以看到mysqlbinlog 命令有4个筛选条件：

start-datetime/stop-datetime 分别表示想要筛选的记录区间的`起始时间`和`截止时间`
start-position/stop-position 则表示想要筛选的记录区间的`起始位置`和`截止位置`

我们需要用这几个筛选条件来定位所需要恢复的日志区间

然后我们来瞅一下这个binlog.000001文件

/*-d参数表示筛选的数据库名称*/
mysqlbinlog -d test /data/mysql/binlog.000001

由于我们使用的隔离限制为rc，mixed会自动选择rows格式记录对行的数据操作，所以看不到对应的sql,只能看到对应的上下文

But whatever 直接向下拉，然后我们找到一个重点！

position 699 有一个drop table t1 的操作, 而后面的position 814 和 poistion 939则是恢复备份时的删表建表操作。

因此我们确定了stop-position 就是699

然后我们在找下备份时间，因为我们不好确定start-position,而不指定开始位置全部恢复又有点太说不过去了。。因此直接查看下备份时间。

ok，这样我们的start-datetime也确定了，有了 start-datetime 和 stop-position，我们就能确定所要恢复的日志区间了

接下来就要用mysqlbinlog 命令开始恢复了

/*这里的 -D 参数表示防止出现新的日志记录，我不想把恢复时添加的数据记录也加到binlog里面*/
mysqlbinlog -d test -D --start-datetime="2019-05-25 09:39:00" --start-position=699 /data/mysql/binlog.000001 | mysql -uroot -p -h127.0.0.1 test

可以看到加了 -D 参数 `position`数目没有发生改变，即没有生成新的日志记录

查询t1表，发现已经恢复成功了，id 567都恢复进来了

恢复结束

上面的操作是直接通过mysqlbinlog 导入到库中的，当然你也可以选择用mysqlbinlog生成sql文件，然后再导入sql文件进行恢复，我们再试一下。

先恢复到备份状态（因为-D 参数没有生成新的记录，所以直接start-position = 699即可）

然后用mysqlbinlog 生成sql

/*没有加-D 参数就是为了测试会不会出现新的日志记录*/
mysqlbinlog -d test --start-datetime="2019-05-25 09:39:00" --stop-position=699 /data/mysql/binlog.000001 > binlog_201905025.sql

生成成功，接着导入sql

mysql -uroot -p -h127.0.0.1 test < /data/mysql/binlog_20190525.sql

ok，发现恢复也成功了

然后查看position个数

show master status;

果然position变多啦~

  一般建议恢复时添加-D 参数，因为恢复时记录也会被记录到binlog中，相当于重复了。

that"s all ~~~

第一次在思否发文章，感觉格式啥不太标准。。见谅。。

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/49123.html

教你MySQL Binlog实用攻略

本文由云+社区发表 1.概述 binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以事务的形式保存在磁盘中；作用主要有： [x] 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slave...

codecook 2019-08-06 14:27 评论0 收藏0
教你MySQL Binlog实用攻略

本文由云+社区发表 1.概述 binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以事务的形式保存在磁盘中；作用主要有： [x] 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slave...

leoperfect 2019-08-01 10:22 评论0 收藏0
MySQL 复制 - 性能与扩展性的基石 3：常见问题及解决方案

摘要：问题原因非正常关机导致没有把数据及时的写入硬盘。丢失的临时表临时表和基于语句的复制方式不相容。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。临时表的特性只对创建临时表的连接可见。主备复制过程中有很大可能会出现各种问题，接下来我们就讨论一些比较普遍的问题，以及当遇到这些问题时，如何解决或者预防问题发生。 1 数据损坏或丢失问题描述：服务器崩溃、断电、磁盘损坏、内存或网络...

canopus4u 2019-08-19 11:35 评论0 收藏0
MySQL 复制 - 性能与扩展性的基石 3：常见问题及解决方案

摘要：问题原因非正常关机导致没有把数据及时的写入硬盘。丢失的临时表临时表和基于语句的复制方式不相容。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。临时表的特性只对创建临时表的连接可见。主备复制过程中有很大可能会出现各种问题，接下来我们就讨论一些比较普遍的问题，以及当遇到这些问题时，如何解决或者预防问题发生。 1 数据损坏或丢失问题描述：服务器崩溃、断电、磁盘损坏、内存或网络...

haobowd 2019-07-01 12:39 评论0 收藏0
MySQL 复制 - 性能与扩展性的基石 3：常见问题及解决方案

摘要：问题原因非正常关机导致没有把数据及时的写入硬盘。丢失的临时表临时表和基于语句的复制方式不相容。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。临时表的特性只对创建临时表的连接可见。主备复制过程中有很大可能会出现各种问题，接下来我们就讨论一些比较普遍的问题，以及当遇到这些问题时，如何解决或者预防问题发生。 1 数据损坏或丢失问题描述：服务器崩溃、断电、磁盘损坏、内存或网络...

weapon 2019-08-01 13:51 评论0 收藏0