MySQL数据误删怎么办？闪回帮你找回手抖误删的数据

随着互联网的飞速发展，数据在企业中扮演着越来越重要的角色。而数据库则是企业中最基础、最核心的技术之一。作为目前世界上使用最广泛的关系型数据库管理系统之一，MySQL的稳定性、可靠性和安全性对于企业的数据存储和应用至关重要。

然而，日常的数据库操作中仍然会遇到各种各样的问题。其中，误删除、误修改等意外操作依然是MySQL数据库管理员最头疼的问题之一。这时候，我们就需要学会使用MySQL的闪回功能，以便快速恢复误删除、误修改等情况下的数据。

MySQL闪回技术是一种逆向数据修改技术，也称为“数据时间旅行”或“基于日志的数据修复”，它能够通过回滚操作来还原已经提交的事务，使其变成未提交的状态，从而达到恢复数据的目的。MySQL实现闪回技术的核心是通过查询binlog日志文件，将已经提交的事务进行反向执行。

MySQL闪回技术可以通过多种方法来实现，包括使用命令行工具、使用第三方工具以及自定义脚本等，其实mysql自带的mysqlbinlog工具就能完成，具体步骤如下：

1. 查询binlog日志文件
   使用mysqlbinlog命令查询需要恢复的时间段内的binlog日志文件，并将其保存到一个文本文件中：

   mysqlbinlog /mysql/logbin.0000001 --start-datetime="2023-05-11 15:03:00" --stop-datetime="2023-05-11 15:07:00" >> /tmp/mysql-bin.txt

   其中，–start-datetime和–stop-datetime参数用于指定需要查询的时间段，>>符号用于将结果追加到指定文件中。

BEGIN
/*!*/;
# at 580
#230511 15:06:20 server id 1  end_log_pos 725 CRC32 0xb775d10c 	Query	thread_id=10	exec_time=0	error_code=0
use `nacos`/*!*/;
SET TIMESTAMP=1683788780/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE `nacos`.`users` SET `password` = '222555888' WHERE `username` = '2222'
/*!*/;
# at 725
#230511 15:06:20 server id 1  end_log_pos 756 CRC32 0xd3bcda44 	Xid = 34
COMMIT/*!*/;
DELIMITER ;
# End of log file

2. 过滤需要闪回的SQL语句
   将查询到的日志文件进行过滤，提取需要闪回的SQL语句，并将其保存到一个sql文件中：

   grep -i 'delete from table_name' /tmp/mysql-bin.txt > /tmp/flashback.sql

   其中，grep命令用于过滤包含指定关键字的行，-i参数表示不区分大小写，并将结果保存到指定文件中。

3. 反向执行SQL语句
   根据binlog信息反向执行SQL语句，将已经提交的事务进行回滚。

使用mysqlbinlog命令查询日志文件时，需要确保MySQL服务器上已开启二进制日志功能。如果未开启，需要修改my.cnf配置文件，并重启MySQL服务。

除了mysqlbinlog外，还有很多不错的开源工具。

binlog2sql

从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL。

代码地址：https://github.com/danfengcao/binlog2sql.git

MyFlash

MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog，完成回滚操作。相对已有的回滚工具，其增加了更多的过滤选项，让回滚更加容易。 该工具已经在美团点评内部使用

代码地址：https://github.com/Meituan-Dianping/MyFlash.git

binlog_rollback

binlog_rollback实现了基于row格式binlog的回滚闪回功能，让误删除或者误更新数据，可以不停机不使用备份而快速回滚误操作;也可以解释binlog（支持非row格式binlog）生成易读的SQL，让查找问题如什么时个某个表的某个字段的值被更新成了1，或者找出某个时间内某个表的所有删除操作等问题变得简单;还可以按配置输出各个表的update/insert/delete统计报表， 也会输出大事务与长事务的分析， 应用是否干了坏事一目了然， 也会输出所有DDL。

代码地址：https://github.com/GoDannyLai/binlog_rollback.git

mysql-flashback

可以使用指定 SQL 语句, 来获取上面需要回滚的参数,可以支持条件过滤, 条件过滤也是使用 SQL 的形式体现的.

./mysql-flashback create \
    --match-sql="SELECT col_1, col_2, col_3 FROM schema.table WHERE col_1 = 1 AND col_2 IN(1, 2, 3) AND col_3 BETWEEN 10 AND 20 AND start_log_file = 'mysql-bin.000001' AND start_log_pos = 4 AND end_log_file = 'mysql-bin.000004' AND end_log_pos = 0 AND start_rollback_time = '2019-06-06 12:00:01' AND end_rollback_time = '2019-06-07 12:00:01' AND thread_id = 0"

上面的 –match-sql 参数值特别长, 主要是因为参数要在一个字符串里面导致的.

格式化看 –match-sql 参数值

SELECT col_1, col_2, col_3                           -- 指定只需要的字段, SELECT * 代表所有字段
FROM schema.table                                    -- 执行需要回滚的表. 需要(显示指定)表所在的数据库
WHERE col_1 = 1                                      -- 过滤条件
    AND col_2 IN(1, 2, 3)                            -- 过滤条件 IN 表达式
    AND col_3 BETWEEN 10 AND 20                      -- 过滤条件 BEWTEEN ... AND ... 表达式
    AND start_log_file = 'mysql-bin.000001'          -- 指定需要回滚的范围(开始binlog[文件]), 非过滤条件.
    AND start_log_pos = 4                            -- 指定需要回滚的范围(开始binlog[位点]), 非过滤条件.
    AND end_log_file = 'mysql-bin.000004'            -- 指定需要回滚的范围(结束binlog[文件]), 非过滤条件.
    AND end_log_pos = 0                              -- 指定需要回滚的范围(结束binlog[位点]), 非过滤条件.
    AND start_rollback_time = '2019-06-06 12:00:01'  -- 指定需要回滚的范围(开始时间), 非过滤条件.
    AND end_rollback_time = '2019-06-07 12:00:01'    -- 指定需要回滚的范围(结束时间), 非过滤条件.
    AND thread_id = 0                                -- 指定需要回滚的 Tread ID.

代码地址：https://github.com/daiguadaidai/mysql-flashback.git

开源的关于binlog解析的工具比较多，有兴趣的可以去找一下。在MySQL中，binlog日志默认是关闭的。要使用MySQL闪回功能，必须首先开启binlog日志并设置相应的参数，如 log-bin 等。MySQL支持三种不同类型的binlog日志格式，分别是statement、row和mixed。其中，row模式是最常用的模式，它会记录每行数据的变化，包括删除、插入和更新等操作。因此，在使用MySQL闪回时，建议将binlog日志格式设置为row模式。

MySQL闪回是一项非常有用的功能，可以帮助我们快速恢复误删除、误修改等操作导致的数据丢失。除了基于binlog日志的闪回方案外，MySQL Enterprise Backup工具也提供了一种可行的闪回实现方式。但是需要注意的是，对于数据的安全性和完整性，我们仍然要采取一系列措施保障，如定期备份、设置权限、监控数据库等。虽然MySQL闪回功能非常实用，但是在TRUNCATE操作、DROP TABLE操作、系统故障导致数据库文件损坏或丢失等情况下是无法使用闪回的。

只有在确保数据安全性的前提下，才能更好地利用MySQL闪回等技术，提高数据库的可靠性和稳定性。