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:
- Accidental deletion of ibdata1 file
- Data corruption in ibdata1 file due to server crash, hardware failure, ransomware attack, etc.
Now let’s discuss what you can do to salvage the data when the ibdata1 file gets deleted or corrupted.
Methods to Recover Data from ibdata1 File
Method 1 – Recover InnoDB Database Manually
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:
- Open the MySQL configuration file (my.cnf) on your server.
- Locate [mysqld] in the my.cnf file.
- Add the following line in [mysqld] section:
innodb_force_recovery = 1
- Try to restart your MySQL server (mysqld). If it does, continue with the next step. If MySQL service won’t start, repeat step 2 and increase the value of innodb_force_recovery until the restarts.
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.
- Once mysqld starts running in recovery mode, take a dump (backup) of the crashed databases and export all the databases:
mysqldump --all-databases --add-drop-database --add-drop-table --routines > mysqldump-all.sql
Here, the databases will be exported to mysqldump-all.sql.
- Start your mysql program and use the DROP DATABASE command to drop the corrupted database or databases.
Note: If you cannot drop a database, try deleting it manually after stopping the MySQL server (mysqld).
Stop mysqld.
If you were unable to drop the database in step 6 above, try to delete the affected db manually by running the commands:
cd /var/lib/mysql
rm -rf db_name
- Remove the InnoDB engine from recovery mode. To do so, comment out the following line in the [mysqld] section in your my.cnf file:
#innodb_force_recovery=4
- Save the changes that have been made so far in the MySQL configuration file and start the MySQL server.
- Run the following command to import all of the exported databases from the dump file created in step 5:
mysql < mysqldump-all.sql
This will restore the databases. See if the restored databases contain ibdata1 file and its data.
Method 2 – Use MySQL Recovery Tool
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.
Conclusion
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.
