バックアップなしでdropされたテーブルをリカバリする方法(undrop-for-innodbを利用)
17032 ワード
紹介:
このような重要なデータがバックアップされていない(またはバックアップが使用できない)のは理解しにくいかもしれません.しかもわがままなdrop tableです.バックアップが最良であることは明らかですが、必ずしも使用可能ではありません.このような状況は恐ろしいが、希望がないわけではない.多くの場合、データベースまたはテーブルのほとんどのデータをリカバリできます.リカバリ計画は、InnoDBがすべてのデータをibdata 1に保存するか、各テーブルに独自のテーブルスペースがあるかによって異なります.ここではinnodb_を考慮します.file_per_table=OFFの場合.
dropテーブルリカバリの他の方法:
この文書の重要な部分:
環境:
1.ツールのインストール
yum install -y make gcc flex bison
#cd /opt/
[root@db13_19:55:25 /opt]
#git clone https://github.com/twindb/undrop-for-innodb.git
Cloning into 'undrop-for-innodb'...
Resolving deltas: 100% (77/77), done.
#cd /opt/undrop-for-innodb
#make
....
....
[root@db13_20:39:43 /opt/undrop-for-innodb]
#ll
total 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.o
drwxr-xr-x 2 root root 92 Aug 4 19:55 dictionary
-rw-r--r-- 1 root root 1978 Aug 4 19:55 fetch_data.sh
drwxr-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.sh
drwxr-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.sh
drwxr-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/
2.テーブルデータ生成とdropテーブル
[root@db212_20:58:44 /data/57mysql]
#mkdir mysql3507/{data,logs,tmp} -p
#chown -R mysql:mysql mysql3507
[root@db212_21:07:34 /3507]
\\ my3507.cnf mysql3507
#ln -s /data/57mysql/mysql3507/ /3507
#/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf --initialize-insecure
#/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf&
[1] 11669
// :
/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot
(unknown)@localhost [(none)]>alter user user() identified by '*****';
root@localhost [(none)]>CREATE DATABASE wenyz;
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()) ;
Records: 448 Duplicates: 0 Warnings: 0
// checksum
root@localhost [wenyz]>select count(*) from t2;
+----------+
| count(*) |
+----------+
| 896 |
+----------+
root@localhost [wenyz]>checksum table t2;
+----------+------------+
| Table | Checksum |
+----------+------------+
| wenyz.t2 | 3458542072 |
+----------+------------+
//DROP
root@localhost [wenyz]>drop table t2;
Query OK, 0 rows affected (0.01 sec)
3.stream_を利用するparser ibdata 1ファイルをpageファイルにエクスポート
#cd /opt/undrop-for-innodb/
[root@db212_21:25:52 /opt/undrop-for-innodb]
#./stream_parser -f /3507/data/ibdata1
Opening file: /3507/data/ibdata1
File 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 2018
time of last modification: 1533388917 Sat Aug 4 21:21:57 2018
time of last status change: 1533388917 Sat Aug 4 21:21:57 2018
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
All workers finished in 0 sec
4.テーブル構造の復元[Top]
ここでは、公式の説明を紹介します.
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.
[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 "" 0
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '000000000521 3B00000149047E SYS_TABLES "wenyz/t2" 40 3 33 0 64 "" 0
[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 4294967295
SET FOREIGN_KEY_CHECKS=0;
000000000521 3B0000014903A2 SYS_INDEXES 40 41 "PRIMARY" 1 3 0 4294967295
[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... 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 ... 52 recs OK
SYS_COLUMNS ... 284 recs OK
SYS_INDEXES ... 68 recs OK
SYS_FIELDS ... 90 recs OK
All OK
[root@db212_22:14:02 /opt/undrop-for-innodb]
mysql --login-path=p3507
root@localhost [(none)]>use test;
Database changed
root@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/t2
CREATE 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.sql
CREATE 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]
5.テーブルデータ復旧[Top]
[root@db212_22:33:44 /opt/undrop-for-innodb]
#./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000041.page -t /tmp/t2.sql |head -2
-- 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"
[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]
#ll
total 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 END
root@localhost [test]>source /tmp/t2.sql
Query OK, 0 rows affected (0.02 sec)
root@localhost [test]>source /opt/undrop-for-innodb/dumps/default/t2_load.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 896 rows affected (0.01 sec)
Records: 896 Deleted: 0 Skipped: 0 Warnings: 0
root@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)
6.未解決の問題[Top]
問題1.innodb(非独立表領域)の場合、drop表の後、ibdata 1対応データページファイルをツールで読む、この表が350行程度より大きいデータであればページファイルは存在するが、350行程度未満であればページファイルは存在しない(drop前ページファイルは存在する).これは、スクリプトが情報を出力する理由です.
以下は簡略情報で、完全出力情報はダウンロードテキストをクリックしてください(txtをアップロードできないため、ファイル名の後ろにshを付けて、ダウンロード後に.shを削除してください):320行、drop記録がありません:320行、drop 640行、drop
select count(*) from t2
640
Table Checksum
wenyz.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.page
count(*)320( )
Table Checksum
wenyz.t2 3018070873
....
-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 \\ 41.page
-rw-r--r-- 1 root root 16384 Aug 4 23:12 18446744069414584320.page
dropコマンドをスクリプトに注釈した後(320行)
, :
#/tmp/init3507.sh
BrI?Zu>o=1uN
mysql: [Warning] Using a password on the command line interface can be insecure.
root@localhost [(none)]>alter user user() identified by 'xxxxx';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>exit
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
count(*) ---------------------------------------------------------------------------------320
320
Table Checksum
wenyz.t2 2368041617
...
-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 mysqld
rm -rf /3507/data/* /3507/logs/*
/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf --initialize-insecure
/usr/local/mysql57/bin/mysqld --defaults-file=/3507/my3507.cnf&
sleep 2
/usr/local/mysql57/bin/mysql -S /tmp/mysql3507.sock -uroot
/tmp/create_t2.sql(sqlファイルをアップロードできないのでshに変更するしかありません)
alter user user() identified by 'xxxx';
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;
select count(*) from t2;
checksum table t2;
drop table t2;