mysqlnavicat数据库备份与恢复_undrop-for-innodb工具在无备份的情况下恢复数据库

背景：

服务器断电重启，数据库启动时出现报错，数据文件损坏。数据库无备份，尝试使用innodb_force_recovery启动数据库，未成功。于是，尝试使用undrop-for-innodb工具从数据文件中尝试恢复数据。

恢复步骤：

1. 下载和安装undrop-for-innodb工具

下载地址：https://github.com/twindb/undrop-for-innodb.git

由于软件是c语言编写的，因此需要安装相关的依赖包，主要是gcc和glibc相关的包。

解压软件并进行编译

tar -xvf undrop-for-innodb-develop.tar
cd undrop-for-innodb-develop
make

编译完成后，会出现两个可执行的命令stream_parser和c_parser

2.扫描数据文件

分为两种情况，一种是开启了innodb_file_per_table，一种是没开启，innodb_file_per_table是在5.6.6版本后默认开启的，本文主要讨论这种情况。

（1）找到需要恢复的表的idb文件
[root@mysql etcmj]# ls -ltr
total 12953372
-rw-r-----. 1 polkitd input     114688 Dec 26 03:40 dfs_gantry_log_up_retransmission.ibd
-rw-r-----. 1 polkitd input     114688 Dec 26 03:40 dfs_gantry_update_param.ibd
-rw-r-----. 1 polkitd input     114688 Dec 26 03:40 dfs_gantry_rsu_antennal.ibd
-rw-r-----. 1 polkitd input     131072 Dec 26 03:40 dfs_gantry_vehicle_baseinfo.ibd
-rw-r-----. 1 polkitd input     131072 Dec 26 03:40 dfs_gantry_tac.ibd
-rw-r-----. 1 polkitd input      73728 Dec 26 03:40 dfs_system_log.ibd
-rw-r-----. 1 polkitd input    9437184 May 24 04:01 dfs_gantry_log_upload202005.ibd


（2）使用stream_parser命令扫描ibd文件，以表dfs_gantry_log_upload202005为例
[root@mysql etcmj]# /data/undrop-for-innodb-develop/stream_parser -f dfs_gantry_log_upload202005.ibd
Opening file: /data/data/etcmj/dfs_gantry_log_upload202005.ibd
File information:

ID of device containing file:         2051
inode number:                       786662
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      999
group ID of owner:                     999
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          18432
time of last access:            1590461551 Tue May 26 10:52:31 2020
time of last modification:      1590264067 Sun May 24 04:01:07 2020
time of last status change:     1590461551 Tue May 26 10:52:31 2020
total size, in bytes:              9437184 (9.000 MiB)

Size to process:                   9437184 (9.000 MiB)
All workers finished in 0 sec


（3）扫描完成后会在当前目录生成一个文件夹
[root@mysql etcmj]# ls -ltr
drwxr-xr-x. 4 root    root        4096 Jun  2 10:51 pages-dfs_gantry_log_upload202005.ibd

3.进行数据恢复

这里也分为两种情况，一种是没有表结构的备份，一种是有表结构的备份。

有表结构：

（1）查看表结构
[root@mysql sakila]# cat dfs_gantry_log_upload202005.sql 
CREATE TABLE `dfs_gantry_log_upload202005` (
  `GantryId` varchar(19) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '门架编号',
  `DirectivityId` int(11) NOT NULL,
  `SourceId` varchar(37) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `LogType` smallint(6) NOT NULL,
  `LogDate` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `FileName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `FilePath` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `FileMD5` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `CreateTime` datetime NOT NULL,
  `UploadType` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `RecvTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UploadFlag` tinyint(4) NOT NULL,
  `UploadTime` datetime DEFAULT NULL,
  `TotalCount` int(11) NOT NULL DEFAULT '1' COMMENT '总条数',
  `ReceiveFlag` tinyint(4) NOT NULL DEFAULT '2',
  `LnduceFlag` tinyint(4) NOT NULL,
  `LnduceTime` datetime DEFAULT NULL,
  `ComputerOrder` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`GantryId`,`LogType`,`FileName`,`ComputerOrder`) USING BTREE,
  KEY `dfs_gantry_log_upload202005_LogDate_IDX` (`LogDate`) USING BTREE,
  KEY `dfs_gantry_log_upload202005_01` (`GantryId`,`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（2）使用c_parser抽取数据
[root@mysql undrop-for-innodb-develop]# ./c_parser -6Uf pages-dfs_gantry_log_upload202005.ibd/FIL_PAGE_INDEX/ -t dfs_gantry_log_upload202005.sql  > /tmp/dfs_gantry_log_upload202005 2> /tmp/dfs_gantry_log_upload202005.sql

（3）抽取后，导入数据
[root@mysql tmp]# mysql -uroot -p < /tmp/dfs_gantry_log_upload202005.sql

没有表结构：

（1）  分析数据文件ibdata1
[root@mysql undrop-for-innodb-develop]# ./stream_parser -f /data/data/ibdata1 
Opening file: /data/data/ibdata1
File information:

ID of device containing file:         2051
inode number:                       786435
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      999
group ID of owner:                     999
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         548864
time of last access:            1590480161 Tue May 26 16:02:41 2020
time of last modification:      1590374869 Mon May 25 10:47:49 2020
time of last status change:     1590461482 Tue May 26 10:51:22 2020
total size, in bytes:            281018368 (268.000 MiB)

Size to process:                 281018368 (268.000 MiB)
All workers finished in 0 sec

（2）抽取数据字典
[root@mysql undrop-for-innodb-develop]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/ -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql
[root@mysql undrop-for-innodb-develop]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/ -t dictionary/SYS_TABLES.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql
[root@mysql undrop-for-innodb-develop]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/ -t dictionary/SYS_TABLES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql


（3）创建数据字典并加载到数据库
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dictionary/SYS_TABLES.sql 
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dumps/default/SYS_TABLES.sql
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dictionary/SYS_COLUMNS .sql 
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dumps/default/SYS_COLUMNS .sql
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dictionary/SYS_INDEXES .sql 
[root@mysql undrop-for-innodb-develop]# mysql -uroot -p recovery < /data/undrop-for-innodb-develop/dumps/default/SYS_INDEXES .sql


（4）根据数据字典的信息，可获取建表语句，之后的步骤同有表结构恢复。