Thursday, 28 May 2015 00:00

MySQL data recovery from InnoDB

Rate this item
(0 votes)

b2ap3_thumbnail_iStock_000021573240XSmall_20130909-064451_1.jpgMethod No 1
Create backups ibdata1,ib_logfile0 и ib_logfile1 before starting the data recovery.
Also create backups of your folder containing .frm files.

Database recovery from the existing backup.
First of all transfer all backups to another MySQL server, recover data in MySQL data directory.

Grant relevant rights and permissions and designate the owner of files of database files (usually mysql).
Determine the size of Innodb logfiles by running the ls -l command.
You will see the following:
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile1

/usr/sbin/mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6

If everything goes right you will see the following:
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
070625 11:59:36 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
070625 11:59:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQL

Then dump the database that appeared:
mysqldump -u root -p database > database.sql

If you get the following message, it means that the files of system journal Innodb are damaged:
Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLES

To solve the issue with storage of ib_logfile0 file you need to have a valid backup copy, that is why recover all files from the oldest backup copy. It is not a faultless solution but a good try.

Recover your data:
mysql -u root -p database < database.sql

Method No 2

First switch InnoDB to recovery mode and ignore all UPDATEs and INSERTs:

Add a line in /etc/my.cnf:
innodb_force_recovery = 2

Restart the database:
/usr/local/bin/mysqld_safe &

If MySQL does not restarts, increase the number of innodb_force_recovery up to 8.

Save all data in the temporary file alldb.sql :
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql

Turn on MySQLd:
mysqladmin -uUSERNAME -pPASSWORD shutdown

Delete database directory, but before that make sure that you delete what is required to be deleted.
For example:
rm -fdr /usr/local/var

Recreate database and install MySQL base tables:
mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var

Delete innodb_force_recovery from the file /etc/my.cnf and restart the database:
/usr/local/bin/mysqld_safe &

Import all data from the temporary file alldb.sql:
mysql -uroot --compress < /usr/alldb.sql

And finally, reapply privileges (because MySQL tables have been updated)
/usr/local/bin/mysqladmin -uroot flush-privileges
Launch MySQL in the recovery mode.
For some Unix systems you need to run su mysql.
Launch MySQL in the recovery mode, specify the size of logfile and innodb_force_recovery as a parameter.

Last modified on Thursday, 28 May 2015 16:18
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Get Help Now

Thank you for contacting us.
Your Private Investigator will call you shortly.