mysql误操作删除的数据_MySQL误操作删除后，怎么恢复数据？

MySQL误操作删除后，怎么恢复数据？

登陆查数据库

mysql> select * from abc.stad;

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

| id | name |

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

| 1 | abc1 |

| 2 | abc2 |

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

0点全量备份

[[email protected] ~]# mkdir /opt/backup

[[email protected] ~]# mysqldump -uroot -p123456 -F -B --master-data=2 abc|gzip >/opt/backup/quanbei_$(date +%F).sql.gz

[[email protected] ~]# ll /opt/backup/quanbei_2018-11-20.sql.gz

-rw-r--r--. 1 root root 942 Nov 20 11:08 /opt/backup/quanbei_2018-11-20.sql.gz

写入数据

mysql> insert into abc.stad(name) values(‘abc123‘);

mysql> insert into abc.stad(name) values(‘abc134‘);

mysql> select * from stad;

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

| id | name |

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

| 1 | abc1 |

| 2 | abc2 |

| 3 | abc123 |

| 4 | abc134 |

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

模拟误操作，把库删除

mysql> drop database abc;

发现故障，排查问题检查全备份

[[email protected] ~]# ll /opt/backup/quanbei_2018-11-20.sql.gz

-rw-r--r--. 1 root root 942 Nov 20 11:08 /opt/backup/quanbei_2018-11-20.sql.gz

[[email protected] ~]# cd /opt/backup/

[[email protected] backup]# gzip -d quanbei_2018-11-20.sql.gz

[[email protected] backup]# ll

-rw-r--r--. 1 root root 2776 Nov 20 11:08 quanbei_2018-11-20.sql

[[email protected] backup]# grep -i "CHANGE" quanbei_2018-11-20.sql #检查mysqlbinlog确认

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000017‘, MASTER_LOG_POS=120;

检查增量的binlog

[[email protected] backup]# ll /var/lib/mysql/mysql-bin.000017

-rw-rw----. 1 mysql mysql 789 Nov 20 11:13 /var/lib/mysql/mysql-bin.000017

刷新binlog，可以确认恢复的目标，只需要回复mysql-bin.000017即可！

[[email protected] ~]# mysqladmin -uroot -p123456 flush-logs;

[[email protected] backup]# ll /var/lib/mysql/

-rw-rw----. 1 mysql mysql 656137 Nov 20 11:06 mysql-bin.000015

-rw-rw----. 1 mysql mysql 167 Nov 20 11:08 mysql-bin.000016

-rw-rw----. 1 mysql mysql 836 Nov 20 11:22 mysql-bin.000017

-rw-rw----. 1 mysql mysql 120 Nov 20 11:22 mysql-bin.000018

把binlog拷贝至其他地方，方便操作，保留binlog

[[email protected] backup]# cp /var/lib/mysql/mysql-bin.000017 /opt/backup/

[[email protected] backup]# ll /opt/backup/

total 8

-rw-r-----. 1 root root 836 Nov 20 11:24 mysql-bin.000017

-rw-r--r--. 1 root root 2776 Nov 20 11:08 quanbei_2018-11-20.sql

整理汇总binlog，删除不需要的sql语句

[[email protected] backup]# mysqlbinlog /var/lib/mysql/mysql-bin.000017 >bin.sql

[[email protected] backup]# vim bin.sql

drop database abc #把这一行的数据删除，不然就白恢复了

对外不写入的情况下：

[[email protected] backup]# mysql -uroot -p123456

[[email protected] backup]# mysql -uroot -p123456 abc

查看数据是否恢复成功！

mysql> select * from abc.stad;

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

| id | name |

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

| 1 | abc1 |

| 2 | abc2 |

| 3 | abc123 |

| 4 | abc134 |

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

确认完毕，恢复成功！！！！！！

原文：https://www.cnblogs.com/itqingtian/p/9987990.html