MySQL如何使用mysqldump+binlog完整恢复被删除的数据库原理解析

互联网
它是那样的舒展，那样高雅。特别是盛夏时节，热青奔放，有粉的，白的，紫的……，互不相让，竟相开放，它们好象正在表演一场时装表演。在巨大的碧绿的荷叶上，小水珠滚来滚去，偶尔一些小青蛙跳上一片荷叶，把荷叶当做歌台。在陽光的照耀下，水珠闪闪发光，显得楚楚动人。

（一）概述

在日常MySQL数据库运维过程中，可能会遇到用户误删除数据，常见的误删除数据操作有：

  • 用户执行delete，因为条件不对，删除了不应该删除的数据(DML操作)；
  • 用户执行update，因为条件不对，更新数据出错（DML操作）；
  • 用户误删除表drop table（DDL操作）；
  • 用户误清空表truncate(DDL操作)；
  • 用户删除数据库drop database，跑路（DDL操作）
  • …等

这些情况虽然不会经常遇到，但是遇到了，我们需要有能力将其恢复，下面讲述如何恢复。

（二）恢复原理

如果要将数据库恢复到故障点之前，那么需要有数据库全备和全备之后产生的所有二进制日志。

全备作用 ：使用全备将数据库恢复到上一次完整备份的位置；

二进制日志作用：利用全备的备份集将数据库恢复到上一次完整备份的位置之后，需要对上一次全备之后数据库产生的所有动作进行重做，而重做的过程就是解析二进制日志文件为SQL语句，然后放到数据库里面再次执行。

举个例子：小明在4月1日晚上8：00使用了mysqldump对数据库进行了备份，在4月2日早上12：00的时候，小华不小心删除了数据库，那么，在执行数据库恢复的时候，需要使用4月1日晚上的完整备份将数据库恢复到“4月1日晚上8：00”，那4月1日晚上8：00以后到4月2日早上12：00之前的数据如何恢复呢？就得通过解析二进制日志来对这段时间执行过的SQL进行重做。

（三）删库恢复测试

（3.1）实验目的

在本次实验中，我直接测试删库，执行drop database lijiamandb,确认是否可以恢复。

（3.2）测试过程

在测试数据库lijiamandb中创建测试表test01和test02,然后执行mysqldump对数据库进行全备，之后执行drop database,确认database是否可以恢复。

STEP1:创建测试数据，为了模拟日常繁忙的生产环境，频繁的操作数据库产生大量二进制日志，我特地使用存储过程和EVENT产生大量数据。

创建测试表：

use lijiamandb;create table test01
 (
 id1 int not null auto_increment,
 name varchar(30),
 primary key(id1)
 );

create table test02
 (
 id2 int not null auto_increment,
 name varchar(30),
 primary key(id2)
 );

创建存储过程，往测试表里面插入数据，每次执行该存储过程，往test01和test02各自插入10000条数据：

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`()
BEGIN
#Routine body goes here...
DECLARE str1 varchar(30);
DECLARE str2 varchar(30);
DECLARE i int;
set i = 0;

while i < 10000 do
 set str1 = substring(md5(rand()),1,25);
 insert into test01(name) values(str1);
 set str2 = substring(md5(rand()),1,25);
 insert into test02(name) values(str1);
 set i = i + 1;
 end while;
 END

制定事件，每隔10秒钟，执行上面的存储过程：

use lijiamandb;
 create event if not exists e_insert
 on schedule every 10 second
 on completion preserve
 do call p_insert();

启动EVENT，每个10s自动向test01和test02各自插入10000条数据

mysql> show variables like '%event_scheduler%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| event_scheduler | OFF |
+----------------------------------------------------------+-------+

mysql> set global event_scheduler = on;
 Query OK, 0 rows affected (0.08 sec)

--过3分钟。。。STEP2:第一步生成大量测试数据后，使用mysqldump对lijiamandb数据库执行完全备份 mysqldump -h192.168.10.11 -uroot -p123456 -P3306 -