在MySQL中恢复误删的表及数据 + 实战演练

前言】:MySQL本地环境有2个库,mydb和mysql;其中mydb中有tb1和tb2,为父子关系。在mydb的上下文环境下,试玩RENAME TABLE的时候,意在将mydb的tb1移到mysql中,SQL语句如下:

RENAME TABLE tb1 TO mysql.tb1;

在检查成功移到mysql后,无意间将tb1删除了。。。

事先没有通过navicat手动备份(稳妥姿势如下图)的前提下,如何还原tb1及数据,保持测试父级数据在后续过程使用的便捷性,成了当下一个头痛的问题。这也就成了我后续写这边博客的初心。

数据恢复方式】:

当数据丢失后,已知恢复方式有2种:

#1. 从备份的数据中恢复;

在探究Navicat GUI备份本质的时候,我们不难通过下图提取出来的用于备份的SQL中看出脚本的3个过程:
a. 删除现存的表 (数据随之被删除);
b. 创建备份的表;
c. 插入备份数据;

#2. 从binlog中恢复;

结合本文的背景,接下来将通过DEMO重点介绍通过binlog恢复的过程。

实战演练】:

案例设计与过程:在mydb中创建test_binlog表 -> 插入4条记录 -> 删除表 -> 表与数据恢复。

/* 数据恢复实操210426 */

-- ***** 01. 预处理 ***** --
-- 查看数据库是否开启binlog日志及所在位置
	show variables like '%log_bin%';
	
-- 查看所有二进制日志列表
	show master logs;
	
-- 结束当前日志,开启一个新的日志并查看正在使用的二进制日志
	flush logs;
	show master status; -- - satrt position
	/*
	LAPTOP-6301VV5L-bin.000005	155
	*/

-- ***** 02. 创建表与数据 ***** --
-- 创建测试表
	create table test_binlog (
		id int not null auto_increment primary key,
		name varchar(50)
	);

-- 插入测试数据
	insert into test_binlog (name) 
	values
	('name1'),
	('name2'),
	('name3'),
	('name4');
	
-- 查看表与数据创建后的正在使用的二进制日志 - stop position
	show master status;
	/*
	LAPTOP-6301VV5L-bin.000005	748
	*/
	
-- 查看插入的数据
	select * from test_binlog;
	
-- ***** 03. 删除表 ***** --
-- 删除test_binlog
	drop table if exists test_binlog;
	
	-- 查看表与数据删除后的正在使用的二进制日志
	show master status;
	/*
	LAPTOP-6301VV5L-bin.000005	971
	*/
	
-- 	SQL语句查看binlog
	show binlog events in 'LAPTOP-6301VV5L-bin.000005';
	
-- ***** 04. 使用DOS恢复数据 ***** --
-- DOS命令查看binlog
	mysqlbinlog -v --base64-output=decode-rows "C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-6301VV5L-bin.000005"
	
-- backup the log from specified position range
	mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-6301VV5L-bin.000005" --start-position 234 --stop-position 748 > d:\backup\test.sql
	
-- DOS命令恢复数据
	mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-6301VV5L-bin.000005" --start-position 234 --stop-position 748 |mysql -u root -p
	

关键成功因素】:

#1. 在DOS里边成功使用到mysqlbinlog命令需要配好环境变量,如下图:

#2. 找到删表及删除数据在binlog的位置很繁琐,但很重要。

结合上图我们不难理解,binlog其实记录了每个操作的过程,其中包含了表创建与数据插入的脚本,恢复的本质也就水落石出了。

 

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页