Mysql – Trying to recover/import/transfer database from MySQL data dir

MySQLphpmyadmin

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused.

However at the time of doing the export I also saved the whole data directory located at mysql/data/ which has many .ibd and .frm filetypes, which seem to reflect the tables I used to have.

I have tried to copy the entire data directory and paste it into my new setup, but I am not sure if the versions of MySQL / phpMyAdmin are the same, how can I find the MySQL version from my old data directory? Possibly in one of those .frm / .ibd files?

When I do paste the entire data folder, some of the tables in the database are working like normal but some display the following error.

#1033 - Incorrect information in file: '.\db\a_table.frm'

Also it seems every table which shows this same error message is marked in use under Collation in the Structure view in phpMyAdmin

What can I do to fix this? What does it mean?

I am wondering if I re-create my old OS setup, it could work?
Is there a better way?

Best Answer

I managed to find a workaround for this but I am not very happy about it. I installed my previuos setup which was EasyPHP and copied my old MySQL *data* dir into the EasyPHP *MySQL* data directory.

This time around the database was able to read the .ibd / .frm files correctly.(even though these files did not change at all)

I then used phpMyAdmin to export the database(this time around all tables/data was stored in the sql file)

I then disabled EasyPHP enviroment and started up my new enviroment and used phpMyAdmin->import with the new sql file which loaded the data correctly.

Overall I think its pretty poor that MySQL cant simply output some type of notice stating that the .ini enviroment variables are not set corresponding to the .ibd / .frm files rather than just output incorrect information and leave the user in the dark with all sorts of angles to think about. After googling around it seems many people seem to believe that their data is corrupt when that's really not the case.

Related Question