Recover MyISAM from an old state

  1. Backup and Data Recovery for MySQL
  2. Recover MyISAM from an old state

I just imported an old version of my database on the “Import” tab of phpmyadmin.

Is it possible to recover?

The tables are MyISAM.

  • You must to post comments

I assume the old database is a mysqldump output that contains for each table:

  • DROP TABLE statement
  • CREATE TABLE statement
  • INSERT INTO statements that populate the table with values

If the table is MyISAM the “DROP TABLE” statement will delete respective MYD, MYI and frm file. Disk space used by these table becomes free and the operating system is free to reuse it. Particularly, MySQL can use it when following INSERT statement are executed.

In general, data recovery from this scenario is extremely hard and unlikely to succeed.

Depending on file system you may try to use “undelete tools” to restore the original MYD file. Some of examples:

Few bits of information can be found with a simple `strings` tool. For example, you can scan a partition to find emails of the users to contact them.

# strings /dev/mapper/VolGroup-lv_root | grep -E "[a-zA-Z0-9]@[a-zA-Z0-9]+\."

The last hope is to write a C program that would scan the partition and tried to match records by their allowed values.

For example, for actor table from sakila database:

CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)

we know that an integer from range 1..200 is followed by a string with length between 1 and 45, then the same kind of string as the last name. After that a time stamp comes. The timestamp can be some integer between 1356998400 (‘2013-01-01 00:00:00’) and 1451606400 (‘2016-01-01 00:00:00’).

    • Guest
    • 4 years ago
    I also assume there are no backups and no binary logs
  • You must to post comments
Showing 1 result
Your Answer

Please first to submit.