mysql执行drop table 数据恢复方法

对于MySQL数据库的innodb引擎的数据库中,由于误操作删除表,或者由于sqldump自动生成语句含drop table create table语句导致数据丢失,在没有覆盖的情况下,可以实现完美恢复
创建测试表

mysql> CREATE TABLE recover.`t_drop` (

    ->   `messageId` varchar(30) NOT NULL,

    ->   `msgContent` varchar(1000) default NULL,

    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

    ->   `deliverState` int(1) default NULL,

    ->   PRIMARY KEY  (`messageId`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into recover.t_drop select messageId,msgContent,scheduleDate,deliverState from sms_send_record;

Query OK, 11 rows affected (0.00 sec)

Records: 11  Duplicates: 0  Warnings: 0

 

mysql> select * from recover.`t_drop`;

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

| messageId          | msgContent                                                                       | scheduleDate        | deliverState |

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

| 10235259536125650  | 尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 10235353811295807  | 尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 102354211240398235 | 尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 102354554052884567 | 尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 132213454294519126 | 尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 82329022242584577  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 82329022242584581  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 8233400415607376   | 尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 82334502212106951  | 尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 82339012756833423  | 尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

| 8234322198577796   | 尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 

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

11 rows in set (0.00 sec)

 

mysql> checksum table t_drop;

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

| Table           | Checksum  |

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

| recover.t_drop  | 920719058 | 

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

1 row in set (0.00 sec)

删除测试表,后续用来恢复

mysql> drop table recover.t_drop;

Query OK, 0 rows affected (0.00 sec)

解析ibdata文件

[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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440825416 Sat Aug 29 13:16:56 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440855928 Sat Aug 29 21:45:28 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.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:         463312

time of last access:            1440855928 Sat Aug 29 21:45:28 2015

time of last modification:      1440855835 Sat Aug 29 21:43:55 2015

time of last status change:     1440855835 Sat Aug 29 21:43:55 2015

total size, in bytes:            236978176 (226.000 MiB)

 

Size to process:                 236978176 (226.000 MiB)

Worker(1): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(0): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(4): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(3): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(2): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(7): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(5): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

Worker(6): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec

All workers finished in 1 sec

恢复mysql字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh 

Generating dictionary tables dumps... OK

Creating test database ... OK

Creating dictionary tables in database test:

SYS_TABLES ... OK

SYS_COLUMNS ... OK

SYS_INDEXES ... OK

SYS_FIELDS ... OK

All OK

Loading dictionary tables data:

SYS_TABLES ... 162 recs OK

SYS_COLUMNS ... 1247 recs OK

SYS_INDEXES ... 216 recs OK

SYS_FIELDS ... 411 recs OK

All OK

[root@web103 mysql_recovery]# mysql test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10162

Server version: 5.0.95 Source distribution

 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> select * from SYS_TABLES WHERE name like 'recover%';

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

| NAME                    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |

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

| recover/#sql-64a9_1f6d  | 120 |      8 |    1 |      0 |       0 |              |     0 | 

| recover/#sql2-64a9-1f6d | 115 |      8 |    1 |      0 |       0 |              |     0 | 

| recover/t_delete        |  74 |      2 |    1 |      0 |       0 |              |     0 | 

| recover/t_delete1       |  84 |      2 |    1 |      0 |       0 |              |     0 | 

| recover/t_drop          | 125 |      4 |    1 |      0 |       0 |              |     0 | 

| recover/t_truncate      | 120 |      8 |    1 |      0 |       0 |              |     0 | 

| recover/t_xifenfei      |  75 |      2 |    1 |      0 |       0 |              |     0 | 

| recover/zx_users        |  89 |     85 |    1 |      0 |       0 |              |     0 | 

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

8 rows in set (0.00 sec)

 

mysql> select * from SYS_INDEXES WHERE TABLE_ID=125;

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

| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |

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

|      125 | 142 | PRIMARY |        1 |    3 |     0 | 4294967295 | 

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

1 row in set (0.00 sec)

恢复被删除表记录

[root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000142.page  -t dictionary/t_drop.sql >dumps/default/t_drop 2>2.sql

[root@web103 mysql_recovery]# more dumps/default/t_drop

-- Page id: 9860, Format: COMPACT, Records list: Valid, Expected records: (11 11)

00000099F9F2    80000026800110  t_drop  "10235259536125650"     "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    80000026800129  t_drop  "10235353811295807"     "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    80000026800142  t_drop  "102354211240398235"    "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    8000002680015C  t_drop  "102354554052884567"    "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    80000026800176  t_drop  "132213454294519126"    "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    80000026800190  t_drop  "82329022242584577"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    800000268001A9  t_drop  "82329022242584581"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    800000268001C2  t_drop  "8233400415607376"      "尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    800000268001DA  t_drop  "82334502212106951"     "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    800000268001F3  t_drop  "82339012756833423"     "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

00000099F9F2    8000002680020C  t_drop  "8234322198577796"      "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0

-- Page id: 9860, Found records: 11, Lost records: NO, Leaf page: YES

恢复数据入库

mysql> source dictionary/t_drop.sql

Query OK, 0 rows affected (0.00 sec)

 

mysql> source 2.sql

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 11 rows affected (0.00 sec)

Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql> checksum table t_drop;

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

| Table           | Checksum  |

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

| recover.t_drop  | 920719058 | 

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

1 row in set (0.00 sec)

至此实现删除数据完美恢复

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索用户
mysql drop table恢复、mysql drop table、mysql drop table 慢、mysqldump drop table、mysql drop table回滚，以便于您获取更多的相关知识。

时间： 2024-08-31 22:35:22

mysql执行drop table 数据恢复方法的相关文章

利用硬链接和truncate降低drop table对线上环境的影响

作者简介 肖鹏 微博研发中心数据库技术负责人,主要负责微博数据库(MySQL/Reids/HBase/Memcached)相关的业务保障,性能优化,架构设计以及周边的自动化系统建设.10年互联网数据库架构和管理经验,专注于数据库的高性能和高可用技术保障方向. 众所周知drop table会严重的消耗服务器IO性能,如果被drop的table容量较大,甚至会影响到线上的正常. 首先,我们看一下为什么drop容量大的table会影响线上服务 直接执行drop table,mysql会将表定义和表数据

MySQL · 引擎特性 · DROP TABLE之binlog解析

Drop Table的特殊之处 Drop Table乍一看,与其它DDL 也没什么区别,但当你深入去研究它的时候,发现还是有很多不同.最明显的地方就是DropTable后面可以紧跟多个表,并且可以是不同类型的表,这些表还不需要显式指明其类型,比如是普通表还是临时表,是支持事务的存储引擎的表还是不支持事务的存储引擎的表等.这些特殊之处对于代码实现有什么影响呢?对于普通表,无论是创建还是删除,数据库都会产生相应的binlog日志,而对于临时表来说,记录binlog日志就不是必须的.对于采用不同存储引

[MySQL 源码] MySQL drop table(压缩表)效率与流程分析

 之前发生过一起连续drop压缩表,最后长时间等待信号量crash,线上alert log里的报错是: OS WAIT ARRAY INFO: reservation count 36647199, signal count 34050225 --Thread 1331538240 has waited at row0purge.c line 680 for 950.00 seconds the semaphore: S-lock on RW-latch at 0xe60b60 '&dict_o

MySQL · 特性分析 · drop table的优化

背景 系统为了加速对象的访问,通常都会增加一层缓存,以缓解下一层IO的瓶颈,OS的page cache和数据库的buffer pool都基于此. 但对象的删除,如果同步清理对象的缓存的话,不仅大大增加了延时,同时可能因为缓存过大导致IO blooding.所以针对缓存的清理,都会采用lazy drop的优化,下面我们就来对比下percona和官方针对drop table的lazy drop 优化. 假设使用innodb_file_per_table为表创建独立的tablespace,在业务处理过

MySQL 数据恢复方法（一）

数据库故障,需要恢复新实例 使用场景 主数据库故障,无法恢复,且没有容灾及高可用.需要通过备份文件进行恢复. 所需资源 数据库备份文件 备份点-故障点之间的binlog文件 机器资源 操作说明 mysqldump备份文件恢复 拷贝备份文件到目标机器 登录新建的目标数据库实例 /opt/alisql/bin/mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your My

在MySQL数据库中使用C执行SQL语句的方法_Mysql

他们将讨论返回数据的语句,例如INSERT以及不返回数据的语句,例如UPDATE和DELETE.然后,他们将编写从数据库检索数据的简单程序 执行SQL语句 现在,我们已经有了一个连接,并且知道如何处理错误,是时候讨论使用我们的数据库来作一些实际工作了.执行所有类型的SQL的主关键字是mysql_query: int mysql_query(MYSQL *connection, const char *query) 正如您所见,它非常简单.它取一个指向连接结构的指针和包含要执行的SQL的文本字符串

解决MySql error #1036 Table is read only 错误方法

这次网站搬家采用直接打包mysql数据库和网页文件的形式进行迁移,上传好mysql data目录里面的网站数据库至VPS上mysql存放数据库的目录里面,解压就行.我的VPS存放数据库的路径是 /usr/local/mysql/var. 上传好网站数据,解压,配置好数据库链接参数就行,网站就能正常连接上了,我本以为这已经是顺利迁移完成了,但后来操作的时候,发现只能读取数据库的内容,不能更改写入任何信息,提示#1036 – Table '* ' is read only (*号为任意表),也就是说

Mysql中 show table status 获取表信息的方法_Mysql

使用方法 mysql>show table status; mysql>show table status like 'esf_seller_history'\G; mysql>show table status like 'esf_%'\G; 样例: mysql>show table status like 'esf_seller_history'\G; 1.Name 表名称 2.Engine: 表的存储引擎 3.Version: 版本 4.Row_format 行格式.对于My

mysql批量执行sql文件的方法_Mysql

1.待执行的sql文件为1.sql.2.sql.3.sql.4.sql等 2.写一个batch.sql文件: source 1.sql; source 2.sql; source 3.sql; source 4.sql; 3.在mysql下执行source /batch文件所在目录/batch.sql; 以上就是小编为大家带来的mysql批量执行sql文件的方法全部内容了,希望大家多多支持~ 以上是小编为您精心准备的的内容,在的博客.问答.公众号.人物.课程等栏目也有的相关内容,欢迎继续使用右上