【前言】: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其实记录了每个操作的过程,其中包含了表创建与数据插入的脚本,恢复的本质也就水落石出了。