Linux下MySQL误操作(UPDATE、DELETE等操作没加WHERE条件)导致数据丢失，如何恢复？

发表时间： 2018-01-26 11:37:16 | 浏览次数：

我们在操作数据的时候，难免会有操作失误，那么误操作怎么将数据恢复呢？下面我们来看看，小编是基于ubuntu下测试的。

1.以下方法是通过MySQL日志文件恢复数据，那么首先就是要开启日志
登录MySQL，开始是否开启日志：

show variables like 'log_%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| log_bin        | ON     |
|......
+----------------+--------+

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row

2.查看MySQL日志是否开启成功，登录MySQL随便执行一条UPDATE、DELETE误操作语句，如下

mysql> select id,username from fun_admin;
+------+------------+
| id   | username   |
+------+------------+
| 6    | lrfun.com  |
+------+------------+
mysql> update fun_admin set username='lrfun' where id=6;
mysql> select id,username from fun_admin;
+------+------------+
| id   | username   |
+------+------------+
| 6    | lrfun      |
+------+------------+

我们误操作了一条记录，将lrfun.com改成了lrfun，现在查看以下是否有新的日志文件生成

mysql> show master status; #或者show binary logs;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     1073 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

root@ubuntu:~# pip install -r requirements.txt

root@ubuntu:~# python ~/binlog2sql/binlog2sql.py -h 127.0.0.1 -u root -p'fun123' -d cpxgj -t fun_admin --start-file='mysql-bin.000005' --start-datetime='2018-01-26 10:55:00' --stop-datetime='2018-01-26 11:00:00';

UPDATE `cpxgj`.`fun_admin` SET `username`='lrfun', `update_time`=1466217074, `code`='45VkZp', `is_delete`=1, `id`=6, `group_id`=4, `create_time`=1461210228, `avatar`='', `password`='aa6b1b76c91371dd203e25ee3d584492', `email`='2947253330@qq.com' WHERE `username`='lrfun.com' AND `update_time`=1466217074 AND `code`='45VkZp' AND `is_delete`=1 AND `id`=6 AND `group_id`=4 AND `create_time`=1461210228 AND `avatar`='' AND `password`='aa6b1b76c91371dd203e25ee3d584492' AND `email`='2947253330@qq.com' LIMIT 1; #start 613 end 1042 time 2018-01-26 10:57:01

root@ubuntu:~# python ~/binlog2sql/binlog2sql.py -h 127.0.0.1 -u root -p'fun123' -d cpxgj -t fun_admin --start-file='mysql-bin.000005' --start-pos=613 --end-pos=1042 -B;

UPDATE `cpxgj`.`fun_admin` SET `username`='lrfun.com', `update_time`=1466217074, `code`='45VkZp', `is_delete`=1, `id`=6, `group_id`=4, `create_time`=1461210228, `avatar`='', `password`='aa6b1b76c91371dd203e25ee3d584492', `email`='2947253330@qq.com' WHERE `username`='lrfun' AND `update_time`=1466217074 AND `code`='45VkZp' AND `is_delete`=1 AND `id`=6 AND `group_id`=4 AND `create_time`=1461210228 AND `avatar`='' AND `password`='aa6b1b76c91371dd203e25ee3d584492' AND `email`='2947253330@qq.com' LIMIT 1; #start 613 end 1042 time 2018-01-26 10:57:01

root@ubuntu:~# python ~/binlog2sql/binlog2sql.py -h 127.0.0.1 -u root -p'fun123' -d cpxgj -t fun_admin --start-file='mysql-bin.000005' --start-pos=613 --end-pos=1042 -B | mysql -h 127.0.0.1 -u root -p'fun123';

mysql> select id,username from fun_admin;
+----+-----------+
| id | username  |
+----+-----------+
|  6 | lrfun.com |
+----+-----------+