测试一下MYSQL8.4误删数据恢复

本地测试一次误删数据恢复

 

查版本

mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.4.0 |
+-----------+

查看自己的binlog的格式

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.07 sec)

 MySQL 5.7.7 起为默认格式

 

 

模拟一次MYSQL误删数据

复制一个测试表，

mysql> create table emp_test as select * from emp;

 

mysql> show tables;

+------------------+

| Tables_in_wifite |

+------------------+

| CITY             |

| DEPARTMENT       |

| EMP              |

| EMPLOYEE         |

| JOB              |

| JOB_HISTORY      |

| LOCATION         |

| REGION           |

| dever            |

| emp              |

| emp_test         |

| test1            |

+------------------+

12 rows in set (0.01 sec)

 

 

查看创建表的语句

 

mysql> show create table emp_test;

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table    | Create Table                                                                                                                                                                                                                                                                                                          |

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp_test | CREATE TABLE `emp_test` (

  `eid` int DEFAULT NULL,

  `ename` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,

  `age` int DEFAULT NULL,

  `hiredate` date DEFAULT NULL,

  `sal` int DEFAULT NULL,

  `deptno` int DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

mysql> delete from emp_test where eid<'49990';

Query OK, 49989 rows affected (1.31 sec)

 

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select count(*) from emp_test;

+----------+

| count(*) |

+----------+

|       11 |

+----------+

1 row in set (0.04 sec)

 

 

就剩下11行数据

正确查看 binlog 是否开启

 

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

 

查看当前 binlog 文件名和位置

 

mysql> show binary log status;

+---------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000025 |  2461662 |              |                  |                   |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

查看所有的binlog

mysql> show binary logs;

+---------------+-----------+-----------+

| Log_name      | File_size | Encrypted |

+---------------+-----------+-----------+

| binlog.000024 |       158 | No        |

| binlog.000025 |   2461662 | No        |

+---------------+-----------+-----------+

2 rows in set (0.00 sec)

先锁表，避免新数据写入覆盖 binlog

 

mysql> LOCK TABLES emp_test WRITE;

 

Query OK, 0 rows affected (0.14 sec)

 

（解锁 UNLOCK TABLES;）

 

 查看被锁的表

SHOW OPEN TABLES WHERE In_use > 0;

 

不要关客户端、不要重启 MySQL！

退出 mysql，在系统命令行用 mysqlbinlog 找到误删那条 SQL

 

[root@host ~]# find / -name binlog.000025

find: ‘/proc/71059’: 没有那个文件或目录

/data/mysql/binlog.000025

解析 binlog，找到 delete from emp_test where eid<'49990';

 

也可以找到误删文件的位置

 1. 先定位误删的binlog文件和位置

mysqlbinlog \ --base64-output=decode-rows \ -v \ --start-datetime="2026-06-01 14:00:00" \ --stop-datetime="2026-06-01 15:05:00" \ /data/mysql/binlog.000025 \ | grep -A 10 -B 5 "DELETE FROM.*emp_test"

 

转化格式

mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog.000025 | \

grep -A 20 '### DELETE FROM' | \

awk 'BEGIN {RS="### DELETE FROM"; FS="\n"}

NR>1 {

    tbl = $1

    gsub(/`/,"",tbl)

    val = ""

    for(i=2;i<=NF;i++) {

        if($i ~ /^###   @/) {

            sub(/^###   @[0-9]+=/,"",$i)

            val = val $i ","

        }

    }

    sub(/,$/,"",val)

    print "INSERT INTO " tbl " VALUES (" val ");"

}' > /tmp/fix.sql

 

查看删了多少行数数据

 

[root@host tmp]# mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog.000025 | grep -c '### DELETE FROM'

49990

[root@host tmp]# grep -c '^INSERT INTO' /tmp/fix.sql

49990

执行修复后，统计生成的 INSERT 语句数量，与 binlog 中的实际删除行数对比：

[root@host tmp]# mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog.000025 | grep -c '### DELETE FROM'

49990

[root@host tmp]# grep -c '^INSERT INTO' /tmp/fix.sql

再执行导入

mysql -uroot -proot1234 wifite < /tmp/fix.sql

 

永久防止误删

SET GLOBAL SQL_SAFE_UPDATES = 1;

 

开启后：

• 没有 WHERE 条件的 DELETE 直接报错，无法执行
• 永远不会再删空表！