InnoDB data restore

When data can’t be extracted by “select” statement because of InnoDB table corruption try this:

  1. Shut down MySQL. Make a backup of /var/lib/mysql or wherever your DB is.
  2. Edit /etc/my.cnf
    and restart MySQL. This puts MySQL into read-only mode. Afterwards try to get data or at least structure.

  3. Set up new MySQL instance.
  4. Try Percona: Percona Data Recovery Tool for InnoDB
  5. unzip; cd percona......
  6. cp /var/lib/mysql/DB/table.ibd ./
  7. ./ --db=DBname –table=tablename_without_ibd > table_defs.h
    Look into table_defs.h, there should be something like
    { /* int(11) */
            name: "id",
            type: FT_INT,
            fixed_length: 4,
            has_limits: TRUE,
            limits: {
                    int_min_val: 1,
                    int_max_val: 10000
            can_be_null: FALSE

  8. If you didn’t get table structure in step 2 of this manual, fry to get fields name from tablename.frm using any text pager like “less”. The table_defs.h helps you.
  9. cp table_defs.h include/
  10. make clean && make
  11. ./page_parser -5 -f tablename.ibd (If you want more verbosity add “-V”).
    This will give you directory like pages-1327085316

  12. If there is only 1 directory inside pages-1327085316/FIL_PAGE_INDEX/ just move all *.page files into pages-1327085316 directory, othervise
    move pages-1327085316/FIL_PAGE_INDEX/*/* to pages-1327085316/.
    So you will get all your *.page files in one (pages-1327085316) directory.
    cd pages-1327085316

  13. ls -C1 | sort -n | xargs cat >> ../concat
    This will give you concatenated file concat in percona directory.

  14. ./constraints_parser -5 -f concat >> table.dat
    At the end you will get something likie
    LOAD DATA INFILE '/root/percona-data-recovery-tool-for-innodb-0.5/dumps/default/table‘ REPLACE INTO TABLE `table` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘table\t’ (field1, field2, field3);

  15. Look into table.dat. Delete obviously incorrect data.
  16. Create the new table in the new MySQL instance.
  17. move /root/tmp/percona-data-recovery-tool-for-innodb-0.5/dumps/default/table to /tmp
  18. log in to new mysql then run:
    LOAD DATA INFILE '/tmp/table.dat’ REPLACE INTO TABLE `table` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘table\t’ (field1, field2, field3);

  19. Cross your fingers. If you get an error - look into table.dat. If you’re succeed - musqldump freshly restored table, tie it with a blue ribbon and present it to the client like a gift. It was a joke.
» »

Оставить комментарий - Feel free to ask me in English

Он появится после того, как будет одобрен. - All comments are pre-moderated.

Внимание! Пожалуйста, не пишите в комментарии [url=... или [url]... - это будет считаться спамом. Если хотите дать ссылку - пишите просто адрес, до 2х на один коммент.
Attention! Please don't use "[url=..." or "[url]..." in comment's body or it will be deleted as spam
If you want to send me a link just type url, up to 2 urls in one comment.

Спасибо за отзыв! Thank you for a comment!