MySQL – Recovering Data Lost in InnoDB

data lossMySQL

Today I was making some work with site and I get error what MySQL can't rename file (to make changes in BD). Next I tried to restart MySQL, and it failed to stop MySQL. Then I did go to /var/lib/mysql and all I see is: ib_logfile0+1, ibdata1, and site folder, with tables that I was changing (not tables themselves, just #sql-b61_25a8e.MYI).

Can I recover my MySQL data? I don't need all tables, because I have old backup of them. I just need that one table with all users (yea I was changing table with users) And yes, I did shutdown MySQL for some stupid reason.

Best Answer

  1. Keep a copy of MySQL datadir before any tries to restore the database. Especially ibdata1 and *.ibd files where InnoDB keeps all data.

  2. Start MySQL with innodb_purge_threads=0 and innodb_force_recovery=4 . If it doesn't start - try values 5 and 6.

  3. If MySQL starts take a dump of all databases with mysqldump. Then start fresh instance of MySQL and reload data back.

  4. If MySQL doesn't start you'd need to recover from a backup if you have. If not - it's still may be possible to extract records from ibdata1 (or *.ibd).

Related Question