mysql恢复之delete 忘加where条件误删除恢复(binglog格式必须是ROW)

最新推荐文章于 2025-06-07 16:33:57 发布

anzhen0429

最新推荐文章于 2025-06-07 16:33:57 发布

阅读量1.7k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： MYSQL备份和恢复

本文链接：https://blog.youkuaiyun.com/anzhen0429/article/details/75893345

MYSQL备份和恢复专栏收录该内容

29 篇文章

订阅专栏

（一）恢复的前提条件就是开启了二进制日志和格式为行格式，二个条件缺一不行！

mysql> show variables like "%log_bin%";

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

mysql> show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+

（二）准备测试数据

mysql> create table testdb.person_t(id int,name varchar(10),address varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into testdb.person_t values(1,'anzhen','nanning'),(2,'liming','beijng'),(3,'xiaohon','gzhou'),(4,'sfds','nhui');
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0

（三）误delete操作，没有加where条件

mysql> delete from testdb.person_t;
Query OK, 4 rows affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.person_t;
Empty set (0.00 sec)

（四）首先要确定delete误操作写进了那个binlog二进制日志文件里面，然后这个二进制日志从中恢复相关行操作。因为启用的是row行格式，所有的每条记录

的修改等等操作都会记录在二进制日志里面。

如果没有其他人flush logs的话，误操作就记录在mysql_bin.000401这个二进制日志文件中。如果有他人flush logs。就往以上找。直到发现误操作的记录。

开始恢复，在线上的话，应该比较复杂，要先进行锁表，以免数据再次被污染。（锁表，查看正在写哪个二进制日志）

mysql> lock table testdb.person_t read;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs；

（五）查看binlog的相关信息

查看MySQL的安装目录：

查看binlog的日志目录和格式：

（六）新建一个session。查看mysqlbinlog里面delete操作的格式，因为版本不一样，可能格式不一样。

/usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/data/mysql_bin.000401 > /tmp/info.txt

[root@localhost104 mysql]# cat /tmp/info.txt | grep delete
[root@localhost104 mysql]# cat /tmp/info.txt | grep DELETE
### DELETE FROM `testdb`.`person_t`
### DELETE FROM `testdb`.`person_t`
### DELETE FROM `testdb`.`person_t`
### DELETE FROM `testdb`.`person_t`

(七）在binglog中去查找相关误delete操作的记录，delete from testdb.person_t操作删除了四行数据，mysql_bin.000401，建议写全部目录。

[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/data/mysql_bin.000401| sed -n '/### DELETE FROM `testdb`.`person_t`/,/COMMIT/p' > /tmp/delete.txt
[root@localhost104 mysql]#
[root@localhost104 mysql]#
[root@localhost104 mysql]# cat /tmp/delete.txt
### DELETE FROM `testdb`.`person_t`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='anzhen' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`person_t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='liming' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='beijng' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`person_t`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaohon' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='gzhou' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`person_t`
### WHERE
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
### @2='sfds' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='nhui' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
# at 1014
#170723 11:35:54 server id 10 end_log_pos 1045 CRC32 0x731dc552 Xid = 113
COMMIT/*!*/;

（8）把delete操作还原成insert操作。这样可以把数据找回来。@1，@2，@3分别表示第1个字段，第2个字段，第3个字段。误删的表有3个字段，所

以就配置cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@3.*),/\1;/g' | sed 's/@[1-3]=//g' > /tmp/person_t.sql，如果有17个字段，就配置为cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@17.*),/\1;/g' | sed 's/@[1-17]=//g' > /tmp/person_t.sql就可以了。只需要改数字。

[root@localhost104 mysql]# cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@3.*),/\1;/g' | sed 's/@[1-3]=//g' > /tmp/person_t.sql

[root@localhost104 mysql]# cat /tmp/person_t.sql
INSERT INTO `testdb`.`person_t`
SELECT
1 ,
'anzhen' ,
'nanning' ;
INSERT INTO `testdb`.`person_t`
SELECT
2 ,
'liming' ,
'beijng' ;
INSERT INTO `testdb`.`person_t`
SELECT
3 ,
'xiaohon' ,
'gzhou' ;
INSERT INTO `testdb`.`person_t`
SELECT
4 ,
'sfds' ,
'nhui' ;

查看一下数据是否是要恢复的数据，如果是的话，就可以进行恢复操作。

在进行lock表的mysql客户端里面执行。

mysql>unlock tables; source /tmp/person_t.sql；

Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

(9)验证数据是否恢复完成！

恢复成功！

可以看见数据已经完全恢复，这种方法的优点是快速，方便。前提是在误操作之前开启了二进制日志和行格式。

到这里数据就完整回来了。将binglog格式设置为row有利有弊，好处是记录了每一行的实际变化，在主从复制时也不容易出问题。但是由于记录每行的变化，会占用大量磁盘，主从复制时带宽占用会有所消耗。到底是使用row还是mixed，需要在实际工作中自己去衡量，但从整体上来说，binglog的格式设置为row，都是不二的选择。

总结：

所以在数据库操作的过程中我们需要格外小心，当然开发那边我们需要做好权限的控制，不过有一个参数可以解决我们的问题，让我们不用担心类似的问题发生：

在[mysql]段落开启这个参数：

safe-updates

这样当我们在做DML操作时忘记加where条件时，mysqld服务器是不会执行操作的：

mysql> select *  from t1;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | yayun            |
|  2 | atlas            |
|  3 | mysql            |
|  6 | good yayun heheh |
+----+------------------+
4 rows in set (0.00 sec)

mysql> delete from t1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql>