mysql 误删表怎么处理 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其实记录了每个操作的过程，其中包含了表创建与数据插入的脚本，恢复的本质也就水落石出了。