バックアップなしでdropされたテーブルをリカバリする方法(undrop-for-innodbを利用)

17032 ワード

紹介:


このような重要なデータがバックアップされていない(またはバックアップが使用できない)のは理解しにくいかもしれません.しかもわがままなdrop tableです.バックアップが最良であることは明らかですが、必ずしも使用可能ではありません.このような状況は恐ろしいが、希望がないわけではない.多くの場合、データベースまたはテーブルのほとんどのデータをリカバリできます.リカバリ計画は、InnoDBがすべてのデータをibdata 1に保存するか、各テーブルに独自のテーブルスペースがあるかによって異なります.ここではinnodb_を考慮します.file_per_table=OFFの場合.

dropテーブルリカバリの他の方法:

  • masterによる同期
  • 擬似master+Binlog+同期
  • binlogを利用してrealy-logに変更して一時slaveで
  • を更新する

    この文書の重要な部分:

  • 表構造回復
  • テーブルデータ復旧
  • 未解決の問題
  • 環境:

  • 時間:#Sat Aug 4 19:37:24 CST 2018
  • CentOS Linux release 7.2.1511 (Core)
  • MySQL:5.7.23-log
  • 使用ツール:undrop-for-innodb
  • 1.ツールのインストール

  • 依存パッケージインストール
  • yum install -y make gcc flex bison
  • ダウンロードキット(github)
  • #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.
  • make
  • #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]  
  • テーブル構造を復元するためのツールsys_を追加parser(公式ドキュメント未使用):
  • #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.
  • 次にtable idとINDEXの見つけ方を見てみましょうID(pageファイル番号)の場合、原理を理解してからプログラムで1回走り、一時データベースに
  • にインポートする.
  • 手動でtable idを検索観察した結果、wenyz/t 2後の40、すなわちtable 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
    
  • table idでpageファイル番号を表示して、以下の結果の40後の41を観察します.すなわちINDEX_です.ID(pageファイル番号)
  • [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
    
  • プログラムで辞書情報を復元する:mysqlログイン情報を編集する.
  • [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 
  • 実行/recover_dictionary.sh,リカバリ辞書情報
  • [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ログイン情報辞書情報を表示する:
  • 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]>
  • 表回復t 2表構造[Top]
  • ./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]  
  • 復元テーブル構造を/tmp/t 2に格納.sql
  • #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]

  • データが存在するかどうかを確認する
  • 次のコマンドで使用する00000004.1億円Pageは前述のINDEX_ID(pageファイル番号)/tmp/t 2.sqlは上で復元するテーブル構造である.
    [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]  
  • 利用c_parserは1億4千万41億ドルPage実行可能sql
  • にエクスポート
    // : , 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
  • は、テーブル構造t 2を.sqlおよびテーブルデータt 2およびt 2_load.sqlインポートデータベース
  • 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]  
    #
    
  • データ復旧を迅速にテストするために使用するスクリプトinit 3507.sh/tmp/init3507.sh
  • 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;
    

    7.参考にした資料[Top]

  • 公式ドキュメント:twindb:https://github.com/twindb/undrop-for-innodbhttps://twindb.com/recover-after-drop-table-innodb_file_per_table-is-off/
  • 葉先生ブログ:B+ツリー
  • 李真旭(Roger)ブログ:
  • yunqishequ 1のブログ
  • 転載先:https://www.cnblogs.com/2woods/p/9420414.html