mysql 无备份恢复drop数据-共享表空间
一.安装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
随机函数
点击(此处)折叠或打开
-
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 ;
点击(此处)折叠或打开
-
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)
点击(此处)折叠或打开
- [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
点击(此处)折叠或打开
- [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;
点击(此处)折叠或打开
- [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"
点击(此处)折叠或打开
- ./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/
点击(此处)折叠或打开
- [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)
- Copyright (c) 2000, 2015, 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> 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)
此文章参阅https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-off/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24486203/viewspace-2147147/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
mysql无备份恢复-独立表空间
请登录后发表评论
登录
全部评论
