Restoring MySQL Databases when reinstalling Wamp

Question

I am attempting to restore my old databases to a new Wamp installation. However, I did not take a backup of Mysql files before I did this. Thankfully the uninstallation has not deleted all the files, but in my data folder I have :

1) All the databases (As folders)

2) auto.cnf

There are no ib* files in the old folder. Is it still possible to restore my databases somehow?

I have tried to replace the entire mysql5.6.17 folder (there has been no change of version between the installations), but Wamp remains orange and never turns green.

If I replace only the data folder, the MySQL service starts, I can see the databases, but not the tables.

If I change the path in my.ini to point to the old wamp's data folder, I cannot see the old databases at all.

EDIT : Was experimenting a bit, copying the database folders directly into the new Wamp's data folder allows me to get a list of all the tables properly, but there is no actual data in the tables, and trying to query it using MySQLWorkbench gives me the error :

Error Code: 1146. Table 'testdb.users' doesn't exist

EDIT 2 : Found this in the error log file in my data folder :

2014-08-03 06:24:46 25164 [Warning] InnoDB: Cannot open table testdb/users from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

As I thought, its a problem with InnoDB's own mapping. Is there a way to resolve this manually?

Answer 1

If you created an INNODB database, and made no other config changes to store the actual data in seperate table spaces then all the actual data for that INNODB databases will have been stored in the \wamp\bin\mysql\mysql5.6.17\ibdata1 file.

If you do not have that file from the old install then your data is lost.

There will also be info in a \wamp\bin\mysql\mysql5.6.17\data\DATA_BASE_NAME\ folder, one .frm file for each table that was in the database, but this is basically just schema information and not actual data.

For future reference, InnoDB Backup and Recovery

Answer 2

GOLDEN RULE:

Always backup database (from phpmyadmin) before major changes.. better to spend 1 minute for that, than going into trouble..

How to Copy data folder

If you want to copy the database, then INSTALLATION/mysql versions should be same.. then rename newly installed data folder to i.e. data_my_old,so now there you can place the backuped data folder (it should contain ibdata1 file).

Answer 3

Depends on innodb_file_per_table setting.

If it was OFF then all your tables were in ibdata1. Now you have quite new ibdata1 (since you see errors "Cannot open table testdb/users from the internal data dictionary", that means InnoDB runs fresh new dictionary). That means that now the data is somewhere on the disk. The recovery is quite difficult and requires a lot of tedious work. You need to take an image of the disk, scan it with stream_parser to find InnoDB pages, then recover the InnoDB dictionary, then find index_id of every table and if the stream_parser has found this index run c_parser to extract records from the index.

If innodb_file_per_table was ON and you do see .ibd files in the databases directories then recovery is much easier. First, you may try to reconnect individual ibd files with ibdconnect or extract records from the ibd files with stream_parser/c_parser.

The mentioned tools are part of TwinDB data recovery toolkit

Answer 4

i have just done a google search and found this....

Go to C:\wamp\bin\mysql\mysql5.5.24\data

Find a file called "mysql-bin.index"

Rename that file to "mysql-bin.index-corrupt"

Restart the server and it will work fine, all your database will be where you left them.