????也许大家都难以理解,这么重要的数据为啥不备份(或者备份不可用)?而且还任性的drop table了。显然有备份是最好的，但是它们并不总是可用的。这种情况令人恐惧，但并非毫无希望。在许多情况下，可以恢复数据库或表中的几乎所有数据。恢复计划取决于InnoDB是否将所有数据保存在一个ibdata1中，还是每个表都有自己的表空间。本文将考虑innodb_file_per_table=OFF的情况。
yum install -y make gcc flex bison#cd /opt/[root@db13_19:55:25 /opt] #git clone https://github.com/twindb/undrop-for-innodb.gitCloning into ‘undrop-for-innodb‘...remote: Counting objects: 279, done.remote: Total 279 (delta 0), reused 0 (delta 0), pack-reused 279Receiving objects: 100% (279/279), 1.12 MiB | 731.00 KiB/s, done.Resolving deltas: 100% (77/77), done.#cd /opt/undrop-for-innodb#make........[root@db13_20:39:43 /opt/undrop-for-innodb] #lltotal 2920-rw-r--r-- 1 root root 6271 Aug 4 19:55 check_data.c-rw-r--r-- 1 root root 66128 Aug 4 20:39 check_data.o-rwxr-xr-x 1 root root 727801 Aug 4 20:39 c_parser-rw-r--r-- 1 root root 28587 Aug 4 19:55 c_parser.c-rw-r--r-- 1 root root 1030296 Aug 4 20:39 c_parser.odrwxr-xr-x 2 root root 92 Aug 4 19:55 dictionary-rw-r--r-- 1 root root 1978 Aug 4 19:55 fetch_data.shdrwxr-xr-x 2 root root 4096 Aug 4 19:55 include-rw-r--r-- 1 root root 8936 Aug 4 19:55 innochecksum.c-rwxr-xr-x 1 root root 36343 Aug 4 20:39 innochecksum_changer-rw-r--r-- 1 root root 154459 Aug 4 20:39 lex.yy.c-rw-r--r-- 1 root root 18047 Aug 4 19:55 LICENSE-rw-r--r-- 1 root root 1942 Aug 4 19:55 Makefile-rw-r--r-- 1 root root 16585 Aug 4 19:55 print_data.c-rw-r--r-- 1 root root 127176 Aug 4 20:39 print_data.o-rw-r--r-- 1 root root 3464 Aug 4 19:55 README.md-rwxr-xr-x 1 root root 1536 Aug 4 19:55 recover_dictionary.shdrwxr-xr-x 2 root root 4096 Aug 4 19:55 sakila-rw-r--r-- 1 root root 103506 Aug 4 20:39 sql_parser.c-rw-r--r-- 1 root root 8462 Aug 4 19:55 sql_parser.l-rw-r--r-- 1 root root 296840 Aug 4 20:39 sql_parser.o-rw-r--r-- 1 root root 26355 Aug 4 19:55 sql_parser.y-rwxr-xr-x 1 root root 61725 Aug 4 20:39 stream_parser-rw-r--r-- 1 root root 23103 Aug 4 19:55 stream_parser.c-rw-r--r-- 1 root root 109304 Aug 4 20:39 stream_parser.o-rw-r--r-- 1 root root 14764 Aug 4 19:55 sys_parser.c-rw-r--r-- 1 root root 2182 Aug 4 19:55 tables_dict.c-rw-r--r-- 1 root root 40264 Aug 4 20:39 tables_dict.o-rwxr-xr-x 1 root root 6629 Aug 4 19:55 test.shdrwxr-xr-x 3 root root 42 Aug 4 19:55 vagrant[root@db13_20:39:57 /opt/undrop-for-innodb] #gcc `/usr/local/mysql57/bin/mysql_config --cflags` `/usr/local/mysql57/bin/mysql_config --libs` -o sys_parser sys_parser.c 注:mysql_basedir: /usr/local/mysql57/bin/ [root@db212_20:58:44 /data/57mysql] #mkdir mysql3507/{data,logs,tmp} -p[root@db212_20:59:50 /data/57mysql] #chown -R mysql:mysql mysql3507复制my3507.cnf到mysql3507下#ln -s /data/57mysql/mysql3507/ /3507[root@db212_21:05:37 /3507] #/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf --initialize[root@db212_21:07:34 /3507] #/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf&[1] 11669//取初始密码并登录:defaultmysqlpwd=`grep ‘A temporary password‘ /3507/data/error.log | awk -F"root@localhost: " ‘{ print $2}‘ `echo $defaultmysqlpwd/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot -p${defaultmysqlpwd}mysql: [Warning] Using a password on the command line interface can (unknown)@localhost [(none)]>alter user user() identified by ‘*****‘;Query OK, 0 rows affected (0.01 sec)root@localhost [(none)]>CREATE DATABASE wenyz;Query OK, 1 row affected (0.00 sec)root@localhost [(none)]>use wenyz;Database changed//创建表root@localhost [wenyz]>CREATE TABLE `t2` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `ti` varchar(100) NOT NULL, -> `date` date DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=4079859 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.01 sec)//造数据root@localhost [wenyz]>insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;root@localhost [wenyz]>insert into t2(ti,date) select ti,date from t2;Query OK, 448 rows affected (0.00 sec)Records: 448 Duplicates: 0 Warnings: 0//查看数据行数和checksum值root@localhost [wenyz]>select count(*) from t2;+----------+| count(*) |+----------+| 896 |+----------+1 row in set (0.00 sec)root@localhost [wenyz]>checksum table t2;+----------+------------+| Table | Checksum |+----------+------------+| wenyz.t2 | 3458542072 |+----------+------------+1 row in set (0.00 sec)//DROP 表root@localhost [wenyz]>drop table t2;Query OK, 0 rows affected (0.01 sec)#cd /opt/undrop-for-innodb/[root@db212_21:25:52 /opt/undrop-for-innodb] #./stream_parser -f /3507/data/ibdata1Opening file: /3507/data/ibdata1File information:....Size to process: 104857600 (100.000 MiB)Size to process: 104857600 (100.000 MiB)time of last access: 1533388916 Sat Aug 4 21:21:56 2018time of last modification: 1533388917 Sat Aug 4 21:21:57 2018time of last status change: 1533388917 Sat Aug 4 21:21:57 2018total size, in bytes: 104857600 (100.000 MiB)Size to process: 104857600 (100.000 MiB)All workers finished in 0 sec这里引入官方的一段描述:
InnoDB stores all data in B+tree indexes. A table has one clustered index PRIMARY, all fields are stored there. Thus, if the table has secondary keys, each key has an index. Each index is identified by index_id.
Consequently, if we want to recover a table, we have to find all pages that belong to a particular index_id.
接下来我们先来看看怎么找到table id和INDEX_ID(page文件编号)的，理解原理后再用程序跑一次，导入到临时数据库中
[root@db212_21:25:55 /opt/undrop-for-innodb] #./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep ‘wenyz/t2‘ 000000000521 3B00000149047E SYS_TABLES "wenyz/t2" 40 3 33 0 64 "" 0SET FOREIGN_KEY_CHECKS=0;LOAD DATA LOCAL INFILE ‘000000000521 3B00000149047E SYS_TABLES "wenyz/t2" 40 3 33 0 64 "" 0/opt/undrop-for-innodb/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`);-- STATUS {"records_expected": 50, "records_dumped": 2, "records_lost": true} STATUS END[root@db212_21:29:54 /opt/undrop-for-innodb] #./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep ‘40‘000000000521 3B0000014903A2 SYS_INDEXES 40 41 "PRIMARY" 1 3 0 4294967295SET FOREIGN_KEY_CHECKS=0;000000000521 3B0000014903A2 SYS_INDEXES 40 41 "PRIMARY" 1 3 0 4294967295LOAD DATA LOCAL INFILE ‘/opt/undrop-for-innodb/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`);-- STATUS {"records_expected": 60, "records_dumped": 8, "records_lost": true} STATUS END[root@db212_21:57:04 /opt/undrop-for-innodb] //将文件中三处mysql替换成/usr/local/mysql57/bin/mysql --login-path=p3507#vi recover_dictionary.sh 43 /usr/local/mysql57/bin/mysql --login-path=p3507 -e "CREATE DATABASE IF NOT EXISTS test"... 50 /usr/local/mysql57/bin/mysql --login-path=p3507 test < dictionary/$t.sql... 58 /usr/local/mysql57/bin/mysql --login-path=p3507 test < dumps/default/$t.sql [root@db212_22:13:57 /opt/undrop-for-innodb] #./recover_dictionary.sh Generating dictionary tables dumps... OKCreating test database ... OKCreating dictionary tables in database test:SYS_TABLES ... OKSYS_COLUMNS ... OKSYS_INDEXES ... OKSYS_FIELDS ... OKAll OKLoading dictionary tables data:SYS_TABLES ... 52 recs OKSYS_COLUMNS ... 284 recs OKSYS_INDEXES ... 68 recs OKSYS_FIELDS ... 90 recs OKAll OK[root@db212_22:14:02 /opt/undrop-for-innodb] mysql --login-path=p3507root@localhost [(none)]>use test;Database changedroot@localhost [test]>select * from SYS_TABLES where name like ‘wenyz/t2%‘;+----------+----+--------+------+--------+---------+--------------+-------+| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |+----------+----+--------+------+--------+---------+--------------+-------+| wenyz/t2 | 40 | 3 | 33 | 0 | 64 | | 0 |+----------+----+--------+------+--------+---------+--------------+-------+1 row in set (0.00 sec)root@localhost [test]>select * from SYS_INDEXES where table_id=40;+----------+----+---------+----------+------+-------+------------+| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |+----------+----+---------+----------+------+-------+------------+| 40 | 41 | PRIMARY | 1 | 3 | 0 | 4294967295 |+----------+----+---------+----------+------+-------+------------+//注意记录上表中id,此ID为INDEX_ID(page文件编号)等会表数据恢复要使用1 row in set (0.00 sec)root@localhost [test]>./sys_parser -h 127.0.0.1 -u root -p xxxx -d test wenyz/t2./sys_parser: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory#ln -s /opt/mysql-5.7.23-linux-glibc2.12-x86_64/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20 [root@db212_22:23:25 /opt/undrop-for-innodb] #./sys_parser -h 127.0.0.1 -u root -p zstzst -d test wenyz/t2CREATE TABLE `t2`( `id` INT UNSIGNED NOT NULL, `ti` VARCHAR(100) CHARACTER SET ‘utf8‘ COLLATE ‘utf8_general_ci‘ NOT NULL, `date` DATE, PRIMARY KEY (`id`)) ENGINE=InnoDB;[root@db212_22:23:30 /opt/undrop-for-innodb] #cat /tmp/t2.sqlCREATE TABLE `t2`( `id` INT UNSIGNED NOT NULL, `ti` VARCHAR(100) CHARACTER SET ‘utf8‘ COLLATE ‘utf8_general_ci‘ NOT NULL, `date` DATE, PRIMARY KEY (`id`)) ENGINE=InnoDB;[root@db212_22:26:20 /opt/undrop-for-innodb] [root@db212_22:33:44 /opt/undrop-for-innodb] #./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000041.page -t /tmp/t2.sql |head -5-- Page id: 459, Format: COMPACT, Records list: Valid, Expected records: (180 180)000000000507 A7000001210110 t2 4079859 "d553635af1a3b" "2018-08-04"000000000508 A8000001230110 t2 4079860 "44d64b99fc30d1b" "2018-08-04"00000000050D AB0000012A0110 t2 4079861 "6008e73d9437991" "2018-08-04"00000000050E AC0000012C0110 t2 4079862 "a1e5f194540dd20" "2018-08-04"[root@db212_22:33:44 /opt/undrop-for-innodb] //注意:此处几个文件名很巧妙,程序把导出的两个数据文件的文件名关系是写死的,以下dumps/default/t2中的t2是需要和表名一致,在t2_load.sql中会引用此文件路经../c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000041.page -t /tmp/t2.sql > dumps/default/t2 2> dumps/default/t2_load.sql #cd dumps/default/[root@db212_22:41:01 /opt/undrop-for-innodb/dumps/default] #lltotal 132-rw-r--r-- 1 root root 21232 Aug 4 22:14 SYS_COLUMNS...-rw-r--r-- 1 root root 62923 Aug 4 22:40 t2-rw-r--r-- 1 root root 308 Aug 4 22:40 t2_load.sql[root@db212_22:41:04 /opt/undrop-for-innodb/dumps/default] #cat t2_load.sql SET FOREIGN_KEY_CHECKS=0;LOAD DATA LOCAL INFILE ‘/opt/undrop-for-innodb/dumps/default/t2‘ REPLACE INTO TABLE `t2` FIELDS TERMINATED BY ‘\t‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY ‘t2\t‘ (`id`, `ti`, `date`);-- STATUS {"records_expected": 896, "records_dumped": 896, "records_lost": false} STATUS ENDroot@localhost [test]>source /tmp/t2.sqlQuery OK, 0 rows affected (0.02 sec)root@localhost [test]>source /opt/undrop-for-innodb/dumps/default/t2_load.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 896 rows affected (0.01 sec)Records: 896 Deleted: 0 Skipped: 0 Warnings: 0root@localhost [test]>select count(*) from t2;+----------+| count(*) |+----------+| 896 |+----------+1 row in set (0.00 sec)root@localhost [test]>checksum table t2;+---------+------------+| Table | Checksum |+---------+------------+| test.t2 | 3458542072 |+---------+------------+1 row in set (0.00 sec)root@localhost [test]>//drop前数据信息对比:root@localhost [wenyz]>select count(*) from t2;+----------+| count(*) |+----------+| 896 |+----------+1 row in set (0.00 sec)root@localhost [wenyz]>checksum table t2;+----------+------------+| Table | Checksum |+----------+------------+| wenyz.t2 | 3458542072 |+----------+------------+1 row in set (0.00 sec)问题1.
innodb(非独立表空间)情况,drop表后,用工具读ibdata1对应数据页文件,如果是这个表大于350行左右的数据,页文件是存在的,但小于350行左右,页文件就不存在(drop前页文件是存在的).这是个什么原因呢
脚本输出信息:
select count(*) from t2640Table Checksumwenyz.t2 1273189789...-rw-r--r-- 1 root root 16384 Aug 4 23:09 0000000000000040.page-rw-r--r-- 1 root root 98304 Aug 4 23:09 0000000000000041.page /被drop表空间文件-rw-r--r-- 1 root root 16384 Aug 4 23:09 18446744069414584320.pagecount(*)320(行)Table Checksumwenyz.t2 3018070873....-rw-r--r-- 1 root root 16384 Aug 4 23:12 0000000000000036.page-rw-r--r-- 1 root root 16384 Aug 4 23:12 0000000000000037.page-rw-r--r-- 1 root root 16384 Aug 4 23:12 0000000000000038.page-rw-r--r-- 1 root root 32768 Aug 4 23:12 0000000000000039.page-rw-r--r-- 1 root root 16384 Aug 4 23:12 0000000000000040.page \\这里缺0000000000000041.page-rw-r--r-- 1 root root 16384 Aug 4 23:12 18446744069414584320.page把drop命令在脚本里注释后(还是320行)
以下为完整输出信息:#/tmp/init3507.shBrI?Zu>o=1uNmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.23-logCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.root@localhost [(none)]>alter user user() identified by ‘zstzst‘;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>exitByemysql: [Warning] Using a password on the command line interface can be insecure.count(*) ---------------------------------------------------------------------------------320行数据320Table Checksumwenyz.t2 2368041617Opening file: /3507/data/ibdata1File information:Opening file: /3507/data/ibdata1ID of device containing file: 64768File information:inode number: 2149120802protection: 100640 (regular file)number of hard links: 1user ID of owner: 1000Opening file: /3507/data/ibdata1ID of device containing file: 64768group ID of owner: 1000File information:inode number: 2149120802ID of device containing file: 64768inode number: 2149120802protection: 100640 (regular file)number of hard links: 1protection: 100640 user ID of owner: 1000(regular file)number of hard links: 1Opening file: /3507/data/ibdata1Opening file: /3507/data/ibdata1File information:File information:device ID (if special file): 0ID of device containing file: 64768user ID of owner: 1000group ID of owner: 1000group ID of owner: 1000ID of device containing file: 64768device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 204800inode number: 2149120802device ID (if special file): 0inode number: 2149120802Opening file: /3507/data/ibdata1Opening file: /3507/data/ibdata1File information:blocksize for filesystem I/O: 4096Opening file: /3507/data/ibdata1protection: 100640 File information:number of blocks allocated: 204800ID of device containing file: 64768blocksize for filesystem I/O: 4096protection: 100640 number of blocks allocated: 204800File information:time of last access: 1533395776 Sat Aug 4 23:16:16 2018ID of device containing file: 64768inode number: 2149120802time of last modification: 1533395777 Sat Aug 4 23:16:17 2018(regular file)inode number: 2149120802time of last access: 1533395776 Sat Aug 4 23:16:16 2018protection: 100640 ID of device containing file: 64768time of last modification: 1533395777 Sat Aug 4 23:16:17 2018time of last status change: 1533395777 Sat Aug 4 23:16:17 2018(regular file)protection: 100640 total size, in bytes: 104857600 (100.000 MiB)number of hard links: 1user ID of owner: 1000group ID of owner: 1000device ID (if special file): 0blocksize for filesystem I/O: 4096Size to process: 104857600 (100.000 MiB)time of last status change: 1533395777 Sat Aug 4 23:16:17 2018time of last access: 1533395776 Sat Aug 4 23:16:16 2018total size, in bytes: 104857600 (100.000 MiB)number of hard links: 1Size to process: 104857600 (100.000 MiB)(regular file)inode number: 2149120802protection: 100640 (regular file)number of hard links: 1number of blocks allocated: 204800time of last modification: 1533395777 Sat Aug 4 23:16:17 2018user ID of owner: 1000group ID of owner: 1000time of last status change: 1533395777 Sat Aug 4 23:16:17 2018number of hard links: 1total size, in bytes: 104857600 (100.000 MiB)time of last access: 1533395776 Sat Aug 4 23:16:16 2018(regular file)Size to process: 104857600 (100.000 MiB)user ID of owner: 1000device ID (if special file): 0blocksize for filesystem I/O: 4096user ID of owner: 1000group ID of owner: 1000device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 204800time of last modification: 1533395777 Sat Aug 4 23:16:17 2018number of hard links: 1user ID of owner: 1000group ID of owner: 1000device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 204800group ID of owner: 1000number of blocks allocated: 204800device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 204800time of last access: 1533395776 Sat Aug 4 23:16:16 2018time of last status change: 1533395777 Sat Aug 4 23:16:17 2018time of last modification: 1533395777 Sat Aug 4 23:16:17 2018total size, in bytes: 104857600 (100.000 MiB)time of last status change: 1533395777 Sat Aug 4 23:16:17 2018time of last access: 1533395776 Sat Aug 4 23:16:16 2018total size, in bytes: 104857600 (100.000 MiB)Size to process: 104857600 (100.000 MiB)time of last modification: 1533395777 Sat Aug 4 23:16:17 2018time of last access: 1533395776 Sat Aug 4 23:16:16 2018time of last access: 1533395776 Sat Aug 4 23:16:16 2018Size to process: 104857600 (100.000 MiB)time of last modification: 1533395777 Sat Aug 4 23:16:17 2018time of last status change: 1533395777 Sat Aug 4 23:16:17 2018time of last status change: 1533395777 Sat Aug 4 23:16:17 2018total size, in bytes: 104857600 (100.000 MiB)time of last modification: 1533395777 Sat Aug 4 23:16:17 2018total size, in bytes: 104857600 (100.000 MiB)Size to process: 104857600 (100.000 MiB)time of last status change: 1533395777 Sat Aug 4 23:16:17 2018Size to process: 104857600 (100.000 MiB)total size, in bytes: 104857600 (100.000 MiB)Size to process: 104857600 (100.000 MiB)All workers finished in 0 sectotal 3472-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000001.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000002.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000003.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000004.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000005.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000011.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000012.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000013.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000014.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000015.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000016.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000017.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000018.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000019.page-rw-r--r-- 1 root root 1605632 Aug 4 23:16 0000000000000020.page-rw-r--r-- 1 root root 163840 Aug 4 23:16 0000000000000021.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000022.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000023.page-rw-r--r-- 1 root root 98304 Aug 4 23:16 0000000000000024.page-rw-r--r-- 1 root root 114688 Aug 4 23:16 0000000000000025.page-rw-r--r-- 1 root root 98304 Aug 4 23:16 0000000000000026.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000027.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000028.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000029.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000030.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000031.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000032.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000033.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000034.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000035.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000036.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000037.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000038.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000039.page-rw-r--r-- 1 root root 16384 Aug 4 23:16 0000000000000040.page-rw-r--r-- 1 root root 32768 Aug 4 23:16 0000000000000041.page //320行时不drop是有此表空间文件的-rw-r--r-- 1 root root 16384 Aug 4 23:16 18446744069414584320.page[root@db211_23:16:21 /opt/undrop-for-innodb] #pkill mysqldrm -rf /3507/data/* /3507/logs/*/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf --initialize/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf&sleep 1defaultmysqlpwd=`grep ‘A temporary password‘ /3507/data/error.log | awk -F"root@localhost: " ‘{ print $2}‘ `echo $defaultmysqlpwd/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot -p${defaultmysqlpwd}/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot -pzstzst </tmp/create_t2.sql#/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot -pzstzstrm -rf /opt/undrop-for-innodb/pages-ibdata1#ls -lcd /opt/undrop-for-innodb/sleep 5rm -rf /opt/undrop-for-innodb/pages-ibdata1cd /opt/undrop-for-innodb//opt/undrop-for-innodb/stream_parser -f /3507/data/ibdata1ls -l /opt/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/tmp/create_t2.sql
CREATE DATABASE wenyz;use wenyz;CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ti` varchar(100) NOT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4079859 DEFAULT CHARSET=utf8;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) values(substring(MD5(RAND()),floor(RAND()*26)+1,15),now()) ;insert into t2(ti,date) select ti,date from t2;insert into t2(ti,date) select ti,date from t2;insert into t2(ti,date) select ti,date from t2;insert into t2(ti,date) select ti,date from t2;insert into t2(ti,date) select ti,date from t2;insert into t2(ti,date) select ti,date from t2;select count(*) from t2;checksum table t2;drop table t2;
