一.安裝undrop-for-innodb工具

點選(此處)摺疊或開啟

[root@mysqltest-213-2 tools]# unzip undrop-for-innodb-master.zip

安裝依賴包

點選(此處)摺疊或開啟

yum -y install gcc flex bison

編譯安裝

點選(此處)摺疊或開啟

cd undrop-for-innodb-master
make
[root@mysqltest-213-2 undrop-for-innodb-master]# make
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c stream_parser.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -pthread -lm stream_parser.o -o stream_parser
flex sql_parser.l
bison -o sql_parser.c sql_parser.y
sql_parser.y: conflicts: 6 shift/reduce
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c sql_parser.c
lex.yy.c:3084: warning: ‘yyunput’ defined but not used
lex.yy.c:3125: warning: ‘input’ defined but not used
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c c_parser.c
./include/ctype-latin1.c:359: warning: ‘my_mb_wc_latin1’ defined but not used
./include/ctype-latin1.c:372: warning: ‘my_wc_mb_latin1’ defined but not used
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c tables_dict.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c print_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c check_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -o innochecksum_changer innochecksum.c
Bye

二測試
未開啟binlog

點選(此處)摺疊或開啟

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+

2.1建立一個庫，然後建一張表

點選(此處)摺疊或開啟

mysql> show create database recovery;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| recovery | CREATE DATABASE `recovery` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+

建立恢復相關事例表

點選(此處)摺疊或開啟

mysql> show create table accountinfo \G
*************************** 1. row ***************************
Table: accountinfo
Create Table: CREATE TABLE `accountinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`userpwd` varchar(50) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.2 建立批次插入儲存過程
隨機函式

點選(此處)摺疊或開啟

DELIMITER $$
USE `recovery`$$
DROP FUNCTION IF EXISTS `rand_string`$$
CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS VARCHAR(255) CHARSET latin1
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END$$
DELIMITER ;

2.3 批次插入儲存過程

點選(此處)摺疊或開啟

DELIMITER $$
USE `recovery` $$
DROP PROCEDURE IF EXISTS `p_repeat` $$
CREATE DEFINER = `root` @`%` PROCEDURE `p_repeat` ()
BEGIN
DECLARE v INT ;
SET v = 0 ;
REPEAT
INSERT INTO accountinfo
VALUES
(
NULL,
rand_string (5),
rand_string (15),
NOW()
) ;
SET v = v + 1 ;
UNTIL v >= 1000
END REPEAT ;
END $$
DELIMITER ;

2.4呼叫儲存過程

點選(此處)摺疊或開啟

CALL p_repeat；
mysql> select count(*) from accountinfo;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+

2.5 刪除表

點選(此處)摺疊或開啟

mysql> use recovery;
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> drop table accountinfo;
Query OK, 0 rows affected (0.02 sec)

2.6 undorp for innodb掃描檔案

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 64768
inode number: 393238
protection: 100660 (regular file)
number of hard links: 1
user ID of owner: 498
group ID of owner: 500
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 155648
time of last access: 1510411206 Sat Nov 11 22:40:06 2017
time of last modification: 1510411254 Sat Nov 11 22:40:54 2017
time of last status change: 1510411254 Sat Nov 11 22:40:54 2017
total size, in bytes: 79691776 (76.000 MiB)
Size to process: 79691776 (76.000 MiB)
Worker(0): 52.61% done. 2017-11-11 22:41:49 ETA(in 00:00:01). Processing speed: 31.984 MiB/sec
Worker(0): 94.72% done. 2017-11-11 22:41:49 ETA(in 00:00:00). Processing speed: 32.000 MiB/sec
All workers finished in 2 sec
[root@mysqltest-213-2 undrop-for-innodb-master]# ls
check_data.c dictionary innochecksum_changer pages-ibdata1 recover_dictionary.sh sql_parser.y tables_dict.c vote_record.sql
check_data.o dumps lex.yy.c print_data.c sakila stream_parser tables_dict.o voterecord.sql
c_parser fetch_data.sh LICENSE print_data.o sql_parser.c stream_parser.c test.sh
c_parser.c include Makefile README.md sql_parser.l stream_parser.o t_userinfo.sql
c_parser.o innochecksum.c member.sql recover sql_parser.o sys_parser.c userinfo.sql

2.7 解析檔案

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep accountinfo
00000007A103 030000024B0AC4 SYS_TABLES "recovery/accountinfo" 69 4 1 0 80 "" 55
00000007A103 030000024B0AC4 SYS_TABLES "recovery/accountinfo" 69 4 1 0 80 "" 55
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/usr/local/tools/undrop-for-innodb-master/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

2.8 根據索引找到對應資料檔案72

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 69
00000007A103 030000024B096F SYS_INDEXES 69 72 "PRIMARY" 1 3 55 4294967295
00000007A103 030000024B096F SYS_INDEXES 69 72 "PRIMARY" 1 3 55 4294967295
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/usr/local/tools/undrop-for-innodb-master/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

2.9 建立表結構

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# cat accountinfo.sql
CREATE TABLE `accountinfo` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) DEFAULT NULL,
`userpwd` VARCHAR(50) DEFAULT NULL,
`createtime` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2.10 解析檔案

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t accountinfo.sql | head -5
-- Page id: 5, Format: COMPACT, Records list: Valid, Expected records: (301 301)
000000079BC0 B70000028B0110 accountinfo 151 "1TemU" "jHpNAo1LCC4mpQN" "2017-11-11 22:34:33.0"
000000079BC1 B80000016B0110 accountinfo 152 "iVzVM" "ZoQURpjcSEsbm7l" "2017-11-11 22:34:33.0"
000000079BC2 B90000016C0110 accountinfo 153 "cMcrs" "QDxBd9X9IYAPewJ" "2017-11-11 22:34:33.0"
000000079BC3 BA0000016D0110 accountinfo 154 "Xq4Ud" "c90s5T1ie6DLIav" "2017-11-11 22:34:33.0"

2.12建立恢復目錄

點選(此處)摺疊或開啟

./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t accountinfo.sql -o dumps/default/accountinfo.dump -l dumps/default/accountinfo.sql
[root@mysqltest-213-2 undrop-for-innodb-master]# ls dumps/default/accountinfo.*
dumps/default/accountinfo.dump dumps/default/accountinfo.sql
mkdir -pv dumps/default/

2.13 恢復資料

點選(此處)摺疊或開啟

[root@mysqltest-213-2 undrop-for-innodb-master]# mysql recovery;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.27 MySQL Community Server (GPL)
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> source accountinfo.sql
Query OK, 0 rows affected (0.04 sec)

恢復資料

點選(此處)摺疊或開啟

mysql> source dumps/default/accountinfo.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1000 rows affected (0.02 sec)
Records: 1000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from accountinfo;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

此文章參閱

mysql 無備份恢復drop資料-共享表空間

相關文章