导航：首页 > 数据库 >

mysql恢复drop表

发表于：2025-01-30 作者：千家信息网编辑

千家信息网最后更新 2025年01月30日，drop误操作删除表后，恢复的大概流程是1、从备份中将表恢复到备份时间点2、找到drop操作点3、从binlog中找到备份点到drop点中间所有事件，并筛选出该表的事件4、执行找到该表的事件一、实验数

千家信息网最后更新 2025年01月30日mysql恢复drop表drop误操作删除表后，恢复的大概流程是
1、从备份中将表恢复到备份时间点
2、找到drop操作点
3、从binlog中找到备份点到drop点中间所有事件，并筛选出该表的事件
4、执行找到该表的事件

一、实验数据：
mysql> select * from sale; +--------+---------+--------+ | month | user_id | amount | +--------+---------+--------+ | 201601 | 1 | 500 | | 201601 | 2 | 300 | | 201601 | 3 | 500 | | 201602 | 1 | 1000 | | 201602 | 2 | 800 | | 201603 | 2 | 1000 | | 201603 | 3 | 500 | | 201604 | 1 | 1000 | +--------+---------+--------+ 8 rows in set (0.00 sec)

二：备份 [root@wd-gtt-system-db data]# mysqldump -S /data/DB/mysql/mysql.sock -h272.30.249.143 -P3306 -uroot -p --single-transaction -B test --tables sale --master-data=2 >/data/backup/mysql_dump.sql 这里一定要加--master-data参数，因为要记录备份时binlog位置
[root@wd-gtt-system-db data]# cat /data/backup/mysql_dump.sql -- MySQL dump 10.13 Distrib 5.6.27-76.0, for Linux (x86_64) -- -- Host: 172.30.249.143 Database: test -- ------------------------------------------------------ -- Server version 5.6.27-76.0-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- -- Position to start replication or point-in-time recovery from --
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=309610;
....... 标红处就是备份点，在后面从binlog中找到备份点到drop点中间所有事件用得上
三：增加测试数据并drop表 insert into sale values(201605,'5',10000) update sale set amount=0 where user_id=1 mysql> select * from sale; +--------+---------+--------+ | month | user_id | amount | +--------+---------+--------+ | 201601 | 1 | 0 | | 201601 | 2 | 300 | | 201601 | 3 | 500 | | 201602 | 1 | 0 | | 201602 | 2 | 800 | | 201603 | 2 | 1000 | | 201603 | 3 | 500 | | 201604 | 1 | 0 | | 201605 | 5 | 10000 | +--------+---------+--------+ 9 rows in set (0.00 sec) mysql> drop table sale;
四：从备份中恢复数据到备份点
因为备份的是全库，要从备份中找到sale表相关的建表语句和数据
[root@wd-gtt-system-db ~]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `sale`/!d;q' /data/backup/mysql_dump.sql DROP TABLE IF EXISTS `sale`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sale` ( `month` int(10) DEFAULT NULL, `user_id` varchar(64) DEFAULT NULL, `amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; [root@wd-gtt-system-db ~]# grep 'INSERT INTO `sale`' /data/backup/mysql_dump.sql>data.sql [root@wd-gtt-system-db ~]# cat data.sql INSERT INTO `sale` VALUES (201601,'1',500),(201601,'2',300),(201601,'3',500),(201602,'1',1000),(201602,'2',800),(201603,'2',1000),(201603,'3',500),(201604,'1',1000);
执行上面的语句，查看数据，已经恢复备份点的状态 mysql> select * from sale; +--------+---------+--------+ | month | user_id | amount | +--------+---------+--------+ | 201601 | 1 | 500 | | 201601 | 2 | 300 | | 201601 | 3 | 500 | | 201602 | 1 | 1000 | | 201602 | 2 | 800 | | 201603 | 2 | 1000 | | 201603 | 3 | 500 | | 201604 | 1 | 1000 | +--------+---------+--------+ 8 rows in set (0.00 sec)
五、查询dorp操作的position
因为drop操作是在备份后发生的，加个 --start-position=309610 [root@wd-gtt-system-db mysql]# mysqlbinlog -v -v --base64-output=DECODE-ROWS --set-charset=UTF-8 --start-position=309610 /data/DB/mysql/mysql-bin.000002 |grep DROP -A10 -B10 ### @1=201604 /* INT meta=0 nullable=1 is_null=0 */ ### @2='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */ ### @3=0 /* INT meta=0 nullable=1 is_null=0 */ # at 328220 #161220 15:30:49 server id 2 end_log_pos 328251 CRC32 0xb42e62b5 Xid = 8713 COMMIT/*!*/; # at 328251 #161220 15:31:12 server id 2 end_log_pos 328368 CRC32 0xf8c5dde3 Query thread_id=127 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1482219072/*!*/; DROP TABLE `sale` /* generated by server */ ...... 标红的地方表示position=328368 执行的drop操作，我们要恢复到这个之前，也就是328251 这个事件
六：从binlog中找到备份点到drop点中间所有事件
--start-position=备份点
--stop-position=drop操作前的事件点
[root@wd-gtt-system-db mysql]# mysqlbinlog -v -v --base64-output=DECODE-ROWS --set-charset=UTF-8 --start-position=309610 --stop-position=328251 mysql-bin.000002 > recover.sql
检索出sale表相关事件
[root@trcloud opt]# more recover.sql |grep --ignore-case -E 'insert|update|delete' -A2 -B2|grep sale insert into sale values(201605,'5',10000) update sale set amount=0 where user_id=1

注：binlog是MIXED或者Statement模式才可通过上述方法找到事件的DML语句
七：执行上面找出来的语句
查看数据，全部恢复
mysql> select * from sale; +--------+---------+--------+ | month | user_id | amount | +--------+---------+--------+ | 201601 | 1 | 0 | | 201601 | 2 | 300 | | 201601 | 3 | 500 | | 201602 | 1 | 0 | | 201602 | 2 | 800 | | 201603 | 2 | 1000 | | 201603 | 3 | 500 | | 201604 | 1 | 0 | | 201605 | 5 | 10000 | +--------+---------+--------+ 9 rows in set (0.00 sec)

很赞哦！