Tag Archives: mysql恢复

mysql delete 恢复

Posted on by

在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数据字典恢复

Posted on by

在以前的文章中说过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出现问题,或者误操作之时提供恢复处理.