技术分享 | 误删表以及表中数据,该如何恢复?
2023-06-13 09:11:04 时间
作者:杨小云
爱可生数据库工程师,负责 MySQL 日常维护及 DMP 产品支持。擅长mysql故障处理。
本文来源:原创投稿
* 爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
场景:
客户误删了一张表,所以想恢复某张表的数据,期望恢复到删除之前的数据。
前提:
数据库误删某表恢复方法,下面介绍的的方法是针对每天有备份的数据库和开启 binlog 日志的。
说明:本文中的测试库为 test 数据库,测试表是 test 中 student 表。
一、开启 binlog 日志,并备份数据
1. 查看数据库是否开启 binlog 日志
如果没有开启,需要下面的方法进行开启
(1)在linux系统下,修改/etc/my.cnf文件
#编辑模式进入/etc/my.cnf
vi /etc/my.cnf
# i开始进行编辑
# 在#log bin 后面添加内容
server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
log_bin_basename= /数据库实例安装目录/log/binlog/端口号/mysql-bin
log_bin_index=/数据库实例安装目录/log/binlog/端口号/mysql-bin.index
# esc 退出编辑,shift+:保存
(2)重启 mysql 服务
systemctl restart mysqld
到这里已经开启 binlog 了,可以用查看命令看一下是否开启
2. 查看一下数据表中的数据
3. 备份数据
备份命令格式:
mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项] > 脚本名
(1)备份所有数据库
mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ -all-databases > /test1.sql
(2)备份单库(多个库之间以空格间隔)
mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database > test2.sql
(3)备份单表(多个表以空格间隔)
mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database table > test3.sql
(4)备份指定数据库排除某些表
mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database --ignore-table=db.tb --ignore-table=db.tb2 > /test4.sql
4. 插入数据后删除数据库
插入数据,生成 binlog 日志
mysql> insert into student values('201215130','张三','男',21,'IS');
Query OK, 1 row affected (0.03 sec)
mysql> insert into student values('201215131','李四','女',20,'MA');
Query OK, 1 row affected (0.02 sec)
删除数据库
mysql> drop database test;
Query OK, 1 row affected (0.10 sec)
切记这个时候不要有任何的操作!!!
二、恢复数据
1. 查看当前的 binlog
mysql> show master status\G;
************************ 1. row ***************************
File: mysql-bin.000021
Position: 68403303
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 130df5fa-01c1-11ed-916a-02000aba3f04:1-446151
1 row in set (0.00 sec)
ERROR:
No query specified
注:mysql-bin.000021文件将作为恢复删除数据的来源之一
2. 拷贝 binlog 日志
将当前的binlog日志拷贝到其他目录,以免后续操作对 binlog日志产生影响
cp /test/data/mysql/log/binlog/4149/mysql-bin.000021 /root
3. 将转换 binlog 日志为 sql
命令格式为:mysqlbinlog -d database mysql-bin文件 > xx.sql ,如:
/data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000021 > 0021bin.sql
[root@test2 4149]# /data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000022 > 0022.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
编辑转化后的sql文件
vi 0021bin.sql
将里面的误操作命令(DROP命令)全部删除
保存后开始进行恢复数据
4. 恢复备份文件
/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@ < test.sql
查看数据库备份文件是否恢复
mysql> show databases;
mysql> use test;
mysql> show tables;
mysql>select * from table;
5. 恢复备份之后被删除的数据
注释掉 binlog 转化后的 sql 文件中的这一行
SET @@GLOBAL.GTID_PURGED=
/*SET @@GLOBAL.GTID_PURGED=XXXX*/;
指定被删除表所在的数据库,导入数据
/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@ test < 0021bin.sql
查看恢复后的数据
mysql> select * from test.student;
+-----------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
| 201215126 | 李一平 | 男 | 18 | IS |
| 201215127 | 张琴 | 女 | 19 | CS |
| 201215128 | 王方 | 女 | 20 | MA |
| 201215129 | 黄林林 | 男 | 21 | IS |
| 201215130 | 李四 | 女 | 20 | MA |
| 201215131 | 张三 | 男 | 21 | IS |
+-----------+-----------+------+------+-------+
10 rows in set (0.00 sec)
本文关键字:#binlog# #备份恢复#
相关文章
- 【Techo Day腾讯技术开放日】如何查看 Series、DataFrame 对象的数据
- Excel技术:如何在一个工作表中筛选并获取另一工作表中的数据
- 金融科技推动风险、数据和技术之间的结构转变—2022年金融服务业现状报告
- 性能测试技术笔记(二):如何准备测试环境和数据
- 编译过程中的并行性优化(三):软件流水线化与SIMD技术
- Linux内核转发技术
- VBA技术:你需要知道的一些VBA操作形状的代码
- 维护Oracle数据库:完美修复技术(oracle数据修复)
- Redis:存储分布式数据的可靠底层技术(redis是干嘛用的)
- Oracle大批量数据插入技术研究(oracle大批量插入)
- MySQL数据库技术:让数据更加智能(数据库技术mysql)
- 突破界限:Oracle 大数据技术驱动新时代科技进步(oracle大数据产品)
- MySQL实时数据同步技术研究(mysql数据实时同步)
- MySQL多表连接技术:实现数据之间的关联(mysql多表连接)
- 程序Linux下运行C语言程序的技术指南(linux执行c)
- 取Linux下串口数据实时读取技术(linux串口读)
- 数据隐藏技术
- 如何共享数据?- 每天5分钟玩转 Docker 容器技术(41)
- 实现完美数据同步:探索双向oracle数据同步技术(数据同步oracle)
- 激发黑客的心思:探究Linux攻击技术(linux怎么攻击)
- Oracle实现数据分类管理的利器——范围分区技术(oracle范围分区)
- RSA和Oracle技术提升数据安全性(rsaoracle)
- MSSQL实战:如何利用演示数据实现数据技术最佳实践(mssql 演示数据)
- Oracle数据库全表插入技术实现(oracle 全表插入)
- Nub利用Oracle恢复技术,有效恢复数据(nub恢复oracle)
- Oracle中国区ACE推动数据新技术发展(Oracle中国区ACE)