mysql报错回滚并提示_mysql误删数据后快速回滚及在解决过程 遇到的问题及解决...

于 2021-02-08 10:02:20 发布
阅读量405 收藏
点赞数
文章标签： mysql报错回滚并提示
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_30844865/article/details/113951458
版权
本文详细介绍了如何利用MySQL的binlog进行数据回滚，包括确认binlog开启状态，安装binlog2sql工具，以及通过binlog定位并恢复误删数据的步骤。同时，文中还展示了在数据包含中文情况下处理编码问题的方法。
摘要由CSDN通过智能技术生成

binlog2sql快速回滚

首先，确认你的MySQL server开启是否开启binlog，

1.查看mysql是否开启 binlog

运行mysql

mysql > show variables like 'log_bin';

结果如图一：

图一：

bbadd162ad967e46ddb6c67113d6084b.png

如果log_bin 为ON，则已开启，如果为OFF,则需要在mysql的 mys.ini 配置文件中设置了以下参数:

[mysqld]

server-id = 1

log_bin = mysql-bin

max_binlog_size = 1000M

binlog-format = row

log_bin = mysql-bin  为默认日志存放地址，路径如下：也可以自定义

图二：

def0b04508bb4574dc40ceb40f5bb95c.png

如果没有开启binlog，也没有预先生成回滚SQL，那真的无法快速回滚了。对存放重要业务数据的MySQL，强烈建议开启binlog。

设置成功后，重启mysql，

运行cmd ，打开dos窗口，运行  net stop mysql(安装mysql时数据库名，如果未做更改，一般默认为mysql)；

提示关闭成功后，重启，运行 net start mysql

提示启动成功。

如果重启失败，如图三：

图三

64deefa29d73a0c20dac3f6729cafe83.png

则需要已管理员身份运行cmd

在运行栏输入cmd ，右击以管理员身份运行，如下图四

e24cab85bdc50240d1809b7be71683cf.png

图四

就可以正常重启了。

如果在关闭成功后，启动不了，提示异常 ，如下图五，查看下是否为my.ini 中某些设置不正确，如果没有，请忽略

35cf3c8308f6013c850a1bc788590173.png

图五

随后，安装开源工具binlog2sql。binlog2sql是一款简单易用的binlog解析工具，其中一个功能就是生成回滚SQL。

使用git bash：

git clone https://github.com/danfengcao/binlog2sql.git  //下载binlog2sql

pip install -r requirements.txt

然后，我们就可以生成回滚SQL了。

背景：误删了test库f表整张表的数据，需要紧急回滚。

原有数据

mysql> select * from f;

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

| uid | did | updateTime          |

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

|   1 |  18 | 2016-12-06 12:28:18 |

|   2 |  19 | 2016-12-06 12:55:56 |

|   3 |  20 | 2016-12-07 14:00:58 |

|   4 |  21 | 2016-12-07 14:01:00 |

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

误操作

mysql> delete from f;

Query OK, 4 rows affected (0.00 sec)

f表被清空

mysql> select * from f;

Empty set (0.00 sec)

回滚步骤：

登录mysql，查看目前的binlog文件

mysql> show master logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |  12262268 |

| mysql-bin.000002 |    132776 |

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

最新的binlog文件是mysql-bin.000002，我们再定位误操作SQL的binlog位置

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002'

输出：

DELETE FROM `test`.`f` WHERE `did`=18 AND `updateTime`='2016-12-06 12:28:18' AND `uid`=1 LIMIT 1; #start 4 end 314

DELETE FROM `test`.`f` WHERE `did`=19 AND `updateTime`='2016-12-06 12:55:56' AND `uid`=2 LIMIT 1; #start 4 end 314

DELETE FROM `test`.`f` WHERE `did`=20 AND `updateTime`='2016-12-07 14:00:58' AND `uid`=3 LIMIT 1; #start 4 end 314

DELETE FROM `test`.`f` WHERE `did`=21 AND `updateTime`='2016-12-07 14:01:00' AND `uid`=4 LIMIT 1; #start 4 end 314

生成回滚sql，并检查回滚sql是否正确

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B

输出：

INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (21, '2016-12-07 14:01:00', 4); #start 4 end 314

INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (20, '2016-12-07 14:00:58', 3); #start 4 end 314

INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (19, '2016-12-06 12:55:56', 2); #start 4 end 314

INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (18, '2016-12-06 12:28:18', 1); #start 4 end 314

确认回滚sql正确，执行回滚语句。登录mysql，数据回滚成功。

$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'

mysql> select * from f;

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

| uid | did | updateTime          |

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

|   1 |  18 | 2016-12-06 12:28:18 |

|   2 |  19 | 2016-12-06 12:55:56 |

|   3 |  20 | 2016-12-07 14:00:58 |

|   4 |  21 | 2016-12-07 14:01:00 |

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

test库tbl表原有数据

mysql> select * from tbl;

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

| id | name   | addtime             |

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

|  1 | 小赵   | 2016-12-10 00:04:33 |

|  2 | 小钱   | 2016-12-10 00:04:48 |

|  3 | 小孙   | 2016-12-13 20:25:00 |

|  4 | 小李   | 2016-12-12 00:00:00 |

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

4 rows in set (0.00 sec)

mysql> delete from tbl;

Query OK, 4 rows affected (0.00 sec)

20:28时，tbl表误操作被清空

mysql> select * from tbl;

Empty set (0.00 sec)

**恢复数据步骤**：

1. 登录mysql，查看目前的binlog文件

mysql> show master status;   或 show master logs;

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

| Log_name         | File_size |

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

| mysql-bin.000002 |       967 |

| mysql-bin.000002 |       965 |

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

2. 最新的binlog文件是mysql-bin.000002，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。

git bash 执行：

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'

输出：

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26

UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. 我们得到了误操作sql的准确位置在728-938之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat

输出：

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

```

4. 确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。

mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql

mysql> select * from tbl;

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

| id | name   | addtime             |

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

|  1 | 小赵   | 2016-12-10 00:04:33 |

|  2 | 小钱   | 2016-12-10 00:04:48 |

|  3 | 小孙   | 2016-12-13 20:25:00 |

|  4 | 小李   | 2016-12-12 00:00:00 |

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

以上，数据库表字段含有中文，数据库字符集为utf-8，本人在直接执行

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -t studtent --start-file='mysql-bin.000001' --start-pos=497 --end-pos=690 -B | mysql -h127.0.0.1 -P3306 -uroot -p'root'

报错，如下图：

360baac84b2c6493524bcf276fd36178.png

提示编码问题；后来进行原文件修改

84fcf65c94e0c400e49ca6bf74b0533e.png

解码utf-8 ,修改为gbk2312，后，执行

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -tt --start-file='mysql-bin.000003' --start-position=1479 --stop-position=1643 -B > rollback4.sql | cat

将sql 打印到sql文件里，可以，但是如果之前执行 脚本回滚还是不行，后来发现是生成的 rollback4.sql 文件编码格式是gbk的，考虑可能是不是 安装的python 的字符集不是utf-8的问题导致在解码时出现问题，待测试……

user0exe
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值