Summary: This blog discusses the ibdata1 file, its use, and instances that require recovering data from an ibdata1 file. It explains the most effective way to recover InnoDB data without the ibdata1 file. If it fails to recover the data, use Stellar Repair for MySQL software to repair the InnoDB database and recover its data. The software demo version is available that helps to preview all the recoverable data.
In MySQL, ibdata1 is a single system tablespace data file. The file is used to store data and indexes of all the tables of a MySQL database running on an InnoDB storage engine.
There may be instances when you will need to recover data from ibdata1 file, such as:
Now let’s discuss what you can do to salvage the data when the ibdata1 file gets deleted or corrupted.
You can try restoring backup to recover the ibdata1 file data. If you don’t have the backup, try to dump, drop, and recreate corrupted databases.
Note: In some cases, data corruption may happen due to the operating system causing corruption in its cache file. In that case, restarting your system might fix the corruption issue. If this doesn’t help, continue troubleshooting the steps below.
Unlike the MyISAM database, you can repair the InnoDB tables. However, InnoDB automatically starts crash recovery on startup to recover from an unexpected server exit. But, if you cannot start the InnoDB engine, force start InnoDB recovery. Doing so will force the InnoDB engine to start and prevent background operations from allowing you to dump the data.
The steps are listed as below:
innodb_force_recovery = 1
Caution! You can increment ‘innodb_force_recovery’ value from 1 till 6. However, setting the value to 4 or greater can corrupt the data file and lead to permanent data loss. Also, innodb_force_recovery set to a value greater than 3 doesn’t support dropping and creating a table. Learn more about innodb_force_recovery from here.
mysqldump –all-databases –add-drop-database –add-drop-table –routines > mysqldump-all.sql
Here, the databases will be exported to mysqldump-all.sql.
Note: If you cannot drop a database, try deleting it manually after stopping the MySQL server (mysqld).
If you were unable to drop the database in step 6 above, try to delete the affected db manually by running the commands:
rm -rf db_name
mysql < mysqldump-all.sql
This will restore the databases. See if the restored databases contain ibdata1 file and its data.
If important data is missing in the restored InnoDB database or the steps mentioned above fail to recover data, try using Stellar Repair for MySQL. It is a specialized MySQL recovery tool that repairs corrupt database running on the InnoDB storage engine. The repaired database contains the data folder containing the ibdata1 and other InnoDB data files. You can recover data from the recovered files while maintaining data integrity. Also, it helps recover data of MyISAM database engine.
Check out how the software works:
Do you want to recover data from ibdata1 file that is deleted or corrupted? This may prevent you from starting the InnoDB database engine. Forcing InnoDB recovery may help you dump and export the affected databases. You can then drop the databases and restart the server, which will recreate the ibdata1 file. Next, import the databases from the dump file back into your MySQL server and restore the database. If this doesn’t work or causes a data integrity issue, using Stellar Repair for MySQL may help.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.
Stellar Data Recovery has the right Windows Recovery tool for all your data recoveryRead More
Stellar Data Recovery for Mac program performs safe..
A comprehensive photo recovery software to restore photos, music & video files