您好,欢迎来到站长目录(28sn.com)!


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

来源:网络整理 浏览:172次 时间:2021-10-27

(一)概述

在日常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 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql

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

--过3分钟。。。


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


mysql> set global event_scheduler = off;Query OK, 0 rows affected (0.00 sec)mysql> select count(*) from test01; +----------+ | count(*) | +----------+ |   930000 | +----------+1 row in set (0.14 sec)mysql> select count(*) from test02; +----------+ | count(*) | +----------+ |   930000 | +----------+1 row in set (0.13 sec)



STEP4:删除数据库

mysql> drop database lijiamandb;Query OK, 2 rows affected (0.07 sec)


STEP5:使用mysqldump的全备导入


mysql> create database lijiamandb;Query OK, 1 row affected (0.01 sec)mysql> exit Bye [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql  mysql: [Warning] Using a password on the command line interface can be insecure.



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


[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb mysql> select count(*) from test01; +----------+ | count(*) | +----------+ |   753238 | +----------+1 row in set (0.12 sec)mysql> select count(*) from test02; +----------+ | count(*) | +----------+ |   753238 | +----------+1 row in set (0.11 sec)


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


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

[root@masterdb backup]# cat lijiamandb.sql |grep "CHANGE MASTER"-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828

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

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


[root@masterdb binlog]# ls master-bin.000001  master-bin.000010  master-bin.000019  master-bin.000028  master-bin.000037  master-bin.000046  master-bin.000055 master-bin.000002  master-bin.000011  master-bin.000020  master-bin.000029  master-bin.000038  master-bin.000047  master-bin.000056 master-bin.000003  master-bin.000012  master-bin.000021  master-bin.000030  master-bin.000039  master-bin.000048  master-bin.000057 master-bin.000004  master-bin.000013  master-bin.000022  master-bin.000031  master-bin.000040  master-bin.000049  master-bin.000058 master-bin.000005  master-bin.000014  master-bin.000023  master-bin.000032  master-bin.000041  master-bin.000050  master-bin.000059 master-bin.000006  master-bin.000015  master-bin.000024  master-bin.000033  master-bin.000042  master-bin.000051  master-bin.index master-bin.000007  master-bin.000016  master-bin.000025  master-bin.000034  master-bin.000043  master-bin.000052 master-bin.000008  master-bin.000017  master-bin.000026  master-bin.000035  master-bin.000044  master-bin.000053 master-bin.000009  master-bin.000018  master-bin.000027  master-bin.000036  master-bin.000045  master-bin.000054# 多次查找,发现drop database在54号日志文件[root@masterdb binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb" [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb" [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb" [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"
drop database lijiamandb# 保存到文本,便于搜索[root@masterdb binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt# 确认drop database之前的位置为:54号文件的9019487 # at 9019422 #200423 16:07:46 server id 11  end_log_pos 9019487 CRC32 0x86f13148     Anonymous_GTID  last_committed=30266    sequence_number=30267   rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 9019487 #200423 16:07:46 server id 11  end_log_pos 9019597 CRC32 0xbd6ea5dd     Query   thread_id=100   exec_time=0     error_code=0 SET TIMESTAMP=1587629266/*!*/; SET @@session.sql_auto_is_null=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; drop database lijiamandb /*!*/; # at 9019597 #200423 16:09:25 server id 11  end_log_pos 9019662 CRC32 0x8f7b11dc     Anonymous_GTID  last_committed=30267    sequence_number=30268   rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 9019662 #200423 16:09:25 server id 11  end_log_pos 9019774 CRC32 0x9b42423d     Query   thread_id=100   exec_time=0     error_code=0 SET TIMESTAMP=1587629365/*!*/; create database lijiamandb



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@masterdb binlog]# mysql -uroot -p123456 lijiamandbmysql> select count(*) from test01;+----------+| count(*) |+----------+|   930000 |+----------+1 row in set (0.15 sec)mysql> select count(*) from test02;+----------+ | count(*) |+----------+ |   930000 |+----------+1 row in set (0.13 sec)



(四)总结                                                                        
1.对于DML操作,binlog记录了所有的DML数据变化:                                                                   
     --对于insert,binlog记录了insert的行数据                                                                   
     --对于update,binlog记录了改变前的行数据和改变后的行数据                                                                   
     --对于delete,binlog记录了删除前的数据                                                                   
假如用户不小心误执行了DML操作,可以使用mysqlbinlog将数据库恢复到故障点之前。                        
                                                                                                   
   2.对于DDL操作,binlog只记录用户行为,而不记录行变化,但是并不影响我们将数据库恢复到故障点之前。
                                                                                                   
  总之,使用mysqldump全备加binlog日志,可以将数据恢复到故障前的任意时刻。 



推荐站点

  • 我爱发烧音乐我爱发烧音乐

    我爱发烧音乐囊括了从流行音乐到古典音乐多个类型的音乐作品,专栏推荐最新的音乐,提供音乐排名榜单!可供免费线上收听音乐,歌曲流畅,音效极佳! 网站提供的钢琴以及二胡专栏,可供收听者,陶冶情操,改善心情,是难得的轻音乐典藏!

    www.520fs.com
  • 世纪音乐网世纪音乐网

    世纪音乐网是专业的在线音乐试听MP3下载网站。歌曲总计30余万首,收录了网上最新歌曲和流行音乐,DJ舞曲,非主流音乐,经典老歌,劲舞团歌曲,搞笑歌曲,儿童歌曲,英文歌曲等。是您上网听歌的最佳网站。

    www.ssjj.com
  • 杭州网杭州网

      杭州网是杭州地区唯一的新闻门户网站,由中共杭州市委宣传部、杭州日报报业集团和杭州广播电视集团共同组建的杭州网络传媒有限公司运营。

    www.hangzhou.com.cn
  • 深圳在线深圳在线

      深圳在线 www.szol.net是深圳本地最大、最早的地方生活资讯网站之一,网站名“深圳在线www.szol.net”由南方报业传媒集团编辑委员会总编辑、南方日报社总编辑、南方都市报总编辑、南方书画院名誉院长王春芙亲笔题名,深圳在线www.szol.net团队与深圳热线www.szonline.net、奥一网www.oeeee.com都源于全国最早成立于1996年的知名网络公司——深圳万用网。

    www.szol.net
  • 今题网今题网

     今题网- 中国领先的社区服务网,提供社区服务, 在线交友和商家推广服务,于2004年创建上线,公司现有员工超过百名。今题网自成立以来,凭借其独特的定位和丰富的社区交友功能, 凭借其团队超强的搜索引擎优化技术吸引超过千万的用户成为今题网的注册会员。

    www.jinti.com

鄂公网安备 42062502000001号