Recovering MySQL databases when reinstalling Wamp
I am trying to restore old databases to a fresh Wamp installation. However, I didn't take a backup of the Mysql files before I did. Fortunately deleting did not delete all files, but in my data folder I have:
1) All databases (as folders)
2) auto.cnf
There are no files in the old folder ib*
. Is there any way to restore my databases?
I tried replacing the entire mysql5.6.17 folder ( there were no version changes between installs ), but Wamp stays 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 data folder, I can't see the old databases at all.
EDIT: Experimenting a bit, copying the database folders directly to a new data folder allowing me to get a list of all tables, but there is no actual data in the tables, and when trying to query it, MySQLWorkbench gives me an error:
Error Code: 1146. Table 'testdb.users' doesn't exist
EDIT 2: Found this in the error log file in the 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 this is an issue with native InnoDB mapping. Is there a way to manually resolve this?
source to share
If you created an INNODB database and did not make any other configuration changes to store the actual data in separate tablespaces, then all the actual data for those INNODB databases will be stored in a file \wamp\bin\mysql\mysql5.6.17\ibdata1
.
If you do not have this file from your old installation, your data will be lost.
There will also be information in the folder \wamp\bin\mysql\mysql5.6.17\data\DATA_BASE_NAME\
, one .frm file for each table that was in the database, but this is mostly just schema information, not actual data.
For future reference InnoDB Backup and Restore
source to share
GOLDEN RULE:
Always create a backup database (from phpmyadmin) before major changes. Better to spend 1 minute on this rather than getting into trouble.
How to copy a folder data
If you want to copy the database, then the INSTALLATION / mysql versions must be the same .. then rename the new installed folder data
to ie data_my_old
, so you can now put the backup folder data
(it should contain the file ibdata1
).
source to share
Depends on innodb_file_per_table parameters.
If it was OFF, all your tables were in ibdata1. You now have a brand new ibdata1 (since you see the errors "Cannot open table testdb / users from internal data dictionary", this means that InnoDB is starting a new new dictionary). This means that the data is now somewhere on disk. Recovery is quite difficult and requires a lot of tedious work. You need to take a disk image, scan it with stream_parser to find InnoDB pages, then rebuild the InnoDB dictionary, then find the index_id for each table, and if stream_parser found that index, it runs c_parser to fetch records from the index.
If innodb_file_per_table is enabled and you see .ibd files in the database directories, then recovery is much easier. First, you can try to reconnect individual ibd files using ibdconnect, or retrieve records from ibd files using stream_parser / c_parser.
The mentioned tools are part of TwinDB Data Recovery Toolkit
source to share