删库了别跑路 你还应该学会如何恢复数据

作者:鱼仔 博客首页: codeease.top 公众号:Java鱼仔

前言

首先问大家一个问题,如果你上生产的代码在执行delete的时候跳过了where条件,或者执行update时忘记加where条件了,导致大量数据被删除或更新,你会如何处理?

上面的这些问题可能会导致很多数据被错误的更新或删除,当遇到这种情况时,用开玩笑的说法来说只能删库跑路,而更应该学会的是如何将这些数据恢复。

数据安全策略

为了保证数据的安全性,DBA一般会对数据库做下面这两个操作

  • 对数据库进行定期备份:这个操作可能是一天一次,也可能是一周一次,频率越高,恢复的速度越快。
  • 开启binlog日志:binlog日志可以做很多事情,比如在主从同步过程中,作为从库的数据来源,又比如在数据丢失的情况下做数据恢复。
  • 通过下面的命令查看是否开启binlog日志

    SHOW VARIABLES LIKE 'log_bin';
    

    输出结果为on则代表binlog日志被开启

    数据恢复流程演示

    本次演示基于MySQL8.0.45,数据库引擎为InnoDB

    创建数据

    首先创建一张表,并往这张表里插入两条数据:

    CREATE TABLE `test_person` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `school` varchar(255) DEFAULT NULL,
      `address` varchar(255) DEFAULT NULL,
      `age` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    INSERT INTO `test`.`test_person` (`id`, `name`, `school`, `address`, `age`) VALUES (1, 'aa', 'testSchool', 'hz', 25);
    INSERT INTO `test`.`test_person` (`id`, `name`, `school`, `address`, `age`) VALUES (2, 'bb', 'testSchool2', 'sh', 26); 
    
    

    备份数据

    接着模拟日常的备份动作,通过mysqldump命令进行数据备份

    ./mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot -p test > backup.sql
    

    下面是每个参数的含义:

    --single-transaction:表示在备份过程中,使用单个事务来确保数据的一致性。

    --flush-logs:表示在备份过程中,刷新日志文件,以便在备份完成后,日志文件中的内容不会影响到备份数据。

    --source-data=2:表示在备份过程中,将主服务器的二进制日志位置信息也备份到文件中。这个参数比较重要,用于在恢复数据时找到位置信息。老版本叫做master-data

    --routines:表示在备份过程中,备份存储过程和函数。

    --opt:表示使用优化过的备份方式,以提高备份速度和减少备份文件的大小。

    执行完成上面的命令之后,会发现多了一个backup.sql的文件

    模拟数据的删除

    接着在数据库中执行数据删除,模拟数据被误删

    DELETE FROM test_person where 1=1
    

    进行数据恢复

    当发现数据被删除之后,此时就需要进行紧急数据恢复了,数据恢复的原理就是以定期备份文件为全量基础,加上binlog的增量数据,从而恢复数据到任何一秒。

    首先需要从备份文件中找到最后的binlog写入位置:

    cat backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
    

    以上图为例,可以看到备份文件一直包含了mysql-bin.000007的157这个位置,意味着增量恢复只需要从157这个位置开始即可。
    接着找到删除语句执行的位置

    ./mysqlbinlog --no-defaults -vv /usr/local/mysql/data/mysql-bin.000007 | grep -i -B 4 'delete from test_person'
    

    从上面的命令可以看出,这条删除命令对应的binlog执行的位置是从318到428

    从157到318的这中间的数据就是增量的数据,将这些数据整理成增量sql

    ./mysqlbinlog  --no-defaults --start-position=157 --stop-position=318 /usr/local/mysql/data/mysql-bin.000007 > backup_inc.sql
    

    执行完成后,服务器上已经有backup.sql和backup_inc.sql两个文件了

    分别执行下面两段sql进行数据恢复:

    ./mysql -uroot -p test < backup.sql
    ./mysql -uroot -p test < backup_inc.sql
    

    再次运行查找语句会发现被删除的数据已经回来了。

    举一反三

    删除数据之后,可能还会有不少语句已经执行了,如果要将后续的数据也补上,要怎么实现呢?

    也很简单,上面删除语句的执行位置是从318到428,那只需要将428之后的sql整理出来执行就行:

    ./mysqlbinlog  --no-defaults --start-position=428 /usr/local/mysql/data/mysql-bin.000007 > backup_inc2.sql
    

    总结

    这是一个值得学习的技能,也是一个最好永远不要用到的技能。不过了解数据恢复会加深对binlog日志的认识,也不错。