通过binlog文件恢复MySQL数据实验

有时候会不小心误操作,删除了数据,那么如何恢复被删除的数据?如果在开启了binlog的情况下,那么还可以恢复。

最近一次在delete from 表的时候,忘记写where条件,导致整张表的数据全部删除了,这可要了老命了,那么如何恢复被删除的数据?
下面通过实验来模拟一下当时的情况。

数据库里有一张test表,原本有数据100行。

test表结构如下:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

此时先在数据库里运行一次:

flush logs

以方便重新记录binlog日志。执行以上语句,最新的binlog文件,也就是数字最大的文件。我这里是:mysql-bin.000009

现在新产生了5条数据,在mysql中执行以下5句sql命令:

insert into `test` (id,`data`) value (null,'第101行数据');
insert into `test` (id,`data`) value (null,'第102行数据');
insert into `test` (id,`data`) value (null,'第103行数据');
insert into `test` (id,`data`) value (null,'第104行数据');
insert into `test` (id,`data`) value (null,'第105行数据');

此时test表有105条数据。

这时候由于管理员不小心操作了以下语句:

delete from test

现在test表已被清空,现在如何恢复到刚才的105行数据。

还原最近一次完整的数据

通常我们会每天都备份数据库,甚至备份多次。那么首先还原最近的一次备份。
现在还原之前备份的sql文件,还原后,test表恢复到了100条数据,但是缺少了刚插入的5行数据。

备份命令

mysqldump -uroot -p --single-transaction --master-data=2 test_db > H:/backup/test_db.sql

参数说明:
--single-transaction:备份时不锁表,适用于innodb引擎
--master-data=2:记录主库 binlog 信息,不加这个参数导出的备份文件中没有position位置信息
加上--master-data=2会在备份文件里添加以下信息,便于后续通过binlog来确定恢复的位置:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000302', MASTER_LOG_POS=16524613;

test_db:要备份的数据库名

导出binlog日志到sql文件(通过时间段)

回忆大概清空表的时间,例如我这里是2020-03-06 13:35:00,这个时间也就是我们需要从binlog日志中查到的最后的时间
开始时间则是备份文件的备份时间,例如是2020-03-06 13:00:00

#进入你的mysql的bin文件夹
cd D:\mysql\mysql3310\bin
#执行以下命令
mysqlbinlog --no-defaults --database=test_db --start-datetime="2020-03-06 13:00:00" --stop-datetime="2020-03-06 13:35:00" D:\mysql\mysql3310\data\mysql-bin.000009 > 9.sql

导出binlog日志到sql文件(通过position)

确定position位置

# 先把所有日志都导出
mysqlbinlog -v --base64-output=decode-rows E:\www\server\mysql\mysql3310\data\mysql-bin.001058 > 58.sql
# 打开58.sql查看里面的内容,通过搜索关键词,例如:drop table等 如下图:

也可以通过show binlog events in 'mysql-bin.001058'查看,但是没有导出来的详细:

导出指定position的日志

# 导出指定范围的日志
mysqlbinlog --no-defaults --database=test_db --start-position=332 --stop-position=571 E:\www\server\mysql\mysql3310\data\mysql-bin.001058 > 58_4.sql

导入sql文件

登录mysql,通过source命令导入9.sql文件即可

现在已恢复至105条数据。

查看是否开启binlog

show VARIABLES like '%log_bin%'

查看是否已开启binlog:

如果没有开启,则需要在mysql配置文件中,在[mysqld]后面添加以下三行:

server-id=1
log_bin = mysql-bin
max_binlog_size = 1G
binlog_format = ROW
binlog_row_image = full

基于GTID的数据恢复

mysqlbinlog --no-defaults --skip-gtids --include-gtids=c4fcc51a-a9aa-11e8-8fb9-0250f2000002:10 E:\www\server\mysql\mysql3310\data\mysql-bin.001060 | mysql -uroot -p123456

参数说明:
--include-gtids:包含事务
--exclude-gtids:排除事务
--skip-gtids:跳过事务

总体步骤

# 1.发现误删后,先执行以下语句生成新的日志文件;这样误删的语句日志就存在于上一个日志文件中
flush logs;
# 2.导入最近的完整备份文件
source bak.sql
# 3.从binlog中提取需要还原的sql语句
mysqlbinlog --no-defaults *** mysql-bin***
# 4.执行需要还原的sql语句
source *.sql
# 3和4可以合并为一步:
mysqlbinlog --no-defaults *** mysql-bin*** | mysql -uroot -p123456

发表评论

邮箱地址不会被公开。 必填项已用*标注