-
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr kgegpa MySQL恢复 ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01190 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (106)
- 数据库 (1,801)
- DB2 (22)
- MySQL (79)
- Oracle (1,637)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (611)
- Oracle安装升级 (101)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (33)
- pdu工具 (7)
- PostgreSQL恢复 (11)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (43)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (26)
-
最近发表
- obet(Oracle Block Editor Tool)第二版发布
- Oracle数据块编辑工具( Oracle Block Editor Tool)-obet
- Oracle坏块修复工具:Patch_blk
- ORA-01172 ORA-01151故障处理
- C_OBJ#_INTCOL#坏块导致数据库无法open故障处理
- ORA-600 kkkicreatecgmap:!efn3
- Oracle 19c 202510补丁（RUs+OJVM）-19.29
- 记录一次raid恢复之后数据库故障处理(ora-01200,ORA-26101,ORA-600)
- nbu备份文件img格式直接rman恢复
- ORA-600 kokasgi1故障处理(sys被重命名)
- Patch_SCN for Linux 功能完善
- ORA-600 2662错误处理-202510
- system表空间丢失部分文件恢复
- arm环境vg损坏mysql数据库恢复
- redhat系列7/8进入单用户模式
- Failed to open \EFI\redhat\grubx64.efi – Not Found 故障处理
- 11.2.0.4升级到19c详细操作过程
- Postgres数据库truncate表无有效备份恢复
- 一次幸运的ORA-07445 kdxlin故障恢复
- ORA-704 ORA-604 ORA-1426故障分析处理
标签归档：mysql delete恢复
MyISAM表delete记录恢复
有客户delete删除了myisam表中的部分数据,本来表中4-5w条记录,删除之后值剩余几十条,希望对其进行恢复（该库没有开启binlog）
mysql> select count(1) from db_bet; +----------+ | count(1) | +----------+ | 46 | +----------+ 1 row in set (0.00 sec)
通过分析.MYD文件确认数据依旧在文件中
通过工具对其删除操作分析,找到记录如下
对其记录入库确认
mysql> select count(1) from db_bet_deleted; +----------+ | count(1) | +----------+ | 40158 | +----------+ 1 row in set (0.10 sec)
[MySQL异常恢复]mysql delete 数据恢复
在mysql(innodb引擎)中,有些时候犹豫误操作导致表中数据被删除,从而导致不可挽回的损失,本文模拟在数据库被误delete的情况下,实现较为完美删除,当然在实际中可能有少量不覆盖或者无法恢复回来,但是在覆盖不多或者未覆盖的情况下,可以实现绝大多数甚至全部恢复.因此在发生误操作时候,应当第一时间保护现场,尽可能防止复写导致不可挽回的损失.在测试恢复过程中,由于mysql和操作系统编码问题,折腾了很久，感谢Lunar的指点
创建模拟表并插入数据
mysql> CREATE TABLE `sms_send_record_del` (
-> `messageId` varchar(30) NOT NULL,
-> `tokenId` varchar(20) NOT NULL,
-> `mobile` varchar(14) default NULL,
-> `msgFormat` int(1) NOT NULL,
-> `msgContent` varchar(1000) default NULL,
-> `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> `deliverState` int(1) default NULL,
-> `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`messageId`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sms_send_record_del select * from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table | Checksum |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 |
+---------------------------------+------------+
1 row in set (0.00 sec)
mysql> checksum table sms_send_record;
+-----------------------------+------------+
| Table | Checksum |
+-----------------------------+------------+
| sms_service.sms_send_record | 2258631583 |
+-----------------------------+------------+
1 row in set (0.00 sec)
确定innodb文件对应位置
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec)
删除表记录
模拟错误操作,误删除表所有数据
mysql> delete from sms_send_record_del; Query OK, 11 rows affected (0.00 sec)
解析ibdata1文件
[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 Opening file: /var/lib/mysql/ibdata1 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 File information: total size, in bytes: 161480704 (154.000 MiB) ID of device containing file: 2049 Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 File information: blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) time of last access: 1440601884 Wed Aug 26 23:11:24 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) All workers finished in 0 sec
分析数据字典
mysql> show tables
-> ;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS |
| SYS_FIELDS |
| SYS_INDEXES |
| SYS_TABLES |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| recover/t_delete | 74 | 2 | 1 | 0 | 0 | | 0 |
| recover/t_delete1 | 84 | 2 | 1 | 0 | 0 | | 0 |
| recover/t_xifenfei | 75 | 2 | 1 | 0 | 0 | | 0 |
| recover/zx_users | 89 | 85 | 1 | 0 | 0 | | 0 |
| sms_service/sms_send_record | 36 | 8 | 1 | 0 | 0 | | 0 |
| sms_service/sms_send_record_del | 90 | 8 | 1 | 0 | 0 | | 0 |
| SYS_FOREIGN | 11 | -2147483644 | 1 | 0 | 0 | | 0 |
| SYS_FOREIGN_COLS | 12 | -2147483644 | 1 | 0 | 0 | | 0 |
| test/SYS_COLUMNS | 86 | 7 | 1 | 0 | 0 | | 0 |
| test/SYS_FIELDS | 88 | 3 | 1 | 0 | 0 | | 0 |
| test/SYS_INDEXES | 87 | 7 | 1 | 0 | 0 | | 0 |
| test/SYS_TABLES | 85 | 8 | 1 | 0 | 0 | | 0 |
| test/zx_users | 43 | 85 | 1 | 0 | 0 | | 0 |
| xifenfei/t_delete | 44 | 8 | 1 | 0 | 0 | | 0 |
| xifenfei/t_xifenfei | 59 | 2 | 1 | 0 | 0 | | 0 |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
39 rows in set (0.00 sec)
mysql> select * from SYS_INDEXES WHERE TABLE_ID=90;
+----------+-----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+---------+----------+------+-------+---------+
| 90 | 110 | PRIMARY | 1 | 3 | 0 | 2955 |
+----------+-----+---------+----------+------+-------+---------+
1 row in set (0.00 sec)
找回被删除记录
[root@web103 mysql_recovery]# ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000110.page \ [root@web103 mysql_recovery]# -t dictionary/sms_send_record_del.sql >/tmp/t_1.txt 2>/tmp/t_1.sql
加载数据并验证
mysql> use sms_service; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> source /tmp/t_1.sql Query OK, 0 rows affected (0.00 sec) Query OK, 11 rows affected, 8 warnings (0.01 sec) Records: 11 Deleted: 0 Skipped: 0 Warnings: 8 mysql> checksum table sms_send_record_del; +---------------------------------+------------+ | Table | Checksum | +---------------------------------+------------+ | sms_service.sms_send_record_del | 2258631583 | +---------------------------------+------------+ 1 row in set (0.00 sec)
发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.