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 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)

mysql-delete-recovery-1


确定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-delete-recovery-2


加载数据并验证

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)

发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.

mysql数据字典恢复

在以前的文章中说过mysql的数据字典的恢复(使用工具直接抽取MySQL数据字典,缺少SYS_FIELDS表),主要的数据字典有一下几个,在本文中主要对这些数据字典的意义进行一些讲解,为大家更深一步了解mysql恢复处理思路
MySQL恢复字典表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    | 
| SYS_FIELDS     | 
| SYS_INDEXES    | 
| SYS_TABLES     | 
+----------------+
4 rows in set (0.00 sec)

SYS_TABLES
这个表是mysql恢复的最核心的表之一,主要是记录数据库在InnoDB中表的信息。它默认写在InnoDB的index_ids为1的里面,它的根页在8号page上,他的主要列结构为:

mysql> desc SYS_TABLES;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| NAME         | varchar(255)        | NO   | PRI |         |       | 
| ID           | bigint(20) unsigned | NO   |     | 0       |       | 
| N_COLS       | int(10)             | YES  |     | NULL    |       | 
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       | 
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       | 
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       | 
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       | 
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

NAME:顾名思义,就是表的名字,但是注意他记录的格式是db/table,例如:xifenfei/zx_users,表示为xifenfei数据库中的zx_users表
ID:表的编号
N_COLS:表一共包含的列的数量
TYPE, MIX_ID, MIX_LEN 和 CLUSTER_NAME列,对于数据库恢复无任何意义不做描述
SPACE:表空间的标示列. 例如: ibdata1 是 SPACE 0, ibdata2 是 SPACE 1, 每一个 ibd 文件都有自己的表空间标示.

SYS_INDEXES
这个也是mysql恢复的最核心表之一,主要是记录InnoDB的index信息,它默认InnoDB的index_ids为3的里面,他的主要结构为:

mysql> desc SYS_INDEXES;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | 0       |       | 
| ID       | bigint(20) unsigned | NO   | PRI | 0       |       | 
| NAME     | varchar(120)        | YES  |     | NULL    |       | 
| N_FIELDS | int(10) unsigned    | YES  |     | NULL    |       | 
| TYPE     | int(10) unsigned    | YES  |     | NULL    |       | 
| SPACE    | int(10) unsigned    | YES  |     | NULL    |       | 
| PAGE_NO  | int(10) unsigned    | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
ID:为InnoDB中的index的编号,这个在恢复中非常重要,恢复之时需要根据这个去定位具体的文件
NAME:主要表的index的名字,有PRIMARY 和 普通列的index信息,一般恢复之时我们选择PRIMARY
N_FIELDS:表名index包含列的数量,在mysql恢复中不重要
TYPE:恢复之中使用不到该列,不做说明
PAGE:用途等同SYS_TABLES.SPACE
PAGE_NO:标示为每个index的root page的page号,关于index中的page结构如下图所示
Bplustree


SYS_COLUMNS
这个表主要记录数据库中表的列的情况,它存储在index_id 2中.主要用它来确定需要恢复表的列的情况,如果你知道完全的列结构,该表不是MySQL恢复所必须的,它的主要结构为:

mysql> desc SYS_COLUMNS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | NULL    |       | 
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       | 
| NAME     | varchar(255)        | YES  |     | NULL    |       | 
| MTYPE    | int(10) unsigned    | YES  |     | NULL    |       | 
| PRTYPE   | int(10) unsigned    | YES  |     | NULL    |       | 
| LEN      | int(10) unsigned    | YES  |     | NULL    |       | 
| PREC     | int(10) unsigned    | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
POS:该列所在表中的位置,该值从0开始
NAME:列的名字
MTYPE 和 PRTYPE:主要是为了记录列的类型,出现此类问题主要是由于InnoDB最初并不是为MySQL而设计,到了后面为更好支持MySQL,因此出现了两种情况.
LEN:列的长度.这个需要注意编码,比如数据库是utf8编码,定义的varchar(10),实际该处长度显示为30,因为每个除英文外的字符编码为3个byte.
PREC:有些特殊类型中,列的精确度定义

SYS_FIELDS
记录所有index的列的分布信息,它存储在index_id 4中,该表不是MySQL恢复所必须的,它的主要结构为:

mysql> desc SYS_FIELDS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(20) unsigned | NO   | PRI | NULL    |       | 
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       | 
| COL_NAME | varchar(255)        | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

INDEX_ID:index的标志,等同SYS_INDEXES.ID
POS:列在index中的位置,从0开始
COL_NAME:列的名称
通过上述相关表和列,然后结合MySQL相关恢复工具,就可以从底层在InnoDB出现问题,或者误操作之时提供恢复处理.

Mysql查询视图:ERROR 1449 (HY000)

1、问题重现
前几天因为有人删除了数据库中的记录,今天关闭了数据库的远程访问功能,今天接到开发报告,说出现 The user specified as a definer (‘air’@'%’) does not exist错误,他们定位是一张视图不能访问。利用实验重现了他们的情况

[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8846
Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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 user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| xff  | %             |
| root | 127.0.0.1     |
| repl | 192.168.11.10 |
| root | ::1           |
|      | ECP-UC-DB1    |
| root | ECP-UC-DB1    |
| root | localhost     |
+------+---------------+
7 rows in set (0.08 sec)

mysql> use xifenfei;
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> create view v_users as select * from wp_users;
Query OK, 0 rows affected (0.14 sec)

mysql> select count(*) from xifenfei.v_users;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

mysql> update mysql.user set host='localhost' where user='xff' and host='%';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;                   
Query OK, 0 rows affected (0.12 sec)

mysql> exit
Bye
[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8847
Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> use xff;
ERROR 1049 (42000): Unknown database 'xff'
mysql> use xifenfei;
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> select * from v_users ;
ERROR 1449 (HY000): The user specified as a definer ('xff'@'%') does not exist

2、解决方法

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xifenfei           |
+--------------------+
5 rows in set (0.00 sec)

mysql> use information_schema;
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> desc VIEWS;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views;
+--------------+------------+---------+
| TABLE_SCHEMA | TABLE_NAME | DEFINER |
+--------------+------------+---------+
| xifenfei     | v_users    | xff@%   |
+--------------+------------+---------+
1 row in set (0.16 sec)

mysql> create or replace view v_users as select * from wp_users;
ERROR 1044 (42000): Access denied for user 'xff'@'localhost' to database 'information_schema'
mysql> create or replace view xifenfei.v_users as select * from xifenfei.wp_users;
Query OK, 0 rows affected (0.02 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views;
+--------------+------------+---------------+
| TABLE_SCHEMA | TABLE_NAME | DEFINER       |
+--------------+------------+---------------+
| xifenfei     | v_users    | xff@localhost |
+--------------+------------+---------------+
1 row in set (0.01 sec)

mysql> select count(*) from xifenfei.v_users;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

3、原因分析
因为创建视图使用的是xff@%用户(目前已经不存在),然后登录用户使用的是xff@localhost用户,导致mysql认为现在的用户无权限访问该视图,解决方法就是在当前用户下重建该视图

Mysql误删除数据及其bug分析

一、现状描述
11月25日晚上8点40分接到现场电话,说我们公司所有员工的im不能正常登陆,im数据库服务器(mysql数据库)的公司表中对应的我们公司名称被删除,需要处理。接到这个异常后,第一想到的是下午下班前,收到一封ogg的警告邮件说ogg进程终止,然后我登陆数据库查看的时候,发现ogg已经工作正常,所以也就没有太多关注。既然已经出现了这个问题,那么先解决问题,再分析原因。因为这些都是ogg从oracle端同步过来的一些数据,所以直接从oracle那边初始化一份过来,然后重设同步程序就可以了。

二、错误分析
1、出现这个问题,第一想到的就是binlog,因为公司表的数据是从我们oracle那边同步过去的,而且oracle那边没有任何关于这个表的删除操作,所以我定位这个表的delete操作

[mysql@ezgclient mysqllog]$ mysqlbinlog mysqlbin.000150>/tmp/11_25.txt
[mysql@ezgclient mysqllog]$ vi /tmp/11_25.txt
……
# at 1396789
# at 1396875
#111125 16:15:31 server id 2  end_log_pos 1396875       Table_map: `a`.`abc` mapped to number 5304
#111125 16:15:31 server id 2  end_log_pos 1397000       Delete_rows: table id 5304 flags: STMT_END_F

BINLOG '
o07PThMCAAAAVgAAAItQFQAAALgUAAAAAAAABnNydGFpcgAPdGFiX3V1bV9jb21wYW55AA4DD/b2
CAwMCA8PDw8PCBAAARQAFgCAAIAAQABAAEAA+D8=
o07PThkCAAAAfQAAAAhRFQAQALgUAAAAAAEADv//AP7iAAAAFAC6vNbdysC1vL/GvLzT0M/euavL
voAAAAAAOLkeQYAAAAAAAAAAAAEpAAAAAAAAANkqumA8EgAAO8kaakoSAAABAAAAAAAAAA9CVTAw
MDAwMDAzNTgwNzg=
……

2、因为binlog_format采用的是row模式,所以需要进一步解析binglog

[mysql@ezgclient mysqllog]$ mysqlbinlog -v -v mysqlbin.000150>/tmp/11_25.txt
[mysql@ezgclient mysqllog]$ vi /tmp/11_25.txt
#111125 16:15:31 server id 2  end_log_pos 1396875   Table_map: `a`.`abc` mapped to number 5304
#111125 16:15:31 server id 2  end_log_pos 1397000   Delete_rows: table id 5304 flags: STMT_END_F

BINLOG '
o07PThMCAAAAVgAAAItQFQAAALgUAAAAAAAABnNydGFpcgAPdGFiX3V1bV9jb21wYW55AA4DD/b2
CAwMCA8PDw8PCBAAARQAFgCAAIAAQABAAEAA+D8=
o07PThkCAAAAfQAAAAhRFQAQALgUAAAAAAEADv//AP7iAAAAFAC6vNbdysC1vL/GvLzT0M/euavL
voAAAAAAOLkeQYAAAAAAAAAAAAEpAAAAAAAAANkqumA8EgAAO8kaakoSAAABAAAAAAAAAA9CVTAw
MDAwMDAzNTgwNzg=
'/*!*/;
### DELETE FROM a.abc
### WHERE
###   @1=226 /* INT meta=0 nullable=0 is_null=0 */
###   @2='*****有限公司' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
###   @3=951656001 /* DECIMAL(20,0) meta=5120 nullable=0 is_null=0 */
###   @4=000000001 /* DECIMAL(22,0) meta=5632 nullable=1 is_null=0 */
###   @5=41 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @6=2005-05-30 15:11:29 /* DATETIME meta=0 nullable=1 is_null=0 */
###   @7=2011-08-17 02:02:19 /* DATETIME meta=0 nullable=1 is_null=0 */
###   @8=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @9='BU0000000358078' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @10=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @11=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */
###   @13=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */
###   @14=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */
# at 1397000
#111125 16:15:31 server id 2  end_log_pos 1397027   Xid = 79238866
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

通过上面的分析和这里的日志情况显示,很明显有人误删除了这条记录,导致我们公司所有员工不能登录im(登录在线的,不会使用到这条记录,这个也就是导致了我们到晚上八点多才发现这个异常)

3、检查error日志
检查这个日志,发现一个很明显的bug,这个是导致数据库重启,以及那个时间因为数据库重启导致ogg进程异常收到邮件

111125 16:15:35 InnoDB: Assertion failure in thread 1095162176 in file row/row0mysql.c line 1534
InnoDB: Failing assertion: index->type & DICT_CLUSTERED
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
111125 16:15:35 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=1048576
max_used_connections=30
max_threads=1000
threads_connected=14
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 9234379 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xb8bf170
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4146d100 thread_stack 0x30000
/opt/mysql/product/5.1/bin/mysqld(my_print_stacktrace+0x2e)[0x8a74ce]
/opt/mysql/product/5.1/bin/mysqld(handle_segfault+0x322)[0x5dc992]
/lib64/libpthread.so.0[0x357980eb10]
/lib64/libc.so.6(gsignal+0x35)[0x3578c30265]
/lib64/libc.so.6(abort+0x110)[0x3578c31d10]
/opt/mysql/product/5.1/bin/mysqld(row_unlock_for_mysql+0x2f2)[0x7f4a52]
/opt/mysql/product/5.1/bin/mysqld(row_search_for_mysql+0x22e1)[0x802591]
/opt/mysql/product/5.1/bin/mysqld(_ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function+0x192)[0x7724d2]
/opt/mysql/product/5.1/bin/mysqld(_ZN7handler16read_range_firstEPK12st_key_rangeS2_bb+0xbe)[0x6caa9e]
/opt/mysql/product/5.1/bin/mysqld(_ZN7handler22read_multi_range_firstEPP18st_key_multi_rangeS1_jbP17st_handler_buffer+0xce)[0x6c85be]
/opt/mysql/product/5.1/bin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x127)[0x6aa557]
/opt/mysql/product/5.1/bin/mysqld[0x6c415d]
/opt/mysql/product/5.1/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP11st_sql_listyyb+0x86c)[0x66fc5c]
/opt/mysql/product/5.1/bin/mysqld(_Z21mysql_execute_commandP3THD+0x38bf)[0x5f03af]
/opt/mysql/product/5.1/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f25e7]
/opt/mysql/product/5.1/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe63)[0x5f3453]
/opt/mysql/product/5.1/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f3d16]
/opt/mysql/product/5.1/bin/mysqld(handle_one_connection+0x236)[0x5e66d6]
/lib64/libpthread.so.0[0x357980673d]
/lib64/libc.so.6(clone+0x6d)[0x3578cd3d1d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2aab4890fcd0 is an invalid pointer
thd->thread_id=62259
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
111125 16:15:35 mysqld_safe Number of processes running now: 0
111125 16:15:35 mysqld_safe mysqld restarted
InnoDB: Log scan progressed past the checkpoint lsn 0 694228728
111125 16:15:36 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 694229872
111125 16:15:36 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 5
6 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 1397027, file name /opt/mysql/mysqldata/mysqllog/mysqlbin.000149
111125 16:15:37 InnoDB: Started; log sequence number 0 694229872
111125 16:15:37 [Note] Recovering after a crash using /opt/mysql/mysqldata/mysqllog/mysqlbin
111125 16:15:37 [Note] Starting crash recovery...
111125 16:15:37 [Note] Crash recovery finished.

而且还有个巧合就是查看binlog发现,DELETE FROM a.abc where ……之后,数据库就因为这个bug自动重启了。

4、网友解释

# /opt/mysql/product/5.1/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP11st_sql_listyyb+0x86c)[0x66fc5c]
# /opt/mysql/product/5.1/bin/mysqld(_Z21mysql_execute_commandP3THD+0x38bf)[0x5f03af]
---从这信息看是整理簇索引,导致表空间出现损坏
---分析的情况,你的系统应该正在做一个DELETE操作,而且应该无索引可走,删除的数据量也比较大
---可能是大量数据被缓存在innodb_buffer_pool_size中,并且其内部有创建自适应的hash索引,因删除数据而不得不重新创建,
---以及你的服务器当时IO出现瓶颈,导致一时无法响应Innodb master thread,而出现问题,并且InnoDB引擎在此方面出现过BUG
---解决版本是5.1.37之后,所以建议使用:5.1.40版本,较稳定

感谢jinguanding前辈热情帮助

http://www.itpub.net/forum.php?mod=viewthread&tid=1515971&page=1#pid18593129