MySQL tables gone after deleting ibdata1
A couple of days ago I couldn't get mysql to run anymore(xampp) after some googling I found this post: XAMPP - MySQL shutdown unexpectedly
It said to remove the ibdata1 file which I did, mysql starts again but the tables of one database are gone, however I can still see the tables that contain values in the data folder(ibd and frm files) is there anyway that I can recover those tables?
Thanks.
Answer
The ibdata1 file is important, and normally you should not delete it unless you want to drop all your InnoDB data and start over with an empty MySQL instance.
Even if you used innodb_file_per_table=1 and all your tables are stored in .ibd files, the ibdata1 file still contains the data dictionary, which is basically like a global table of contents for InnoDB tablespaces. It's how InnoDB knows about what tables you have and which files they reside in. The ibdata1 file can also include important data in the rollback segment and change buffer, which will eventually be merged into your tables but that can take time.
If you have removed ibdata1, and you have been storing table data in file-per-table format, it is sometimes possible to recover, but it's a meticulous process. Here are a few references:
- A recovery trivia or how to recover from a lost ibdata1 file
- Connecting orphaned .ibd files
- How to recover an orphaned .ibd file with MySQL 5.6
It's simpler to restore your most recent backup and then use the binary log to play back changes made subsequent to your most recent backup.