0

My mysql backup is running everyday at 4 am & accidently my database got corrupted at 7am. Now how to restore data updated between time period of 4am & 7am

Huzefa
  • 65
  • 4
  • 13

1 Answers1

2
  • Copy your your original datadir (by default /var/lib/mysql on Linux) to safe place.
  • Restore database from the last backup
  • Download and compile recovery toolkit

If innodb_file_per_table is ON

  • Split each ibd file with stream_parser

  • Take *.page file with minimal id and fetch records with c_parser. Save output to a file.

    ./c_parser -f pages-mytable.ibd/FIL_PAGE_INDEX/<minimal id>.page \
  -t path/to/create_statements/mytable.sql \
  > dumps/default/mytable 
  2> dumps/default/mytable.sql

If innodb_file_per_table is OFF

  • Recover InnoDB Dictionary

  • Get index_id for mytable from the dictionary and run c_parser as in the example above.

    • Change REPLACE in dumps/default/mytable.sql to IGNORE

    • Load the dump

      mysql db < dumps/default/mytable.sql

akuzminsky
  • 738
  • 4
  • 9
  • @Kuzminsky, Can u please help me elaborate, what is and /path/to/create_statements/. Should I have to create the file with create_statements for all the table. – Huzefa Jul 12 '14 at 18:41
  • path/to/create_statements/mytable.sql is a file with CREATE TABLE statement. You need to know index_id of your table. The table name => index_id correspondence is stored in the dictionary. In case of innodb_file_per_table=ON index_id of your table is just the minimal index_id out of all available. Check my posts - there is a comprehensive description how InnoDB stores data on disk – akuzminsky Jul 12 '14 at 18:46