devbox
2023面试高手
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：2023面试高手 | 2024-04-10 17:18:03

赞

踩

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;



  1. create table test01
  2.  (
  3.  id1 int not null auto_increment,
  4.  name varchar(30),
  5.  primary key(id1)
  6.  );
  7.  
  8. create table test02
  9.  (
  10.  id2 int not null auto_increment,
  11.  name varchar(30),
  12.  primary key(id2)
  13.  );

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

  1. CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`()
  2. BEGIN
  3. #Routine body goes here...
  4. DECLARE str1 varchar(30);
  5. DECLARE str2 varchar(30);
  6. DECLARE i int;
  7. set i = 0;
  8.  
  9. while i < 10000 do
  10.  set str1 = substring(md5(rand()),1,25);
  11.  insert into test01(name) values(str1);
  12.  set str2 = substring(md5(rand()),1,25);
  13.  insert into test02(name) values(str1);
  14.  set i = i + 1;
  15.  end while;
  16.  END

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

  1. use lijiamandb;
  2.  create event if not exists e_insert
  3.  on schedule every 10 second
  4.  on completion preserve
  5.  do call p_insert();

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

  1. mysql> show variables like '%event_scheduler%';
  2. +----------------------------------------------------------+-------+
  3. | Variable_name | Value |
  4. +----------------------------------------------------------+-------+
  5. | event_scheduler | OFF |
  6. +----------------------------------------------------------+-------+
  7.  
  8. mysql> set global event_scheduler = on;
  9.  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行数据。

  1. mysql> set global event_scheduler = off;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select count(*) from test01;
  5.  +----------+
  6.  | count(*) |
  7.  +----------+
  8.  |   930000 |
  9.  +----------+
  10. 1 row in set (0.14 sec)
  11.  
  12. mysql> select count(*) from test02;
  13.  +----------+
  14.  | count(*) |
  15.  +----------+
  16.  |   930000 |
  17.  +----------+
  18. 1 row in set (0.13 sec)

STEP4:删除数据库

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

STEP5:使用mysqldump的全备导入 

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

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

  1. [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb 
  2.  
  3. mysql> select count(*) from test01;
  4.  +----------+
  5.  | count(*) |
  6.  +----------+
  7.  |   753238 |
  8.  +----------+
  9. 1 row in set (0.12 sec)
  10.  
  11. mysql> select count(*) from test02;
  12.  +----------+
  13.  | count(*) |
  14.  +----------+
  15.  |   753238 |
  16.  +----------+
  17. 1 row in set (0.11 sec)

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

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

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

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

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

  1. [root@masterdb binlog]# ls
  2.  master-bin.000001  master-bin.000010  master-bin.000019  master-bin.000028  master-bin.000037  master-bin.000046  master-bin.000055
  3.  master-bin.000002  master-bin.000011  master-bin.000020  master-bin.000029  master-bin.000038  master-bin.000047  master-bin.000056
  4.  master-bin.000003  master-bin.000012  master-bin.000021  master-bin.000030  master-bin.000039  master-bin.000048  master-bin.000057
  5.  master-bin.000004  master-bin.000013  master-bin.000022  master-bin.000031  master-bin.000040  master-bin.000049  master-bin.000058
  6.  master-bin.000005  master-bin.000014  master-bin.000023  master-bin.000032  master-bin.000041  master-bin.000050  master-bin.000059
  7.  master-bin.000006  master-bin.000015  master-bin.000024  master-bin.000033  master-bin.000042  master-bin.000051  master-bin.index
  8.  master-bin.000007  master-bin.000016  master-bin.000025  master-bin.000034  master-bin.000043  master-bin.000052
  9.  master-bin.000008  master-bin.000017  master-bin.000026  master-bin.000035  master-bin.000044  master-bin.000053
  10.  master-bin.000009  master-bin.000018  master-bin.000027  master-bin.000036  master-bin.000045  master-bin.000054
  11.  
  12. # 多次查找，发现drop database在54号日志文件
  13. [root@masterdb binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb"
  14.  [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
  15.  [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
  16.  [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"


  1. drop database lijiamandb
  2.  
  3. # 保存到文本，便于搜索
  4. [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt
  5.  
  6.  
  7. # 确认drop database之前的位置为：54号文件的9019487
  8.  # at 9019422
  9.  #200423 16:07:46 server id 11  end_log_pos 9019487 CRC32 0x86f13148     Anonymous_GTID  last_committed=30266    sequence_number=30267   rbr_only=no
  10.  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  11.  # at 9019487
  12.  #200423 16:07:46 server id 11  end_log_pos 9019597 CRC32 0xbd6ea5dd     Query   thread_id=100   exec_time=0     error_code=0
  13.  SET TIMESTAMP=1587629266/*!*/;
  14.  SET @@session.sql_auto_is_null=0/*!*/;
  15.  /*!\C utf8 *//*!*/;
  16.  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  17.  drop database lijiamandb
  18.  /*!*/;
  19.  # at 9019597
  20.  #200423 16:09:25 server id 11  end_log_pos 9019662 CRC32 0x8f7b11dc     Anonymous_GTID  last_committed=30267    sequence_number=30268   rbr_only=no
  21. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  22.  # at 9019662
  23.  #200423 16:09:25 server id 11  end_log_pos 9019774 CRC32 0x9b42423d     Query   thread_id=100   exec_time=0     error_code=0
  24.  SET TIMESTAMP=1587629365/*!*/;
  25.  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


 

# 终止日志文件 

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

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

  1. [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb
  2. mysql> select count(*) from test01;
  3. +----------+
  4. | count(*) |
  5. +----------+
  6. |   930000 |
  7. +----------+
  8. 1 row in set (0.15 sec)
  9.  
  10. mysql> select count(*) from test02;
  11. +----------+
  12.  | count(*) |
  13. +----------+
  14.  |   930000 |
  15. +----------+
  16. 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日志，可以将数据恢复到故障前的任意时刻。 

相关文档集合：

1.MySQL日志–二进制日志(binlog)                  
2.使用mysqlbinlog查看二进制日志                  
3.MySQL使用mysqldump+binlog完整恢复被删除的数据库
4.使用binlog2sql工具来恢复数据库                 
5.MySQL闪回工具—MyFlash                          

作者：gegeman

原文地址：https://www.cnblogs.com/lijiaman/p/12764268.html

喜欢 0
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/400024
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号