mysql数据恢复方案

前言

本文主要讲述在误操作后，恢复某个时间点之前的数据方式。

实验环境

1. mysql - 8.0.19
2. macOS catalina 10.15.3
3. mysql binlog 开启，并为row格式

数据库准备

1. 创建数据库

CREATE TABLE `user_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 通过存储过程创建实验数据

delimiter;; 
CREATE PROCEDURE test_data () BEGIN DECLARE i INT; 
SET i=1; WHILE (i<=10) 
DO 
INSERT INTO `user_info`( `name`, `age`) VALUES ( CONCAT("姓名",i), i);
SET i=i+1; END WHILE; END;; 
delimiter; 
CALL test_data ();

3. 备份数据库
   在打开一个命令行，到mysql的bin目录下，使用mysqldump工具（如果是mac实现环境，前面需要加./）

mysqldump -u root -h 127.0.0.1 -p mysql_binlog_test > /usr/local/mysql/bak/bak200504.sql

4. 查看当前时间

select now();
结果：2020-05-04 16:30:52

5. 再次通过存储过程创建实验数据，这部分数据就是我们需要回复的数据

CALL test_data ();

6. 再次查看创建时间

select now();
结果：2020-05-04 16:31:16

7. 模拟误操作

update user_info set age = 1; 

8. 通过mysqlbinlog来恢复数据
   1. 暂停mysql的服务（本实验环境不需要）
   2. 清空mysql_binlog_test库 drop table if exists `user_info`;
   3. 运行备份数据 source /usr/local/mysql/bak/bak200504.sql
   4. 通过命令，获取时间段的sql mysqlbinlog --start-datetime='2020-05-04 16:30:52' --stop-datetime='2020-05-04 16:31:16' /usr/local/mysql/data/binlog.000001 > /usr/local/mysql/bak/binlogbak.sql;
   5. 通过source 命令，运行时间段sql source /usr/local/mysql/bak/binlogbak.sql;
9. 这样，就得到了误操作之前的数据