mysql恢复drop表
发表于:2024-11-20 作者:千家信息网编辑
千家信息网最后更新 2024年11月20日,drop误操作删除表后,恢复的大概流程是1、从备份中将表恢复到备份时间点2、找到drop操作点3、从binlog中找到备份点到drop点中间所有事件,并筛选出该表的事件4、执行找到该表的事件一、实验数
千家信息网最后更新 2024年11月20日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)
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)
备份
事件
数据
语句
点到
中将
也就是
位置
参数
地方
就是
方法
时间
是在
模式
流程
状态
选出
面的
可通
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
游览器无法与服务器建立安全链接
誉达网络技术有限公司
网络安全性要求包括
数据库主要面临哪些安全风险
南京渔网互联网科技有限公司
武汉电脑软件开发公司
海棠到了凌晨服务器就进不去
足球竞彩数据库设计
网络技术最适合多媒体通信需求
青岛点睛网络技术
预测平台软件开发
金山区网络技术服务市场价格
c语言手机软件开发工具包
FM数据库兼容
幼儿园网络安全日排查表
数据库服务器内存估算
网络安全入门安全法
数据库 密码储存
云服务器做电脑主机
微重复微缺失综合征数据库
2019的网络技术
现在软件开发主流框架
怎么检查远程服务器端口
软件开发网校
服务器能安装吗
大学网络安全主题手抄报
郑州办公软件开发
盈趣科技工业互联网新闻
有此文件的sql数据库怎么恢复
史上最富有的服务器