12

I am using LINUX and I have mistakenly deleted ibdata1 file in mysql folder.

I restarted the 'mysqld' service and Now when I am trying to read data from the databases it is not able to find out older tables.

Am I in a big trouble? Please throw some light on this if any idea.

Thanks for your time.

Thanks and Regards, SachinJadhav.

SachinJadhav
  • 291
  • 4
  • 11
  • 1
    +1 out of sympathy. You're screwed. Your last chance would be to attempt to undelete the files using eg. debugfs. But it's quite likely you've already written enough to the partition to ruin any chance of recovering the data intact. – bobince Feb 08 '10 at 16:03

3 Answers3

6

You are in big trouble...

By default ibdata1 contains all the table's data (and therefore is lost). Even if you configure it to use a separate file per table, all internal MySQL's data is stored in that file, and as far as I know, there is no way to restore it.

Really hope you have a backup....

Johnco
  • 4,037
  • 4
  • 34
  • 43
1

Unless you defined innodb_file_per_table, this is a single tablespace that along with the transaction logs (stored in ib_logfile*) keep your table data.

Deleting it will effictively delete all data stored in the InnoDB tables.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Is there any way I can restore the this file? I am deadly looking for the data(if it is there in that file) Regards, Sachin Jadhav – SachinJadhav Feb 09 '10 at 12:37
  • @CachinJadhav did find a way to do so at the end? my only hope from looking at this situation, is to be able to rebuild the data from the log files (depending on what type of info is recorded there). – eglasius Sep 03 '10 at 21:49
0

if innodb_file_per_table is enabled then the tables can be restored via this and this.

Blatent copy of https://dba.stackexchange.com/a/57157/189538

MyISAM

For a MyISAM table mydb.mytable, you should have three files

  • \bin\mysql\mysql5.6.12\data\mydb\mytable.frm
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

InnoDB Architecture

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

  • You have the folder \bin\mysql\mysql5.6.12\data\mydb
  • Inside that folder, you have
    • mytable.frm
    • mytable.ibd

You need the .frm. If you look at my post https://dba.stackexchange.com/questions/44314/how-can-extract-the-table-schema-from-just-the-frm-file/44316#44316, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

  • Move mytable.ibd to \bin\mysql\mysql5.6.12\data
  • Run the SQL to create the InnoDB table
  • Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
  • Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
  • Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly

Blatant copy of https://dba.stackexchange.com/a/93511/189538

Not as part of MySQL, but tools like Percona Xtrabackup makes the process a bit faster for exporting/backing up the tables, allowing things like using regular expressions or lists for filtering:

For importing a list of tables, you can use some automation oneliners like this one found on Bill Karwin tools:

mysqldump --no-data $schema > schema-ddl.sql
mysql -N -B <<'EOF' > discard-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF
mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF

Basically, you can use the information_schema.tables table for listing the tables you want using "dynamic sql". For example, change $schema above for the desired database name.

Brenda.ZMPOV
  • 61
  • 1
  • 7