标签归档：MySQL恢复

InnoDB: Database page corruption on disk or a failed file read of page恢复

发表于 2023 年 2 月 12 日 由 惜分飞

由于文件系统或者硬件故障导致mysql启动报错InnoDB: Database page corruption on disk or a failed file read of page
mysql-error

使用innodb_force_recovery参数,数据库启动成功,但是部分表查询报错,对于这种情况,是由于ibd文件本身有损坏,通过DISCARD TABLESPACE和IMPORT TABLESPACE也无法解决,只能对ibd文件进行恢复，通过工具直接解析ibd文件恢复其中数据
20230212125347
发表在 MySQL恢复 | 标签为 , | 评论关闭

MySQL 8.0版本ibd文件恢复

发表于 2022 年 12 月 18 日 由 惜分飞

对于单个的ibd文件,大部分情况下可以通过DISCARD TABLESPACE和IMPORT TABLESPACE方式进行恢复

mysql> use test;
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 TABLE `t1` (
    ->   `id` int DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

关闭mysql服务，备份mysql中的t1.ibd文件

[root@xifenfei ~]# service mysql stop
Shutting down MySQL..... SUCCESS! 
[root@xifenfei test]# cp t1.ibd  t1_bak

启动mysql服务,并删除并创建新的t1表(表结构相同)

[root@xifenfei test]# service mysql start
Starting MySQL..................... SUCCESS! 


[root@xifenfei test]# mysql -uroot -poracle test
mysql: [Warning] Using a password on the command line interface can be insecure.
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 8
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> drop table t1;
Query OK, 0 rows affected (0.20 sec)


mysql> 
mysql> CREATE TABLE `t1` (
    ->   `id` int DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)

DISCARD TABLESPACE操作

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

把备份的t1.ibd还原回去并修改权限

[root@xifenfei test]# mv t1_bak t1.ibd
[root@xifenfei test]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Dec 18 17:24 t1.ibd
[root@xifenfei test]# chown mysql.mysql t1.ibd

IMPORT TABLESPACE并验证数据

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.24 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

在恢复途中如果遇到表定义不对,或者ibd文件损坏，或者版本不匹配等各种情况,可能在IMPORT TABLESPACE的时候可能出现类似ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)错误

mysql>  alter table       `t1` import tablespace;                    
ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. 
Because the .cfg file is missing, table definition of the IBD file could be different. 
Or the data file itself is already corrupted.)

如果出现此类错误,无法直接通过该方法进行解决,参考frm和ibd文件数据库恢复,使用专业恢复工具进行处理

发表在 MySQL恢复 | 标签为 , , , , | 评论关闭

[MySQL异常恢复]mysql drop table 数据恢复

发表于 2015 年 8 月 29 日 由 惜分飞

对于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恢复 | 标签为 , , , , , | 评论关闭