Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

MySQL InnoDB data recovery


  • Please log in to reply
1 reply to this topic

#1 holgergoebel

holgergoebel

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:06:29 AM

Posted 23 October 2015 - 03:10 PM

I am to attempting to recover data from a schema that had both MyISAM and InnoDB tables.  I can recover the MyISAM tables without any problem, however I am running into difficulty trying to get the InnoDB tables back. 

 

Steps taken:

I have backup of the schema folder in the data directory containing the .frm .myd and .myi files for the MyISAM tables and .frm for the InnoDB table. I also have the log files and the ibdata1 file.  I managed to peek at the ibdata1 file with a hex editor and it clearly has references to my tables.  I am starting the mysqld process with both -innodb_force_recovery=4 and -innodb_force_recovery=6 and recieve the following:

 

[ERROR] Cannot find or open table 'table' from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database?

Or, the table contains indexes that this version of the engine doesn't support.

From what I have read I feel I have enough to make a proper recovery, however everything I have read so far has not helped.

 

Thank you in advance for any help.



BC AdBot (Login to Remove)

 


#2 leonramberg

leonramberg

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:06:29 AM

Posted 29 October 2015 - 07:15 PM

To fix this do the following please or in case it isn’t enough for your mysql database restoration, then I would advise you to learn helpful threads dedicated to solving MySQL issues

 

http://community.office365.com/en-us/f/172/p/266451/815406.aspx

https://social.msdn.microsoft.com/Forums/en-US/fb94219c-fbf8-455d-b912-5544be11f186/how-to-fix-my-sql-database?forum=sqldataaccess

https://www.repairtoolbox.com/mysqlrepair.html  Repair Toolbox for MySQL – in case you can’t get back mysql database, you may apply this one solution

 

Force InnoDB Recovery

  1. Stop mysqld and back up all files located in the /var/lib/mysql/ directory:
  2. # /etc/init.d/mysqld stop
  3. # mkdir /root/mysql_backup
  4. # cp -r /var/lib/mysql/* /root/mysql_backup/
  5. Add the option innodb_force_recovery to the [mysqld] section in /etc/my.cnf. This option will allow you to start mysqld and create a database dump.
  6. #/etc/my.cnf
  7. [mysqld]
  8. innodb_force_recovery = 4

NOTE: You can increase the option to five or six until you receive a proper dump.

  1. Start the mysqld service:
  2. # /etc/init.d/mysqld start
  3. Dump all databases:
  4. # mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/dumpall.sql

If the dump fails with an error that reads:

Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"`

... then you need to increase innodb_force_recovery and try to dump the databases again. If you are not able to dump the databases, try using method II ("Copy table content") or III ("Restore from the backup") below.

  1. Remove all files from /var/lib/mysql/ except the mysql folder:
  2. # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
  3. Remove the innodb_force_recovery option from the /etc/my.cnf file and restart mysqld:
  4. # /etc/init.d/mysqld restart
  5. Check the /var/log/mysqld.log for any errors.
  6. Restore the databases from the dump:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` < /root/dumpall.sql






1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users