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

2023-07-26 16:52:04 189

（一）概述

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

用户执行delete，因为条件不对，删除了不应该删除的数据(DML操作)；
用户执行update，因为条件不对，更新数据出错（DML操作）；
用户误删除表droptable（DDL操作）；
用户误清空表truncate(DDL操作)；
用户删除数据库dropdatabase，跑路（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）实验目的

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

（3.2）测试过程

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

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

创建测试表：

uselijiamandb;createtabletest01
(
id1intnotnullauto_increment,
namevarchar(30),
primarykey(id1)
);

createtabletest02
(
id2intnotnullauto_increment,
namevarchar(30),
primarykey(id2)
);

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

CREATEDEFINER=`root`@`%`PROCEDURE`p_insert`()
BEGIN
#Routinebodygoeshere...
DECLAREstr1varchar(30);
DECLAREstr2varchar(30);
DECLAREiint;
seti=0;

whilei<10000do
setstr1=substring(md5(rand()),1,25);
insertintotest01(name)values(str1);
setstr2=substring(md5(rand()),1,25);
insertintotest02(name)values(str1);
seti=i+1;
endwhile;
END

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

uselijiamandb;
createeventifnotexistse_insert
onscheduleevery10second
oncompletionpreserve
docallp_insert();

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

mysql>showvariableslike'%event_scheduler%';
+----------------------------------------------------------+-------+
|Variable_name|Value|
+----------------------------------------------------------+-------+
|event_scheduler|OFF|
+----------------------------------------------------------+-------+

mysql>setglobalevent_scheduler=on;
QueryOK,0rowsaffected(0.08sec)

--过3分钟。。。
STEP2:第一步生成大量测试数据后，使用mysqldump对lijiamandb数据库执行完全备份
mysqldump-h192.168.10.11-uroot-p123456-P3306--single-transaction--master-data=2--events--routines--databaseslijiamandb>/mysql/backup/lijiamandb.sql

注意：必须要添加--master-data=2，这样才会备份集里面mysqldump备份的终点位置。

--过3分钟。。。

STEP3:为了便于数据库删除前与删除后数据一致性校验，先停止表的数据插入，此时test01和test02都有930000行数据，我们后续恢复也要保证有930000行数据。

mysql>setglobalevent_scheduler=off;
QueryOK,0rowsaffected(0.00sec)

mysql>selectcount(*)fromtest01;
+----------+
|count(*)|
+----------+
|930000|
+----------+
rowinset(0.14sec)

mysql>selectcount(*)fromtest02;
+----------+
|count(*)|
+----------+
|930000|
+----------+
rowinset(0.13sec)

STEP4:删除数据库

mysql>dropdatabaselijiamandb;
QueryOK,2rowsaffected(0.07sec)

STEP5:使用mysqldump的全备导入

mysql>createdatabaselijiamandb; QueryOK,1rowaffected(0.01sec) mysql>exit Bye [root@masterdbbinlog]#mysql-uroot-p123456lijiamandb

在执行全量备份恢复之后，发现只有753238笔数据：

[root@masterdbbinlog]#mysql-uroot-p123456lijiamandb

mysql>selectcount(*)fromtest01;
+----------+
|count(*)|
+----------+
|753238|
+----------+
rowinset(0.12sec)

mysql>selectcount(*)fromtest02;
+----------+
|count(*)|
+----------+
|753238|
+----------+
rowinset(0.11sec)

很明显，全量导入之后，数据不完整，接下来使用mysqlbinlog对二进制日志执行增量恢复。

使用mysqlbinlog进行增量日志恢复最重要的就是确定待恢复的起始位置(start-position)和终止位置(stop-position)，起始位置(start-position)是我们执行全被之后的位置，而终止位置则是故障发生之前的位置。
STEP6:确认mysqldump备份到的最终位置

[root@masterdbbackup]#catlijiamandb.sql|grep"CHANGEMASTER"
--CHANGEMASTERTOMASTER_LOG_FILE='master-bin.000044',MASTER_LOG_POS=8526828

备份到了44号日志的8526828位置，那么恢复的起点可以设置为：44号日志的8526828。

--接下来确认要恢复的终点位置，即执行"DROPDATABASELIJIAMAN"之前的位置，需要到binlog里面确认。

[root@masterdbbinlog]#ls
master-bin.000001master-bin.000010master-bin.000019master-bin.000028master-bin.000037master-bin.000046master-bin.000055
master-bin.000002master-bin.000011master-bin.000020master-bin.000029master-bin.000038master-bin.000047master-bin.000056
master-bin.000003master-bin.000012master-bin.000021master-bin.000030master-bin.000039master-bin.000048master-bin.000057
master-bin.000004master-bin.000013master-bin.000022master-bin.000031master-bin.000040master-bin.000049master-bin.000058
master-bin.000005master-bin.000014master-bin.000023master-bin.000032master-bin.000041master-bin.000050master-bin.000059
master-bin.000006master-bin.000015master-bin.000024master-bin.000033master-bin.000042master-bin.000051master-bin.index
master-bin.000007master-bin.000016master-bin.000025master-bin.000034master-bin.000043master-bin.000052
master-bin.000008master-bin.000017master-bin.000026master-bin.000035master-bin.000044master-bin.000053
master-bin.000009master-bin.000018master-bin.000027master-bin.000036master-bin.000045master-bin.000054

#多次查找，发现dropdatabase在54号日志文件
[root@masterdbbinlog]#mysqlbinlog-vmaster-bin.000056|grep-i"dropdatabaselijiamandb"
[root@masterdbbinlog]#mysqlbinlog-vmaster-bin.000055|grep-i"dropdatabaselijiamandb"
[root@masterdbbinlog]#mysqlbinlog-vmaster-bin.000055|grep-i"dropdatabaselijiamandb"
[root@masterdbbinlog]#mysqlbinlog-vmaster-bin.000054|grep-i"dropdatabaselijiamandb"
dropdatabaselijiamandb

#保存到文本，便于搜索
[root@masterdbbinlog]#mysqlbinlog-vmaster-bin.000054>master-bin.txt


#确认dropdatabase之前的位置为：54号文件的9019487
#at9019422
#20042316:07:46serverid11end_log_pos9019487CRC320x86f13148Anonymous_GTIDlast_committed=30266sequence_number=30267rbr_only=no
SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
#at9019487
#20042316:07:46serverid11end_log_pos9019597CRC320xbd6ea5ddQuerythread_id=100exec_time=0error_code=0
SETTIMESTAMP=1587629266/*!*/;
SET@@session.sql_auto_is_null=0/*!*/;
/*!\Cutf8*//*!*/;
SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
dropdatabaselijiamandb
/*!*/;
#at9019597
#20042316:09:25serverid11end_log_pos9019662CRC320x8f7b11dcAnonymous_GTIDlast_committed=30267sequence_number=30268rbr_only=no
SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
#at9019662
#20042316:09:25serverid11end_log_pos9019774CRC320x9b42423dQuerythread_id=100exec_time=0error_code=0
SETTIMESTAMP=1587629365/*!*/;
createdatabaselijiamandb

STEP7:确定了开始结束点，执行增量恢复
开始：44号日志的8526828
结束：54号文件的9019487

这里分为3条命令执行，起始日志文件涉及到参数start-position参数，单独执行；中止文件涉及到stop-position参数，单独执行；中间的日志文件不涉及到特殊参数，全部一起执行。

#起始日志文件

#起始日志文件
mysqlbinlog--start-position=8526828/mysql/binlog/master-bin.000044|mysql-uroot-p123456


#中间日志文件
mysqlbinlog/mysql/binlog/master-bin.000045/mysql/binlog/master-bin.000046/mysql/binlog/master-bin.000047/mysql/binlog/master-bin.000048/mysql/binlog/master-bin.000049/mysql/binlog/master-bin.000050/mysql/binlog/master-bin.000051/mysql/binlog/master-bin.000052/mysql/binlog/master-bin.000053|mysql-uroot-p123456


#终止日志文件

mysqlbinlog--stop-position=9019487/mysql/binlog/master-bin.000054|mysql-uroot-p123456

STEP8:恢复结束，确认全部数据已经还原

[root@masterdbbinlog]#mysql-uroot-p123456lijiamandb
mysql>selectcount(*)fromtest01;
+----------+
|count(*)|
+----------+
|930000|
+----------+
rowinset(0.15sec)

mysql>selectcount(*)fromtest02;
+----------+
|count(*)|
+----------+
|930000|
+----------+
rowinset(0.13sec)

（四）总结

1.对于DML操作，binlog记录了所有的DML数据变化：
--对于insert，binlog记录了insert的行数据
--对于update，binlog记录了改变前的行数据和改变后的行数据
--对于delete，binlog记录了删除前的数据
假如用户不小心误执行了DML操作，可以使用mysqlbinlog将数据库恢复到故障点之前。

2.对于DDL操作，binlog只记录用户行为，而不记录行变化，但是并不影响我们将数据库恢复到故障点之前。

总之，使用mysqldump全备加binlog日志，可以将数据恢复到故障前的任意时刻。

到此这篇关于MySQL使用mysqldump+binlog完整恢复被删除的数据库的文章就介绍到这了,更多相关MySQL恢复被删除的数据库内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

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

热门推荐

随机推荐