Mysql – Recover MySQL ibdata1 file

innodbMySQL

I had mysql running on my VPS. Today it crashed for no good reason!

I tried service mysql start, it gave me start: Job failed to start.

First thing I did was to backup /var/lib/mysql which contained folders corresponding to my databases and these other files:

debian-5.5.flag
ibdata1
ib_logfile0
ib_logfile1
mysql_upgrade_info

I removed /etc/mysql and did sudo apt-get purge mysql-server mysql-common and then installed everything again, the result was the same: start: Job failed to start.

I went to /var/lib/mysql/ and deleted ib* files. I could start mysql then, but some of the tables were problematic: I could see their names in PhpMyAdmin, but when tried to run queries, mysql errored out that the table doesn't exist.

I assume ibdata1 is corrupted! I put innodb_force_recovery = 0 and tried it upto innodb_force_recovery = 6, none of them worked and I got the same error when I tried to start mysql.

I still have the whole /var/lib/mysql backup.

Any ideas why this happened and how I can recover the data and start mysql again?

Best Answer

What You make right - it is backup all files from /var/lib/mysql

all other steps - wrong

Your current error (in PHPMyAdmin with phantom tables) - because You have Your database folder with .frm files for table structure, and empty (new) ibdata.

What You can do now - revert all back (restore files in it original place) Then You can: - check error log file, for error message - start change innodb_force_recovery from 1 to 6

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 How can extract the table schema from just the .frm file?, 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

Best Answer

Related Solutions

Mysql – Recover a MySQL InnoDB from ibdata1 that won’t repair using innodb_force_recovery

Mysql – Recover MySQL database from data folder without ibdata1 from ibd files

MyISAM

InnoDB

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly

Related Question